MDX Solutions: With Microsoft® SQL Server™ Analysis Services 2005 and Hyperion® Essbase, Second Edition

Chapter 16. Optimizing MDX

Just as with any other language, there is more than one way to say the same thing in MDX. Some alternatives, however, may be more efficient than others in their execution. In this chapter, we look at some of the factors that control efficiency (in terms of CPU and memory used) when executing MDX in Microsoft Analysis Services. If you are interested in efficiency but use a different OLE DB for OLAP provider, some of the techniques described may still apply.

We are not going to discuss optimal database design in this chapter. In our experience building analysis systems using OLAP and Analysis Services, we have found many other issues and factors in total system efficiency, some of which affect MDX execution. However, in keeping with the thrust of this as an MDX book, we will restrict our discussion to the issues most directly connected to composing and using MDX, and only look at one design issue that trades MDX work for cube construction work.

Although we will present different areas of optimization, it is also important to remember that faster is not necessarily "better." If your applications respond too slowly, then something may need to be made faster. If it's fast enough and it works, it doesn't need to be fixed. Composing queries that are optimal from a resource point of view may require more cumbersome database designs or front-end query builders as well. But when you need to tune it up, this chapter may help.

Architecture Change from Analysis Services ...