1500 characters remaining

Understanding Pass Order and Solve Order (MDX)

SQL Server 2008

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 Analysis Services 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.

Calculation Precedence

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

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
```