Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
This chapter provides information on how to use Intelligent Calculation to optimize the performance of Analytic Services calculations. This chapter includes the following sections:
For information on using other methods to optimize database calculations, see the following chapters:
When you do a full calculation of a database, Analytic Services 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 Analytic Services has not yet calculated but need calculation, and those 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 change this default setting in the essbase.cfg
file. For more information about the essbase.cfg
file, see the Technical Reference.
You can also turn Intelligent Calculation on or off in a calculation script. For a brief explanation, see Turning Intelligent Calculation On and Off.
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 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, Analytic Services checks the status of the data blocks in a database. Data blocks have a calculation status of either clean or dirty. Analytic Services marks a data block as clean after certain calculations.
When Intelligent Calculation is enabled, Analytic Services calculates only dirty blocks and their dependent parents. Disabling Intelligent Calculation means that Analytic Services 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:
Analytic Services marks data blocks as clean in these types of calculations:
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 Analytic Services to do at least two calculation passes through the database. In this calculation, Analytic Services 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 an example, see Maintaining Clean and Dirty Status.
Analytic Services marks calculated data blocks as clean only in situations described above, unless you use the SET CLEARUPDATESTATUS command in a calculation script. For a comprehensive discussion of using the command, see Using the SET CLEARUPDATESTATUS Command.
Analytic Services 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 Analytic Services 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 Analytic Services 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. To ensure accurate calculation results, review the information in Using the SET CLEARUPDATESTATUS Command and the Technical Reference (Calculation Commands List: CLEARUPDATESTATUS) before creating the calculation 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 calculation script by using the SET UPDATECALC command in a calculation script. Enabling Intelligent Calculation means that Analytic Services calculates only dirty blocks and their dependent parents. Disabling Intelligent Calculation means that Analytic Services calculates all data blocks, regardless of whether they are marked as clean or dirty.
For detailed information on these commands and on the essbase.cfg
file, see the Technical Reference.
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.
To check the current calculation setting, see "Setting the Default Calculation" in the Essbase Administration Services Online Help.
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, Analytic Services 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, Analytic Services checks each block to see if it is marked as clean or dirty. For an explanation of 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.
Analytic Services 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 a description of the types of calculations for which data blocks are marked clean, see Marking Blocks As Clean.
In any other calculations, Analytic Services does not mark calculated data blocks as clean, unless you use the SET CLEARUPDATESTATUS command in a calculation script. For example, if you calculate a subset of a database or calculate a database in two calculation passes, Analytic Services does not mark the calculated blocks as clean, unless you use the SET CLEARUPDATESTATUS command.
The following calculation scripts do not cause Analytic Services 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, Analytic Services 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 calculation script.
Use these sections to understand the command SET CLEARUPDATESTATUS, choose a setting, and for instructions about how to use the command:
For a comprehensive discussion of the relationship between Intelligent Calculation and data block status, see Intelligent Calculation and Data Block Status.
The SET CLEARUPDATESTATUS command has three parameters-AFTER, ONLY, and OFF.
Analytic Services marks calculated data blocks as clean, even if it is calculating a subset of a database.
Analytic Services 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.
Analytic Services 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 parameter (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, Analytic Services 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.) Analytic Services does not mark the level 0 data blocks as clean because they are not calculated. For information on level 0 blocks, seeDefining Calculation Order.
SET CLEARUPDATESTATUS ONLY; FIX("New York") CALC DIM(Product); ENDFIX
Analytic Services 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). Analytic Services marks the dirty parent data blocks as clean, but does not calculate the data blocks. Analytic Services 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, Analytic Services first calculates all the dirty data blocks in the database. The calculated data blocks remain dirty. Analytic Services does not mark them as clean.
Analytic Services 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, Analytic Services recalculates them. Again, it does not mark the calculated data blocks as clean.
Analytic Services 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.
Analytic Services 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 explanations of these terms, to describe roles and relationships, and for an explanation of how Analytic Services creates data blocks, see Data Storage in Data Blocks.
When you calculate a dense dimension and do not use a FIX command, Analytic Services calculates at least some of the data values in every data block in the database. For example, the following calculation 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, Analytic Services calculates data values in every data block. Because the script uses the SET CLEARUPDATESTATUS AFTER command, Analytic Services marks all the data blocks as clean.
When you calculate a sparse dimension, Analytic Services may not need to calculate every data block in the database. For example, the following calculation 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, Analytic Services does not need to calculate this data block. Nor does Analytic Services mark the data block for New York -> 100-10 as clean, even though the script uses the SET CLEARUPDATESTATUS AFTER command.
Note: Analytic Services 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 Analytic Services does not calculate them. Therefore, when you recalculate only a sparse dimension or dimensions, Analytic Services 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. Analytic Services needs to calculate values for Colas, so Analytic Services calculates this data block. Because the script uses the SET CLEARUPDATESTATUS AFTER command, Analytic Services marks the data block as clean.
When Analytic Services 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, Analytic Services 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 calculation script, Analytic Services calculates the Actual values for all data blocks that represent New York. Analytic Services 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 calculation script above to calculate the Budget values for New York, Analytic Services 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 calculation script correctly calculates Budget data block.
Whenever possible, Analytic Services calculates a database in one calculation pass through the database. For an explanation of why and how multiple calculation passes are performed, see Calculation Passes.
When you use a calculation script to calculate a database, the number of calculation passes that Analytic Services performs depends upon the calculation script. For detailed information about the relationship between calculation passes and Intelligent Calculation, see Intelligent Calculation and Data Block Status. For information about grouping formulas and calculations, see Grouping Formulas and Calculations.
For example, assume Analytic Services 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, Analytic Services 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 calculation script does a default calculation and then a two-pass calculation:
CALC ALL; CALC TWOPASS;
Analytic Services calculates the dirty data blocks in the database and marks all the data blocks as clean. Analytic Services then needs to recalculate the members tagged as two pass in the dimension tagged as accounts. However, Analytic Services 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 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;
Analytic Services performs the following processes:
You can calculate the correct results by disabling Intelligent Calculation for the two pass calculation.
This calculation script bases the database calculation on the Product and Year dimensions. Because two CALC DIM commands are used, Analytic Services does two calculation passes through the database:
SET CLEARUPDATESTATUS AFTER; CALC DIM(Product); CALC DIM(Year);
Analytic Services performs the following processes:
You can calculate the correct results by using one CALC DIM command to calculate both the Product and Year dimensions. Analytic Services then 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, Analytic Services calculates the dimensions in the default calculation order and not in the order in which you list them in the command. For a description of default calculation order, see Member Calculation Order.
This example calculates data values for New York but calculates based on two different dimensions using two separate calculation scripts. The first calculation script calculates the Product dimension:
SET CLEARUPDATESTATUS AFTER; FIX("New York") CALC DIM(Product); ENDFIX
Analytic Services calculates the data blocks that include New York. The calculation is based on the Product dimension. Thus, Analytic Services 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, Analytic Services marks the calculated data blocks as clean, even though 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
Analytic Services calculates the data blocks that represent New York. The calculation is based on the Year dimension, which is a dense dimension. Thus, Analytic Services should calculate all data blocks that include New York, although within each block, Analytic Services 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. Analytic Services does not recalculate these data blocks with the second calculation script because the data blocks are already marked as clean. The calculation results are not correct.
You can calculate the correct results by telling Analytic Services 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
The SET CLEARUPDATESTATUS OFF command. With it, Analytic Services 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 calculation 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, Analytic Services 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 actions:
Neither changing a formula in the database outline nor changing an accounts property in the database outline causes Analytic Services 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, Analytic Services 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, the changes are not calculated. To recalculate the appropriate data blocks, use a calculation script to perform any of the following tasks:
If you use relationship functions (for example, @PRIOR or @NEXT) or financial functions (for example, @ACCUM, @NPV, or @INTEREST) in a formula on a sparse dimension or a dense dimension, Analytic Services always recalculates the data block that contains the formula.
For detailed information on specific relationship functions and financial functions, see the Technical Reference.
When you restructure a database (for example, by adding a member to a dense dimension), all data blocks potentially need recalculating. Therefore, Analytic Services 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 Analytic Services to restructure the database. You must recalculate the appropriate data blocks. For more information, see Changing Formulas and Accounts Properties.
When you copy values to a data block by using the DATACOPY command, the resulting data block is marked as dirty. Analytic Services calculates the block when you recalculate a database.
When you clear data values by using the CLEARDATA and CLEARBLOCK commands, Analytic Services clears all the blocks regardless of whether they are marked as clean or dirty.
When you convert currencies using the CCONV command, the resulting data blocks are marked as dirty. Analytic Services calculates all converted blocks when you recalculate a database.
![]() |