Intelligent Calculation of Data Blocks

Learn how dense and sparse calculations, concurrent calculations, and multiple pass calculations are handled by Essbase Intelligent Calculation.

Essbase creates a data block for each unique combination of sparse dimension members, provided that at least one data value exists for the combination. Each data block represents all dense dimension member values for that unique combination of sparse dimension members.

For example, in the Sample Basic database, the Market and Product dimensions are sparse. Therefore, the data block New York -> Colas represents all the member values on the Year, Measures, and Scenario dimensions for the sparse combination New York -> Colas.

The next sections assume that you are familiar with the concepts of upper-level, level 0, and input data blocks, as discussed in Data Storage in Data Blocks.

Intelligent Calculation of Dense Dimensions

When you calculate a dense dimension and do not use a FIX command, Essbase calculates at least some of the data values in every data block in the database. In terms of Intelligent Calculation status, blocks get marked as clean from a dense calculation.

For example, the following calculation script calculates the Year dimension, which is dense.

SET CLEARUPDATESTATUS AFTER;
CALC DIM(Year);

Because Year is dense, every block in the cube includes members of Year. Therefore, Essbase calculates every block.

Then, because the script uses the SET CLEARUPDATESTATUS AFTER command, Essbase marks all data blocks in the cube as clean.

Intelligent Calculation of Sparse Dimensions

When you calculate a sparse dimension, Essbase may not need to calculate every data block in the database. Learn how Intelligent Calculation's clean/dirty update status can impact the status of level 0 and upper level blocks, and how to avoid unnecessary calculation.

For example, the following calculation script is based on the Sample Basic database:

SET CLEARUPDATESTATUS AFTER;
CALC DIM(Product);

This script calculates the Product dimension, which is a sparse dimension. Because Product is sparse, a data block exists for each member on the Product dimension. For example, one data block exists for New York -> Colas and another for New York -> 100-10.

Level 0 Effects

If you load data into the cube at level 0, the impacted blocks are marked as dirty. If you subsequently calculate only a sparse dimension, the level 0 blocks are not calculated, and remain dirty. When you recalculate only a sparse dimension or dimensions, Essbase recalculates upper-level data blocks.

Note:

Essbase calculates level 0 data blocks if a corresponding sparse, level 0 member has a formula applied to it.

The data block New York -> 100-10 is a level 0 block; it does not represent a parent member on either sparse dimension (Market or Product). The data values for New York -> 100-10 are input values; they are loaded into the database. Therefore, Essbase does not need to calculate this data block. Nor does Essbase mark the data block for New York -> 100-10 as clean, even though the script uses the SET CLEARUPDATESTATUS AFTER command.

Upper-Level Effects

When Essbase calculates a sparse dimension, it recalculates an upper-level data block if the block is dependent on one or more dirty child blocks.

Colas is a parent-level member on the Product dimension. Essbase must calculate values for Colas, so Essbase calculates this data block. Because the script uses the SET CLEARUPDATESTATUS AFTER command, Essbase marks the data block as clean.

Unnecessary Calculation

You can avoid unnecessary calculation by calculating at least one dense dimension. When you calculate a dense dimension without using the FIX command, data values are calculated in every block, including the level 0 blocks. So the level 0 blocks are marked as clean.

Intelligent Calculation and Concurrent Calculations

Take precautions with concurrent calculations when Intelligent Calculation is enabled. If multiple calculations attempt to calculate the same blocks, Essbase may not recalculate the data blocks, because they are already marked as clean.

In the following example, based on the Sample Basic database, Actual and Budget are members of the dense Scenario dimension. Because Scenario is dense, each block in the database contains Actual and Budget values. If User 1 runs the following calculation script, Essbase calculates the Actual values for all data blocks that represent New York.

SET CLEARUPDATESTATUS AFTER;
FIX("New York", Actual)
   CALC DIM(Product, Year);
ENDFIX

Essbase marks the calculated data blocks as clean, although not all the values in each calculated block have been calculated. For example, the Budget values have not been calculated.

If User 2 runs the following calculation script to calculate the Budget values for New York, Essbase does not recalculate the specified blocks, because they are already marked as clean. The calculation results for Budget are not correct.

SET CLEARUPDATESTATUS AFTER;
FIX("New York", Budget)
   CALC DIM(Product, Year);
ENDFIX

One way to solve this problem is to make the Scenario dimension sparse. Then the Actual and Budget values are in different blocks; for example, New York -> Colas -> Actual and New York -> Colas -> Budget. In this case, the second calculation script correctly calculates the Budget block.

Running concurrent calculations might require an increase in the data cache.

Intelligent Calculation and Multiple-Pass Calculations

Essbase calculation scripts that require multiple passes may be incompatible with the use of Intelligent Calculation in some situations.

When Essbase calculates data blocks on a first calculation pass through a database, it marks the data blocks as clean. If you try to calculate the same data blocks on a subsequent pass with Intelligent Calculation enabled, Essbase does not recalculate the data blocks, because they are already marked as clean.

Whenever possible, Essbase calculates a database in one calculation pass through the database.

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

For example, assume that Essbase calculates blocks on a first calculation pass, and marks them as clean. If you attempt to calculate the same blocks on a subsequent pass while Intelligent Calculation is enabled, Essbase does not recalculate the data blocks, because they are already marked as clean.

Examples and Solutions for Multiple-Pass Calculations

These examples describe situations that produce incorrect Essbase calculation results, with solutions to obtain correct results. They are based on the Sample Basic database and assume that Intelligent Calculation is turned on.

Example 1: Intelligent Calculation and Two-Pass

The following calculation script does a default calculation and then a two-pass calculation:

CALC ALL;
CALC TWOPASS;

Error

Essbase calculates the dirty data blocks in the database and marks all the data blocks as clean. Essbase then needs to recalculate the members tagged as two-pass in the dimension tagged as accounts. However, Essbase does not recalculate the specified data blocks because they are already marked as clean. The calculation results are not correct.

Solution

You can calculate the correct results by disabling Intelligent Calculation for the two-pass calculation.

Example 2: SET CLEARUPDATESTATUS and FIX

This calculation script calculates data values for New York. The calculation is based on the Product dimension:

SET CLEARUPDATESTATUS AFTER;
FIX("New York")
   CALC DIM(Product);
ENDFIX
CALC TWOPASS;

Error

Essbase performs the following processes:

Essbase cycles through the database calculating the dirty data blocks that represent New York. The calculation is based on the Product dimension. Thus, Essbase calculates only the blocks that represent a parent member on the Product dimension (for example, New York -> Colas, New York -> Root Beer, and New York -> Fruit Soda), and then only calculates the aggregations and formulas for the Product dimension.
Because the SET CLEARUPDATESTATUS AFTER command is used, Essbase marks the calculated data blocks as clean, although not all data values in each calculated block have been calculated.
Essbase should recalculate the members tagged as two-pass in the dimension tagged as accounts; however, some of these data blocks are already marked as clean from the calculation in the previous step. Essbase does not recalculate the data blocks that are marked as clean. The calculation results are not correct.

Solution

You can calculate the correct results by disabling Intelligent Calculation for the two-pass calculation.

Example 3: SET CLEARUPDATESTATUS and Two CALC DIM Commands

This calculation script bases the database calculation on the Product and Year dimensions. Because two CALC DIM commands are used, Essbase does two calculation passes through the database:

SET CLEARUPDATESTATUS AFTER;
CALC DIM(Product);
CALC DIM(Year);

Error

Essbase performs the following processes:

Essbase cycles through the database calculating the dirty data blocks. The calculation is based on the Product dimension, as in Example 2.
Because the SET CLEARUPDATESTATUS AFTER command is used, Essbase marks the calculated data blocks as clean, although not all data values in each calculated block have been calculated.
Essbase should recalculate the data blocks. The recalculation is based on the Year dimension. However, as a result of the calculation in the previous step, some data blocks are already marked as clean, and Essbase does not recalculate them. The calculation results are not correct.

Solution

You can calculate the correct results by using one CALC DIM command to calculate the Product and Year dimensions. Essbase calculates both dimensions in one calculation pass through the database.

The following calculation script calculates the correct results:

SET CLEARUPDATESTATUS AFTER;
CALC DIM(Product, Year);

Note:

When you calculate several dimensions in one CALC DIM command, Essbase calculates the dimensions in the default calculation order and not in the order in which you list them in the command. See Member Calculation Order.

Example 4: Two Calculation Scripts

This example calculates data values for New York but calculates based on two dimensions using two calculation scripts. The first calculation script calculates the Product dimension:

SET CLEARUPDATESTATUS AFTER;
FIX("New York")
   CALC DIM(Product);
ENDFIX

Essbase calculates the data blocks that include New York. Because the calculation is based on the Product dimension, Essbase calculates only the dirty blocks that include a parent member on the Product dimension (for example, New York -> Colas, New York -> Root Beer, and New York -> Fruit Soda), and calculates only the aggregations and formulas for the Product dimension.

Because of the CLEARUPDATESTATUS AFTER command, Essbase marks the calculated data blocks as clean, although not all data values in each calculated block have been calculated.

The second calculation script calculates the Year dimension:

SET CLEARUPDATESTATUS AFTER;
FIX("New York")
   CALC DIM(Year);
ENDFIX

Essbase calculates the data blocks that represent New York. Because the calculation is based on the Year dimension, which is a dense dimension, Essbase should calculate all data blocks that include New York, although within each block Essbase calculates only the aggregations and formulas for the Year dimension.

Error

As a result of the first calculation, some data blocks for New York are already marked as clean. Essbase does not recalculate these data blocks with the second calculation script because the data blocks are marked as clean. The calculation results are not correct.

Solution

You can calculate the correct results by telling Essbase not to mark the calculated data blocks as clean. The following calculation script calculates the correct results:

SET CLEARUPDATESTATUS OFF;
FIX("New York")
   CALC DIM(Product);
ENDFIX
SET CLEARUPDATESTATUS AFTER;
FIX("New York")
   CALC DIM(Year);
ENDFIX

With the SET CLEARUPDATESTATUS OFF command, Essbase calculates dirty data blocks but does not to mark them as clean, unlike the SET CLEARUPDATESTATUS AFTER command.

This solution assumes that the data blocks are not marked as clean from a previous partial calculation of the database.

You can ensure that all data blocks are calculated, regardless of their status, by disabling Intelligent Calculation. The following calculation script calculates all specified data blocks, regardless of their clean or dirty status:

SET UPDATECALC OFF;
FIX("New York")
   CALC DIM(Year, Product);
ENDFIX

Because you have not used the SET CLEARUPDATESTATUS AFTER command, Essbase does not mark calculated data blocks as clean.