About Intelligent Calculation

By default, when Essbase performs a full calculation of a database, it tracks which data blocks it calculates. If you then load a subset of data, on subsequent calculations, Essbase calculates only the data blocks that have not been calculated and the calculated blocks that require recalculation because of the new data. This process is called Intelligent Calculation.

By default, Intelligent Calculation is turned on.

You can also turn Intelligent Calculation on or off in a calculation script. See Turning Intelligent Calculation On and Off.

For information on other calculation optimization methods, see:

Benefits of Intelligent Calculation

Intelligent Calculation is designed to provide significant calculation performance benefits for these types of calculations:

  • A full calculation of a database (CALC ALL), with some exceptions.

    See Limitations of Intelligent Calculation.

  • A calculation script that calculates all members in one CALC DIM statement.

  • For database calculations that cannot use Intelligent Calculation for the full calculation, you may be able to use Intelligent Calculation for part of the calculation.

    For example, to significantly improve calculation performance for a case in which you calculate a database by doing a default consolidation and then an allocation of data, enable Intelligent Calculation for the default consolidation and then disable Intelligent Calculation for the allocation.

    Assuming that Intelligent Calculation is turned on (the default), create a calculation script to perform these steps for a partial Intelligent Calculation:

    • Enable Intelligent Calculation, if it is disabled

    • Use CALC ALL to calculate the database

    • Use the SET UPDATECALC command to disable Intelligent Calculation

    • Allocate data

    • Optionally, enable Intelligent Calculation again

Intelligent Calculation and Data Block Status

To provide Intelligent Calculation, Essbase checks the status of the data blocks in a database. Data blocks have a calculation status of clean or dirty. Essbase marks a data block as clean after certain calculations.

When Intelligent Calculation is enabled, Essbase calculates only dirty blocks and their dependent parents. When disabled, Essbase calculates all data blocks, regardless of whether they are marked as clean or dirty.

Marking Blocks as Clean

Essbase marks data blocks as clean in these types of calculations:

  • A full calculation (CALC ALL) of a database (the default calculation).

  • A calculation script that calculates all the dimensions in one CALC DIM statement.

    For example, the following calculation script calculates all members in the Sample.Basic database:

    CALC DIM(Measures, Product, Market, Year, Scenario);

    Compare this calculation script to a calculation script that calculates all the members with two CALC DIM statements:

    CALC DIM(Measures, Product);
    CALC DIM(Market, Year, Scenario);

Using two CALC DIM statements causes Essbase to do at least two calculation passes through the database. In this calculation, Essbase does not, by default, mark the data blocks as clean. Because Intelligent Calculation depends on accurate clean and dirty status, you must manage these markers carefully. See Maintaining Clean and Dirty Status.

Essbase marks calculated data blocks as clean only in the situations described above, unless you use the SET CLEARUPDATESTATUS command in a calculation script. See Using the SET CLEARUPDATESTATUS Command.

Marking Blocks as Dirty

Essbase marks a data block as dirty in these situations:

  • Calculating the data block for a partial calculation of the database only if SET CLEARUPDATESTATUS AFTER is not part of the partial calculation statement in the calculation script

  • Loading data into the data block

  • Restructuring the database (for example, by adding a member to a dense dimension)

  • Copying data to the data block; for example, using DATACOPY

Maintaining Clean and Dirty Status

To use Intelligent Calculation when calculating a subset of a database or when performing multiple calculation passes through a database, consider carefully the implications of how Essbase marks data blocks as clean. When using Intelligent Calculation, you must accurately maintain the clean and dirty status of the data blocks to ensure that Essbase recalculates the database as efficiently as possible.

For example, when you calculate a subset of a database, the newly calculated data blocks are not marked as clean by default. You can ensure that the newly calculated blocks are marked as clean by using the SET CLEARUPDATESTATUS AFTER command in a calculation script. Before creating the calculation script, see Using the SET CLEARUPDATESTATUS Command.

Limitations of Intelligent Calculation

Consider the following limitations and situations when using Intelligent Calculation:

  • Intelligent Calculation works on a data block level and not on a cell level. For example, if you load a data value into one cell of a data block, the whole data block is marked as dirty.

  • A CALC ALL that requires two passes through the database may calculate incorrectly. The problem occurs because blocks that are marked clean during the first pass are skipped during the second pass. To avoid this problem, turn Intelligent Calculation off or perform a CALC DIM for each dimension (rather than a CALC ALL for the database). A CALC ALL requires two passes through the database in either of these situations:

    • When the accounts dimension is sparse

    • When the accounts dimension is dense, the time dimension is sparse, and there is at least one more dense dimension in the outline

  • Changing a formula on the database outline or changing an accounts property on the database outline does not cause Essbase to restructure the database. Therefore, Essbase does not mark the affected blocks as dirty. You must recalculate the appropriate data blocks. See Changing Formulas and Accounts Properties: Impact on Block Status.

  • Whenever possible, Essbase calculates formulas that are tagged as two-pass and in the dimension tagged as accounts as part of the main calculation of a database. You may, however, need to use a calculation script to calculate some formulas twice. When you use a calculation script, disable Intelligent Calculation before recalculating formulas.

  • When SET CREATENONMISSINGBLK is set to ON in a calculation script, Intelligent Calculation is turned off, and affected blocks are calculated whether they are marked clean or dirty.

Considerations for Essbase Intelligent Calculation on Oracle Exalytics In-Memory Machine

The following functionality applies only to Essbase running on an Oracle Exalytics In-Memory machine.

Status bits for Intelligent Calculation on a block storage database are not persisted. Therefore, Intelligent Calculation works as intended only if both of the following conditions are true:

  • The whole index fits in the index cache

  • Related calculation scripts that use Intelligent Calculation complete within the lifetime of the Essbase Server

For Intelligent Calculation, set the index cache size large enough to fit the whole index and to account for future index growth due to data load or calculation.