Home > MDX Challenges > MDX #23 – “Hello World!” Lesson in MDX

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 https://bisherryli.wordpress.com/.

Categories: MDX Challenges Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

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: