none
MDX Ranking and Ties with thresholds

    Pregunta

  • Hi All,

    I'm hoping someone can help me with this one. I have a real world example involving MDX ranking that I am trying to solve. I have created a query using Adventure Works that contains the essence of what I am trying to solve in my project. If anyone can help me with this I would appreciate it.

    What I am trying to do is to come up with a ranking that involves logic for Ties. In my case a rank tie can occur when the difference of the measure between the current member and previous member of an ordered set is within a certain threshold (in this Adventure Works example say that the number 5 is the threshold value. How could I create a new member that would re-rank the ordered set based on this threshold. Using the third parameter to the Rank function gives me performance problems. Here is the query:

    with

    // order the set - get the top 30 for this example
    set [CustsWithSales] as
    topcount(order([Customer].[Customer].[Customer].members,[Measures].[Internet Sales Amount],BDESC),30)

    // I chose the 2 parameter version of Rank() because adding the third parameter to determine rank took many minutes (I had to kill it). With this version I am
    // relying on an ordered set so this query takes 3 to 4 secs
    member [Measures].[Customer Rank - Orig] as
    rank([Customer].[Customer].currentmember,[CustsWithSales])

    // from this ordered set iterate over each member.
    // Compare the current member to the prev member and if the difference in the Internet Sales Amt is <= 5 then mark it as a Tie with the prev member (i.e. give it the same rank as prev member)


    select
    {[Measures].[Internet Sales Amount],[Measures].[Customer Rank - Orig]} on 0,
    non empty [CustsWithSales] on 1
    from
    (
    select
    {[Geography].[State-Province].&[CA]&[US]} on 0  // just California customers for now
    from [Adventure Works]
    )

    jueves, 21 de junio de 2012 21:06

Todas las respuestas

  • hi,

    your quest raises a theoretical question. your function, lets say: [ f(a,b) -- > rank(a)= rank(b)  if b-a < constant] is not transitive !

    a = 2

    b = 6

    c = 9

    rank(a)= rank(b) 

    rank(b)= rank(c) 

    f((a,c) ??

    as an automated process, we have to handle all kind of possible chain equalities.

    I suggest to change the rules to alleviate those ambiguities

    Philip,


    • Editado VHteghem_Ph jueves, 21 de junio de 2012 23:03
    jueves, 21 de junio de 2012 22:55