Cube-Based MDX Calculations - Microsoft SQL Server 2008 Analysis Services - page 212

Calculate;
M1=1;
(M1,M2)=2;

When the cube is loaded, Analysis Services processes the script and creates a pass for each
statement in it, as shown in Figure 12.15.

Pass 3 2

(M1, M2) = 2;

Pass 2

1

M1=1;

Pass 1

Calculate;

Pass 0

M1 + M2 + M3 Loaded from the Fact Table

All M1 M2 M3 D1

FIGURE 12.15

Analysis Services creates a pass for each statement in the MDX script.

When a cell of a cube is calculated, the order in which calculations are applied depends
on the type of calculations used to calculate the cell. If only cube calculations cover a cell,
Analysis Services uses the “highest pass wins” rule.

A calculation covers a cell if the calculation is explicitly specified for a subcube or if the
calculation is specified for a descendant of the cell (because to calculate the value of that
cell, Analysis Services must aggregate values of its descendants). So, in our example, the
calculation on pass 2— (M1, M2)=2; —covers not only the cells associated with members M1
and M2 , but also those associated with their ancestor: the member ALL .

The Highest Pass Wins

Cube calculations are applied to a cell starting from the highest pass (the “highest pass
wins” rule). In the preceding example, if you query a cell associated with M1 , the query
will return 2 because the highest pass available for that coordinate assigned the value 2 to
the cell. For a cell associated with M3, the highest pass is pass 1, so a query will return a
fact data value.

If you use legacy Create Cell Calculation syntax or you create a calculated member
using Create Member syntax, you can explicitly specify the pass on which you want to
create your calculation. If you do this, however, more than one calculation might end up
on the same pass, and Analysis Services would have to choose which calculation should
take precedence over the other. It will use the SOLVE_ORDER property, if you specified one
for your calculation. If even the SOLVE_ORDER properties for the two calculations are the
same, Analysis Services first uses the calculation created before the other (see Figure 12.16).