Understanding Pass Order and Solve Order (MDX)

Calculation precedence defines the order in which values are obtained for a defined expression at the current cell. Calculation precedence is resolved according to the following algorithm:

1. A Calculation List (CL) is created from all calculations at the granularity of the current cell or below

2. Calculations in CL are classified into: Highest Wins (HW) or Closest Wins (CW)

   1. CW are Custom Rollups, Unary Operators, Semi-Additive Measures, Freeze

   2. HW are all other calculations

3. An Ordered Calculation List (OCL) is created from the all the HW ordered by pass

4. For each of the remaining CW

   1. For each calculation (C) in OCL (from highest to lowest)

   2. If CW is closer to the current cell than C, then insert CW in OCL and continue to the next CW

   3. If CW is at a higher pass than C, then insert CW in OCL and continue to the next CW

   4. Continue with next C

5. If the highest calculation in OCL is not at the same granularity of the current cell, then calculate aggregate value using aggregate functions

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.

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.

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.

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.

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.

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.

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