Hi ,

Im new to MDX and I stuck in calculating rank of a measure based on
multiples dimensions (SSAS 2005) .Here is insight of the problem,

Below is the set that I defined.

WITH
SET [GROUPITEMS] AS '
NONEMPTYCROSSJOIN(
{[DIMENSION1].[ID].&[009361264]},
[DIMENSION2].[code].CHILDREN,
[DIMENSION3].[ATT1].CHILDREN,
[DIMENSION4].[ATT2].CHILDREN,
[DIMENSION5].[ATT3].CHILDREN,
)
I also have a measure called [Measures].[SALE AMT]

I need to calculate RANK of [Measures].[SALE AMT], based on the above
set,and here is the MDX that I have

WITH
MEMBER measures.[SALE AMT RANK] as
RANK
((
[DIMENSION1].[ID].CURRENTMEMBER,
[DIMENSION2].[code].CURRENTMEMBER,
[DIMENSION3].[ATT1].CURRENTMEMBER,
[DIMENSION4].[ATT2].CURRENTMEMBER,
[DIMENSION5].[ATT3].CURRENTMEMBER,
),

Order(Axis(1), ([Measures].[SALE AMT]), BASC))

SELECT
{
[Measures].[SALE AMT],[MEASURES].[SALE AMT RANK]
}
ON COLUMNS,
{ [GROUPITEMS] } ON ROWS FROM SALES

Can someone suggest this is the best way of calculating the rank ,the issue
here is it is very very slow and I am not sure whether the answer is 100%
correct.Thanks in advance for the help.

Maneesh

Hi Maneesh,

For the example you provided, the query may run faster if you order the
set just once, like in a named set:

SET [GROUPITEMS] AS
NONEMPTYCROSSJOIN(
{[DIMENSION1].[ID].&[009361264]},
[DIMENSION2].[code].CHILDREN,
[DIMENSION3].[ATT1].CHILDREN,
[DIMENSION4].[ATT2].CHILDREN,
[DIMENSION5].[ATT3].CHILDREN,
)
SET [ORDERITEMS] as
Order([GROUPITEMS], [Measures].[SALE AMT], BASC)
MEMBER measures.[SALE AMT RANK] as
RANK
((
[DIMENSION1].[ID].CURRENTMEMBER,
[DIMENSION2].[code].CURRENTMEMBER,
[DIMENSION3].[ATT1].CURRENTMEMBER,
[DIMENSION4].[ATT2].CURRENTMEMBER,
[DIMENSION5].[ATT3].CURRENTMEMBER,
),
[ORDERITEMS]
)

SELECT
{
[Measures].[SALE AMT],[MEASURES].[SALE AMT RANK]
}
ON COLUMNS,
[GROUPITEMS] ON ROWS
FROM SALES

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***