Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

TopCounts With Ties In MDX

leave a comment »

Pretty much everyone that knows MDX knows the TopCount() function, which is used to find the top n items in a set – it can be used for finding your top 10 products, your top 20 salespeople, and so on. However most people don’t consider it’s biggest drawback: it always returns n items, and doesn’t take ties into account.

Consider the following query on Adventure Works, which returns the top 9 dates by Internet Order Count:

SELECT {[Measures].[Internet Order Count]} ON 0,
TOPCOUNT(
[Date].[Date].[Date].MEMBERS
, 9
,[Measures].[Internet Order Count])
ON 1
FROM [Adventure Works]

image

Now look at this query, which does the same thing but returns the top 10 dates:

SELECT {[Measures].[Internet Order Count]} ON 0,
TOPCOUNT(
[Date].[Date].[Date].MEMBERS
, 10
,[Measures].[Internet Order Count])
ON 1
FROM [Adventure Works]

image

Notice how June 11 2004 and June 17 2004 both have the same Internet Order Count of 86, but the latter date isn’t included in the first query. From this you can see that it’s important, when you’re doing a top n query, not to always return n items but to take tied values into account.

Luckily there’s a way of doing this. If you can download and install the dll from the Analysis Services Stored Procedure Project, you can use the TopCountWithTies() function that it provides, like so:

SELECT {[Measures].[Internet Order Count]} ON 0,
ASSP.TOPCOUNTWITHTIES(
[Date].[Date].[Date].MEMBERS
, 9
,[Measures].[Internet Order Count])
ON 1
FROM [Adventure Works]

image

Unfortunately, installing third-party dlls is not always allowed in a production environment, and SSAS 2012 Tabular doesn’t support dlls at all. However there is a way of getting the same result in pure MDX. Here’s an example:

WITH
SET TOP9 AS
TOPCOUNT(
[Date].[Date].[Date].MEMBERS
, 9
,[Measures].[Internet Order Count])
MEMBER MEASURES.TIEDRANK AS
RANK(
[Date].[Date].CURRENTMEMBER
, TOP9
, [Measures].[Internet Order Count])
SET TOP9WITHTIES AS
ORDER(
FILTER(
[Date].[Date].[Date].MEMBERS
, MEASURES.TIEDRANK>0 AND MEASURES.TIEDRANK<=9)
, MEASURES.TIEDRANK
, BASC)

SELECT {[Measures].[Internet Order Count], MEASURES.TIEDRANK} ON 0,
TOP9WITHTIES
ON 1
FROM [Adventure Works]

image

What I’m doing here is first using the TopCount() function to find the set of the top 9 dates (this step isn’t strictly necessary, but it has a significant positive impact on performance), and then using the Rank() function with the third parameter to find the tied rank. The Rank() function should find the position of a member in a set, but when the third parameter for it is specified it doesn’t match members based on the members themselves but on the values of the tuple specified in the third parameter. Hence, even when I have  a date that doesn’t appear in the set TOP9, the Rank() function can still return a value for it if that date has a value for Internet Order Count that does appear in that set.

Written by Chris Webb

February 9, 2013 at 11:05 am

Posted in MDX

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,302 other followers

%d bloggers like this: