Understanding Pass Order and Solve Order

Two of the most powerful and, correspondingly, most difficult concepts in Microsoft® SQL Server™ 2000 Analysis Services, solve order and pass order together determine the manner in which a cube is resolved when queries are processed. This topic assumes that you have a basic understanding of cubes, custom members, calculated members, and custom rollups.

Pass Order

When a cube is calculated as the result of a Multidimensional Expressions (MDX) query, it goes through at least one stage of computation, and potentially more stages depending on the use of various calculation-related features, such as custom rollup formulas, custom rollup operators, and calculated cells.

Each stage is referred to as a calculation pass, because the Analysis server makes a complete pass of the calculations applicable for that stage. A calculation pass can be referred to by an ordinal position, called the calculation pass number. The count of calculation passes required to fully compute all the cells of a cube is referred to as the calculation pass depth of the cube.

A cube always has one calculation pass, which retrieves data stored for the cube. Because the ordinal position of the pass number begins at zero, this is always referred to as calculation pass 0. All calculated members and custom members are also calculated on pass 0, and every calculation pass thereafter, with formula precedence within this calculation pass established by the solve order of each calculated member. No other actions are allowed for this calculation pass; calculated cells cannot have calculation pass 0 assigned to their calculation pass number.

If a cube has custom rollup formulas or custom rollup operators, a second calculation pass is performed to handle the computations needed to calculate these features. These features are calculated starting at calculation pass 1, and for every calculation pass thereafter as determined by calculated cells definitions. The calculation pass number cannot be changed for custom rollup formulas or custom rollup operators, because they are calculated on each calculation pass, with formula precedence handled by solve order. However, calculated cells can have calculation pass 1 assigned to their calculation pass number, described in more detail later in this topic.

A cube without calculated cells will have at most two calculation passes. Calculated cells, however, can specify the last calculation pass number on which the calculated cells definition is calculated, and how many passes with which the calculated cells definition is used, providing the ability to create cubes that use two or more calculation passes.

Calculated cells can specify the calculation pass number by using the Calculation Pass Number property on the Advanced tab of Cube Editor, or by using the CALCULATION_PASS_NUMBER property in MDX statements. Additionally, recursive calculation is allowed by specifying the number of calculation passes to which the calculation formula is recursively applied to the calculation subcube. This feature, accessed through the Calculation Pass Depth property on the Advanced tab of Cube Editor or by using the CALCULATION_PASS_DEPTH property in MDX statements, can allow highly complex calculations, such as goal-seeking equations, to be employed in a cube. The calculation pass number determines the calculation pass at which evaluation starts and calculation finishes for a calculated cells definition. The calculation pass depth determines how many calculation passes are required to fully compute a calculated cells definition. Only calculated cells will have a calculation pass number higher than 1.

The number of the inclusive range of calculation passes required to fully compute calculated cells can be defined by the formula CALCULATION_PASS_NUMBER to (CALCULATION_PASS_NUMBER - CALCULATION_PASS_DEPTH) + 1, using the cell properties CALCULATION_PASS_NUMBER and CALCULATION_PASS_DEPTH of the calculated cells definition. In other words, if a calculated cells definition has a CALCULATION_PASS_NUMBER of 4 and a CALCULATION_PASS_DEPTH of 3, the calculated cells definition is evaluated in calculation passes 4, 3, and 2, then calculated in calculation passes 2, 3, and 4.

All calculation passes are retained in memory, to facilitate references to previous pass values in calculation formulas. This ability to refer to previous pass values for a given cell allows for complex calculations, such as speculative analysis and goal-seeking formulas, with an increase in performance.

The number of calculation passes required to fully compute all of the cells of a cube is determined by first evaluating all of the custom members, custom rollups, calculated members, and calculated cells. Evaluation is done from highest calculation pass to lowest calculation pass, determined by the CALCULATION_PASS_NUMBER property, in order to accurately determine formula precedence across calculation passes. The order is then reversed when calculating the calculation passes, by calculating from lowest to highest. Essentially, each calculation pass is treated as a nested calculation, with the lowest calculation pass being the most nested.

The following table illustrates the effects of calculation pass number and calculation pass depth on a sample cube. The sample cube contains four calculations:

• A calculated member, shaded in dark gray, with a SOLVE_ORDER of 1.
  
  
• A custom rollup formula, shaded in dark blue, with a SOLVE_ORDER of 2.
  
  
• A calculated cells definition, shaded in green, with a CALCULATION_PASS_NUMBER of 2, a CALCULATION_PASS_DEPTH of 1, and a SOLVE_ORDER of 1.
  
  
• A calculated cells definition, shaded in red, with a CALCULATION_PASS_NUMBER of 3, a CALCULATION_PASS_DEPTH of 2, and a SOLVE_ORDER of 2.

Pass diagram

Pass description

Calculation Pass 3 Because the CALCULATION_PASS_DEPTH of the calculated cells definition shaded with red is 2, the cells are recursively calculated again, using the values derived from the previous calculation pass. The calculated member and custom rollup formula are also calculated again on this pass. Calculation Pass 2 The calculations for both calculated cells definitions start here, based on the evaluation of CALCULATION_PASS_NUMBER and CALCULATION_PASS_DEPTH. The calculated member and custom rollup formula are also calculated again on this pass. Calculation Pass 1 All custom rollup formulas and custom rollup operators start calculation on pass 1. Calculation Pass 0 Data is retrieved from sources. Calculated and custom members are calculated. No other calculations can be performed at this point.

Color-coded arrows show evaluation and calculation order for the various calculations in the diagram shown in the previous table. In cubes with multiple calculations, some of the calculations can overlap. When this occurs, the solve order of the overlapping calculations is used to resolve formula precedence, but only within a given pass. If a solve order is specified for a calculated cells definition that encompasses multiple passes (that is, the CALCULATION_PASS_DEPTH is greater than 1), the solve order is applied to each pass to resolve formula precedence. Because solve order is applied on each calculation pass, overlapping calculated cells definitions can generate different values for cells that may be involved in custom rollup or calculated member resolution. A more detailed discussion of solve order is given later in this topic.

Recursive calculations and goal-seeking calculations can make use of values obtained in previous passes through the use of the CalculationPassValue and CalculationCurrentPass functions in MDX. The CalculationCurrentPass function provides the current calculation pass number, and the CalculationPassValue, given an MDX expression and a calculation pass number, evaluates the MDX expression within the specified calculation pass number and returns the result.

Solve Order

Within a single pass, solve order determines two things: the order in which dimensions, members, calculated members, custom rollups, and calculated cells are evaluated, and the order in which they are calculated. The member with the highest solve order is evaluated first, but calculated last. This is similar in behavior to any other nested operation: the outermost operation cannot complete until the innermost operation is completed, but the outermost operation is evaluated first in order to determine that the innermost operation must be completed before the outermost can be completed. The lower the solve order, the more nested the member in terms of evaluation and calculation, with the member having the highest solve order occupying the outermost position.

In cubes with dimensions that contain custom members, custom rollup formulas, calculated members, and calculated cells, the solve order determines the order in which various calculations are evaluated. The highest solve order is always evaluated first, then the next highest, and so on.

The order in which they are calculated, however, is reversed. The lowest solve order is calculated first, then the next lowest, and so on. Solve order essentially nests formulas, and as with any nested formula, the outermost formulas are evaluated first, but calculated last. The innermost formulas are evaluated last, but calculated first, because the outermost formulas may depend on the values produced by the innermost formulas for their calculation.

Although measures are usually treated as another dimension, they are always evaluated last and calculated first for solve order purposes. In other words, measures are always treated as having the lowest possible solve order.

In Cube Editor, the solve order for calculated members and calculated cells can be changed by altering the Solve Order property in the Advanced tab of the properties pane. The solve order for dimensions can be changed by reordering the positions of the dimensions within the tree pane. For more information about using Cube Editor, see Cube Editor - Schema View.

In MDX the SOLVE_ORDER member property can be used when creating or changing calculated members and calculated cells

Solve order directly affects the results generated by the calculation of dimensions and members in this fashion. The following diagram describes the behavior of two dimensions, each with a calculated member, that intersect at a cell. Two examples are presented in the following diagram, with different solve orders.

In the first example, Dimension 2 has a higher solve order. So, the intersection is evaluated using the formula for Dimension 2. However, in order to provide data for this formula, the calculated member in Dimension 1 must be evaluated and calculated. So, the formula for Dimension 1 is calculated to provide the values needed to calculate the formula for Dimension 2. Then, the formula for Dimension 2 is calculated and the result is placed in the cell.

In the second example, Dimension 1 has a higher solve order. The cell at the intersection is evaluated using the formula for Dimension 1. As Dimension 2 has a lower solve order, it is evaluated next, then calculated first and the values provided to the formula for Dimension 1. Then, the formula for Dimension 1 is calculated and the result is placed in the cell.

To further demonstrate the potential complexities of solve order, a series of example MDX queries is presented here.

For the first example, you are interested in seeing the difference in income and expenses for each half of the year. You would then construct a simple MDX query similar to the following example:

WITH
MEMBER [Time].[Year Difference] AS
   '[Time].[2nd half] - [Time].[1st half]
SELECT 
   { [Money].[Income], [Money].[Expenses] } ON COLUMNS,
   { [Time].[1st half], [Time].[2nd half], [Time].[Year Difference] } ON ROWS
FROM TestCube

This MDX query would produce a result set similar to the following table, with the calculated member shaded.

For this query, solve order is not an issue, assuming the cube does not use any calculated members, because there is only one calculated member in the query.

Now, for the second example, you are interested in seeing the percentage of net income after expenses for each half of the year, using the following MDX query:

WITH
MEMBER [Money].[Net Income] AS
   '([Money].[Income]  [Money].[Expenses]) / [Money].[Income]'
SELECT
   { [Money].[Income], [Money].[Expenses], [Money].[Net Income] } ON COLUMNS,
   { [Time].[1st half], [Time].[2nd half] } ON ROWS
FROM TestCube

This MDX query would produce a slightly different result set, similar to the following table, with the calculated member shaded.

This MDX query, like the previous one, does not have any solve order complications, because it also has only a single calculated member. Notice the placement of the calculated member in the result dataset of this example, as well as in the previous example. The first MDX query example uses a calculated member as part of the ROWS axis dimension, but this query example uses a calculated member as part of the COLUMNS axis dimension. This placement becomes important in the next example, which combines the two calculated members in a single MDX query.

Finally, you decide you want to combine both of the previous examples into a single MDX query. In this case, solve order becomes important. Take, for example, the first attempt at this combination in the following MDX query:

WITH
MEMBER [Time].[Year Difference] AS
   '[Time].[2nd half] - [Time].[1st half],
   SOLVE_ORDER = 1
MEMBER [Money].[Net Income] AS
   '([Money].[Income] - [Money].[Expenses]) / [Money].[Income]',
   SOLVE_ORDER = 2
SELECT
   { [Money].[Income], [Money].[Expenses], [Money].[Net Income] } ON COLUMNS,
   { [Time].[1st half], [Time].[2nd half], [Time].[Year Difference] } ON ROWS
FROM TestCube

The two calculated members, Year Difference and Net Income, intersect at a single cell in the result dataset of the MDX query example. The only way to determine how this cell will be evaluated is by the solve order. The formulas used to construct this cell will produce different results depending upon the solve order of the two calculated members.

The SOLVE_ORDER keyword is be used to specify the solve order of calculated members in an MDX query or the CREATE MEMBER command. If the solve order is not specified, it defaults to zero. In that case, the order of the dimensions in the cube whose context is specified in the MDX query is used to determine the solve order, following the rules listed earlier in this topic. This also applies to calculated members in different dimensions that are assigned the same SOLVE_ORDER value.

The integer values used with the SOLVE_ORDER keyword are relative; the value simply tells MDX to calculate a member based on values derived from calculating members with a higher value. If a calculated member is defined without the SOLVE_ORDER keyword, its default value is zero. The specified values do not need to start at zero, nor do they need to be consecutive.

In the MDX query example, Net Income has the highest solve order, so the cell in question is evaluated using the Net Income formula. But the Net Income formula is calculated last; in order to calculate Net Income, the next calculated member, Year Difference, must be calculated first so that Net Income can use the results of that calculated member to perform its own calculation.

The results of this nested calculation can be viewed in the following table.

As you can see, the result in the shared cell is based on the formula for Net Income; in other words, it was calculated with the Year Difference data, producing the following formula (the result is rounded for clarity):

((8000 - 5000) - (7000 - 4200)) / (8000 - 5000) = 0.066

or

(3000 - 2800) / 3000 = 0.066

The result in the shared cell, however, is calculated differently if the solve orders for the calculated members in the MDX query are switched, as demonstrated here:

WITH
MEMBER [Time].[Year Difference] AS
   '[Time].[2nd half] - [Time].[1st half],
   SOLVE_ORDER = 2
MEMBER [Money].[Net Income] AS
   '([Money].[Income] - [Money].[Expenses]) / [Money].[Income]',
   SOLVE_ORDER = 1
SELECT
   { [Money].[Income], [Money].[Expenses], [Money].[Net Income] } ON COLUMNS,
   { [Time].[1st half], [Time].[2nd half], [Time].[Year Difference] } ON ROWS
FROM TestCube

As the order of the calculated members has been switched, the Year Difference formula is used to evaluate the cell. The Net Income calculated member is resolved first, and then the Year Difference calculated member is resolved, producing a strikingly different result as shown in the following table.

Because it uses the Year Difference formula with the Net Income data, the formula for the shared cell resembles the following calculation:

((8000 - 7000) / 8000) - ((5000 - 4200) / 5000) = -0.035

Or

0.125 - 0.16 = -0.035

Changing Solve Order Values

Solve order values can range from -8181 to 65535. It is highly recommended that you use only positive integers when setting solve order values. Certain calculations reside at specific solve orders, as listed in the following table. The solve order for a pass can become unpredictable if these values are used by other calculations.

For example, changing the solve order for a calculated cells definition below the default custom rollup formula value of -5119 causes the calculated cells definition to be calculated before the custom rollup formulas; this can produce incorrect results.

In the case of multiple calculations having the same solve order, the following formula precedence is used:

1. Calculated cells
   
   
2. Custom rollup formulas
   
   
3. Custom and calculated members
   
   
4. 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. For example, if two calculated cells definitions have the same solve order for the same calculation pass, the declaration order determines which is evaluated first.

Additional Considerations

The combination of pass order and solve order can be a very complex issue to deal with, especially in cubes with a high number of dimensions involving calculated member, custom rollup formulas, or calculated cells. When MDX evaluates an MDX query, the solve order values for everything involved within a given pass, including the dimensions of the cube specified in the MDX query, are taken into account.

When a query with calculated members is executed against a cube with calculated members, for example, the solve orders for both the query and the cube are evaluated as if the query were part of the cube; it is executed within the context of a cube. Because it can be difficult to review the solve order of the dimensions on a cube, it can be challenging to ensure that the solve order for calculated members in a complex MDX query are correctly handled within the context of a cube.

Also, as the solve order for the dimensions on a cube can be changed from Cube Editor, MDX queries can be affected; a once-working MDX query can return unexpected results because the solve order of the cube on which context the MDX query executes is changed.

See Also

CalculationCurrentPass

CalculationPassValue

Cube Editor - Schema View

CREATE MEMBER Statement