Home > MDX Challenges > MDX #7 – Get only 10 elements from a MDX query

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

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:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: