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

[Time].[Time].[Month] ON ROWS
FROM [Warehouse and Sales]
After executing this query, we get the same result (shown in Figure 12.12) we had before
with a static set. A filter set uses the default member of the Time dimension (member All ),
and not the current member (the current month that corresponds to each individual cell).
Order of Execution for Cube Calculations
To get the results you expect from cube calculation, you must pay attention to the order
Analysis Services uses to calculate a cell value when more than one calculation (assign-
ment operator or calculated member) applies to the cell. Cube data is loaded into Analysis
Services in multiple stages, called passes . First (at pass 0), the cube's logical space is not
calculated. Only leaf members are loaded from the fact data; the values of nonleaf cells are
null. After fact data is loaded (at pass 0.5), Analysis Services loads the values of the cells
associated with measures that have one of the following aggregate functions: SUM , COUNT ,
MIN , MAX , and DISTINCT COUNT . At this stage, cells associated with semi-additive measures
are not populated and dimension and cube calculations are not applied.
Analysis Services populates the logical space of the cube when it executes the Calculate
command. The Calculate command usually is the first command encountered in an
MDX script, but if a cube does not have an MDX script, Analysis Services generates a
Calculate command.
NOTE
When a cube has an MDX script, it should have a Calculate command that populates
the nonleaf cells with data.
The Calculate command has very simple syntax:
Calculate;
When we use the word populate , we do not mean that every cell of the cube is calculated.
The cell values and other cell properties will be calculated when the query requests a
specific cell (unless retrieved from the cache). Populate means that all the data structures
are prepared and when the client application eventually requests the cell, the structures
will be ready.
During execution of the Calculate command, the cells associated with semi-additive
measures are populated; unary operators and custom member formulas are applied. For
information about semi-additive measures, unary operators, and custom member formu-
las, see Chapter 13.
Each operator in an MDX script, including the Calculate command, creates a new pass.
We will show how passes work in a simple cube with a single dimension, D1 , which has
four members: ALL , M1 , M2 , and M3 . The cube contains the following MDX script: