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

M1 M3

Pass 3 2

(M1, M2) = 2;

M1=1;

Pass 2 1

Calculate;

Pass 1

Pass 0 M1 + M2 + M3 Loaded from the Fact Table

All M1 M2 M3 D1

FIGURE 12.16

Cell values are calculated starting from the highest pass available.

Analysis Services provides a robust and powerful way to use MDX scripts to create and store
calculations. We do not recommend that you use legacy functionality and play with passes
unless you are sure that you want to change the order in which you call the calculations.

There are side effects to the “highest pass wins” rule, but there are ways to achieve the
behavior you want. The following script provides an example:

M1=1;
M2=M1;
M1=2;

If you are used to programming in any programming language, you probably expect for
the cell associated with the member M1 to have the value 2 and the cell associated with M2
to equal 1 . However, that is not what happens. Figure 12.17 demonstrates the steps
Analysis Services takes to retrieve both cells.

M1

M1

Pass 4 M1=2;

M2

Pass 3 M2=M1;

Pass 2 M1=1;

Calculate;

Pass 1

Pass 0 M1 + M2 + M3 Loaded from the Fact Table

All M1 M2 M3 D1

FIGURE 12.17

The value of M2 equals the value of M1 on the highest pass.