Performance of IIF function in MDX

IIF is one of the most popular MDX functions, (this was proved by these surveys). Yet, it can cause significant performance degradation, which is often blamed on other parts of the system. Many times it is simple to rewrite the MDX expression to get rid of IIF altogether, and other times it is possible to slightly change the IIF to increase performance. While I and others mentioned this fact several times in blog entries, forum postings etc, people keep using and using it (and using it wrong). What is worse, after seeing CASE operator in samples and BOL, people start using CASE instead of IIF. From the performance point of view, CASE is always worse than IIF, at least IIF is possible to optimize in some case, but never CASE. So I decided to dedicate special blog to go over IIF usage and demonstrate with examples how horrible it can be and how to fix it.

To make it easier to demonstrate this on standard Adventure Works cube, the examples are somewhat artificial, but it is not hard to imagine how real-life example would fit into the same pattern.

Let's assume that we want to compute 'Normalized Cost' measure, which is the same as Standard Product Cost measure, except for when there is no promotion, i.e. when Promotion Type is No Discount, it should be Freight Cost plus Standard Product Cost. Using the IIF function, the solution would look like following:

Read more...