Chris Webb's BI Blog

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

It just works–but why?

with 9 comments

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:

image

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!

Written by Chris Webb

June 30, 2011 at 10:36 am

Posted in MDX

9 Responses

Subscribe to comments with RSS.

  1. Spot on Chris. Just one of many many problems and oddities found daily in MDX code.
    Next step, don’t spam NONEMPTY everywhere throughout the query where it isn’t needed..!

    Alex Whittles

    June 30, 2011 at 1:15 pm

  2. Good point, Chris. I support several SSRS developers with MDX help and I’m guilty of using those shortcuts at times. I usually have to explain why they work. What’s worse is when I look at my old code that uses shortcuts and have to fill in the blanks to figure out why I wrote it that way.

    David Clem

    June 30, 2011 at 1:29 pm

  3. Hi Chis,
    A so common problem that nobody has wrote before !!!. Besides usual data quality tests I often add full join querys between SSAS and data source for convincing the User about consistency and demostrate that values are correct !!
    Thanks

    Leandro Tubia

    July 1, 2011 at 8:50 am

  4. Hi Chirs ,

    Can you clarify why this below MDX is not working . Surprisingly it is working only when the name of the calculated member name is any one of the names like lastdate and Firstdate

    with member firstdate
    as [Measures].[Internet Sales Amount]
    select firstdate on columns ,
    [Date].[Calendar].[Calendar Year].members ON ROWS
    FROM [Adventure Works]

    Error Message:

    Executing the query …
    Query (1, 13) Parser: The syntax for ‘firstdate’ is incorrect.

    Please clarify me how mdx is treating the above statement ..

    Rajesh

    July 1, 2011 at 10:39 am

    • Is this on R2? Then I think it’s because there’s a DAX FirstDate function: http://technet.microsoft.com/en-us/library/ee634806.aspx

      You should be explicitly defining this calc on the measures dimension too. Here’s a version of the query that works:

      with member measures.[firstdate]
      as [Measures].[Internet Sales Amount]
      select measures.[firstdate] on columns ,
      [Date].[Calendar].[Calendar Year].members ON ROWS
      FROM [Adventure Works]

      Chris Webb

      July 1, 2011 at 3:15 pm

      • Although as per your post, you would of course add {} around measures.[firstdate] :-)

        Alex Whittles

        July 1, 2011 at 3:18 pm

  5. [...] my last post I made the point that it’s a bit too easy to write and MDX query that works, even if you don’t [...]

  6. […] I explained in this blog post, when SSAS sees the unique name of a level it sticks the .MEMBERS function on the end and this […]

  7. […] I explained in this blog post, when SSAS sees the unique name of a level it sticks the .MEMBERS function on the end and this […]


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,146 other followers

%d bloggers like this: