Summary

After reading this chapter, on hearing the very word performance, your head should swell with visions of highly performing, scalable systems, each optimized to fulfill its designated mission — never again will this word simply evoke images of entertainment provided by a theatrical group! To build a system that scales up and performs well, you have to consider high and low-level issues. At the high level, aggregation design forms the foundation for an optimally performing OLAP system; take the time to analyze the requirements of the application to get this step right. Another high-level consideration is the type of storage you choose for your system, be it MOLAP, HOLAP, or ROLAP. At lower levels there are many ways to optimize design, like avoiding the use of unnecessary attributes that are never used in customer queries. They can waste precious system data storage and slow down both processing performance and query performance. You learned other lower-level issues, about natural and unnatural hierarchies, and about how you should specify one-to-many relationships whenever possible for better query performance and reduced system data space requirements. You even dove down deep into tuning the Analysis Services instance by changing certain server properties. Finally, you learned how to tune query performance through use of aggregations and MDX query format optimization. And please, don't ignore this final important take-away, use the NonEmpty filter! In fact, use it unless there is a compelling reason not to (like empty cells don't exist in some data set). Finally you learned about tracing Analysis Services operations using SQL Server Profiler. We are confident you exploit the ability to collect Analysis Services traces using Profiler; further, that you will use the traces to enhance performance of the system.