Gautham KAMATH

BI Developer specializing in SSAS and MDX

Always use {} and ()

leave a comment »

When SSAS 2005 was released, MDX has a very nice change (at least it looked nice that time). The change was that it was no longer necessary to be very strict about i.e curly bracing {} or bracketing () the MDX syntax. This leniency sounded fun at that time but now after years relying in the intrinsic conversion between members, tuples and sets, it does not seem all that good now. Let me explain.

Consider the following simple query

     SELECT {[Measures].[Internet Sales Amount]} ON 0,
     {[Customer].[Customer Geography].[City].&[Berkshire]&[ENG]
     * [Product].[Product Categories].[Category].Members
     * [Date].[Calendar].[Calendar Year].[CY 2008]} ON 1
     FROM [Adventure Works]

The results are good.

But if we swap a couple of members ([Date] and [Product]) in the ROWS axis, it bangs.

     SELECT {[Measures].[Internet Sales Amount]} ON 0,
     {[Customer].[Customer Geography].[City].&[Berkshire]&[ENG]
     * [Date].[Calendar].[Calendar Year].[CY 2008]
     * [Product].[Product Categories].[Category].Members} ON 1
     FROM [Adventure Works]

The error reads…

Executing the query …
Query (2, 2) The * function expects a tuple set expression for the 1 argument. A string or numeric expression was used.
Execution complete

There is so much internal conversion going on between members, tuples, and sets that interpretation and construction of MDX statements have become more complex.
The row and column axes require sets. CrossJoin statement also require sets and neither of the above queries properly declares it.
The lesson: Always declare your sets with {} and tuples with ().

So if the above query is written as

     SELECT {[Measures].[Internet Sales Amount]} ON 0,
     {[Customer].[Customer Geography].[City].&[Berkshire]&[ENG]}
     * {[Date].[Calendar].[Calendar Year].[CY 2008]}
     * {[Product].[Product Categories].[Category].Members} ON 1
     FROM [Adventure Works]

…everyone is happy

Please note that by contrast the WHERE clause is not as lenient as the rest of the query. A single hierarchy in the WHERE clause without round brackets is acceptable as the member will be explicitly converted to a tuple. Modify it to reference a second hierarchy and add round brackets, it will not like it as it expects curly braces {}.

Advertisements

Written by Gautham KAMATH

03/11/2011 at 18:36

Posted in MDX

Tagged with , , ,

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

%d bloggers like this: