Tutorials MSAS
Every cube calculated as the result of a multidimensional Expression Query goes through a number of stages of calculations. Analysis server makes a complete pass of the calculations applicable for a stage and the stage is referred to as a calculation pass. The number of passes required to compute fully all the cells of a cube are referred to as the calculation pass depth of a cube. A cube will have at least two calculation passes with at least one to retrieve the data in the cube. The ordinal position of the pass begins at zero and is referred to as pass 0. All calculated members and custom members are calculated on pass 0. The precedence within this calculation is established by the Solve Order of each calculated member.
If a cube has custom rollup formulas or custom rollup operators another calculation pass is made to handle computations for such calculations. The calculations begin with calculation pass 1 and all other calculations succeeding them are determined by calculated cell definitions. The calculation pass number cannot be changed for custom rollup formulas or custom rollup operators. The formula precedence is handled by solve order.
A cube without calculated cells will have at least two calculation passes. The calculated cells will specify the last calculation pass on which the calculated cell definition is calculated and the number of passes with which the calculated cell definition was used. This provides the user with an ability to create cubes that use two or more calculation passes.
Calculation passes are stored in memory to facilitate reference to previous pass values while calculating formulas. This allows for complex calculations such as speculative analysis.
Analysis server determines the number of passes required to compute the value of a cube by evaluating all the custom members, custom rollups, calculated members and calculated cells. Evaluation begins at the highest calculation pass and ends at the lowest calculation pass. This is determined by the CALCULATION_PASS_NUMBER property. The order is reverse when calculating the calculation passes. Each calculation pass is treated as a nested calculation. The lowest calculation pass is usually the most nested.
When calculations overlap in multiple calculations cubes, the solve order of the overlapping calculations is used to resolve the precedence of solution. It is also applied within each calculation pass where multiple calculations are involved.
The range of Solve order values are -8181 to 65535. It is useful to set positive integers while setting solve orders because solve order for a pass can become unpredictable if the values are used for calculations. For instance, if the custom rollup formula has a negative value, the calculated cells definition is calculated before the custom rollup formula and this causes errors.
When multiple calculations have the same solve order the order of precedence is as under:
Calculated cells take precedence over all other calculations in the case of solve order conflict. If multiple calculations occur within the same category, the declaration order of the calculation is used.
The pass order together with the solve order can make for very complex issues. This is further complicated when cubes contain a number of dimensions, calculated members, custom rollup formulas and calculated cells. When MDX evaluates a MDX query, everything is given a pass for determining the solve order values. When a query containing calculated members is executed against a cube containing calculated members both solve orders are evaluated as if the query were a part of the cube and the query is executed in the context of the cube. This is made more complex by the fact that the solve order of the dimensions of a cube cannot be reviewed. Moreover the solve order for dimensions of a cube can be changed using the cube editor.
In this lesson we have looked at various methods in which calculations are defined in cubes. We have also briefly examined pass order and solve order and their usage in the calculations in cubes. In the next lesson we shall study Virtual cubes and their features.
