Home > MDX > MDX – DENSE RANK

MDX – DENSE RANK

It is very common scenario to have a TOP N reporting requirements. In most of the scenarios MDX TopCount and Rank can be used to solve but at the same time it is very common to miss the big picture of TIES. Unfortunately, we don’t have a DENSE_RANK function so requires bit logical calculations.
Going forward with a scenario from AdventureWorks Cube, for each month get the Top 3 Products in all countries. It is bit complex to include for each scenario with Top N requirements but it can be a common scenario in most of reporting requirements.

WITH SET [Sorted Models] AS
// For each month get Top 25 records, choosed a Top 25 from business case 
 Generate ( [Date].[Calendar].[Month].Members,
		TopCount 
		  ( nonempty(
			   {[Date].[Calendar].CurrentMember
			  * [Product].[Model Name].[Model Name].MEMBERS
			  * [Geography].[Country].[Country].MEMBERS
                            }
			   ,[Measures].[ORDER Count] 
			     ) 
		   , 25
		   ,[Measures].[ORDER Count]
		  )
	      )

MEMBER [Measures].[Rank] AS
// Get the Rank of current member Tuple to Current Month Set
// Do not use Sorted Models set due to dynamic for each set
Rank ( (  [Date].[Calendar].CurrentMember 
	, [Product].[Model Name].CurrentMember
	, [Geography].[Country].CurrentMember
        )
  ,  Generate ( [Date].[Calendar].CurrentMember,
	TopCount 
	  ( nonempty(
		 {  [Date].[Calendar].CurrentMember
		  * [Product].[Model Name].[Model Name].MEMBERS
		  * [Geography].[Country].[Country].MEMBERS
                  }
		   ,[Measures].[ORDER Count] 
		   ) 
	   , 25
	   ,[Measures].[ORDER Count]
	  )
	 )
    , [Measures].[ORDER Count] 
    )
	  
MEMBER [Measures].[Previous Set Index] AS
// Get the Set Index using Rank 
( 
[Measures].[Rank] - 2 
) 
MEMBER [Measures].[Dense Rank] AS
// Get the Dense Rank using the Index position value 
CASE 
 WHEN [Measures].[Rank] = 1 
  THEN 1 
 ELSE 
		( 
		[Sorted Models].Item([Measures].[Previous Set Index]), 
		[Measures].[Dense Rank] 
		) 
	 + 
		Iif 
		( 
		     ( 
		     [Sorted Models].Item([Measures].[Previous Set Index]), 
		     [Measures].[ORDER Count] 
		     ) 
		  = 
		     [Measures].[ORDER Count] 
		 ,0 
		 ,1 
		) 
End 
 
SELECT
  {
      [Measures].[ORDER Count]
    , [Measures].[Rank]
    , [Measures].[Dense Rank]
    //, [Measures].[Previous Set Index]
  } ON 0
 ,NON EMPTY
	 {
	 // FILTER Can be used to get the TOP 3 using DENSE RANK 
	 // FILTER (  
			   [Sorted Models]
	//		 , [Measures].[Dense Rank] <=3
	//	    )
	 
	 } ON 1
	FROM (
		SELECT [Date].[Calendar].[Calendar Year].&[2006] ON 0 
                 FROM
                     [Adventure Works]
		 )

DENSE RANK

Advertisements
Categories: MDX Tags:
  1. No comments yet.
  1. No trackbacks yet.

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: