Creating TOP X queries with SSAS 2008
Once you start working with BI, one of the most popular requests is “Can you create a TOP 5 Products report”? So let’s see how SSAS 2008 can help us with that. There are different ways to create TOP X queries/reports with SSAS.
The way I prefer is creating a new Named Set. So let’s create a TOP 5 Products (I am using the Adventure Works DW 2008 SE sample database). On the Calculations tab of the Cube Designer window click on New Named Set button.
Name it [Top 5 Products] and, for the expression use
TopCount (
[Product].[Product].[Product].Members, /*Set Expression*/
5, /*Count*/
[Measures].[Reseller Sales Amount] /*Numeric Expression*/)
As you can see, I am using TopCount MDX function. The TopCount function sorts, in descending order, the tuples from the Set Epxression based on the evaluation of the values of the numeric expression.
The next configuration is type, for this example, select Dynamic. Dynamic named Sets is a new feature in SSAS 2008. Dynamic sets are calculated before each query in the context of the WHERE clause and subselect statements. If you select static, the set is evaluated only once.
After deploying the changes and processing the cube, let’s see the results.
1st Test: No where clause
SELECT
[Measures].[Reseller Sales Amount] ON COLUMNS,
[Top 5 Products] ON ROWS
FROM [Adventure Works]
These are the top selling products.
| Reseller Sales Amount | |
| Mountain-200 Black, 48 | $1,634,647.94 |
| Mountain-200 Black, 38 | $1,471,078.72 |
| Road-350-W Yellow, 48 | $1,380,253.88 |
| Touring-1000 Blue, 60 | $1,370,784.22 |
| Mountain-200 Black, 42 | $1,360,828.02 |
2nd Test: Use a where clause
SELECT
[Measures].[Reseller Sales Amount] ON COLUMNS,
[Top 5 Products] ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2003]
As you can see now the Top 5 products are different from the previous query. These are the top 5 products for 2003:
| Reseller Sales Amount | |
| Mountain-200 Black, 48 | $894,207.97 |
| Touring-1000 Blue, 60 | $759,596.65 |
| Road-350-W Yellow, 48 | $744,988.37 |
| Mountain-200 Black, 42 | $722,137.65 |
| Mountain-200 Black, 38 | $681,120.23 |
To know more about named sets and new features of SSAS 2008, you can join us in one of our SSAS classes.
6234 Implementing and Maintaining Microsoft SQL Server2008 Analysis Services
Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically each day to your feed reader.
