There are two primary aspects to tuning Analysis Services: tuning processing performance and tuning query performance. This is often a balancing act because improving one often has a negative impact on the other. In a well-tuned Analysis Services instance, increasing aggregation improves query performance. This has a negative impact on processing times. The goal is to intelligently aggregate based on what is known about query requests.
Usage-Based Optimization (UBO)
When no additional information is given, Analysis Services creates aggregations randomly, with no knowledge of what aggregations will be most used. This is similar to indiscriminately creating indexes on all columns of a SQL Server table, without thinking about what columns will most frequently be searched on. One difference in a data warehouse is that it must be assumed that any piece of information may be used as a filter. Nonetheless, there will certainly be dimension levels that will be far more active than others. As previously mentioned, aggregation design can be influenced by setting properties at various levels in a dimension or by implementing usage-based optimization (UBO). UBO takes a workload from Analysis Services and designs aggregations based on the queries that were logged. You'll learn more about the implementation of UBO later in this chapter, in the section "Administering Analysis Services."
Partitioning continues to be a performance-enhancing technique in Analysis Services 2005. Partitioning can improve the performance of queries against cubes by minimizing the amount of data that needs to be interrogated. A common partitioning strategy in Analysis Services uses a date as the partitioning key. Dates are an integral part of most analysis across industry verticals, providing feedback on trends and comparisons to historical segments in time. Partitioning by date has the side benefit of facilitating administration of the OLAP cubes. As new data is loaded into cubes, only the affected partitions require any type of processing. This can significantly diminish the batch window required for MOLAP and HOLAP processing. In addition, you can easily archive data out of an active cube by simply deleting older partitions. Otherwise, the data would need to be removed from the underlying source tables, and the entire cube/measure group would have to be reprocessed to remove the older data.
The implementation of partitioning in Analysis Services 2005 has been enhanced in a couple ways. First, data slices are no longer necessary if the aggregation design will be MOLAP. With MOLAP, Analysis Services keeps heuristics on the data stored in the partitions and can quickly examine that data to determine what partitions contain data relevant to the query. This does not apply to other aggregation storage levels. Second, partitions are processed in parallel by default. In Analysis Services 2000, this behavior required the use of a DSO application.
The Optimize Schema Option
One of the primary options used in Analysis Services 2000 and earlier implementations was Optimize Schema. By default, Analysis Services 2000 was pessimistic about the underlying integrity of the star schema tables. When processing a cube, it would issue a SQL query that joined the fact table to each associated dimension table as defined in the cube. This greatly slowed cube processing. Because the foreign key in the fact table is usually the same as the cube's member key, these joins could be removed by using the Optimize Schema option in the Cube Editor. This assumed that referential integrity was checked through foreign key relationships in the fact table or as part of the star schema loading process. Analysis Services 2005 uses the fact table's member key instead of joining to the dimension table by default. The Optimize Schema option was unknown by many users of Analysis Services, so the fact that this is now automatically implemented will speed up processing by default.