Performance Optimization - Professional SQL Server Analysis Services 2005 with MDX - page 713

Summary
After reading this chapter, on hearing the very word performance , your head
should swell with visions of highly performing, scalable systems, each optim-
ized 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 per-
forming OLAP system; take the time to analyze the requirements of the applic-
ation to get this step right. Another high-level consideration is the type of stor-
age 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 unne-
cessary 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 re-
lationships whenever possible for better query performance and reduced sys-
tem data space requirements. You even dove down deep into tuning the Ana-
lysis 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 reas-
on 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 Pro-
filer; further, that you will use the traces to enhance performance of the sys-
tem.