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.
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
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.
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.
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.
The Member Value is the one I can use to sort the months explicitly
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.
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.
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.
