Almost every tool we learned has some sort of “Hello World!” tutorial lesson.
So here comes the “Hello World!” lesson in MDX.
Putting “Hello World!” directly on the X axis doesn’t work
You would think this query would work, by putting “Hello World!” directly on the X axis.
Unfortunately it doesn’t.
[Measures].[x] is a perfectly legitimate and unique name for a tuple
The X axis expects a tuple/set expression. Or put it simply that it expects something like this:
[A Dimension].[A Hierarchy].[A Member]
To save some typing, I am going straight to the one special dimension in any SSAS cube, that is, the [Measures] dimension. This special dimension also has only one hierarchy, which happens to have the same name, [Measures]. This save me time to type it twice.
To further save myself typing, I am going to use one letter x for the member.
[Measures].[x] is a perfectly legitimate and unique name for a tuple. It’s a very rare chance that any cube designer would name any of the measures [x].
Do not use any existing member
If you try to use a measure that has already existed in the cube to represent “Hello World!”, you will get an error.
MDX for arithmetic calculation
If you are like me who writes so much SQL code every day that I never use the Calculator software on my PC for arithmetic calculation. SELECT 9999999 + 1 is a perfectly fine query and will return 10000000.
A bit more typing than in TSQL, but this works in MDX.
Even better, if you want to format it, go ahead use the format_string.
We can create a member on any dimension
[Measures] is not the only choice for a perfect “Hello World!” lesson. As a matter of fact, you can use any dimension in the cube, but it requires a bit more typing.
Let’s pick the Date dimension.
But don’t forget to pick a hierarchy
It didn’t work, because unlike the special [Measures] dimension, the [Date] has many hierarchies, including a dozen or so attribute hierarchies and a few user-defined hierarchies.
Let’s pick the user-defined hierarchy [Calendar].
If you want to summarize the “Hello World!” tutorial lesson in MDX, go right ahead!
For my other MDX blogs, visit http://bisherryli.wordpress.com/.