Introduction to MDX for PowerPivot Users, Part 1

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:
  • 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]

In Part 2 I’ll introduce the concept of sets and how they can be used.

8 responses

  1. Chris,

    One useful application of the OLAP Pivot Table Extensions add-in is the ability to enhance the PowerPivot model through functions that are present in Excel and MDX but not in DAX. I wrote an example of this, when calculating internal rate of return or quartiles in PowerPivot:

    This would be particularly useful when using cube functions, as one could use MDX to add a calculation to the model and then retrieve DAX and MDX values side by side;

  2. Pingback: Introduction to MDX for PowerPivot Users, Part 2: Basic Sets « Chris Webb's BI Blog

  3. Pingback: Introduction to MDX for PowerPivot Users, Part 3: The Members() and Crossjoin() functions « Chris Webb's BI Blog

  4. Pingback: Returning Selected Items in an Excel Slicer Using MDX in PowerPivot and SSAS « Chris Webb's BI Blog

  5. Pingback: Introduction to MDX for PowerPivot Users, Part 1 | Chris Webb’s BI Blog | CompkSoft

  6. Pingback: Is there a way to display in pivot table a subtotal of a power pivot measure only and to skip empty columns?

  7. Pingback: Handling Missing Members In The CubeSet() Function With Power Pivot – Chris Webb's BI Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: