Take advantage of FE caching to optimize MDX performance

We usually treat caching system of Analysis Services as a black box, trusting that it will do the "right thing" to optimize the execution. And this is how things should be in the ideal world, caching system should be completely transparent to the end user. However, even though MDX query optimizer and caching system are very sophisticated, they are not perfect. In this article we will see how with very simple MDX rewrites, we can take better advantage of caching, and increase performance of MDX calculations significantly.

(In order to follow examples in this article, you will need to use MDX Studio tool)

As an example we will use very simple statistical analysis of the data, where we would apply "Three sigma rule" to find outliers across combination of attributes. It is based on the fact that for data which conforms normal distribution almost all of the values (99.7% of them) will lay within 3 standard deviations of the mean. So the values which are farther from the mean by more than 3 standard deviations can be considered outliers. (There are of course more advanced methods to find outliers in the data, but for the purpose of this article this is representative enough).