1. Database Administrator's Guide for Oracle Essbase
  2. Optimizing Calculations
  3. Using Bottom-Up Calculation

Using Bottom-Up Calculation

A top-down calculation is less efficient than a bottom-up calculation, because more blocks are calculated than is necessary. Although a top-down calculation is less efficient than a bottom-up calculation, top-down calculations are necessary in some cases to ensure that calculation results are correct.

The following topics describe which calculation to use in different situations:

Bottom-Up and Top-Down Calculation

Essbase uses one of two calculation methods to do a full calculation of a database outline—bottom-up calculation or top-down calculation. By default, Essbase does a bottom-up calculation.

For a bottom-up calculation, Essbase determines which data blocks must be calculated before it calculates the database. Essbase then calculates only the blocks that must be calculated. The calculation begins with the existing block with the lowest block number and works up through each block in number order until the existing block with the highest block number is reached. See Block Calculation Order.

If the database outline contains a complex member formula, Essbase performs a top-down calculation for the relevant member.

Use the following information to learn more about simple and complex formula interactions with bottom-up and top-down calculation:

Bottom-Up Calculations and Simple Formulas

For simple formulas, Essbase does a bottom-up calculation to determine which blocks must be calculated before running the full calculation. For example, for a simple formula on a member (such as A = B + C), A is calculated only if B or C exists in the database. That is, the dependency of the formula on B and C is known before the calculation is started.

Top-Down Calculations and Complex Formulas

Before starting a calculation, Essbase searches the database outline and marks complex formulas that require top-down calculation; for example, a member formula that contains a cross-dimensional reference. When Essbase reaches a member with a top-down formula, it does a top-down calculation for the member.

When a formula on a member is complex, all possible blocks for the member must be examined to see if an existing block must be changed or a new block created; it is difficult to determine the dependency that blocks have on other blocks before the start of the calculation. The top-down method slows calculation performance because Essbase must search for appropriate blocks to calculate to execute the formula.

When a formula is compiled, if the formula is to be calculated top-down, Essbase logs a message in the application log file.

Consider the following complex formula:

A = B -> D + C -> D

To calculate the formula, Essbase must examine every combination of A to see whether B -> D or C -> D exists.

See Using Complex Formulas.

Forcing a Bottom-Up Calculation

If it is appropriate for the site, you can force a bottom-up calculation on a top-down formula.

To force a bottom-up calculation, use the use one of the following methods:

  • Calculation function—@CALCMODE in a formula

  • Calculation script command—SET FRMLBOTTOMUP

  • Configuration settings:

    • CALCOPTFRMLBOTTOMUP

    • CALCMODE

Forcing a bottom-up calculation on a formula usually increases performance time. If the formula contains complex functions (for example, range functions) or if the formula's dependencies are not straightforward, a bottom-up calculation may produce results different from those of a top-down calculation.

Caution:

Before changing the setting CALCOPTFRMLBOTTOMUP or using the calculation script command SET FRMLBOTTOMUP in a production environment, check the validity of calculation results by comparing, relative to the same data, the results of a bottom-up calculation and the results of a top-down calculation.