MDX Ranking Question

Hi

We are trying to create a Ranking of a dimension based on multiple different measures. An example would be to rank the Sales Consultant by Num Sales, Total Sales and Num Customers.

In SQL, I would create three different temp tables, with the Sales Consultant, Measure and Ranking for each different measure. Then you could join these results together and generate the final ranking based on this result set.

Now the problem we are having is doing this in MDX. You can only order by one of the measures in any result set. Any way I look at this, it looks to be a multi step process to create this data. The only way I can think that this might work is to create a stored proc, but then this will limit the users ability to slice data by all the dimensions.

Are there any ideas on how we can do this that I have missed?

Thanks

Michael

Hi Michael,

Here's a sample Adventure Works ranking query - could you give more details, with examples, of your scenario?

With

Set [OrderedProducts] as

Order([Product].[Category].[Category]

* {[Measures].[Order Quantity]},

[Measures].[Order Quantity], BDESC)

+ Order([Product].[Category].[Category]

* {[Measures].[Sales Amount]},

[Measures].[Sales Amount], BDESC)

+ Order([Product].[Category].[Category]

* {[Measures].[Total Product Cost]},

[Measures].[Total Product Cost], BDESC)

Member [Measures].[QuantityRank] as

Rank(([Product].[Category].CurrentMember,

[Measures].[Order Quantity]),

Exists([OrderedProducts],

{[Measures].[Order Quantity]}))

Member [Measures].[SalesRank] as

Rank(([Product].[Category].CurrentMember,

[Measures].[Sales Amount]),

Exists([OrderedProducts],

{[Measures].[Sales Amount]}))

Member [Measures].[CostRank] as

Rank(([Product].[Category].CurrentMember,

[Measures].[Total Product Cost]),

Exists([OrderedProducts],

{[Measures].[Total Product Cost]}))

select

{[Measures].[Order Quantity],

[Measures].[QuantityRank],

[Measures].[Sales Amount],

[Measures].[SalesRank],

[Measures].[Total Product Cost],

[Measures].[CostRank]} on 0,

Extract(Exists([OrderedProducts],

{[Measures].[Order Quantity]}),

[Product].[Category]) on 1

from [Adventure Works]

--

Order Quantity QuantityRank Sales Amount SalesRank Total Product Cost CostRank
Bikes 90,220 1 $94,620,526.21 1 $84,105,429.60 1
Clothing 73,598 2 $2,117,613.45 3 $1,748,777.45 3
Accessories 61,931 3 $1,272,057.89 4 $637,590.73 4
Components 49,027 4 $11,799,076.66 2 $10,766,110.18 2

|||

Hi

Thanks very much!!!! This just shows how far I have to go to understand MDX...

I took your query and expanded it to give what we were looking for:

With

Set [OrderedProducts] as

Order([Product].[Category].[Category]

* {[Measures].[Order Quantity]},

[Measures].[Order Quantity], BDESC)

+ Order([Product].[Category].[Category]

* {[Measures].[Sales Amount]},

[Measures].[Sales Amount], BDESC)

+ Order([Product].[Category].[Category]

* {[Measures].[Total Product Cost]},

[Measures].[Total Product Cost], BDESC)

Member [Measures].[QuantityRank] as

Rank(([Product].[Category].CurrentMember,

[Measures].[Order Quantity]),

Exists([OrderedProducts],

{[Measures].[Order Quantity]}))

Member [Measures].[SalesRank] as

Rank(([Product].[Category].CurrentMember,

[Measures].[Sales Amount]),

Exists([OrderedProducts],

{[Measures].[Sales Amount]}))

Member [Measures].[CostRank] as

Rank(([Product].[Category].CurrentMember,

[Measures].[Total Product Cost]),

Exists([OrderedProducts],

{[Measures].[Total Product Cost]}))

Member [Measures].[OverallActivity] as

1/(

(1/[Measures].[QuantityRank])^2 +

(1/[Measures].[SalesRank])^2 +

(1/[Measures].[CostRank])^2

)

Set [OrderedActivity] as

Order([Product].[Category].[Category]

* {[Measures].[OverallActivity]},

[Measures].[OverallActivity], BASC)

Member [Measures].[ActivityRank] as

Rank(([Product].[Category].CurrentMember,

[Measures].[OverallActivity]),

Exists([OrderedActivity],

{[Measures].[OverallActivity]}))

select

{[Measures].[Order Quantity],

[Measures].[QuantityRank],

[Measures].[Sales Amount],

[Measures].[SalesRank],

[Measures].[Total Product Cost],

[Measures].[CostRank],

[Measures].[OverallActivity],

[Measures].[ActivityRank]} on 0,

Extract(Exists([OrderedProducts],

{[Measures].[Order Quantity]}),

[Product].[Category]) on 1

from [Adventure Works]

Which gives us the Overall ranking of the Product by these three rankings:

Order Quantity QuantityRank Sales Amount SalesRank Total Product Cost CostRank OverallActivity ActivityRank
Bikes 90,220 1 $94,620,526.21 1 $84,105,429.60 1 0.333333333333333 1
Clothing 73,598 2 $2,117,613.45 3 $1,748,777.45 3 2.11764705882353 3
Accessories 61,931 3 $1,272,057.89 4 $637,590.73 4 4.23529411764706 4
Components 49,027 4 $11,799,076.66 2 $10,766,110.18 2 1.77777777777778 2

Once again, Thanks for the help!!!!

|||

Hi

A further question to this. We are also wanting to make this ranking Time aware. Currently, you can only run the quesry for a specific time period (eg [dimDate].[Year].[2006]). If you try to create a crossjoin in the report to have all the above measures by 2005 and 2006, then the ranking does not work anymore. As far as I understand it, this will be the case as the Order Set does not know about the time dimension, and hasn't ordered the Products by Time.

So, how I see this is that I will need to add the time Dim to the order Set, but at what level. We would like to make this dynamic within the cube and would like the ranking to work in a year on year, quarter on quarter, month on month ...etc level. I would think that ordering by the lowest level of time (day in my case) might work, but would create a set that is extremely large.

Would this work, or is there a better way to do this?

Michael

|||

Hi Michael,

This is a common requirement, but it's beyond the ordered SQL temp table approach. You could use Rank() dynamically, as shown in the query below. But there might be a performance penalty - see this 2005 thread from the OLAP newsgroup:

With

Member [Measures].[QuantityRank] as

Rank([Product].[Category].CurrentMember,

[Product].[Category].[Category],

[Measures].[Order Quantity])

Member [Measures].[SalesRank] as

Rank([Product].[Category].CurrentMember,

[Product].[Category].[Category],

[Measures].[Sales Amount])

Member [Measures].[CostRank] as

Rank([Product].[Category].CurrentMember,

[Product].[Category].[Category],

[Measures].[Total Product Cost])

Member [Measures].[OverallActivity] as

1/(

(1/[Measures].[QuantityRank])^2 +

(1/[Measures].[SalesRank])^2 +

(1/[Measures].[CostRank])^2

)

Member [Measures].[ActivityRank] as

Rank([Product].[Category].CurrentMember,

[Product].[Category].[Category],

-[Measures].[OverallActivity])

select

{[Measures].[Order Quantity],

[Measures].[QuantityRank],

[Measures].[Sales Amount],

[Measures].[SalesRank],

[Measures].[Total Product Cost],

[Measures].[CostRank],

[Measures].[OverallActivity],

[Measures].[ActivityRank]} on 0,

[Date].[Calendar Year].[Calendar Year]

* [Product].[Category].[Category] on 1

from [Adventure Works]

|||Thanks for all the help! That is working fine, and is not too slow. It is not a commonly used calc so shouldn't pose a problem.