Cloud-Based DAX and MDX Formatter

It’s been away for a while, but now it’s back – the site where you can enter your MDX or DAX code and have it formatted for you! Here’s the link:

http://formatmdx.azurewebsites.net/

Thanks to Nick Medveditskov, the man responsible for it.

SSAS on Windows Azure Virtual Machines

You may have already seen the announcement about Windows Azure Virtual Machines today; what isn’t immediately clear (thanks to Teo Lachev for the link) is that Analysis Services 2012 Multidimensional and Reporting Services are installed on the new SQL Server images. For more details, see:
http://msdn.microsoft.com/en-us/library/jj992719.aspx

SSAS 2012 Tabular is also supported but not initially installed.

Applying a Function to Every Cell in a Table in Data Explorer

Now that the PASS Business Analytics Conference is over, I can get back to playing around with Data Explorer and blogging about it. I’ve been working on a fun demo that I’ll try to blog about later this week, but in the course of creating this demo I came across a technique that I didn’t end up using but which I thought deserved a post on its own: how to apply a function to every cell in a table, rather than just every cell in a column.

For example, let’s imagine that you have a table that looks like this:

…and you want to add one to every single cell in the table, so you get:

It’s possible in the UI by creating lots of custom columns and then deleting the original columns, for sure, but I found a more elegant solution. Here’s the full DE code:

let

    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],

    FunctionToApply = (x) =>; x + 1,

    GetColumnNames = Table.ColumnNames(Source),

    TransformList = List.Transform(GetColumnNames, each {_ , FunctionToApply}),

    Output = Table.TransformColumns(Source, TransformList)

in

    Output

Here’s what each step does:

• Source: gets the data from the Excel table named Input

  

• FunctionToApply: defines a new function that takes a single parameter, x, and returns the value x+1. See this post for more details on using function in Data Explorer.
• GetColumnNames: returns a list object which contains the names of all of the columns in the table returned in the Source step.

  

• TransformList: this is the interesting step! It creates a new list based on GetColumnNames, but whereas GetColumnNames contains just one record per item in the list (the column name), this returns a list of lists, each with two items: the column name and a reference to the function FunctionToApply. It looks like this in the UI:

  If you click on the first of the list links shown (and this isn’t part of the process, I’m only doing this to show what’s there) you see the following:

  

• Output: the list of lists created in the previous step can now be passed to the Table.TransformColumns() function to apply the function FunctionToApply() to every cell in every column in the table.

  

I’m sure this is going to be useful to me at some point in the future… You can download the sample workbook here.

PASS Business Analytics Conference Summary

The PASS Business Analytics Conference in Chicago finished yesterday, and because I was there and because I did a fair amount of cheerleading for it (see here and here for example) I wanted to post a few thoughts on how it went.

I’ll be honest and say that I had a few worries before the event. Would anyone want to go? Would the sessions be a repeat of what get on the BI tracks at the PASS Summit and hundreds of other SQL Server conferences? In fact, everything went really, really well. Some of the sessions were quite sparsely attended (though this had nothing to do with the quality of the content – some of the best sessions didn’t get much of an audience) but overall there was a very respectable number of people (1200ish?). I had as many people in my session on OData as I’d get at any other large conference, and it was standing room only in at least one of Marco’s sessions. I also rather liked the fact that it was smaller than the Summit – it made it much easier to meet all the people I wanted to meet. If it carries on for a few years it could easily attract a much larger number of people.

Regarding the content I was particularly pleased because a lot of the topics I’d asked for turned up on the schedule. In fact one thing that struck me (and a few other people said the same thing to me as well) was that this was the first conference I’d been to in a long time where there were sessions that I really wanted to see in every time slot. My favourite session of the whole conference was Marc Smith on NodeXL; anyone that reads my blog knows I’ve been a big fan of NodeXL for a long time, but I learned a lot from this session because it concentrated on the basics of social network analysis rather than the tool itself. This was a prime example of the kind of topic that you simply wouldn’t get at a regular SQL Server conference – it was a business analytics session. Even the more technical presentations, such as the one on HPC Services for Excel, was outside the usual boundaries of SQL Server BI. Incidentally, I must get round to playing with HPC Services for Excel – you could use it to parallelise some DAX calculations, or even to batch process large numbers of PowerPivot models on desktop machines overnight…

So, in summary, the conference was a big success and I had a great time. I’ll definitely be going back next year. And did I mention that I got to meet Steven Levitt of Freakonomics fame?

GeoFlow Public Preview Available

First big news from the PASS BA Conference: the public preview for GeoFlow is now available. You can download it here:
http://www.microsoft.com/en-us/download/details.aspx?id=38395

Here are the official announcements with all the details:
http://blogs.technet.com/b/dataplatforminsider/archive/2013/04/11/day-2-pass-business-analytics-conference-new-3d-mapping-analytics-tool-for-excel.aspx
http://blogs.office.com/b/microsoft-excel/archive/2013/04/11/dallas-utilities-electricity-seasonal-use-simulation-with-geoflow-preview-and-powerview.aspx

GeoFlow is an addin for Excel 2013 that allows you to visualise your data on a 3D map, to zoom in and explore that data, and record ‘tours’ of this data. It’s a lot of fun! As a taster, here’s a screenshot of a visualisation showing English secondary schools exam results data (average A-Level point score per pupil) broken down by school gender of entry:

UPDATE: one other thing I have to mention is that when this was announced in the keynote at the PASS BA Conference this morning, Amir Netz did an absolutely astounding demo showing GeoFlow’s touch-based capabilities running on a massive Perceptive Pixel screen (I think it was this one: http://www.perceptivepixel.com/products/82-lcd-multi-touch-display). It was possibly the most impressive demo I’ve seen of any Microsoft BI product. Anyway, I got to play on it myself later and it was as cool as it looked. If you’ve got $80000 burning a hole in your pocket then you could do worse than invest in one of these babies.

SSAS Multidimensional and MDX Training in Australia

This is just a quick post to mention that this summer I’ll be running two courses in Australia, one in Sydney on July 23-26, and one in Melbourne on July 29-August 1, in association with the folks at Wardy IT. The course content will cover all things Analysis Services Multidimensional: there’ll be one day on SSAS cube design, two days on MDX queries and calculations, and one day on SSAS performance tuning; you can see the full course outline here. It’s aimed at intermediate-to-advanced SSAS developers who want to deepen their knowledge and learn best practices. I’m really looking forward to it, and I hope to see you there!

LightSwitch and Self-Service BI

Visual Studio LightSwitch has been on my list of Things To Check Out When I Have Time for a while now; my upcoming session on the uses of OData feeds for BI at the PASS BA Conference (which will be a lot more exciting than it sounds – lots of cool demos – please come!) has forced me to sit down and take a proper look at it. I have to say I’ve been very impressed with it. It makes it very, very easy for people with limited coding skills like me to create data-driven line-of-business applications, the kind that are traditionally built with Access. Check out Beth Massi’s excellent series of blog posts for a good introduction to how it works.

How does LightSwitch relate to self-service BI though? The key thing here is that aside from its application-building functionality, LightSwitch 2012 automatically publishes all the data you pull into it as OData feeds; it also allows you to create parameterisable queries on that data, which are also automatically published as OData. Moreover, you can publish a LightSwitch app that does only this – it has no UI, it just acts as an OData service.

This is important for self-service BI in two ways:

• First of all, when you’re a developer building an app and need to provide some kind of reporting functionality, letting your end users connect direct to the underlying database can cause all kinds of problems. For example, if you have application level security, this will be bypassed if all reporting is done from the underlying database; it makes much more sense for the reporting data to come from the app itself, and LightSwitch of course does this out of the box with its OData feeds. I came across a great post by Paul van Bladel the other day that sums up these arguments much better than I ever could, so I suggest you check it out.
• Secondly, as a BI Pro setting up a self-service BI environment, you have to solve the problem of managing the supply of data to your end users. For example, you have a PowerPivot user that needs sales data aggregated to the day level, but only for the most recent week, plus a few other dimension tables to with it, but who can’t write the necessary SQL themselves. You could write the SQL for them but once that SQL is embedded in PowerPivot it becomes very difficult to maintain – you would want to keep as much of the complexity out of PowerPivot as possible.  You could set up something in the source database – maybe a series of views – that acts as a data supply layer for your end users. But what if you don’t have sufficient permissions on the source database to go in and create the objects you need? What if your source data isn’t actually in a database, but consists of other data feeds (not very likely today, I concede, but it might be in the future)? What if you’re leaving the project and need to set up a data supply layer that can be administered by some only-slightly-more-technical-than-the-rest power user? LightSwitch has an important role to play here too I think: it makes it very extremely easy to create feeds for specific reporting scenarios, and to apply security to those feeds, without any specialist database, .NET coding or SQL knowledge.

These are just thoughts at this stage – as I said, I’m going to do some demos of this in my session at the PASS BA Conference, and I’ll turn these demos into blog posts after that. I haven’t used LightSwitch as a data provisioning layer in the real world, and if I ever do I’m sure that will spur me into writing about it too. In the meantime, I’d be interested in hearing your feedback on this…