SSAS Rank and Row Number Ordering

By: Scott Murray | Comments | Related: > Analysis Services Development

Problem

Does SQL Server Analysis Services SSAS MDX have Row Number, Rank and Dense Rank functions like the SQL Server database engine?

Solution

Many MDX developers quickly become familiar with the "Top and Bottom" functions, such as TopCount, TopSum, TopPercent, which are available SSAS MDX. If you are not familiar, a good place to review these functions would be the MSSQLTips.com tip: https://www.mssqltips.com/sqlservertip/3034/sql-server-analysis-services-ssas-2012-top-and-bottom-functions/.

One would think that all the various rank and row number functions and features would be available in SSAS MDX. Unfortunately, that is not the case. MDX includes a Rank and Order function which can be used in conjunction with each other to achieve some the ranking functionality.

In order to show several examples, we will use the Adventure Works databases as the basis for our SSAS cube and querying. The 2014 versions of the regular and data warehouse databases, along with the SSAS cube database backups are available on Codeplex: https://msftdbprodsamples.codeplex.com/releases/view/125550. Once you download and install the SQL Server databases, we will subsequently use SQL Server Data Tools for Business Intelligence (SSDT-BI) for Visual Studio 2013 to setup and process the AdventureWorks cube. You can download SSDT-BI from: http://www.microsoft.com/en-us/download/details.aspx?id=42313.

MDX Rank Function

The MDX Rank function is our path to achieving the various rank results we are seeking. As with most any function or feature, there are some caveats to the use of the function. First, which arguments you pass to the Rank Function determines how "tied" results are displayed. Furthermore, you must test the performance of the rank function as you can quickly diminish performance if you use rank over a large tuple; in some cases to improve performance you can use the Order function to first order a set, and then use that set as the input argument for the Rank function. The basic syntax of the Rank function is as follows: Rank(Tuple_Expression, Set_Expression [ ,Numeric Expression ]) where:

Tuple_Expression - A valid tuple
Set_Expression - A valid set
Numeric Expression - The numeric value determines how the tuple is evaluated for the rank

When this argument is included, then the Rank function will assign the same rank value to matching rows

This argument is optional

Let us move forward with some examples and explore some of the fun challenges that appear when using the Rank function. The below simple query from the AdventureWorks SSAS database ranks countries by the Internet Sales Amount. To accomplish this functionality, we create a Member to rank the Internet Sales Amount value. Within the Rank function we define the Sales Territory Country current members as the tuple, all the members of the Sales Territory Country dimension as the set, and finally the Internet Sales Amount measure as the numeric value to rank. Next we add a MDX select to put the rank and Internet Sales Amount on the columns and Sales Territory Country on the rows.

WITH MEMBER [Measures].[COUNTRY_RANK] AS
RANK([Sales Territory].[Sales Territory Country].CURRENTMEMBER
 ,[Sales Territory].[Sales Territory Country].[Sales Territory Country].MEMBERS
 ,[Measures].[Internet Sales Amount])

SELECT 
 {
 [Measures].[Internet Sales Amount] 
 , [Measures].[COUNTRY_RANK]
 }
  ON COLUMNS,
 
 [Sales Territory].[Sales Territory Country].[Sales Territory Country].MEMBERS
  ON ROWS 
FROM
[Adventure Works]

This query results in the below output.

Of course you will notice a few things about the results. First, the rank is correct, however the order returned in the query results is by country and not by the order of the ranking. Second, we the query can be simplified a bit. To accomplish both these tasks, we make the following adjustments to the query , as shows below: 1) we create a named set and 2) we apply the Order function to the name set to display the list in order by Internet Sales Amount, descending.

WITH 
SET OrderedCountriesbySales AS
ORDER([Sales Territory].[Sales Territory Country].[Sales Territory Country].MEMBERS,[Measures].[Internet Sales Amount], BDESC)

MEMBER [Measures].[COUNTRY_RANK] AS
RANK([Sales Territory].[Sales Territory Country].CURRENTMEMBER,OrderedCountriesbySales,[Measures].[Internet Sales Amount])

SELECT 
 {
 [Measures].[Internet Sales Amount] 
 , [Measures].[COUNTRY_RANK]
 }
  ON COLUMNS,
 
OrderedCountriesbySales
  ON ROWS 
FROM
[Adventure Works]

Now the query results, illustrated next, are ranked by Internet Sales Amount in descending order.

Now let us switch gears and show you what happens when two values match during the ordering. To see such an example, we need to use a completely different query; one where the ordering values match from one row to the next row. The below query returns the date and Internet Order Count in descending order. You will notice we are using two ranking members. One with the numeric expression argument and one without.

WITH 
SET TiedRanks AS 
ORDER( [Date].[Date].[Date].MEMBERS
, [Measures].[Internet Order Count]
, BDESC)

MEMBER MEASURES.NON_TIED_RANK AS 
RANK( [Date].[Date].CURRENTMEMBER ,TiedRanks)

MEMBER [Measures].TIED_RANK AS 
RANK( [Date].[Date].CURRENTMEMBER, TiedRanks,[Measures].[Internet Order Count])

SELECT {
[Measures].[Internet Order Count], [Measures].NON_TIED_RANK, [Measures].TIED_RANK } ON COLUMNS, 
TiedRanks ON ROWS
FROM [Adventure Works]

This query outputs two different methods of displaying a rank. The first method, which is noted as NON_TIED_RANK in the below illustration, is most similar to the SQL Row Number as it continues to count up even when a tied value occurs. Also notice this value actually ranks by the Internet Order Count first, but then orders the dates in chronological or ascending order on tied rows ( note how on Ranking# 4 and 5, June 5th is listed first and then December 19th). If you review the above MDX code, also notice how no numeric expression is noted.

The third column, TIED_RANK, actually ranks two dates with the same value, as being of "equal" rank, but still continues counting to the next rank (notice how rank #6, after the blue highlighted square, is 6 not 5). This method is most similar to the SQL Rank function. To use this method, we have to specify a number expression for the 3rd argument, Internet Order Count for this example. I should note, that performance on the "TIED_RANK" type queries can worsen if your named set is large or if you try to do the ordering within the RANK itself. You could implement a filter on the set to further limit the set and improve performance.

One last option with ranking is what happens if we want to "reset" our numbering when we hit a new value say in the first value of a two step hierarchy. In the below query, Sales Territory Group is the first level of the hierarchy where as Sales Territory Country is the second level. When we hit a new Sales Territory Group, we want to restart the number ranking. In SQL, this process is easy by using the Partition argument. Unfortunately, with MDX the process is much harder. We actually need to cross join the different levels to get the desired results.

As shown in the below MDX, the first step is to create a member which ranks the first level of our hierarchy, Sales Territory Group. This grouping will repeat the rank of each Sales Territory Group. The second step, is to create a second member which ranks the cross join of the Sales Territory Group with the Sales Territory Country, the second level of the hierarchy. Both of these members are ordered by Internet Sales Amount in descending order. Within the Select portion of the MDX, we then return these two members plus the Internet Sales Amount on the columns. Finally on the rows, we display Sales Territory Group and Sales Territory Country. These rows are ordered by Internet Sales Amount, first at the Group level and then at the Country level.

WITH 
   MEMBER [Measures].[Group_Ranking_InternetSales] AS
   RANK( [Sales Territory].[Sales Territory Group].CurrentMember,
   ORDER( [Sales Territory].[Sales Territory Group].[Sales Territory Group].Members , [Measures].[Internet Sales Amount], BDESC)
   ) 

   MEMBER [Measures].[Group_Territory_Ranking_InternetSales] AS
   RANK(( [Sales Territory].[Sales Territory Group].CurrentMember,   [Sales Territory].[Sales Territory Country].CurrentMember),
   ORDER( CROSSJOIN([Sales Territory].[Sales Territory Group].CurrentMember,  [Sales Territory].[Sales Territory Country].[Sales Territory Country]) , [Measures].[Internet Sales Amount], BDESC)
   ) 
SELECT 
      {
   [Measures].[Group_Ranking_InternetSales]
   ,[Measures].[Group_Territory_Ranking_InternetSales] 
   , [Measures].[Internet Sales Amount]
      } ON COLUMNS,
   (
           ORDER  ([Sales Territory].[Sales Territory Group].[Sales Territory Group], [Measures].[Group_Ranking_InternetSales] , BASC ) 
   ,ORDER  ([Sales Territory].[Sales Territory Country].[Sales Territory Country], [Measures].[Group_Territory_Ranking_InternetSales] , BASC ) 
   )
   ON ROWS 
FROM
      [Adventure Works]

The results of this query are displayed below. Notice how the Group_Territory_Ranking_InternetSales column resets with each new Sales Territory Group.

Conclusion

Using the Rank function in SSAS allows us to simulate some of the Row Number and Rank functionality available in regular SQL. The MDX Rank function requires two arguments, a tuple and a set; optionally, you can provide a third argument, a numeric expression which is used for sorting the dataset. If the third argument is not specified, the default sorting is used. Furthermore, using third argument allows you to determine how "tied values" are treated. The Rank function is often used in conjunction with the Order function as MDX does not automatically "return" the results in the Rank order; you must tell it to do so. Finally, using a combination of Rank functions and cross joins between two sets, a partitioned ranking can also be achieved.

Next Steps

Compare SQL with MDX - https://www.mssqltips.com/sqlservertip/2916/comparison-of-queries-written-in-tsql-and-sql-server-mdx/
A Quick Way to Start Learning MDX - https://www.mssqltips.com/sqlservertip/2865/a-quick-way-to-start-learning-sql-server-mdx/
MDX Order - https://msdn.microsoft.com/en-us/library/ms145587.aspx

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

SQL Server Analysis Services Rank and Row Number Ordering