Averages, ratios, division by zero and NON_EMPTY_BEHAVIOR
How to get averages and other ratio based MDX calculations demonstrate great performance ? I thought I covered this subject in great depth before, but I probably wasn't concise enough. Last week I was asked to help with performance to very big and important customer (who shall remain unnamed). When I arrived on site, I found the project staff to be very knowledgeable about Analysis Services and MDX. The model was designed following all the best practices, they have studied the Performance Guide and implemented all the relevant changes. Yet they couldn't get a very simple MDX calculation of average to perform well in medium size Excel reports. They also studied all of my earlier blog on this subject ("How to check if cell is empty", "Budget Variance and NEB" and "Performance of IIF" blogs), yet it wasn't working for them. We have found a solution which satisfied both the business needs and delivered great performance, so I thought I should share it, since it is such a common scenario.