MDX Ranking and Ties with thresholds

• jueves, 21 de junio de 2012 21:06

   

   
  

  0

  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]
  )

  • Responder
  • Citar