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

Optimize for Processing
To understand how to improve UDM processing performance you first
need to understand the processing operation of Analysis Services. Ana-
lysis Services 2005 supports ROLAP and MOLAP storage modes for di-
mensions 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 separ-
ate relational queries to the retrieve dimension and fact data. The rela-
tional 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 at-
tribute 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 reads fact data from the rela-
tional data source and stores it in proprietary format. Analysis Services
then creates indexes to access the data efficiently. If aggregations are
designed for the partitions then aggregations are built followed by in-
dexes.
Figure 13-15