1. Database Administrator's Guide for Oracle Essbase
  2. Block Storage Calculation Optimization
  3. Bottom-Up and Top-Down Calculation

Bottom-Up and Top-Down Calculation

In an Essbase block storage cube, top-down calculations are less efficient than bottom-up calcs, because more blocks are calculated than is necessary. Top-down calculations may be necessary in some cases to ensure that calculation results are correct.

Essbase performs a full calculation of a database outline using either bottom-up calculation or top-down calculation. By default, Essbase uses bottom-up calculation in block storage cubes that are not hybrid mode.

Note:

If your cube uses hybrid mode, which is the default in Essbase 21c, you can skip the information in this topic. Hybrid mode cubes are already optimized for dynamic dependency analysis. To learn more, refer to Adopt Hybrid Mode for Fast Analytic Processing.

For a bottom-up calculation, Essbase determines which data blocks must be calculated before it calculates the cube. 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 outline contains a complex member formula, Essbase performs a top-down calculation for the relevant member.

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 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 Complex Essbase Member 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:

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.