Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Analysis Services Top N Plus All Other Rollup


By:   |   Read Comments (1)   |   Related Tips: > Analysis Services Development

Problem

I recently used the SSAS TopCount feature to get the top 10 list of products within my products cube.  How can I create a dynamic single rollup for the rest of the values, i.e. an All Other values, so that my total sales number is the true total and not just the top 10? 

Solution 

This question came out of question that was posed to me recently. The powerful topcount and bottomcount and toppercent and bottompercent features were covered in my recent tip, http://www.mssqltips.com/sqlservertip/3034/sql-server-analysis-services-ssas-2012-top-and-bottom-functions/. Certainly if you are wanting just the top 10 or bottom 10 items in particular dimension, those functions work very well. However, you often will be asked to provide not only the top 10 values, but also all the other values rolled up as a single line. In essence, the data users still want to still the grand total, but only want to see the significant values and then roll the remaining insignificant value as one row. Furthermore, maybe you want to see the average of the top 10 values.  For this tip we can cover how to implement these results by utilizing various MDX functions including the topcount / toppercent functions.

Top N plus All Other in SSAS

In order to implement a TopCount N plus "All Other" methodology, we need to create several parts of the query including a named member set and a named measure. The select portion of the query then pulls in each of these items in the set and unions them together with a single calculated measure value. In order to fully describe the entire process, we will again use AdventureWorks 2012 SSAS database; the database and SSAS database are available on Codeplex at: http://msftdbprodsamples.codeplex.com/releases/view/55330. Once you download and install the databases, open up Management Studio and connect to the Analysis Service database. Next create a new MDX query. For this example, the cities attribute of the geography dimension will be used.

The first step in creating our query is to design our top 10 set as describe below. In the below example, we first name the set Top10; next we specify the TopCount function and then the Order function. The Order function uses the BDESC argument to sort the set in descending order by the Reseller Sales Amount; further the BDESC option tells the ordering to "break" any embedded hierarchies and rank all the values as if they were each an individual value irrespective of any hierarchies. Finally, the 10 near the end of the statement designs the set as only the top 10 values by Reseller Sales Amount. 

With
Set [Top10] AS
(TOPCOUNT({ORDER( ({[Geography].[City].[All].Children}),
([Measures].[Reseller Sales Amount]),BDESC)},10))

Using this set in a simple query is shown in the subsequent screen shot:

Top10

Our next step is to add a second calculated value as a calculated measure. The measure will need to calculate the total amount for all of the values except the top 10 values in the previously created set. Note this expression creates a single calculated measure and not a set. In the below example, we create the Top 10 set and then use it as an input for the calculated measure. The calculated measure uses several components to get the end result. First, the except function is utilized. The except function takes 2 arguments: the set you would like to use and then what to exclude from the set. The second component is the aggregate function which allows us to create, in essence, a calculated measure to add up the All Other values. 

With
Set [Top10] AS
(TOPCOUNT({ORDER( ({[Geography].[City].[All].Children})
,([Measures].[Reseller Sales Amount]),BDESC)},10))

MEMBER [Geography].[City].[OtherAll] AS
(AGGREGATE({EXCEPT([Geography].[City].Members, [Top10])})
)

Select
[Measures].[Reseller Sales Amount] on Columns,
{[Geography].[City].[OtherAll]} on Rows
From
[Adventure Works]

The aggregate function actually provides us with the flexibility to not only sum the values in our set, but we could also use the aggregate function to compute any of the following aggregate operations:

  • Sum
  • Count
  • MIN
  • MAX
  • Distinct Count

Using the above query results in the following results. In this example the aggregate sums up all the values for the reseller sales amount except the top 10 city values by reseller sales amount.

All Other Sum

Alternately, the aggregate could be switched to the Average function, AVG, as outlined in the code snippet below. 

With
Set [Top10] AS
(TOPCOUNT({ORDER( ({[Geography].[City].[All].Children})
,([Measures].[Reseller Sales Amount]),BDESC)},10))

MEMBER [Geography].[City].[OtherAll] AS
(AVG({EXCEPT([Geography].[City].Members, [Top10])})
)

Select
[Measures].[Reseller Sales Amount] on Columns,
{[Geography].[City].[OtherAll]} on Rows
From
[Adventure Works]

This query gives us the following average all cities not in the Top 10.

All Otther Average

In order to complete the final part of our problem resolution, we need to union together the top 10 cities' reseller sales amounts with the All Other values rollup. The Union syntax is a bit different than SQL as outlined at: http://msdn.microsoft.com/en-us/library/ms146030.aspx. The union syntax can actually take 3 forms:

  1. UNION(Set 1, Set 2, ...Option "ALL")
  2. Set 1 + Set 2
  3. {Set 1, Set 2}

Notice how only the first syntax uses the Union key word; sharing a similarity with its SQL union function, the ALL option can be included to retain duplicate values as noted in the first syntax above. To complete our example, the following code shows the final version of our query utilizing the third union syntax.

With
Set [Top10] AS
(TOPCOUNT({ORDER( ({[Geography].[City].[All].Children})
,([Measures].[Reseller Sales Amount]),BDESC)},10))

MEMBER [Geography].[City].[OtherAll] AS
(Aggregate({EXCEPT([Geography].[City].Members, [Top10])})
)
Select
[Measures].[Reseller Sales Amount] on Columns,
{
[Top10]
,[Geography].[City].[OtherAll]
} on Rows
From [Adventure Works]

Now, as outlined above and with the query results displayed below, we have a query which shows our top 10 cities, plus a rollup of "All Other" cities

Top 10 and All other

Alternatively, we could use the average function to average the "All Others" cities list.

With
Set [Top10] AS
(TOPCOUNT({ORDER( ({[Geography].[City].[All].Children})
,([Measures].[Reseller Sales Amount]),BDESC)},10))

MEMBER [Geography].[City].[OtherAllAVG] AS
(AVG({EXCEPT([Geography].[City].Members, [Top10])})
)
Select
[Measures].[Reseller Sales Amount] on Columns,
{
[Top10]
,[Geography].[City].[OtherAllAVG]
} on Rows
From [Adventure Works]

This query results in the top 10 list plus the average of the rest  (or "All Other") of the city values. For some users, the average may be more meaningful than the All Other summation.

Top 10 and Average

Conclusion

To build on the top and bottom count functionality, a common request is to build an All Other member to rollup the remaining values that are not in the top count. The process of building both the top n values plus the All Other rollup value requires several steps including creating the top n set, building a member that uses the except function to get a set of all values except the top n values and then aggregate the non top n values.  Commonly, the aggregate function is used to sum up the remaining values outside of the top n set, but we could also display an average function to get the average of the remaining value. Finally, to complete the process, the top n list must be joined together with the All Other calculated member via the Union function.

Next Steps


Last Update:






About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Wednesday, April 27, 2016 - 3:22:36 PM - Mohammed Siddiq Back To Top

 

 

Hi Scott

I am using ssas 2012 and I am trying to build an inventory cube with one measure volume and two dimensions branch and product.

How can I selectively aggregate this volume measure for product dimension but not for the branch dimension.

The reason being, while aggregating article volumes for different branches makes perfect ssense, aggregating volumes for different articles at branch level does not make any sense as different articles may have different packing sizes, units of measurements etc.


Learn more about SQL Server tools