Introduction to MDX for PowerPivot Users, Part 1

Reposted from Chris Webb's blog with the author's permission.

This is a series of posts I've wanted to write for a long time. The more I use PowerPivot, the more I realise how many more cool things you can do when building your Excel reports if you know a bit of MDX. Of course it seems a bit strange to say this, because (at least if you hear Marco and Alberto present at a conference!) MDX is a bit passé and DAX is the native language of PowerPivot, SSAS Tabular and, well the future. But Excel speaks MDX and Excel will continue to speak MDX for a long time to come, so it's MDX that you need to know if you want to get the most out of PowerPivot.

So, before I start, when is it useful to know MDX with PowerPivot? Here's the functionality that it's relevant for:

• The Excel cube functions, especially but by no means limited to, the CubeSet() function.
• The Create Set Based on Rows/Columns options under the Fields, Items and Sets button on the PivotTable Options tab on the ribbon
• When binding the results of an MDX query to a table in Excel, as described here
• If you ever wanted to write MDX calculations using OLAPPivotTableExtensions, though I'm struggling to think of scenarios where you'd want to do this

I'll come back to the practical uses of MDX in future posts; the next task is to understand how the objects in a PowerPivot model map onto the multidimensional objects that MDX understands. From now on I'll be using the MDX terminology so it might be useful to refer back to the list below if you get confused!

• A PowerPivot model is seen as a single cube in MDX; you can think of a cube and a PowerPivot model as being the thing that holds all the data, the thing you're querying. The cube that a PowerPivot model is exposed as is called [Model].
• In PowerPivot a model is made up of multiple tables; in MDX a cube is made up of multiple dimensions. Each table in a PowerPivot model becomes a dimension in MDX.
• In PowerPivot a table is made up of multiple columns, each of which can be dragged onto the rows and columns of a PivotTable. Each of these columns becomes a hierarchy in MDX. Confusingly, hierarchies in PowerPivot also become hierarchies in MDX. Basically, anything that you can put on rows, columns, a filter or in a slicer is a hierarchy in MDX.
• Each distinct value in a column becomes a member on a hierarchy in MDX. For example, the value 'Bikes' from a column called EnglishProductCategoryName on a table called DimProductCategory becomes an MDX member with the name:
  [DimProductCategory].[EnglishProductCategoryName].&[Bikes]
• Each measure in a PowerPivot model becomes a member on a dimension called [Measures] (which only has one, invisible hierarchy) in MDX. For example a PowerPivot model called [Sum of SalesAmount] becomes an MDX member with the following name:
  [Measures].[Sum of SalesAmount]

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com/ .