Understanding Pass Order and Solve Order (MDX)
When a cube is calculated as the result of an MDX script, it can go through many stages of computation depending on the use of various calculation-related features. Each of these stages is referred to as a calculation pass.
A calculation pass can be referred to by an ordinal position, called the calculation pass number. The count of calculation passes that are required to fully compute all the cells of a cube is referred to as the calculation pass depth of the cube.
Fact table and writeback data only impact pass 0. Scripts populate data after pass 0; each assignment and calculate statement in a script creates a new pass. Outside the MDX script, references to absolute pass 0 refer to the last pass created by the script for the cube.
Calculated members are created at all passes, but the expression is applied at the current pass. Prior passes contain the calculated measure, but with a null value.
Solve Order
Solve order determines the priority of calculation in the event of competing expressions. Within a single pass, solve order determines two things:
- The order in which Microsoft SQL Server 2005 Analysis Services (SSAS) evaluates dimensions, members, calculated members, custom rollups, and calculated cells.
- The order in which Analysis Services calculates custom members, calculated members, custom rollup, and calculated cells.
The member with the highest solve order takes precedence.
Note
The exception to this precedence is the Aggregate function. Calculated members with the Aggregate function have a lower solve order than any intersecting calculated measure.
Solve Order Values and Precedence
Solve order values can range from -8181 to 65535. In this range, some solve order values correspond to specific kinds of calculations, as shown in the following table.
| Calculation | Solve Order |
|---|---|
Custom member formulas |
-5119 |
Unary operators |
-5119 |
Visual totals calculation |
-4096 |
All other calculations (if not otherwise specified) |
0 |
It is highly recommended that you use only positive integers when setting solve order values. If you assign values that are lower than the solve order values shown in the previous table, the calculation pass can become unpredictable. For example, the calculation for a calculated member receives a solve order value that is lower than the default custom rollup formula value of -5119. Such a low solve order value causes the calculated members to be calculated before the custom rollup formulas, and can produce incorrect results.
Creating and Changing Solve Order
In Cube Designer, on the Calculations Pane, you can change the solve order for calculated members and calculated cells by changing the order of the calculations.
In MDX, you can use the SOLVE_ORDER keyword to create or change calculated members and calculated cells.
Solve Order Examples
To illustrate the potential complexities of solve order, the following series of MDX queries starts with two queries that each individually have no solve order issues. These two queries are then combined into a query that requires solve order.
Query 1—Differences in Income and Expenses
For the first MDX query, to see the difference in income and expenses for each half of the year, construct a simple MDX query similar to the following example:
WITH
MEMBER [Time].[Year Difference] AS
[Time].[2nd half] - [Time].[1st half]
SELECT
{ [Account].[Income], [Account].[Expenses] } ON COLUMNS,
{ [Time].[1st half], [Time].[2nd half], [Time].[Year Difference] } ON ROWS
FROM Financials
In this query, there is only one calculated member, Year Difference. Because there is only one calculated member, solve order is not an issue, as long as the cube does not use any calculated members.
This MDX query produces a result set similar to the following table.
| Income | Expenses | |
|---|---|---|
1st half |
5000 |
4200 |
2nd half |
8000 |
7000 |
Year Difference |
3000 |
2800 |
Query 2—Percentage of Net Income after Expenses
For the second query, to see the percentage of net income after expenses for each half of the year, use the following MDX query:
WITH
MEMBER [Account].[Net Income] AS
([Account].[Income], [Account].[Expenses]) / [Account].[Income]
SELECT
{ [Account].[Income], [Account].[Expenses], [Account].[Net Income] } ON COLUMNS,
{ [Time].[1st half], [Time].[2nd half] } ON ROWS
FROM Financials
This MDX query, like the previous one, has only a single calculated member, Net Income, and therefore does not have any solve order complications.
This MDX query produces a slightly different result set, similar to the following table.
| Income | Expenses | Net Income | |
|---|---|---|---|
1st half |
5000 |
4200 |
0.16 |
2nd half |
8000 |
7000 |
0.125 |
The difference in result sets between the first query and the second query comes from the difference in placement of the calculated member. In the first query, the calculated member is part of the ROWS axis, not the COLUMNS axis shown in the second query. This difference in placement becomes important in the next query, which combines the two calculated members in a single MDX query.
Query 3—Combined Year Difference and Net Income Calculations
In this final query combining both of the previous examples into a single MDX query, solve order becomes important. To make sure that the calculations occur in the correct sequence, define the sequence in which the calculations occur by using the SOLVE_ORDER keyword.
The SOLVE_ORDER keyword specifies the solve order of calculated members in an MDX query or a CREATE MEMBER command. The integer values used with the SOLVE_ORDER keyword are relative, do not need to start at zero, and do not need to be consecutive. 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, the default value of that calculated member is zero.
For example, if you combine the calculations used in the first two example queries, 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 Analysis Services will evaluate this cell is by the solve order. The formulas that are used to construct this cell will produce different results depending upon the solve order of the two calculated members.
First, try combining the calculations used in the first two queries in the following MDX query:
WITH
MEMBER [Time].[Year Difference] AS
'[Time].[2nd half] - [Time].[1st half],
SOLVE_ORDER = 1
MEMBER [Account].[Net Income] AS
'([Account].[Income] - [Account].[Expenses]) / [Account].[Income]',
SOLVE_ORDER = 2
SELECT
{ [Account].[Income], [Account].[Expenses], [Account].[Net Income] } ON COLUMNS,
{ [Time].[1st half], [Time].[2nd half], [Time].[Year Difference] } ON ROWS
FROM Financials
In this combined MDX query example, Net Income has the highest solve order, so it takes precedence when the two expressions interact. Analysis Services evaluates the cell in question by using the Net Income formula. The results of this nested calculation, as shown in the following table.
| Income | Expenses | Net Income | |
|---|---|---|---|
1st half |
5000 |
4200 |
0.16 |
2nd half |
8000 |
7000 |
0.125 |
Year Difference |
3000 |
2800 |
0.066 |
The result in the shared cell is based on the formula for Net Income. That is, Analysis Services calculates the result in the shared cell 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
However, Analysis Services calculates the result in the shared cell differently if you switch the solve orders for the calculated members in the MDX query. The following combined MDX query reverses the solve order for the calculated members:
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, Analysis Services uses the Year Difference formula to evaluate the cell, as shown in the following table.
| Income | Expenses | Net Income | |
|---|---|---|---|
1st half |
5000 |
4200 |
0.16 |
2nd half |
8000 |
7000 |
0.125 |
Year Difference |
3000 |
2800 |
-0.035 |
Because this query 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
Additional Considerations
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 Analysis Services evaluates an MDX query, Analysis Services takes into account the solve order values for everything involved within a given pass, including the dimensions of the cube specified in the MDX query.
See Also
Reference
CalculationCurrentPass (MDX)
CalculationPassValue (MDX)
CREATE MEMBER Statement (MDX)