Professional SQL Server™ Analysis Services 2005 with MDX

13.2. Optimize for Processing

To understand how to improve UDM processing performance you first need to understand the processing operation of Analysis Services. Analysis Services 2005 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 13-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 reads records from its file storage for the queries and sends the records to Analysis Services. Analysis Services reads the data 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 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 then 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 since all the attributes are related to the key attribute. While processing the partitions Analysis Services ...