Two-Pass Calculation

Two-pass calculation can be applicable for member formulas that must be calculated twice to produce the correct value. You can tag an accounts dimension member as two-pass to improve performance for some block storage Essbase applications.

Note:

Do not use two-pass calculation with hybrid mode cubes. Only use solve order.

For some applications, you may be able to improve performance by tagging an accounts dimension member as two-pass in the outline. However, the combination of data and calculation needs may require the use of a calculation script to calculate a formula twice, instead of two-pass tagging, to preserve accuracy.

Whenever possible, Essbase calculates two-pass formulas at the block level, simultaneously with the main calculation. In some situations, however, an extra calculation pass is needed.

How Essbase should calculate two-pass formulas depends on the dense-sparse configuration of the time and accounts dimensions.

Two-Pass Calculation Example

Consider this calculation required for Profit%:

Profit % = Profit % Sales

Assume that the table below shows a subset of a data block with Measures and Year as dense dimensions. Measures is tagged as accounts, and Year is tagged as time. The AGGMISSG configuration setting is turned off (the default).

Data values have been loaded into the input cells. Essbase calculates the cells in which the numbers 1 through 7 appear, in that order. For example, Profit % -> Jan is calculated first; Profit% -> Qtr1 has multiple consolidation paths.

Table 35-2 Two-Pass Calculation Example: Data and Calculation Order

Measures -> Year	Jan	Feb	Mar	Qtr1
Profit	75	50	120	5
Sales	150	200	240	6
Profit%	1	2	3	4, 7

Note:

For information on how cell calculation order depends on cube configuration, see Cell Calculation Order.

Essbase uses this calculation order:

Essbase calculates the formula Profit % Sales for Profit % -> Jan, Profit % -> Feb, Profit % -> Mar, and Profit % -> Qtr1 (1, 2, 3, 4 above).
Essbase calculates Profit -> Qtr1 and Sales -> Qtr1 by adding the values for Jan, Feb, and Mar (5, 6 above).

Essbase calculates Profit % -> Qtr1 by adding the values for Profit % -> Jan, Profit % -> Feb, and Profit % -> Mar (7 above). This addition of percentages produces the value 125%, which is not the correct result.

Table 35-3 Two-Pass Calculation Example: Incorrect Results

Measures/Year	Jan	Feb	Mar	Qtr1
Profit%	50% (1)	25% (2)	50% (3)	0% (4) 125% (7)

Measures/Year

Jan

Feb

Mar

Qtr1

Profit

120

245 (5)

Sales

150

200

240

590 (6)

Profit%

50% (1)

25% (2)

50% (3)

0% (4)

125% (7)

If you tag Profit% as two-pass in the outline, Essbase uses the Profit % Sales formula to recalculate the Profit% values and produce the correct results.

Table 35-4 Two-Pass Calculation Example: Correct Results

Measures/Year	Jan	Feb	Mar	Qtr1
Profit%	50% (1)	25% (2)	50% (3)	0% (4) 125% (7) 42% (8)

Measures/Year

Jan

Feb

Mar

Qtr1

Profit

120

245 (5)

Sales

150

200

240

590 (6)

Profit%

50% (1)

25% (2)

50% (3)

0% (4)

125% (7)

42% (8)

For information about multiple calculation passes, see Calculation Passes.

Interaction of Two-Pass and Intelligent Calculation

If you are using Intelligent Calculation and two pass calculation in a block storage application, use the scenario that matches the configuration of the cube, to ensure that Essbase accurately calculates the two-pass formulas.

Note:

Do not use two-pass calculation with hybrid mode cubes. Only use solve order.

Scenario A: Two Pass Tag

In this scenario, you place formulas in the outline and, as appropriate, tag specific formulas as two-pass for best performance.

No Extra Calculation Pass for Two-Pass Formulas

Because Essbase calculates the two-pass formulas while it is calculating the data block, Essbase need not do an extra calculation pass.

All Data Blocks Marked As Clean

After the calculation, all data blocks are marked as clean for the purposes of Intelligent Calculation.

When you tag a member formula as two-pass in the outline, Essbase does the two-pass calculation while each data block is being calculated. However, when you repeat a formula in a calculation script, Essbase must read the data blocks and write them to memory to recalculate the formula.

Scenario B: Calculation Script

In this scenario, you create a calculation script to perform the formula calculation for best performance.

Extra Calculation Pass for Two-Pass Formulas

Essbase calculates the cube and then does an extra calculation pass to calculate the two-pass formulas. Even though all data blocks are marked as clean after the first calculation, Essbase ignores the clean status on the blocks that are relevant to the two-pass formula and recalculates these blocks.

Data Blocks for Two-pass Formulas Not Marked As Clean

After the first calculation, Essbase has marked all data blocks as clean. In a second calculation pass, Essbase recalculates the required data blocks for the two-pass formulas. However, because the second calculation is a partial calculation, Essbase does not mark the recalculated blocks as clean. When you recalculate with Intelligent Calculation turned on, these data blocks may be recalculated unnecessarily.

If the cube configuration allows Essbase to use Scenario B, consider using a calculation script to perform two-pass formula calculations. If you use a calculation script, Essbase still does an extra calculation pass through the database; however, you can ensure that Essbase has marked all the data blocks as clean after the calculation. See Calculation Scripts for Two-Pass and Intelligent Calculation.

Choosing Two-Pass Calculation Tag or Calculation Script Method

Although tagging an accounts member as two-pass may bring performance benefits, some applications cannot use this method. Check these qualifications to see whether you should apply a two-pass tag or create a calculation script that performs a calculation twice for best performance and accuracy:

You can tag a member as two-pass if it is in a dimension tagged as accounts. When you perform a default calculation on the database, Essbase automatically recalculates any formulas tagged as two-pass if they are in the dimension tagged as accounts.
You can tag a member as two-pass if it is a Dynamic Calc member of any dimension.
You may need to use a calculation script to calculate a two-pass formula to obtain accurate results, even if the two-pass tag would provide performance benefits. See Calculation Scripts for Two-Pass and Intelligent Calculation.
You must use a calculation script to calculate a formula twice if the database configuration means that Essbase uses Scenario A, and if the formula references values from another data block.
You may want to use a calculation script to calculate two-pass formulas if the database configuration means that Essbase uses Scenario B.

Two-Pass on Default Calculations

A cube setting is available to enable two-pass calculation in default calculations. When you perform a default calculation on a cube with two-pass calculation enabled, Essbase automatically attempts to calculate formulas tagged as two-pass in the dimension tagged as accounts. This is true even if you have customized the default calculation script.

Note:

Do not use two-pass calculation with hybrid mode cubes. Only use solve order.

See these topics:

To perform a default calculation, you can use the execute calculation MaxL statement.

To enable two-pass calculation, you can use the alter database MaxL statement.

Calculation Scripts for Two-Pass and Intelligent Calculation

When designing Essbase calculation scripts to perform two-pass calculations with Intelligent Calculation, learn how to use SET UPDATECALC, SET CLEARUPDATESTATUS, and CALC TWOPASS to optimize the calculations.

Note:

Do not use two-pass calculation with hybrid mode cubes. Only use solve order.

If your outline does use two-pass formulas, follow these guidelines to make the calculation as accurate and fast as possible:

Before a command that recalculates a two-pass formula, add the SET UPDATECALC OFF command to disable Intelligent Calculation. If you have Intelligent Calculation enabled (the default), Essbase calculates only the data blocks that are not marked as clean, but when you perform a default calculation with Intelligent Calculation enabled, all data blocks are marked as clean, so Essbase does not perform the two-pass formula recalculation.
Essbase does not automatically recalculate two-pass formulas in calc scripts, so you must use the CALC TWOPASS command.
If you have changed the default calculation of CALC ALL, and Intelligent Calculation is enabled, the data blocks may not be marked as clean after the first calculation. See Intelligent Calculation for Block Storage Cubes.

To obtain the performance benefits of Intelligent Calculation when performing the first, full calculation of the database, use one of the methods shown in the following series of examples. Your best method depends on the calculation needs and outline structure of your cube.

In the examples that follow, assume the outline has a dimension tagged as accounts, and it is a dense dimension. You want to calculate sales for each product as a percentage of sales for all products. Assume this formula should calculate the dimension:

Sales % Sales -> Product

When Essbase calculates the data block for each product, it has not yet calculated the value Sales -> Product, so the results for the sales of each product as a percentage of total sales are incorrect.

Intelligent Calculation with a Large Index

If the cube index is large, and you want to use Intelligent Calculation, you can use any of the following options for the best performance.

Calculation Script Option

Use this model to create a calculation script that performs a full calculation with Intelligent Calculation enabled:

SET UPDATECALC ON;

CALC ALL;
SET UPDATECALC OFF;
SET CLEARUPDATESTATUS AFTER;
"Share of Sales" = Sales % Sales -> Product;

Calculation Script and Two-Pass Option

To tag a member as two-pass, and use a calculation script to calculate first the full cube, then the two-pass member:

Place a formula in the outline and tag it as two-pass.
Place the formula on the appropriate member in the dimension tagged as accounts (in our example, Share of Sales).
Create a calculation script that performs a full calculation and then a two-pass calculation:
```
SET UPDATECALC ON;
CALC ALL;
SET UPDATECALC OFF;
SET CLEARUPDATESTATUS AFTER;
CALC TWOPASS;
```

Client and Calculation Script Option

To perform a default calculation from a client and then use a calculation script to perform the formula calculation:

Enable Intelligent Calculation, if this default has been changed.
Perform a full calculation.

Use a calculation script similar to this example to disable Intelligent Calculation and calculate the formula:

SET UPDATECALC OFF;
SET CLEARUPDATESTATUS AFTER;
"Share of Sales" = Sales % Sales -> Product;

SET UPDATECALC OFF;
SET CLEARUPDATESTATUS AFTER;
CALC TWOPASS;

All the example options above perform these tasks:

Enable Intelligent Calculation.
Calculate the full cube and mark the data blocks as clean.
Disable Intelligent Calculation.
Mark the recalculated blocks as clean, even though this calculation is a partial calculation of the cube. If you do not use the command SET CLEARUPDATESTATUS AFTER, Essbase marks data blocks as clean only after a full calculation.
Essbase cycles through the cube, calculating only the formula for the relevant member (Share of Sales in our example), or calculating all formulas tagged as two-pass in the outline.

Example: Intelligent Calculation with a Small Index

To use Intelligent Calculation when the cube index is small:

Create a calculation script to calculate the cube, but tell Essbase not to mark the calculated data blocks as clean.
Mark all data blocks as clean and do not recalculate the data blocks.
```
SET CLEARUPDATESTATUS OFF;
CALC ALL;
CALC TWOPASS;
SET CLEARUPDATESTATUS ONLY;
CALC ALL;
```
1. SET CLEARUPDATESTATUS OFF tells Essbase not to mark the calculated data blocks as clean.
2. The first CALC ALL causes Essbase to cycle through the cube, calculating all dirty data blocks. Essbase does not mark the calculated blocks as clean.
  
  Essbase does not automatically recalculate the formulas tagged as two-pass.
3. CALC TWOPASS causes Essbase to cycle through the cube, recalculating the two-pass formulas in the accounts dimension. Essbase recalculates the formulas because the required data blocks were not marked as clean by the previous CALC ALL. Essbase does not mark the recalculated data blocks as clean.
4. SET CLEARUPDATESTATUS ONLY tells Essbase to mark the data blocks as clean, but not to calculate the data blocks (this command disables calculation).
5. The last CALC ALL causes Essbase to cycle through the cube and mark all the data blocks as clean. Essbase does not calculate the data blocks.

Example: Intelligent Calculation Turned Off for a Two-Pass Formula

To turn Intelligent Calculation off for a Two-Pass formula, create a calculation script that performs these tasks:

Disables Intelligent Calculation.
Performs a full calculation.

Repeats the following two-pass formula:

SET UPDATECALC OFF;
CALC ALL;
"Share of Sales" = Sales % Sales -> Product;