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.
Parent-child hierarchy is sorted from top to bottom
However, this result does not seem to be sorted in any noticeable order.
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).
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.
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.
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.
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.
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.
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.)
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.
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.
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.