It just works–but why?

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

One of the things that often confuses people when they learn MDX is the way that certain queries seem to work without them understanding why. This is because MDX tries to be helpful – and I would say too helpful – in correcting your mistakes for you by applying functions to objects, and casting objects to other objects, without you knowing to avoid raising an error. This might seem like a useful thing to do but in the long run I think it stops people getting a proper grasp of how MDX actually works, and reinforces the habit of ‘hack the statement until it works’ that some people have.

Let’s look at a couple of examples. First of all, in a SELECT statement, the way we define what appears on rows or columns is to use an expression that returns a set object. Now look at the following query:

SELECT
[Measures].[Internet Sales Amount]
ON COLUMNS,
[Date].[Calendar].[Calendar Year].&[2003]
ON ROWS
FROM [Adventure Works]

It executes just fine, but what we’ve put on rows and columns is in each case just a member. SSAS is expecting a set but is casting each member to a set with that one member in it, ie

SELECT
{[Measures].[Internet Sales Amount]}
ON COLUMNS,
{[Date].[Calendar].[Calendar Year].&[2003]}
ON ROWS
FROM [Adventure Works]

Now the problem with the former is that as soon as you want to put more than one member on an axis, eg

SELECT
[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]
ON COLUMNS,
[Date].[Calendar].[Calendar Year].&[2003], [Date].[Calendar].[Calendar Year].&[2004]
ON ROWS
FROM [Adventure Works]

The query errors with one of MDX’s famously unhelpful error messages:

Parser: The statement dialect could not be resolved due to ambiguity.

In this case it now can’t tell whether this is a SQL query or an MDX query (and yes, you can query SSAS with SQL) and so it fails; you now need to add those braces that should have been there in the first place:

SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]}
ON COLUMNS,
{[Date].[Calendar].[Calendar Year].&[2003], [Date].[Calendar].[Calendar Year].&[2004]}
ON ROWS
FROM [Adventure Works]

There are numerous other examples. This query returns all Calendar Years on rows:

SELECT
{[Measures].[Internet Sales Amount]}
ON COLUMNS,
[Date].[Calendar].[Calendar Year]
ON ROWS
FROM [Adventure Works]

Even though, in the row axis definition, you’re supplying a level object: [Date].[Calendar].[Calendar Year] . What SSAS is doing here is secretly applying a function to the level object you’ve given it to get the set object that it actually needs, and that function is the .MEMBERS function. So what’s actually happening is this:

SELECT
{[Measures].[Internet Sales Amount]}
ON COLUMNS,
[Date].[Calendar].[Calendar Year].MEMBERS
ON ROWS
FROM [Adventure Works]

However, when you supply a hierarchy object on rows instead:

SELECT
{[Measures].[Internet Sales Amount]}
ON COLUMNS,
[Date].[Calendar]
ON ROWS
FROM [Adventure Works]

You only get the All Member returned, so we’re not getting the .MEMBERS function being applied here (if we were, we’d get every member from every level of the Calendar hierarchy). What we’re actually getting back is the default member – but from which function? It’s actually the .CURRENTMEMBER function, as far I can see, so what’s actually happening is this:

SELECT
{[Measures].[Internet Sales Amount]}
ON COLUMNS,
{[Date].[Calendar].CURRENTMEMBER}
ON ROWS
FROM [Adventure Works]

To prove this, if you run this query with the hierarchy object being used in a calculated measure:

WITH
MEMBER MEASURES.TEST AS
MEMBERTOSTR([Date].[Calendar])
SELECT
{[Measures].[Internet Sales Amount], MEASURES.TEST}
ON COLUMNS,
[Date].[Calendar].MEMBERS
ON ROWS
FROM [Adventure Works]

…you can see the output of the calculated measure varies by row:

Whereas if the function being applied here was, say, the .DEFAULTMEMBER function, we’d always get the default member being returned here.

I could go on, but the real point to make here is that relying on SSAS to fix your mistakes for you is a bad thing. Whenever I write MDX I always try to be as ‘correct’ as possible to help me (and whoever needs to maintain the code after me) understand exactly what’s happening as easily as possible; it’s as important as commenting the code properly in my opinion. And if you’re learning MDX, be curious: if something works but you don’t understand why, take some time to understand it; just think what problems you’ll have when something doesn’t work and you don’t understand why!

---

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 .

---