Essbase Using Two-Pass Calculation

Using Two-Pass Calculation

You can improve performance significantly by tagging an accounts dimension member as two-pass in the database outline, if it is appropriate for the application. 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.

Use these sections to understand more about two-pass calculation. Decide whether you can tag an accounts dimension member as two-pass to improve performance, or whether you must use a calculation script to calculate a formula twice. This section also provides information about how to enable two-pass calculation or create a calculation script for two-pass calculation.

Understanding Two-Pass Calculation

You can use a two-pass calculation on member formulas that must be calculated twice to produce the correct value.

Whenever possible, Essbase calculates two-pass formulas at the data block level, calculating the two-pass formulas at the same time as the main calculation. Thus, Essbase need not do an extra calculation pass through the database. However, in some situations, Essbase needs an extra calculation pass through the database.

How Essbase calculates the two-pass formulas depends on whether there is a dimension tagged as time as well as a dimension tagged as accounts and on the dense-sparse configuration of the time and account dimensions.

Reviewing a Two-Pass Calculation Example

Consider this calculation required for Profit%:

Profit % = Profit % Sales

Assume that the following table 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 setting is turned off (the default).

Data values have been loaded into the input cells. Essbase calculates the shaded cells. The numbers in bold show the calculation order for the cells. Cells with multiple consolidation paths are darkly shaded.

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

Essbase uses this calculation order:

  1. Essbase calculates the formula Profit % Sales for Profit % -> Jan, Profit % -> Feb, Profit % -> Mar, and Profit % -> Qtr1 (1, 2, 3, 4 above).
  2. Essbase calculates Profit -> Qtr1 and Sales -> Qtr1 by adding the values for Jan, Feb, and Mar (5, 6 above).
  3. 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, not the correct result.
    Measures/Year Jan Feb Mar Qtr1
    Profit 75 50 120 245 (5)
    Sales 150 200 240 590 (6)
    Profit% 50% (1) 25% (2) 50% (3) 0% (4)

    125% (7)

  4. If you tag Profit % as two-pass in the database outline, Essbase uses the Profit % Sales formula to recalculate the Profit % values and produce the correct results.
    Measures/Year Jan Feb Mar Qtr1
    Profit 75 50 120 245 (5)
    Sales 150 200 240 590 (6)
    Profit% 50% (1) 25% (2) 50% (3) 0% (4)

    125% (7)

    42% (8)