Chris Webb's BI Blog

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

Introduction to MDX for PowerPivot Users, Part 5: MDX Queries

with 17 comments

In part 4 of this series (sorry for the long wait since then!) I finished off looking at what you can do with named sets. Now, before I go on to more important topics like Excel cube functions and calculated members I’d like to take a high-level look at what you can do with MDX queries running against PowerPivot – high level, because there’s much more to MDX queries than can be covered in a single post and, as I explain below, you probably won’t want to do this very often.

So why would I need to write whole queries against a PowerPivot model?

This is a very good question, given that in my opinion 99% of the time you can achieve what you want when building Excel reports using either PivotTables (either with or without named sets) or Excel cube functions. Having said that, the post I wrote a few years ago about binding a table in Excel to an MDX query has been one of the most popular I’ve ever written, so maybe I’m wrong about how frequently people need to do this…

I’d say that you would probably only want to write your own queries when you needed complete control over the MDX and didn’t mind that it made linking the query up to filters or slicers very difficult – for example, if you wanted a list of unpaid invoices, or a list of customers that met some specific criteria, in a dashboard.

Also, when you run MDX queries in Excel you’re going to use an Excel table to show the results rather than a PivotTable. This is actually the format you need to use to pass data to other Excel-based tools like like Excel Data Mining Addins (as well as PowerPivot), so writing your own MDX queries might actually save you having to convert to formulas, as Kasper does here, or cutting/pasting in cases like this.

Why use MDX instead of DAX?

From PowerPivot V2, PowerPivot models can be queried in either MDX or the DAX query language (if you want to learn about DAX queries take a look at the posts I wrote on this topic last year, starting here), and if you’ve already learned a lot of DAX for PowerPivot you’re probably going to be more comfortable using DAX queries. However, I know there are a lot of old SSAS-fans out there doing work with PowerPivot who prefer MDX, and there are still a few things that MDX can do that PowerPivot can’t, so choosing MDX over DAX is a legitimate choice. Examples would be when you want to pivot your resultset and put something other than measures on columns, or show a calculated member on rows, and I show how to do both of these things below.

How do I display the results of an MDX query in Excel?

As I said, when you display the results of an MDX query in Excel you’ll need to use an Excel table to do so. I blogged about a few ways to do this here but there’s actually a better way now: using DAX Studio. DAX Studio is a free Excel addin for people who want to write DAX queries against a PowerPivot model, but it can run MDX queries too. Unfortunately it doesn’t display any MDX metadata for you to use – only DAX metadata – but it’s still a much more convenient way of running MDX queries than doing a drillthrough and then editing the query property of a table.

The DAX Studio documentation gives you a good overview of how to use the tool and I won’t repeat that here, but to prove it does work here’s a screenshot of an MDX query run against a PowerPivot model:

image

OK, so get on with it and tell me how to write an MDX query…

The basic MDX query is quite simple. Books online has all the details:

http://msdn.microsoft.com/en-us/library/ms146002.aspx
http://msdn.microsoft.com/en-us/library/ms144785.aspx

…but really all you need to know is this:

Each MDX query needs a SELECT clause. Inside the SELECT clause you need to define one or two axes, either just a columns axis or a columns axis or a rows axis, and the way you define what appears on an axis is using a set, an object we’ve seen a lot of in the last few posts in this series. Each MDX query also needs a FROM clause, with the name of the cube that is to be queried; for PowerPivot the name of the ‘cube’ is always [Model].

Here’s an example of a simple MDX query on a PowerPivot model built on Adventure Works DW that returns a measure on columns and three years on rows:

SELECT
{[Measures].[Sum of SalesAmount]}
ON COLUMNS,
{[DimDate].[CalendarYear].&[2005]
, [DimDate].[CalendarYear].&[2006]
, [DimDate].[CalendarYear].&[2007]}
ON ROWS
FROM [Model]

image

Everything you do on columns, you can do on rows, and vice versa, so:

SELECT 
{[DimDate].[CalendarYear].&[2005]
, [DimDate].[CalendarYear].&[2006]
, [DimDate].[CalendarYear].&[2007]}  
ON COLUMNS,
{[Measures].[Sum of SalesAmount]}
ON ROWS
FROM [Model]

Returns this:

image

Using a set of tuples on rows and/or columns gives a crosstabbed effect:

SELECT 
{[Measures].[Sum of SalesAmount]}
*
{[DimProductCategory].[EnglishProductCategoryName].[EnglishProductCategoryName].&[Bikes]
, [DimProductCategory].[EnglishProductCategoryName].[EnglishProductCategoryName].&[Clothing]}
ON COLUMNS,
{[DimDate].[CalendarYear].&[2005]
, [DimDate].[CalendarYear].&[2006]
, [DimDate].[CalendarYear].&[2007]} *
{[DimProduct].[Color].&[Black]
, [DimProduct].[Color].&[Red]}
ON ROWS
FROM [Model]

image

After the FROM clause, you can add a WHERE clause to slice the resultset. Do not confuse the MDX WHERE clause with the SQL WHERE clause: it does something similar but it doesn’t directly affect what appears on rows or columns, it filters the values returned inside the query. For example:

SELECT 
{[Measures].[Sum of SalesAmount]}
ON COLUMNS,
{[DimDate].[CalendarYear].&[2005]
, [DimDate].[CalendarYear].&[2006]
, [DimDate].[CalendarYear].&[2007]}  
ON ROWS
FROM [Model]
WHERE(
[DimProductCategory].[EnglishProductCategoryName].[EnglishProductCategoryName].&[Bikes]
, [DimProduct].[Color].&[Black])

…returns sales for Black Bikes for the years 2005 to 2007:

image

Notice that the Colour Black and the Product Category Bikes don’t appear anywhere on rows or columns, but the values that are shown are for Black Bikes nonetheless.

The WITH clause

You can define your own calculated members (which I’ll talk about in a future post) and named sets inside a query if you add a WITH clause before your SELECT clause. Here’s an example of this:

WITH
SET [MY YEARS] AS
{[DimDate].[CalendarYear].&[2005]
, [DimDate].[CalendarYear].&[2006]
, [DimDate].[CalendarYear].&[2007]}
MEMBER [DimDate].[CalendarYear].[Total 2005-7] AS
AGGREGATE([MY YEARS])
MEMBER [Measures].[Percent of Total] AS
([Measures].[Sum of SalesAmount])
/
([Measures].[Sum of SalesAmount]
, [DimDate].[CalendarYear].[Total 2005-7])
, FORMAT_STRING=’PERCENT’
SELECT 
{[Measures].[Sum of SalesAmount]
,[Measures].[Percent of Total]}
ON COLUMNS,
{[MY YEARS], [DimDate].[CalendarYear].[Total 2005-7]}
ON ROWS
FROM [Model]

image

Here I’ve defined a named set called [MY YEARS] which I’ve then used to define what goes on the rows axis, and two calculated members, [Total 2005-7] which returns the subtotal of the years 2005 to 2007, and a new measure [Percent of Total] that shows the percentage that each row makes up of this subtotal. Incidentally, even though DAX can do this kind of subtotalling, it’s only in MDX that you can define any calculation you want on any axis in your query.

Flattened Rowsets

You might be wondering, looking at the examples above, why the column headers are all in human-unfriendly MDX and why the [Percent of Total] measure hasn’t had any formatting applied. You will also notice in this query how the name of the All Member on the [CalendarYear] hierarchy doesn’t get returned, and you get a blank row name instead:

SELECT 
{[Measures].[Sum of SalesAmount]}
ON COLUMNS,
{[DimDate].[CalendarYear].[All]
,[DimDate].[CalendarYear].&[2005]}
ON ROWS
FROM [Model]

image

This is because, when you run queries that get bound to an Excel table they are returned as flattened rowsets and not cellsets (which is how most SSAS client tools and SQL Server Management Studio returns MDX queries). Basically, this means your nice, multidimensional resultset gets squashed into something tabular – and when this happens, a lot of useful stuff gets lost along the way. Here’s the official documentation on how flattened rowsets are generated:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms716948(v=vs.85).aspx

This is a pain, but there’s no way around it unless you want to write your own code to render a cellset in Excel unfortunately.

Conclusion

Writing your own MDX queries against a PowerPivot model isn’t exactly something you’ll need to do every day, but it’s a useful addition to your PowerPivot toolbox and I wanted to mention it in this series for the sake of completeness. In my next post I’ll be taking a look at MDX calculated members.

Written by Chris Webb

December 18, 2012 at 3:41 pm

Posted in MDX, PowerPivot

17 Responses

Subscribe to comments with RSS.

  1. [...] part 5, I take a look at running MDX queries against a PowerPivot [...]

  2. Hi Chris,
    Nice post as usual. After playing extensively with MDX in Excel, I find that writing a UDF that retuns a Cellset is the most versatile and efficient way.

    Mana

    December 19, 2012 at 6:56 am

  3. Hello Chris,

    thanks for this really helpful series of posts. There still is nothing comparable to find in the community. I meanwhile use MDX-statements to make the strucutre of my pivot tables more dynamic. But I have one question I can’t answer on my own. Maybe you can help me with that:
    I created some pivots, which show the last 12 >rolling months< and some more columns, which e.g. sum up these rolling month, compare them with previouse years and so on. Therefore I wrote 12 MDX-statements (one for each month) and one statement, which decides, which of the monthly statements should be used. Here the example statement:

    IIF(
    [Measures].[MMaxMonthMDX]=201401,
    [G2014_01],
    IIF(
    [Measures].[MMaxMonthMDX]=201402,
    [G2014_02],
    IIF(
    [Measures].[MMaxMonthMDX]=201403,
    [G2014_03],
    IIF(
    [Measures].[MMaxMonthMDX]=201404,
    [G2014_04],
    IIF(
    [Measures].[MMaxMonthMDX]=201405,
    [G2014_05],
    IIF(
    [Measures].[MMaxMonthMDX]=201406,
    [G2014_06],
    IIF(
    [Measures].[MMaxMonthMDX]=201407,
    [G2014_07],
    IIF(
    [Measures].[MMaxMonthMDX]=201408,
    [G2014_08],
    IIF(
    [Measures].[MMaxMonthMDX]=201409,
    [G2014_09],
    IIF(
    [Measures].[MMaxMonthMDX]=201410,
    [G2014_10],
    IIF(
    [Measures].[MMaxMonthMDX]=201411,
    [G2014_11],
    IIF(
    [Measures].[MMaxMonthMDX]=201412,
    [G2014_12],
    [G2014_12]
    )
    )
    )
    )
    )
    )
    )
    )
    )
    )
    )
    )

    Unfortunately I have to rewrite this statement minimum once per year. Is there any possibility to parametrise that MDX-statement, so that I can use the example-statement all the time without the need of adjustment?! I hope I presented my problem clearly. Thanks in advance and greats from Germany.

    Lars

    Lars

    September 25, 2014 at 12:42 pm

    • Hi Lars, it should be possible to simplify this a lot. Does [MMaxMonthMDX] return the last month in your Date table, or the last month that has data?

      Chris Webb

      September 25, 2014 at 1:06 pm

      • Hi Chris,

        I am new to MDX and so I expected that reaction :) [MMaxMonthMDX] is a measure that returns a choosen month in a special slicer (based on a disconnected table in my powerpivot model), so that the user of the excel-file is able to chance the pivot strucutre on his own.

        Lars

        September 25, 2014 at 1:11 pm

      • So I think there are a number of ways of doing this. Maybe the easiest for you would be the StrToMember() or StrToSet() function (see http://msdn.microsoft.com/en-us/library/ms146022.aspx). With a bit of string manipulation you could derive the name of the last period from the value returned by NMaxMonthMDX. Other alternatives would be something like Tail(Existing [Date].[Month].[Month].Members) but it’s hard to say what the exact expression would be without knowing more about your model.

        Chris Webb

        September 25, 2014 at 1:24 pm

      • Thanks for that really fast answer. StrToSet() is one thing I already tried, but I wasn’t successful trying to concatenate a string and the value of my measure. I tried something like this: STRTOSET( ‘{[G’ + [Measures].[MMaxMonthMDX] + ‘]}’ ), because the name of the set is always ‘G’, than 4 digits for the year and 2 digits for the month. But that idea seemed to be not correct, because I always get a data type conflict :(

        Lars

        September 25, 2014 at 1:39 pm

  4. Can you try STRTOSET( “{[G” + cstr([Measures].[MMaxMonthMDX]) + “]}”

    Chris Webb

    September 25, 2014 at 1:58 pm

    • Hi Chris,

      I could solve the problem this way:
      STRTOSET(
      “{([G” + left([Measures].[MMaxMonthMDX],4) +”_” + right([Measures].[MMaxMonthMDX],2) + “])}”
      ).

      It is interesting, that funktions like LEFT(), CStr() etc. are not listed as functions for Named Sets in Excel. You helped me a lot :) BIS THANKS.

      Greets and have a nice day,
      Lars

      Lars

      September 25, 2014 at 2:05 pm

      • Glad to hear you have it working. Left() and Cstr() are functions that return strings; what’s happening here is that you’re building a string up and then casting it to a set.

        Chris Webb

        September 25, 2014 at 2:47 pm

  5. Hi, Chris!

    Many thanks for your excellent and much useful posts!

    I’m working on a model which uses mdx based set and faced with a difficult issue. I have a parent-child hierarchy which second level members have a few members of first level and have a duplicated names therefore (with different keys). The question is how to make a query which would return a consolidated set of all second level set no matter of their keys. In other words, I want to sum values of all identical names of a second level hierarchy. But I don’t know how many of them exist.

    Mer

    October 17, 2014 at 11:37 am

  6. a query which would return a consolidated set of all second level unique names

    Sorry for misprint.

    Mer

    October 17, 2014 at 11:41 am

    • Do you want the MDX uniquenames, or do you want all of the distinct names of the members? I think it would be easier to do this in SQL…

      Chris Webb

      October 18, 2014 at 9:00 pm

      • Hi, Chris,
        I want to write something like: TOPCOUNT (SET, 10, [MEASURES.SALES AMOUNT]) where SET would be all distinct names from the second level of parent-chield hierarchy, no matter what is their paths or parents. It’s a created hierarchy from Power Pivot. I have to use hierarchy in case of switching sets by disconnected sliser.

        Mer

        October 20, 2014 at 5:45 pm

      • If this is a parent/child hierarchy built in Power Pivot, you should be able to use just [Dimension Name].[Hierarchy Name].[Level 2 Name].MEMBERS

        Chris Webb

        October 20, 2014 at 9:14 pm

      • But in that case I have duplicated names. For instance, I have 2 brands at first level hierarchy and some of the second level categories could have two different brands, so they get different keys and duplicates as a result.

        Mer

        October 22, 2014 at 4:35 am

      • This isn’t something you can solve in MDX, unfortunately, this is a design problem. I assume you have another hierarchy somewhere without the duplicated brands? If so, you’ll have to use that; if not, you’ll need to create one. It will be possible to use MDX to find the brands from this other hierarchy that match up with the selection on your original hierarchy – the EXISTING function will probably be useful here.

        Chris Webb

        October 22, 2014 at 8:16 am


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 3,302 other followers

%d bloggers like this: