Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Introduction to MDX for PowerPivot Users, Part 1

with 5 comments

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]

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

Written by Chris Webb

October 7, 2012 at 11:47 pm

Posted in MDX, PowerPivot, Tabular

5 Responses

Subscribe to comments with RSS.

  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: http://javierguillen.wordpress.com/2011/09/13/quartile-percentile-and-median-in-powerpivot-dax/

    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;

    javierguillen

    October 10, 2012 at 2:21 am

  2. Cool post Chris. at least from what our customers are saying… MDX is here for the long run :)

    Navi

    October 10, 2012 at 5:35 pm

  3. [...] the first post in this series I looked at how the objects in a PowerPivot model mapped onto MDX objects; now, [...]

  4. [...] name is [DimDate].[EnglishDayNameOfWeek]. Remember that in PowerPivot, as I said in the first post in this series, a column in a table becomes a hierarchy in MDX and a hierarchy in PowerPivot also [...]

  5. [...] model has become the MDX hierarchy with the unique name [DimDate].[CalendarYear]; please read this post for some background on how PowerPivot objects map to MDX [...]


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,868 other followers

%d bloggers like this: