Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Named Sets and TopCount, some thoughts of how this work

Posted by thomasivarssonmalmo on June 21, 2008

Very often questions about named sets and the TopCount version of that are posted on the SSAS2005 newsgroup.
 
This blog post is written so I will not have to write the same answer over and over and even an experienced MDX developer can sometimes get confused about how this works.
 
You can see MDX Selects like this and a developer that is not sure about what is getting returned.
 
WITH SET TenBestProducts AS
TopCount([Product].[Product Categories].[Product].Members,10,
[Measures].[Internet Sales Amount])
Select {[Customer].[Customer Geography].[Country]} On Columns,
TenBestProducts On Rows
From [Adventure Works]
 
In this Select you actually ask to the top 10 best selling products for all countries and all times. All dimensions that are not mentioned in TopCount part will point to their all level. How can point to a specific year and a specific country? Let us try France and the calendar year 2004.
 
WITH SET TenBestProducts AS
TopCount([Product].[Product Categories].[Product].Members,10,
([Measures].[Internet Sales Amount],[Date].[Calendar].[Calendar Year].&[2004],[Customer].[Customer Geography].[Country].&[France]))
Select {[Customer].[Customer Geography].[Country]} On Columns,
TenBestProducts On Rows
From [Adventure Works]
Where([Measures].[Internet Sales Amount]) ;
 
You change the measure part of TopCount into what is technically called a tuple or cube coordinate. Here is the point where end users and developer gets confused.
The best selling product for France in 2004 is Mountain-200 Silver, 46 with the value of $74,239.68 but in the grid you will see France, the correct top selling product and the Internet Sales Amount of $102,079.56. The reason that this result gets returned is that the named set, made with TopCount, is independent of what you put in the slicer. The named sets shows the ten best selling products in France in 2004, on rows,  but the values in the cells for France points to  the All Date member.
 
If you would like to show the sales (Internet Sales Amount) for the year 2004 in the cells, you will have to put that in the slicer like this. Remember that the slicer will have no impact on the product members on rows, only the cell values that are returned.
 
WITH SET TenBestProducts AS
TopCount([Product].[Product Categories].[Product].Members,10,
([Measures].[Internet Sales Amount],[Date].[Calendar].[Calendar Year].&[2004],[Customer].[Customer Geography].[Country].&[France]))
Select {[Customer].[Customer Geography].[Country]} On Columns,
TenBestProducts On Rows
From [Adventure Works]
Where([Measures].[Internet Sales Amount],[Date].[Calendar].[Calendar Year].&[2004]) ;
 
There is another way to check your TopCount results with the Order function and validate each country. Be aware of that the Order function can be a performance killer.
 
Select {[Measures].[Internet Sales Amount]} On columns,
NON EMPTY ORDER([Product].[Product Categories].[Product],[Measures].[Internet Sales Amount],BDESC) On Rows
From [Adventure Works]
Where ([Customer].[Country].&[France],[Date].[Calendar Year].&[2004]) 
 
 
The Rank function is also related to the way of doing TopCount analysis with MDX. TopCount is a simplification of doing Rank and Order in MDX, if I am not uncorrect.
 
WITH
SET OrderedProducts As
ORDER([Product].[Product Categories].[Product].Members,[Measures].[Internet Sales Amount],BDESC)
MEMBER [Measures].[ProductRank] AS
RANK([Product].[Product Categories].CurrentMember, OrderedProducts)
Select {[Measures].[ProductRank],[Measures].[Internet Sales Amount]} On columns,
ORDER([Product].[Product Categories].[Product].members,[ProductRank],BASC) On Rows
From [Adventure Works]
Where ([Customer].[Country].&[France],[Date].[Calendar Year].&[2004]); 
 
Finally, if you would like to remove empty products at the end(with null in the cells) you can use this select instead. I have added the Filter function for this.
 
WITH
SET OrderedProducts As
ORDER([Product].[Product Categories].[Product].Members,[Measures].[Internet Sales Amount],BDESC)
MEMBER [Measures].[ProductRank] AS
RANK([Product].[Product Categories].CurrentMember, OrderedProducts)
Select {[Measures].[ProductRank],[Measures].[Internet Sales Amount]} On columns,
ORDER(FILTER([Product].[Product Categories].[Product].members,[Measures].[Internet Sales Amount] > 0),[ProductRank],BASC) On Rows
From [Adventure Works]
Where ([Customer].[Country].&[France],[Date].[Calendar Year].&[2004]);   
 
I recommend to play around with these examples in Management Studio and see the effects of changes in the named set and the slicer. The book "Fast Track to MDX" has a dedicated chapter about this problem in AS2000.
Advertisements

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

 
%d bloggers like this: