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.