What is Intelligent Calculation? Why should we care?

Developing calc scripts Series

What is intelligent calc? Why should I care?

A primary goal in calculation script development is optimization (elimination of extra passes through database index). To optimize calculation, you can use FIX and IF statements to focus calculations, or you can use an option called intelligent calculation.

When you perform a full database calculation, Essbase marks which blocks have been calculated. If you then load a subset of data, you can calculate only the changed data blocks and their ancestors. This selective calculation process is intelligent calculation.

By default, intelligent calculation is turned on. You can change the default setting in the essbase.cfg file or on a script-by-script basis wit the SETUPDATECALC OFF command.

Intelligent calculation is based on data-block marking, when intelligent calculation is active, during the normal processes, within the index file, blocks are marked clean or dirty.

Clean Blocks—Blocks that don’t require calculation
Dirty Blocks --- Blocks that require calculation.

When intelligent calculation is active, during calculation, Essbase looks for only dirty blocks.

Exceptions:
Even when the intelligent calculation is enabled, for CALC DIM statements that do not include all dimensions, Essbase does not use intelligent calculation process. Rather, Essbase calculates all relevant data blocks, regardless of clean or dirty status, and all data blocks retain their status, dirty or clean.

SET CLEARUPDATESTATUS AFTER is a calculation command that engages intelligent calc for any calc script, regardless of construction. Typically, you use this command where you cannot meet the conditions for a calc dim on all dimensions.

Example:

SET CLEARUPDATESTATUS AFTER
FIX(@IDESCENDANTS(“Q1”))
CALC DIM (Accounts);
ENDFIX

When you execute a calculation script that includes the SET UPDATESTATUS AFTER command, data blocks that are marked clean are not calculated and data blocks that are marked dirty are calculated and marked clean.

How do you force block marking without calculating?

SET CLEARUPDATESTATUS ONLY command instructs Essbase to mark as clean all data blocks that were previously marked dirty.


How do blocks become dirty?
In the following cases the data blocks are marked as dirty.
Block creation during data input
Data modification (Lock and send)
Creation or modification of descendant blocks
Database Restructure (both dense and sparse)

What are False negative and False positive?
Occasionally, clean data blocks are marked dirty (False negative). In such cases calculation efficiency suffers. A more serious problem, however, is a false positive condition, in which dirty blocks are marked as clean. In such case of false positives, data integrity can suffer.

When does False Positives arise?

Calculation only a subset of a data block:
Essbase marks at block level not at the cell level, so a calculation that I executed on a subset of cells can cause a false positive condition. Only a few cells are calculated but the block is marked clean, although uncalculated cells remain.

Using a FIX statement:

Ancestors of a dirty block are not marked as dirty until the descendant dirty block is calculated. A false positive can result if the descendant dirty block is calculated within a FIX statement that does not include the dirty ancestor block. After calculation, essbase marks the descendant block clean, and the ancestor block remains marked clean, although it should be marked dirty.

Following SET CLEARUPDATESTATUS ONLY with unrelated calculations :
You should follow SET CLEARUPDATESTATUS ONLY with repetition of the section of the script for which you want to force data block marking , A false positive can occur if SET CLEARUPDATESTATUS ONLY touches blocks that are otherwise dirty and that are not calculated.


The Intelligent calc can provide significant performance benefits kin certain situations but require vigilant maintenance of the clean and dirty status of data blocks to avoid skipping the wrong blocks on calculation.

The intelligent calc function most productively used in interactive or iterative situations in which small, incremental changes are made to a database and in which it is not necessary to recalculate the entire database. For example you can use intelligent calc in following situations:

During quarter close periods, allocation rates and adjusting entries may be update multiple times. You use intelligent calc to view update results without recalculating the entire database.
In budgeting or forecasting application, typically, many users update units and drivers on a regular basis. In such applications, for users to analyze the impact of their updates, a short calculation time is imperative.