Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Sherry Li's BI Corner

Always wanting to publish novels, but having the fortune to work in the data warehouse corner of technology, Sherry Li started to write the mysteries of the Microsoft Business Intelligence. She writes everything from T-SQL to MDX, ETL to Expressions to Scripting, Reporting to Cubes. You can find her writings at bisherryli.wordpress.com.

MDX #23 – “Hello World!” Lesson in MDX

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.

image

[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].

image

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.

 

image

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.

 

image

Even better, if you want to format it, go ahead use the format_string.

image 

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.

 

image  

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].

 

image 

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/.


Comments

Leave a comment on the original post [bisherryli.com, opens in a new window]

Loading comments...