SSAS clients and TopCount
Posted by thomasivarssonmalmo on November 23, 2008
Last week I met an end user that struggled with building a simple TopCount report in ProClarity. At the first look I thought it would be a simple problem that the client should handle with standard functionality, but that was not true. The end user requested a report with the top ten symptoms and all the related detailed symptoms to each top 10 item.
We can easily build the same scenario with the Adventure Works cube. Give me the top 10 customers in 2004 according to Internet Sales Amount and show me all the products they have bought. In ProClarity you would place customers and products on the rows and Internet Sales Amount and the Calendar Year 2004 on columns. You then expand the customers and products dimensions to their leaf levels and put a TopCount 10 filter on that. I will start with the top ten customers for 2004.
You set the filter like this.
The result is like the next picture.
If you add the product dimension leaf members to the right of the customers you will get an unexpected result.
You will get another set of customers and one product per customer. This is not what my end user requested. In the next picture you will see the requested result on the Adventure works cube. You will only see part of the result.
The MDX that ProClarity generates will reveal what happens.
SELECT {[Measures].[Internet Sales Amount] } ON COLUMNS ,
{ TOPCOUNT( { { { DESCENDANTS( [Customer].[Customer Geography].[All Customers], [Customer].[Customer Geography].[Customer] ) } *
{ DESCENDANTS( [Product].[Product Categories].[All Products], [Product].[Product Categories].[Product] ) } } }, 10,
( [Date].[Calendar].[Calendar Year].&[2004], [Measures].[Internet Sales Amount] ) ) } ON ROWS
{ DESCENDANTS( [Product].[Product Categories].[All Products], [Product].[Product Categories].[Product] ) } } }, 10,
( [Date].[Calendar].[Calendar Year].&[2004], [Measures].[Internet Sales Amount] ) ) } ON ROWS
FROM [Adventure Works]
Where ([Date].[Calendar].[Calendar Year].&[2004] )
ProClarity crossJoins customers and products first and does the topcount on that result.
How can you solve this problem? Here is how you do it in and MDX statement.
WITH SET [TopTenCustomer2004Sales] As
TopCount(Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Customer]),10,
([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2004]))
Select {[Measures].[Internet Sales Amount]} On Columns,
NON EMPTY CrossJoin([TopTenCustomer2004Sales],Descendants([Product].[Product Categories],[Product].[Product Categories].[Product])) On Rows
From [Adventure Works]
Where([Date].[Calendar].[Calendar Year].&[2004]);
TopCount(Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Customer]),10,
([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2004]))
Select {[Measures].[Internet Sales Amount]} On Columns,
NON EMPTY CrossJoin([TopTenCustomer2004Sales],Descendants([Product].[Product Categories],[Product].[Product Categories].[Product])) On Rows
From [Adventure Works]
Where([Date].[Calendar].[Calendar Year].&[2004]);
I create the TopCount set first and later do a crossjoin of this set and the product dimension leaf members. It will also work if you copy the With Set statement into a ProClarity named set.
What I assumed was a simple task was more complicated and I am not happy about, that this simple analysis requirement, is not supported in the client.
Thomas.I Microsoft BI & Analytics 
Leave a comment