1. Database Administrator's Guide for Oracle Essbase
  2. Defining Calculation Order
  3. Calculation Passes

Calculation Passes

Whenever possible, Essbase calculates a database in one calculation pass through the database. In some situations, Essbase must perform multiple calculation passes.

Usually, Essbase calculates a database in one calculation pass through the database. Thus, it reads each of the required data blocks into memory only once, performing all relevant calculations on the data block and saving it. However, in some situations, Essbase may perform multiple calculation passes through a database. On subsequent calculation passes, Essbase brings data blocks back into memory, performs further calculations on them, and saves them again.

When you perform a default, full calculation of a database (CALC ALL), Essbase attempts to calculate the database in one calculation pass. If you have dimensions that are tagged as accounts or time, Essbase may have to do multiple calculation passes through the database.

The following table shows the number of calculation passes Essbase performs if you have dimensions that are tagged as time or accounts, and you have at least one formula on the accounts dimension:

Table 20-11 Calculation Passes For Accounts and Time Dimension

Dimension Tagged As Accounts Dimension Tagged As Time Calculation Passes During each calculation pass, Essbase calculates based on:

Dense or Sparse

None

1

All dimensions

Dense

Dense

1

All dimensions

Dense

Sparse

2

Pass 1: Accounts and time dimensions

Pass 2: Other dimensions

Sparse

Sparse

2

Pass 1: Accounts and time dimensions

Pass 2: Other dimensions

Sparse

Dense

2

Pass 1: Accounts dimension

Pass 2: Other dimensions

If you are using formulas that are tagged as Two-Pass, Essbase may need to do an extra calculation pass to calculate these formulas.

When you use a calculation script to calculate a database, the number of calculation passes Essbase needs to perform depends upon the calculation script.

If the isolation level is set for committed access, and multiple passes are required, Essbase writes data values at the end of each pass. Data retrievals that occur between passes can pick up intermediate values.

When you calculate a database, Essbase automatically displays the calculation order of the dimensions for each pass through the database and tells you how many times Essbase has cycled through the database during the calculation. Essbase displays this information in the ESSCMD shell window and in the application log.

For each data block, Essbase decides whether to do a dense or a sparse calculation. The type of calculation it chooses depends on the type of values within the data block. When you run a default calculation (CALC ALL) on a database, each block is processed in order, according to its block number.

Essbase calculates the blocks using this procedure:

  • If you have Intelligent Calculation turned on, and if the block does not need to be calculated (if it is marked as clean), Essbase skips the block and moves to the next block.

  • If the block needs recalculating, Essbase checks to see if the block is a level 0, an input, or an upper-level block.

  • If the block is a level 0 block or an input block, Essbase performs a dense calculation on the block. Each cell in the block is calculated.

  • If the block is an upper-level block, Essbase either consolidates the values or performs a sparse calculation on the data block.

    The sparse member combination of each upper-level block contains at least one parent member. Essbase consolidates or calculates the block based on the parent member’s dimension. For example, if the upper-level block is for Product -> Florida from the Sample.Basic database, then Essbase chooses the Product dimension.

    If the sparse member combination for the block has multiple parent members, Essbase chooses the last dimension in the calculation order that includes a parent member. For example, if the block is for Product -> East, and you perform a default calculation on the Sample.Basic database, Essbase chooses the Market dimension, which contains East. The Market dimension is last in the default calculation order because it is placed after the Product dimension in the database outline. See Member Calculation Order.

    Based on the chosen sparse dimension, Essbase either consolidates the values or performs a sparse calculation on the data block:

    • If a formula is applied to the data block member on the chosen sparse dimension, Essbase performs a formula calculation on the sparse dimension. Essbase evaluates each cell in the data block. The formula affects only the member on the sparse dimension, so overall calculation performance is not significantly affected.

    • If the chosen sparse dimension is a default consolidation, Essbase consolidates the values, taking the values of the previously calculated child data blocks.