Archive

Archive for December, 2012

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

December 3, 2012 Leave a comment

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: ,

MDX #22 – What to use for explicit sorting on dimensions

December 3, 2012 Leave a comment

I often think that I am lucky to work in a profession that I enjoy very much. It makes everything I do a fun thing to do. Majority of the fun comes from the fact that I have my hands in almost every aspect of building a data warehouse. From business analysis, to ETL, to star schema/cube design and development to reporting, and to customer service. My blogs most of the time reflect the different roles I play in my profession.

In this example, I am going from a reporting requirement to the MDX queries that allow me to meet the requirement.

Note: all the examples are demonstrated in the sample Adventure Works database (star schema or the multi-dimensional cube) from Microsoft.

Very often we need to produce reports that compare monthly data. Providing two Month reporting filters will be the first step.

My last two blogs dealt with some aspects of this kind of reporting requirements.

MDX #21 – Days in a month

MDX #20 – Last Data Date   

Reporting Requirements

In this example I need to accomplish the followings:

1) Get all the months that have data

2) Sort the months in descending order

3) Default the first filter to the most recent month that has data

4) Default the second filter to the previous month that has data

image

In this blog, I’ll try to accomplish 1) and 2).

To get all the Months, let’s put the Date dimension on Y axis

To make sure that the months I get have Sales data, let’s put the [Sales Amount] measure on X axis.

Note: If you’ve been using the Query editor in SSRS, you might have noticed that the only “Dimension” that is allowed on the X axis is the [Measures] dimension. So you cannot place the Date dimension on the X axis. This can create a bit confusion first, but it really doesn’t prevent me from writing MDX queries to my hearts desire. 

image

Use the key word non empty to remove the months that do not have data

Note: Both the keyword NON EMPTY and the NONEMPTY(set_expression1, set_expression2) function will evaluate the cells for "empty" condition. The first only works on the axis. The latter can be used in any parts of the MDX queries and covers much more ground because it can take any two sets as parameters (although the second one is optional). Both should work in this example.

 

image

So far, I have met the first requirement 1) Get all the months that have data.

Explicitly sort the months in descending order

In order to meet the second requirement, I need to explicitly sort the months in descending order. I know I need some values I can find in the cube about the month for the sorting. Before I can figure out that, I need some help on all the things I can find about the month. 

They are the member properties that I need to find out.

image

The Member Value is the one I can use to sort the months explicitly

image

All looks good, except the first member_key which returned a NULL value. It turned out that I cannot really use the member_key function here because the [Month Name] attribute has a composite key. Open the Date Dimension Editor, we can see the KeyColumns has a collection of Year and Month Number. The property Key0 and Key1 will give us the correct key values.

image

But the keys cannot provide help to sort the months. The Member Value is the one I can use to sort the months explicitly.

Use attribute [Month Name]  rather than user hierarchy [Calendar]

Note: Since I am using the attribute [Month Name] for sorting, I’ll need to use it also on the Y axis. Otherwise using the [Calendar] user hierarchy will not work.

 image

It took us a few tries along the way, but the final query is simple enough.

To summarize, here are what we’ve learned

1) Many member properties, including ID, key, caption, name, member value, can be potential candidates for explicit sorting.

2) I used to use member key for explicit sorting on dimensions. But it will not work on the attributes that have composite keys. To get to the composite keys, we need to use the Key0, Key1, etc.

3) Member Value can be a good choice if we need to sort by numerics. Sometimes, Member Name can also be a good choice if we need to sort alphabetically.

%d bloggers like this: