Sunday, June 7, 2015

Fast CASE statements in MDX

Sometimes we need to create a CASE statement in an MDX calculation to split a measure value. There was a good example in this msdn forum. For a simple example
CASE 
     WHEN Measures.[MyCalc]=0 THEN 0
     WHEN Measures.[MyCalc]<=5 THEN 5
     WHEN Measures.[MyCalc]<=10 THEN 10
     WHEN Measures.[MyCalc]<=15 THEN 15 
     WHEN Measures.[MyCalc]<=20 THEN 20 
     WHEN Measures.[MyCalc]<=25 THEN 25     
...
     ELSE 100
END

What I have found is that Measures.[MyCalc] is recalculated for every WHEN clause until it drops out. So the CASE statement could be rewritten as below with much greater performance. If your calculations are parsing through 20 WHEN clauses, this new expression could be 20 times faster! This is most noticeable when MyCalc is itself expensive, particularly when it is FE (formula engine) intensive.

CASE Round(Measures.[MyCalc]/5)*5
     WHEN 0 THEN 0
     WHEN 5 THEN 5
     WHEN 10 THEN 10
     WHEN 15 THEN 15 
     WHEN 20 THEN 20 
     WHEN 25 THEN 25     
...
     ELSE 100
END

Note; you cannot put < or > operators in the WHEN statements if you use the second format above. However, with a little arithmetic, you might be able to bracket the calculation as I have done above using Round().
Another note; if might not be possible to express the CASE statement as I suggest above because there are several conditions. However, I encourage you to think laterally, as you might be able to nest a few CASE statements on the various conditions, so there are fewer calcs per CASE statement, making perform much better.

No comments: