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

Let's look at how the algorithm works on a simple script:
Calculate;
M1=1;
M1=M1*2;
To calculate the value of the cell associated with the M1 member, the expression on pass
3— M1=M1*2; —is evaluated first. The expression recursively calls M1 . Instead of trying to
evaluate M1 on the highest pass, Analysis Services moves to pass 2—the previous pass
covering space for M1 —and tries to evaluate the expression M1=1; . Because that expression
is not recursive, the result of the evaluation on pass 2 propagates to pass 3, where it is
used to calculate M1*2 . Therefore, the resulting value for this cell is (1*2) or, in other
words, 2 , as shown in Figure 12.19.
Recursion happens not only when an expression explicitly uses the value of itself.
Recursion also occurs in a more subtle case: when the value for a descendant depends on
the value of its ancestor. One of the common cases of this scenario occurs in the distribu-
tion of a budget between different departments of an organization. Consider the following
script as an example:
Calculate;
{M1, M2, M3} = ALL/3;
When a user requests the value associated with one of the members—let's say M2 —Analysis
Services tries to retrieve the value of the ALL member on pass 1. It calculates the value of
the ALL member as an aggregation of its descendants; therefore, Analysis Services needs
the value of M2 . At this stage, we get a recursion. (We are already inside the calculation of
the value for M2 .) Therefore, the recursion resolution algorithm decreases the pass number
to pass 1, and Analysis Services calculates the value for M2 , which happens to be the value
loaded from the fact table. As a result, it calculates the value for the ALL member as an
aggregation of the fact data. The value divides equally among all the descendants. Figure
12.20 shows the steps that the recursion resolution algorithm went through to resolve the
value for M2 . Solid lines indicate steps that it actually performed; dashed lines indicate
steps that it might have happened, but did not because of recursion resolution.