Chapter 8. Query Performance Tuning

One of the main reasons for building Analysis Services cubes as part of a BI solution is because it should mean you get better query performance than if you were querying your relational database directly. While it's certainly the case that Analysis Services is very fast, it would be naive to think that all of our queries, however complex, will return in seconds without any tuning being necessary. This chapter will describe the steps you'll need to go through in order to ensure your cube is as responsive as possible, covering the following topics:

• Partitioning measure groups

• Building aggregations

• Tuning MDX calculations and queries

• Using caching to your advantage

• Understanding Scale-out options

Note

This chapter should be read in conjunction with the very detailed white paper, "The Analysis Services 2008 R2 Performance Guide", which can be downloaded from http://tinyurl.com/ssasR2perf.