Pass order
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.
Solve Order
Solve order determines the order in which dimensions, members, calculated members, custom rollups and calculated cells are evaluated and the order in which they are calculated. The solve order proceeds from the highest to the lowest level. The highest level is evaluated first. This is similar to other nested operations which proceed from the outermost to the innermost nested levels. The order of calculation, however, is reversed. The lowest nested level is calculated first and then the higher levels until the highest level is reached.
Measures occupy a different status. Though they are treated like dimensions, they are evaluated last and calculated first for solve order purposes.
The solve order for calculated members and calculated cells can be changed using the Cube Editor. The solve order property in the Advanced Tab of the properties pane allows the user set the solve order for the calculated cells and members. MDX uses the SOLVE_ORDER member property to create or change the solve order of calculated members and cells.
Solve order impacts upon the results generated by the calculations of dimensions and members. For instance two dimensions with different solve orders would produce different results at the point of interaction depending on the way in which the solve order is applied. For instance is Dimension 2 has a higher solve order when compared to dimension 1 then the solve order adopted would be Dimension 2 first and Dimension 1 next. The calculation of values in Dimension 1 will be done first and the values supplied will be used to calculate the values in Dimension 2. If Dimension1 has a higher solve order, the values in Dimension 2 will be first calculated and the result will be applied for calculating the values in Dimension 1.
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
- Custom rollup formulas
- Custom and calculated members
- All other calculations
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.
[catlist id=181].