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:
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]
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:
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]
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:
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]
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]
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.
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.
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
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?
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.
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.
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 🙁
Can you try STRTOSET( “{[G” + cstr([Measures].[MMaxMonthMDX]) + “]}”
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
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.
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.
a query which would return a consolidated set of all second level unique names
Sorry for misprint.
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…
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.
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
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.
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.
Could you provide an example please?
Should I create a second hierarchy without brands?
The number and uniqueness of the members on a hierarchy depends on the column(s) you use for the KeyColumns property of the attribute on the dimension. You need to have an attribute on the dimension where you have a key that uniquely identifies each brand, and use that attribute’s hierarchy (no need to create a user hierarchy) in your MDX.
I’m not sure that I understand your response clearly. =(
I’ve created a topic that describes my problem here – http://www.mrexcel.com/forum/power-bi/818620-dynamic-charting-issue.html
I would very appreciate it if you look at it. Many thanks for trying to help!
I’ve replied there.
I’ve clarified it a bit more. Got an error message. =(
Hi Chris! Any news considering this issue? Do you any other ways that could do the same thing?
I think I found something but it depends on answer to next question:
Is it possible to write something like this:
IIF([Measures].[CategorySelectionForecast]=”Categories”,[Table].[Classification].[Categories].MEMBERS,DO NOTHING)
Is there any way or function that could “DO NOTHING” here? =) If yes I could just write a few named sets for each type of classification and add them all to rows.
You can return an empty set, for example
IIF([Measures].[CategorySelectionForecast]=”Categories”,[Table].[Classification].[Categories].MEMBERS,{})
Are you thinking of a dual axis chart maybe? That could work…
Already tried an empty set. It doesn’t work. Only one thing that work is to add another column to table with ” ” value (space). And than add this column to every hierarchy for every type of classification.
But in this case I have a Blank rows equal to number of hierarchies… It does not satisfied… If there could be an any way to remove this blank rows from pivot…
IIF([Measures].[CategorySelectionForecast]=”Categories”,[Table].[Classification].[Categories].MEMBERS,[Table].[Classification].[BLANK].MEMBERS)
That’s what I mean.
Have you tried using a text filter in the PivotChart to remove the empty names?
I can’t use a filter because it’s MDX named set. It isn’t allowed. Also if I add a slicer, it’s also meaningless because blank rows correspond to every member in table. If I turn it off I’ll turn off all values.
P.S. Just tried, can’t even turn it off in a slicer.
Hmm, and if you put the filter inside the named set expression then you end up with an empty set and the same problem as before. I strongly suspect that there isn’t an MDX solution to this problem.
Maybe is it possible to write a DAX query for that purpose? I just need to get a rows controlled by slicer (choosing columns from model) in a table. It doesn’t matter what instrument is used for it.
You could, but you’ll need some VBA if you want to use a dynamic DAX query driven by your slicer selections:
https://cwebbbi.wordpress.com/2013/02/15/dynamic-dax-query-tables-in-excel-2013/
What is appropriate DAX query code for this situation? I tried it a bit but didn’t succeed yet.
I’ve tried this one:
evaluate(
SWITCH(Measure_That_Indentifies_Slicer_Value,
“Brands”,
summarize(Table,’Classification'[Brands]),”sales”,Measure_For_Sales),
“Categories”,
summarize(Table,’Classification'[Categories]),”sales”,Measure_For_Sales)))
First I received this error:
Query (6, 1) The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
Then I deleted >>>”sales”,Measure_For_Sales<<< part of summarize just being curious and received next error:
Query (1, 1) The expression specified in the EVALUATE statement is not a valid table expression.
What do you think?
Maybe there should be a VBA which would substitute DAX queries according to slicer value? I don’t know VBA at all. Could you write something like this?
Yes, that’s exactly what this post https://cwebbbi.wordpress.com/2013/02/15/dynamic-dax-query-tables-in-excel-2013/ is about
Thanks Chris! Fortunately I was able to solve it much easier. The solution is so simple that even unexpected. First I created a new hierarchies for every level of that previous consolidated hierarchy. Then I made several mdx queries for every hierarchy and PivotTables for every queries in a hidden sheet. Then I made a multiple IF statement in a common cell which link to all these PivotTables depending on slicer’s value identified by cube functions… And then added there a bit of conditional formatting. Hey presto! It’s not even dynamic rows, it is dynamic PivotTable!
Great, I’m glad you managed to solve the problem!
Hi Chris,
I would like to display the difference between prior year, current year AND difference between the two years. I try to use the formula below
SELECT
{[Measures].[Sum of AMT]}
ON COLUMNS,
{[Table2].[Year].&[2014]
, [Table2].[Year].&[2015]
, ([Table2].[Year].&[2014] – [Table2].[Year].&[2015]}
ON ROWS
FROM tABLE2
But I got an error..
The goal is to have a report that looks as follow;
CUSTOMER| PRIOR YEAR | CURRENT YEAR | DIFFERENCE (CY-PY)
** I try to used DAX formula “PreviousYear” but it return a blank
** Because I couldn’t make the dax formula to work . I decide to use the ” Create Set Based On Column Function” to get the difference between the years
Hi Chris,
Is there a way to pass parameters or filter values to MDX query in Excel for a data pull from SSAS ?
I get this error when I select “parameter” in the “table import wizard” in excel power pivot.
“Parameters are not supported by the wizard. Make sure none of the parameters are checked.”
I know how to pass parameter to power query. But here i am trying to pull data from SSAS in Excel Data Model using MDX.
I have searched all over the internet. There is no answer.
Kindly advise.
Thanks & Regards,
Bhuvanesh Babu