Adding Calculations to the Cube - Expert Cube Development with SSAS Multidimensional Models - page 160

You can see that the rank of a product changes over time. As the calculated measure
is evaluated in the context of the query, with the ordering of the set taking place
for each cell, it will calculate the rank correctly no matter what is used on the rows,
columns, or slicer of the query. Because of that, rank calculations can perform quite
poorly, and should be used with caution. In situations where you have control
over the MDX queries that are being used, for example, when writing Reporting
Services reports, you may be able to optimize the performance of rank calculations
by ordering the set just once in a named set, and then referencing the named set from
the calculated measure. The following query shows how to do this:
WITH
SET OrderedProducts AS
Order (
[Product].[Product].[Product].MEMBERS,
[Measures].[Sales Amount],
BDesc
)
MEMBER MEASURES.[Product Rank] AS
IIF (
[Measures].[Sales Amount] = 0,
NULL,
Rank (
[Product].[Product].CurrentMember,
OrderedProducts
)
)
SELECT
{
[Measures].[Sales Amount],
[Measures].[Product Rank]
} ON 0,
NON EMPTY
[Product].[Product].[Product].MEMBERS ON 1
FROM [Adventure Works]
As always, Mosha Pasumansky's blog is a goldmine of information on the
topic of ranking in MDX, and the following blog entry goes into a lot of detail
on it: http://tinyurl.com/mosharank .