Archive

Archive for June, 2012

MDX #8 – Why the employee parent-child hierarchy is not sorted

June 13, 2012 Leave a comment

Dimension attributes are always pre-sorted

In my previous post, MDX #7 – Get only 10 elements from a MDX query, I mentioned that dimension attributes are always pre-sorted.

This is obvious with the following query.

clip_image001

Parent-child hierarchy is sorted from top to bottom

However, this result does not seem to be sorted in any noticeable order.

clip_image002

Until you realize that the Employees hierarchy in the Employee dimension is a parent-child hierarchy (you can follow this link to see how a typical parent-child hierarchy is constructed:  http://technet.microsoft.com/en-us/library/ms167115.aspx).

clip_image003

The sorting is done from top to bottom. So by default, Jae B. Pak will appear before David R. Campbell, because manager Amy E. Alberts appears before Stephen Y. Jiang.

Now we know that the sorting order is working as expected. But you still want to sort the result alphabetically by employee names.

Use MemberValue function to sort by dimension

The Order function is normally used to sort results by measures. For sorting by dimensions, we can use the Order function and the MemberValue function (or the Member_name) (you can follow this link to see a very good comparison between MemberValue and Member_name and member_key: http://msdn.microsoft.com/en-us/library/ms146082.aspx). Use it as the 2nd parameter for the Order function. 

clip_image004

Change the 3rd parameter from ASC to BASC to break the parent-child hierarchy

The above result is the same as before. Nothing changes. That is because we forgot to tell the MDX engine to break the parent-child hierarchy, and stop sorting from top to bottom.

Changing the 3rd parameter from ASC to BASC will do the trick.

clip_image005

Now the result is sorted alphabetically by employee names, but many of them have no sales.

Let’s add a Non Empty command to the Y axis.

clip_image006

Now we know that a parent-child hierarchy is indeed also pre-sorted, and we also know at least one way now to sort it differently.

MDX #7 – Get only 10 elements from a MDX query

June 3, 2012 Leave a comment

No matter how much we were told that we are better off leaving TSQL behind when we are learning MDX, we are still trying to draw parallels between these two query languages.

One command every SQL developer is very familiar with is the TOP 100 (or any number actually) command. Years ago, a senior developer whom I respected very much told me never to write a query against an unfamiliar table without the TOP 100 command. Tables in that data warehouse can easily go over 10 million records.

So what’s the parallel of the TOP 100 command in MDX? Sometimes I wish we can only get one, and only choice in MDX. But the truth is that MDX designers have given us more than one choice. In this case even more than 2 choices.

Two commonly used MDX functions to achieve TOP 100

  • Head() function
  • TopCount() function

You can follow this link to see more details on these 2 functions: MDX Function Reference

These two functions are very straightforward (if you don’t cluster them with other functions).

Here are a few examples.

The Head() function is the closest to the TOP command

The Customer dimension can be a big dimension. Getting only the top 10 customers back is a good idea if you are just testing out your script. My script has no depth to it, and its only purpose is to show how easy it is to use the Head() function. 

clip_image001

As a SQL developer, we are trained to always notice the order of the data. It’s easy to see that it’s sorted by the customer name. Dimension attributes are always pre-sorted (if you go to the Customer attribute in the Customer dimension, you’ll notice the OrderBy property. OrderBy Name is the default setting.)

clip_image002

Combine Order() function with the Head() function

Instead of the default sorting order by customer name, I can easily sort the customers by their Internet Sales Amount first, and then return only the top 10 customers who have the most Internet Sales Amount.

clip_image003

Notice that the Order() function is very different from the Order By clause in TSQL The Order() function is really trying to re-order the customers by their Internet Sales Amount, rather than using their default sort order of customer name.

The TopCount() function can achieve the same as Head + Order

If you like the above results using the combination of Head + Order function, why not just use the the shorter version. 

clip_image004

With the TopCount() function, you can still get the top 10 customers who have the most Internet Sales Amount.

If drawing parallels between TSQL and MDX gives you more comfort in learning MDX, why not.

Categories: MDX Challenges Tags:
%d bloggers like this: