Calculation Order and Solve Order in ASO Cubes

Calculation order for Essbase aggregate storage (ASO) cubes differs from that of block storage cubes. Stored hierarchies and attribute dimensions are consolidated, and then dynamic hierarchies are evaluated based on a defined solve order.

For aggregate storage cubes, Essbase calculates data in the following order:

1. Aggregates members of stored hierarchies and attribute dimensions. The order in which members and dimensions are aggregated is optimized internally and changes according to the nature of the cube outline and the existing aggregations. Because the aggregation is additive, the order in which Essbase aggregates the dimensions and members does not affect the results.

   Because the internal aggregation order for an aggregate storage cube is not predictable, any inherent rounding errors are also not predictable. These rounding errors are expected behavior in computer calculation and are extremely small in relation to the data values concerned.

2. Calculates dynamic hierarchy dimension members and formulas. The order in which members and formulas are evaluated is defined by the solve order property, which you can set for each member or dimension. Calculation order may affect calculation results.

Solve Order Property

The concept of solve order applies to query execution. When a cell is evaluated in a multidimensional query, the order in which the calculations should be resolved may be ambiguous. To remove ambiguity, you can use the solve order property to specify the required calculation priority.

Note:

It is good practice to specify the solve order by setting the solve order property at the member or dimension level. Members without formulas that do not have a specified solve order inherit the solve order of their dimension. Members with formulas that do not have a specified solve order have a solve order of zero.

To change the solve order, use the outline editor in the Essbase web interface, or use Smart View. To specify the solve order for a calculated member, you can use the solve_order parameter in the With Section of an MDX query.

The value of the solve order property determines the priority with which Essbase calculates the formulas. The formulas on the members that have a specified solve order are calculated in order from the lowest solve order to the highest. You can specify a solve order between 0 and 127. The default is 0.

You can specify the solve order at the member level or at the dimension level. Essbase uses the following information to define calculation precedence:

1. Member solve order

2. Dimension solve order (members without formulas for which you do not specify a member solve order inherit the solve order of their dimension. Members with formulas for which you do not specify a member solve order have a solve order of zero.)

   If multiple members have the same solve order, the members are evaluated in the reverse order in which their dimensions occur in the outline. The member that occurs later in the outline takes precedence.

   The tie situation calculation order is different for calculated members defined in an MDX query for block storage cubes.

   Note:

   When a member formula is dependent on the value of another member, the member with the formula must have a higher solve order than the member or members on which it depends. For example, in the ASOSamp.Basic outline, Avg Units/Transaction depends on the value of Units and of Transactions. Avg Units/Transaction must have a higher solve order than Units and Transactions.

Example Using the Solve Order Property

The following example is based on the ASOSamp.Basic cube. To remove ambiguity in query results, the example uses the solve order property to specify the required calculation priority.

The spreadsheet query shown below retrieves data for the number of units sold and the number of transactions for January of the current year and for January of the previous year. The Variance member shows the difference between the current year and the previous year. The Avg Units/Transaction member shows a ratio of the number of units sold per transaction.

Figure 38-4 Results from Spreadsheet Query of ASOSamp.Basic cube Showing the Variance Between Two Ratios (C12)

The following image shows the cube outline for these members, and the formulas applied to the Variance and Avg Units/Transaction members.

Figure 38-5 ASOSamp.Basic Cube Showing the Measures, Years, and Time Dimensions

When calculating the variance of the average units per transaction (cell C12 in the spreadsheet example), the result could be the variance between the two ratios, or the result could be the ratio of the two variances. The result depends on whether Essbase gives precedence to the formula on Variance or the formula on Avg Units/Transaction.

The value of the solve order property, which is attached to the members in the outline, determines the priority with which Essbase evaluates the formulas. The higher the solve order setting, the later in the order the member is calculated. For example, a formula with a solve order of 1 is solved before a member with a solve order of 2.

In the example, if the Variance member has a lower solve order than the Avg Units/Transaction member, then the formula on the Variance member takes precedence and the result is the variance between two ratios. This is the case in the ASOSamp.Basic cube, because the solve order of the Variance member is 10 and the solve order of the Avg Units/Transaction member is 20. The formula on Variance takes precedence, because the Variance member has the lower solve order. The result for cell C12 of the query in the spreadsheet example is the variance between the two ratios, as shown in the table below:

Table 38-3 Using the Solve Order Property to Specify the Variance Between Two Ratios

Member	Solve Order	Formula	Result of Intersection of Variance and Avg Units/Transaction (cell C12)
Variance	10	Curr Year - Prev Year	Current year average units/transaction - previous year average units/transaction 0.94894382 (cell C6) - 0.954252111 (cell C9) = -0.005308291 (cell C12)
Avg Units/Transaction	20	Units/Transactions

Alternatively, if you change the ASOSamp.Basic cube, and you give the Avg Units/Transaction member a lower solve order than the Variance member, then the formula on the Avg Units/Transaction member takes precedence, and the result is the ratio of two variances, as shown in the table and spreadsheet example below:

Table 38-4 Using the Solve Order Property to Specify the Ratio of Two Variances

Member	Solve Order	Formula	Result of Intersection of Variance and Avg Units/Transaction (cell C12)
Variance	20	Curr Year - Prev Year	Variance (current year to previous year) of units / variance of transactions 10585 (cell C10) / 11340 (cell C11) = 0.933421517 (cell C12)
Avg Units/Transaction	10	Units/Transactions

Figure 38-6 Results from Spreadsheet Query of ASOSamp.Basic Cube Showing the Ratio of Two Variances (C12)