This chapter provides information on how to use Intelligent Calculation to optimize the performance of Essbase calculations. This chapter includes the following sections:
For additional information on optimizing overall database calculations, see the following:
When you do a full calculation of a database, Essbase tracks which data blocks it has calculated. If you then load a subset of data, on subsequent calculations, you can choose to calculate only those data blocks that Essbase has not yet calculated but need calculation, and those calculated blocks that require recalculation because of the new data. In Essbase, this process is called Intelligent Calculation.
By default, Intelligent Calculation is turned on. You can change this default setting in the configuration file essbase.cfg. You can also turn Intelligent Calculation on or off in a calc script. For more information, see Turning Intelligent Calculation On and Off.
You can check the current Intelligent Calculation setting by selecting Database > Set Default Calc in Application Manager.
Intelligent Calculation is designed to provide significant calculation performance benefits for these types of calculations:
For example, consider a case in which you calculate a database by doing a default consolidation and then an allocation of data. To significantly improve your calculation performance in this case, 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:
To provide Intelligent Calculation, Essbase checks the status of the data blocks in a database. Data blocks have a calculation status of either 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. Disabling Intelligent Calculation means that Essbase calculates all data blocks, regardless of whether they are marked as clean or dirty.
Use these topics to understand clean and dirty status, and to learn how to manage clean and dirty status for Intelligent Calculation:
Essbase marks data blocks as clean in these types of calculations:
CALC DIM(Measures, Product, Market, Year, Scenario);Compare this to a calc 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. For more information, see Maintaining Clean and Dirty Status.
Essbase marks calculated data blocks as clean only in situations described above, unless you use the SET CLEARUPDATESTATUS command in a calc script. For more information, see Using the SET CLEARUPDATESTATUS Command.
Essbase marks a data block as dirty in the following situations:
If you want 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 calc script. To ensure accurate calculation results, review the information in Using the SET CLEARUPDATESTATUS Command and the Technical Reference in the docs directory (Calculation Commands List: CLEARUPDATESTATUS) before creating the calc script.
Consider the following limitations when using Intelligent Calculation:
This section provides information on turning Intelligent Calculation on and off and on using Intelligent Calculation with different types of calculations:
By default, Intelligent Calculation is turned on. You can change the default by using the UPDATECALC setting in the ESSBASE.CFG file.
You can turn Intelligent Calculation on and off for the duration of a calc script by using the SET UPDATECALC command in a calc script. Enabling Intelligent Calculation means that Essbase calculates only dirty blocks and their dependent parents. Disabling Intelligent Calculation means that Essbase calculates all data blocks, regardless of whether they are marked as clean or dirty.
For more information on these commands and on ESSBASE.CFG, see the Technical Reference in the docs directory.
Intelligent Calculation provides significant performance benefits when you do a full calculation (CALC ALL) of a database. If you do a full calculation of a database, leave Intelligent Calculation turned on (the default) to take advantage of the performance benefits that it provides.
Unless you have changed the default, a full calculation (CALC ALL) is the default calculation for a database. You can check the default calculation setting by selecting Database > Set Default Calc in Essbase Application Manager.
Caution: When using Intelligent Calculation, note the information in Limitations of Intelligent Calculation.
When you do a full calculation of a database for the first time, Essbase calculates every existing block. The performance is the same whether you have Intelligent Calculation turned on or off.
When you do a full recalculation of a database with Intelligent Calculation turned on, Essbase checks each block to see if it is marked as clean or dirty. For more information on clean and dirty, see Intelligent Calculation and Data Block Status.
Checking the data blocks has a 5% to 10% performance overhead. During most recalculations, this small performance overhead is insignificant when compared to the performance gained by enabling Intelligent Calculation.
However, if you recalculate a database in which more than approximately 80% of the values have changed, the overhead of Intelligent Calculation may outweigh the benefits. In this case, disable Intelligent Calculation.
Essbase marks a data block as clean when it calculates the data block on a full calculation (CALC ALL) or when it calculates all dimensions in one CALC DIM command. For more information, see Intelligent Calculation and Data Block Status.
In any other calculations, Essbase does not mark calculated data blocks as clean, unless you use the SET CLEARUPDATESTATUS command in a calc script. For example, if you calculate a subset of a database or calculate a database in two calculation passes, Essbase does not mark the calculated blocks as clean, unless you use the SET CLEARUPDATESTATUS command.
The following calc scripts do not cause Essbase to mark the calculated data blocks as clean:
FIX("New York") CALC DIM(Product, Measures); ENDFIX CALC DIM(Measures, Product); CALC DIM(Market, Year, Scenario);
Be sure to use SET CLEARUPDATESTATUS to avoid unnecessary recalculations.
In some cases, Essbase does not mark calculated blocks as clean; for example, if you calculate a subset of a database or calculate a database in two calculation passes. To manually mark data blocks as clean for purposes of Intelligent Calculation, use the SET CLEARUPDATESTATUS command in a calc script.
Use these sections to understand the command SET CLEARUPDATESTATUS, choose a setting, and for instructions about how to use the command:
For more information about the relationship of Intelligent Calculation and data block status, see Intelligent Calculation and Data Block Status.
The SET CLEARUPDATESTATUS command has three parameters: AFTER > ONLY > OFF.
Essbase marks calculated data blocks as clean, even if it is calculating a subset of a database.
Essbase marks the specified data blocks as clean but does not calculate the data blocks. This parameter provides the same result as AFTER, but without calculation.
Essbase calculates the data blocks but does not mark the calculated data blocks as clean. Data blocks are not marked as clean, even on a full calculation (CALC ALL) of a database. The existing clean or dirty status of the calculated data blocks remains unchanged.
When you use the SET CLEARUPDATESTATUS command to mark calculated data blocks as clean, be aware of these recommendations before selecting the setting (AFTER, ONLY, OFF):
Assume a scenario using the Sample Basic database:
These three examples show different ways of using SET CLEARUPDATESTATUS:
SET CLEARUPDATESTATUS AFTER; FIX("New York") CALC DIM(Product); ENDFIX
In this example, Essbase searches for dirty parent data blocks for New York (for example New York -> Colas, in which Colas is a parent member). It calculates these dirty blocks and marks them as clean. (The calculation is based on the Product dimension.) Essbase does not mark the level 0 data blocks as clean because they are not calculated. For information on level 0 blocks, see Defining the Calculation Order.
SET CLEARUPDATESTATUS ONLY; FIX("New York") CALC DIM(Product); ENDFIX
Essbase searches for dirty parent data blocks for New York (for example New York -> Colas, in which Colas is a parent member on the Product dimension). Essbase marks the dirty parent data blocks as clean, but does not calculate the data blocks. Essbase does not mark the level 0 data blocks as clean because they are not calculated. For example, if New York -> 100-10 (a level 0 block) is dirty, it remains dirty.
SET CLEARUPDATESTATUS OFF; CALC ALL; CALC TWOPASS; SET CLEARUPDATESTATUS ONLY; CALC ALL;
In this example, Essbase first calculates all the dirty data blocks in the database. The calculated data blocks remain dirty. Essbase does not mark them as clean.
Essbase then calculates the members tagged as two pass that are in the dimension tagged as accounts. Because the data blocks are still marked as dirty, Essbase recalculates them. Again, it does not mark the calculated data blocks as clean.
Essbase then searches for all the dirty blocks in the database and marks them as clean. It does not calculate the blocks, even though a CALC ALL command is used.
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.
These sections provide information about conditions that affect performance with Intelligent Calculation:
These sections assumes that you are familiar with the concepts of upper level, level 0, and input data blocks. For information about levels and data blocks, see Member Relationships, Generations, and Levels.
For more information on how Essbase creates data blocks, see Defining the Calculation Order.
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. For example, the following calc script is based on the Sample Basic database:
SET CLEARUPDATESTATUS AFTER; CALC DIM(Year);
This script calculates the Year dimension, which is a dense dimension. Because Year is dense, every data block in the database includes members of the Year dimension. Therefore, Essbase calculates data values in every data block. Because the script uses the SET CLEARUPDATESTATUS AFTER command, Essbase marks all the data blocks as clean.
When you calculate a sparse dimension, Essbase may not need to calculate every data block in the database. For example, the following calc script is based on Sample Basic:
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.
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.
Note: Essbase does calculate level 0 data blocks if a corresponding sparse, level 0 member has a formula applied to it.
If you load data into a database, the level 0 data blocks into which you load data are marked as dirty. If you subsequently calculate only a sparse dimension or dimensions, the level 0 blocks remain dirty, because Essbase does not calculate them. Therefore, when you recalculate only a sparse dimension or dimensions, Essbase recalculates all upper-level data blocks because the upper-level blocks are marked as dirty if their child blocks are dirty, even though the upper level blocks were originally clean.
Colas is a parent level member on the Product dimension. Essbase needs to 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.
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.
You can avoid unnecessary calculation by ensuring that you calculate at least one dense dimension. When you calculate a dense dimension and do not use the FIX command, data values are calculated in every data block, including the level 0 blocks. So the level 0 blocks are marked as clean.
If concurrent calculations attempt to calculate the same data blocks and Intelligent Calculation is turned on, Essbase may not recalculate the data blocks because they are already marked as clean.
Do not use the SET CLEARUPDATESTATUS AFTER command with concurrent calculations unless you are certain that the concurrent calculations do not calculate the same data block or blocks.
Consider the following example, which is based on the Sample Basic database. Actual and Budget are members of the dense Scenario dimension. Because Scenario is dense, each data block in the database contains both Actual and Budget values.
SET CLEARUPDATESTATUS AFTER; FIX("New York", Actual) CALC DIM(Product, Year); ENDFIX
If User One runs the above calc script, Essbase calculates the Actual values for all data blocks that represent New York. Essbase marks the calculated data blocks as clean, even though not all the data values in each calculated block have been calculated. For example, the Budget values have not yet been calculated.
SET CLEARUPDATESTATUS AFTER; FIX("New York", Budget) CALC DIM(Product, Year); ENDFIX
If User Two runs the calc script above to calculate the Budget values for New York, Essbase does not recalculate the specified data blocks, because they are already marked as clean. The calculation results for Budget are not correct.
One way to solve this problem is to make the Scenario dimension sparse; then the Actual and Budget values are in different data blocks, for example, New York -> Colas -> Actual and New York -> Colas -> Budget. In this case, the second calc script correctly calculates Budget data block.
Whenever possible, Essbase calculates a database in one calculation pass through the database. For information on calculation passes, see Defining the Calculation Order.
When you use a calc script to calculate a database, the number of calculation passes that Essbase performs depends upon the calc script. For more information about the relationship between calculation passes and Intelligent Calculation, see Intelligent Calculation and Data Block Status. For more information about grouping formulas and calculations, see Developing Calculation Scripts.
For example, assume Essbase calculates data blocks on a first calculation pass through a database and then marks them as clean. If you then attempt to calculate the same data blocks on a subsequent pass and Intelligent Calculation enabled, Essbase does not recalculate the data blocks because they are already marked as clean.
These examples describe situations in which you obtain incorrect calculation results, and provide a solution you can implement to obtain correct results:
The examples are based on the Sample Basic database and assume that Intelligent Calculation is turned on.
This calc script does a default calculation and then a two-pass calculation:
CALC ALL; CALC TWOPASS;
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.
You can calculate the correct results by disabling Intelligent Calculation for the two pass calculation.
This calc 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;
Essbase performs the following processes:
You can calculate the correct results by disabling Intelligent Calculation for the two pass calculation. For detailed information on using two pass calculations, see Defining the Calculation Order.
This calc 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);
Essbase performs the following processes:
You can calculate the correct results by using one CALC DIM command to calculate both the Product and Year dimensions. Essbase then calculates both dimensions in one calculation pass through the database. The following calc 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. For more information, see Defining the Calculation Order.
This example calculates data values for New York but calculates based on two different dimensions using two separate calc scripts. The first calc script calculates the Product dimension:
SET CLEARUPDATESTATUS AFTER; FIX("New York") CALC DIM(Product); ENDFIX
Essbase calculates the data blocks that include New York. The calculation is based on the Product dimension. Thus, 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 even then only calculates the aggregations and formulas for the Product dimension.
Because of the CLEARUPDATESTATUS AFTER command, Essbase marks the calculated data blocks as clean, even though not all data values in each calculated block have been calculated.
The second calc script calculates the Year dimension:
SET CLEARUPDATESTATUS AFTER; FIX("New York") CALC DIM(Year); ENDFIX
Essbase calculates the data blocks that represent New York. The calculation is based on the Year dimension, which is a dense dimension. Thus, 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.
As a result of the first calculation, some of the data blocks for New York are already marked as clean. Essbase does not recalculate these data blocks with the second calc script because the data blocks are already marked as clean. The calculation results are not correct.
You can calculate the correct results by telling Essbase not to mark the calculated data blocks as clean. The following calc 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
The SET CLEARUPDATESTATUS OFF command. With it, 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 already marked as clean from a previous partial calculation of the database.
You can ensure that all data blocks are calculated, irrespective of their clean or dirty status, by disabling Intelligent Calculation.
The following calc script calculates all specified data blocks, irrespective 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.
Using Intelligent Calculation may have implications for the way you administer a database. This section discusses the implications of each of the following:
Neither changing a formula in the database outline nor changing an accounts property in the database outline causes Essbase to restructure the database. Thus, data blocks affected by such a change are not marked as dirty. For example, if you change a time balance tag in the dimension tagged as accounts, Essbase does not restructure the database and does not mark the affected blocks as dirty.
When you subsequently run a default calculation with Intelligent Calculation turned on, your changes are not calculated. Since you must recalculate the appropriate data blocks, you can accomplish this by using a calc script to do any of the following:
For more detailed information, see Developing Calculation Scripts.
If you use relationship functions (for example, @PRIOR or @NEXT) or financial functions (for example, @NPV or @INTEREST) in a formula on a sparse dimension, Essbase always recalculates the data block that contains the formula, even if the data block is marked as clean.
For more information on relationship functions and financial functions, see the Technical Reference in the docs directory.
When you restructure a database (for example, by adding a member to a dense dimension), all data blocks potentially need recalculating. Therefore, Essbase marks all data blocks as dirty. When you calculate the restructured database, all blocks are calculated.
Note: Changing a formula in the database outline or changing an accounts property in the database outline does not cause Essbase to restructure the database. You must recalculate the appropriate data blocks. For more information, see Changing a Formula or Accounts Property.
When you copy values to a data block by using the DATACOPY command, the resulting data block is marked as dirty. Essbase calculates the block when you recalculate a database.
When you clear data values by using the CLEARDATA and CLEARBLOCK commands, Essbase clears all the blocks regardless of whether they are marked as clean or dirty.
For more information on these commands, see the Technical Reference in the docs directory.
When you convert currencies using the CCONV command, the resulting data blocks are marked as dirty. Essbase calculates all converted blocks when you recalculate a database.
For more information about the CCONV command, see the Technical Reference in the docs directory.
![]() © 2002 Hyperion Solutions Corporation. All rights reserved. http://www.hyperion.com |