Search code examples
ssasmdx

Is it quicker to create custom set before using RANK function

This runs ok:

WITH 
    MEMBER [Measures].[Players_Rank] AS 
        RANK(
            [Player].[Player].CurrentMember,
            NONEMPTY(
                [Player].[Player].members,
                [Measures].[Score]),
            [Measures].[Score]
            )

But this runs a lot quicker:

WITH 
    SET X AS
        NONEMPTY(
            [Player].[Player].members,
            [Measures].[Score])
    MEMBER [Measures].[Players_Rank] AS 
        RANK(
            [Player].[Player].CurrentMember,
            X,
            [Measures].[Score]
            )

By seperating out the NONEMPTY set and calculating it before hitting the RANK function we get a performance gain - why?

Referring to the reference for the RANK function on MSDN A similar approach is used in here: http://technet.microsoft.com/en-us/library/ms144726.aspx

Solution

  • There is a detailed explanation from one of the developers of Analysis Services here: http://sqlblog.com/blogs/mosha/archive/2006/03/14/ranking-in-mdx.aspx

    Basically, your first query re-builds the set for each cell that needs to show the rank, while the second solution builds the set once, and caches it for all cells that use the ranking member.