ssas - 使用 MDX 划分绑定(bind)的 RANK

Question

仍然掌握解释 MDX 的窍门关于 MSDN 的文档.所以对于 RANK function它具有以下内容:

If a numeric expression is specified, the Rank function determines the one-based rank for the specified tuple by evaluating the specified numeric expression against the tuple. If a numeric expression is specified, the Rank function assigns the same rank to tuples with duplicate values in the set. This assignment of the same rank to duplicate values affects the ranks of subsequent tuples in the set. For example, a set consists of the following tuples, {(a,b), (e,f), (c,d)}. The tuple (a,b) has the same value as the tuple (c,d). If the tuple (a,b) has a rank of 1, then both (a,b) and (c,d) would have a rank of 1. However, the tuple (e,f) would have a rank of 3. There could be no tuple in this set with a rank of 2. If a numeric expression is not specified, the Rank function returns the one-based ordinal position of the specified tuple. The Rank function does not order the set.

在下面的脚本中，如果两个人的最高薪水并列第二，我会得到以下薪水等级:

1
2
2
4

我想做的是用服务年限来决定哪个被捆绑的人的等级更高。这可能吗？

WITH 
    SET [OrderedPeople] AS 
        ORDER(
            NONEMPTY(
                [PeopleDimension].[PeopleHier].[NamesLevel].members,
                { //following means if one or the other is null 
                  //then the row is not excluded
                [Measures].[Salary],
                [Measures].[NumYearsService]
                }
            ),
            [Measures].[Salary]
            *
            [Measures].[NumYearsService]
            , 
            BDESC
            )
    MEMBER [Measures].[Salary_Rank] AS 
        RANK([PeopleDimension].[PeopleHier].CurrentMember,
            [OrderedPeople],
            [Measures].[Salary] //<<<how do I use numYearsService to decide ties?
            )
SELECT 
    NON EMPTY 
        { 
        [Measures].[NumYearsService],       
        [Measures].[Salary],
        [Measures].[Salary_Rank]             
        }
    ON COLUMNS,

    NON EMPTY 
        [OrderedPeople]
    ON ROWS  

FROM [ourCube]
WHERE   
    (
    {TAIL([Date].[Date - Calendar Month].[Calendar Day],7)(0):
    TAIL([Date].[Date - Calendar Month].[Calendar Day],7)(6)}
    )

Answer 1

如果您已经订购了该套装，请使用 Rank没有第三个论点，我。 e.

RANK([PeopleDimension].[PeopleHier].CurrentMember,
     [OrderedPeople]
)

Rank返回第一个参数在作为第二个参数的集合中的位置。第三个参数专门用于您希望关系获得相同值的情况。如果使用第三个参数，则对于集合中的相邻元素，检查第三个参数，返回值是集合中与第三个参数具有相同值的第一个元素的位置。

要在 MDX 中按多个条件排序，请将两个订单相互嵌套:

ORDER(
   ORDER(
        NONEMPTY(
            [PeopleDimension].[PeopleHier].[NamesLevel].members,
            { //following means if one or the other is null 
              //then the row is not excluded
            [Measures].[Salary],
            [Measures].[NumYearsService]
            }
        ),
        [Measures].[NumYearsService]
        , 
        BDESC
    ),
    [Measures].[Salary],
    BDESC
)

作为 MDX Order保证做一个 stable sort ，在执行外层排序时，相同薪水的成员与第一个排序的相对顺序没有改变，这意味着他们继续按服务年限排序。