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:
Post a Comment