Architecture of Query Execution—Calculating MDX Expressions - Microsoft SQL Server 2008 Analysis Services - page 542

Physical Plan Construction

After it builds the logical execution plans, Analysis Services starts to build physical plans.
It iterates each logical plan in the top-down approach, and for each logical operator
creates a physical one. The formula engine can choose one of three physical operands
supported by Analysis Services 2008:

. iterator —Iterates over the cell values

. lookup —Extracts a single cell from collection of cells

. datacache —Materializes in memory a set of cells required by operation

The system analyzes different properties of the logical operand, such as DefaultValue ,
Static / Dynamic , and varying attributes and MDX functions corresponding to the operand
and chooses the right physical operand for this position of the physical plan. For example,
to perform multiplication operations over the multidimensional space, Analysis Services
needs to perform the following steps. First it has to retrieve results of a left-hand operand
and results of the right hand one (see Figure 29.11). Then, it needs to iterate over the first
set of values and for each cell retrieve the corresponding cell from the second one and
then perform mathematical operation on two values, or it does the opposite (iterates over
the second set of values and retrieves the corresponding one from the first set of values).
With both algorithms, the results will be exactly the same, but performance might differ.
Imagine a currency-conversion operation, where for each product you need to multiple
the value of its sale over the current conversion rate. In many cases, the number of prod-
ucts is much larger than the conversion rates, and it will be much faster to iterate over
products and look up into much smaller result set—the currency rates (see Figure 29.14).
Therefore, when choosing the type of the physical operand, Analysis Services iterates over
sparse results and looks up into dense ones.

Currency Product Sales Count Currency Conversion Rate

Euro Product 1 5 US Dollar 1

Euro Product 2 15 Euro 1.5

Euro Product 3 8 Ruble 0.2

Ruble Product 3 17

…

Ruble Product 3 33

FIGURE 29.14

A currency-conversion operation.