Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

14.2. Optimizing for Processing

To understand how to improve UDM processing performance you first need to understand the processing operation of Analysis Services. Analysis Services 2008 supports ROLAP and MOLAP storage modes for dimensions and ROLAP, MOLAP, and HOLAP storage modes for partitions. Assume the data source is a relational database. Figure 14-15 shows the architecture of a regular processing operation when the storage mode for the dimensions and cubes is MOLAP. Analysis Services sends separate relational queries to the retrieve dimension and fact data. The relational data source executes the query and sends the records to Analysis Services. Analysis Services reads the records from the relational data source and stores it in a proprietary format for fast data access. During dimension processing Analysis Services sends separate queries to process each attribute of the dimension. Members from each attribute are stored and indexed by Key and Name for fast data access. The related properties to the attribute are also indexed. If an attribute has a related attribute defined, the related attribute needs to be processed first before the attribute itself. Analysis Services processes attributes in parallel based on resource availability, parallelism specified, and dependencies. The key attribute of the dimension is the last attribute processed because all the attributes are related to the key attribute. While processing the partitions Analysis Services reads fact data from ...