When we start writing any calc scripts in calculation manager, typically, we will start with the SET commands, which is the housekeeping part. The SET commands are restricted to the certain scripts and make sure the script can run in a proper and efficient way. Today, I would like to go through the major 13 housekeeping SET commands.
Keep in mind, SET commands in a calculation script are procedural, so the first occurrence of a SET command in a calculation script stays in effect until the next occurrence of the same SET command. These commands will not make any permanent setting changes to the Essbase servers.
For most cases, we will use the following housekeeping settings. I will go into details one by one.
Intelligent Calculation
Before we discuss SET commands, let’s take a look at what is Intelligent Calculation and what is clean / dirty block. We will be using these concepts for the rest SET commands.
By default, when Essbase performs a full calculation of a database, it tracks which data blocks it calculates. If we 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. 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.
Essbase marks data blocks as clean in these types of calculations:
1) A full calculation (CALC ALL) of a database (the default calculation).
2) A calculation script that calculates all the dimensions in one CALC DIM statement.
For example,
CALC DIM(Account, Entity, Period, Year, Version, Scenario);
Essbase marks a data block as dirty in these situations:
1) 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
2) Loading data into the data block
3) Restructuring the database (for example, by adding a member to a dense dimension)
4) Copying data to the data block; for example, using DATACOPY
Intelligent Calculation is most effective for sparse incremental updates. It is great when we are running default calcs. But Intelligent Calculation is bad when we are running a calc script. Because for most cases, we would like the script to operate on both clean as dirty.
For example, we just calculated the whole database, then we want to run a clear script. If we set the Intelligent Calculation on, then Essbase will decide how many blocks need to calculate, which is none. Because we just calculated everything. The clear script will not work at all. So, for most cases, when we are running a calc script, we would like to set Intelligent Calculation off. We will discuss how to set Intelligent Calculation in the following commands.
Now, let’s start the SET commands.
1. SET AGGMISSG
SET AGGMISSG ON | OFF ;
This command specifies whether Essbase consolidates #MISSING values in the database.
For example, we have the base level data for the following combinations. We run aggregation for Entity.
Then we update the base level data as following.
If we set AGGMISSG ON, then run aggregation. We get:
If we set AGGMISSG OFF, then run aggregation. We get:
Generally, we use
SET AGGMISSG ON;
2. SET CACHE
SET CACHE HIGH | DEFAULT | LOW | OFF | ALL;
This command specifies the size of the calculator cache. Essbase uses the calculator cache to create and track data blocks during calculation. Using the calculator cache significantly improves the calculation performance. The size of the performance improvement depends on the configuration of the database. We could set levels defining the size of the calculator cache. We set the values of HIGH, DEFAULT and LOW in the essbase.cfg file. If we do not set the value of DEFAULT in the essbase.cfg file, Essbase uses a default value of 200,000 bytes. The maximum calculator cache size that we can specify is 200,000,000 bytes.
Generally, we use
SET CACHE HIGH;
3. SET LOCKBLOCK
SET LOCKBLOCK HIGH | DEFAULT | LOW;
This command specifies the maximum number of blocks that Essbase can get addressability to concurrently when calculating a sparse member formula.
By default, Essbase allows up to 100 blocks to be locked (addressable) concurrently when calculating a block. This is sufficient for most database calculations. However, we may want to set a number higher than 100 if we are consolidating very large numbers of children in a formula calculation. This setting ensures that Essbase can get addressability to all the required blocks when calculating a data block and that performance will not be impaired.
Generally, we use
SET LOCKBLOCK HIGH;
4. SET MSG
SET MSG SUMMARY | DETAIL | ERROR | INFO | NONE | ONLY;
This command sets the level of messaging we want about the calculations.
SUMMARY displays calculation settings and provides statistics on the number of:
1) Data blocks created, read, and written
2) Data cells calculated
DETAIL provides the same information as SUMMARY. In addition, it displays a detailed information message every time Essbase calculates a data block.
ERROR displays only error messages.
INFO displays information and error messages.
ONLY, when we use this parameter, Essbase generates some empty upper-level blocks. Make sure to clear upper-level blocks at the end of the simulation/command. calculates a data block.
Generally, we use
SET MSG SUMMARY;
5. SET CALCPARALLEL
SET CALCPARALLEL n;
This command enables parallel calculation in place of the default serial calculation. N is an integer from 1 to 64 on 32-bit platforms or from 1 to 128 on 64-bit platforms, specifying the number of threads to be made available for parallel calculation. The default value specifies serial calculation: no parallel calculation takes place. Also, we can set n = 0 to specify serial calculation. The value of n is usually set one less than the number of processor. Essbase analyzes each pass of a calculation to determine whether parallel calculation is possible. If it is not, Essbase uses serial calculation even if CALCPARALLEL is set.
Generally, we use
SET CALCPARALLEL 7;
6. SET CALCTASKDIMS
SET CALCTASKDIMS n;
This command specifies the number of sparse dimensions included in the identification of tasks for parallel calculation.
A value of 1 indicates that only the last sparse dimension in the outline will be used to identify tasks. A value of 2, for example, indicates that the last and second-to-last sparse dimensions in the outline are used. The maximum value is the number of sparse dimensions in the outline. Use the SET CALCTASKDIMS calculation command only if the outline generates many empty tasks, thus reducing opportunities for parallel calculation.
Generally, we use
SET CALCTASKDIMS 4;
7. SET CLEARUPDATESTATUS
SET CLEARUPDATESTATUS AFTER | ONLY | OFF;
Specifies when Essbase marks data blocks as clean. This clean status is used during Intelligent Calculation.
There are three parameters, AFTER, ONLY and OFF.
1)SET CLEARUPDATESTATUS AFTER;
Essbase marks calculated data blocks as clean, even if it is calculating a subset of a database.
2)SET CLEARUPDATESTATUS ONLY;
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.
3)SET CLEARUPDATESTATUS OFF;
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 remain unchanged.
Generally, we use
SET CLEARUPDATESTATUS OFF;
8. SET CREATENONMISSINGBLK
SET CREATENONMISSINGBLK ON|OFF;
This command controls whether potential blocks are created in memory for calculation purposes, and whether #MISSING blocks are stored. It affects the results of calculations on sparse and dense dimensions.
By default, Essbase applies dense-member formulas only to existing data blocks. SET CREATENONMISSINGBLK ON enables Essbase to create potential blocks in memory where the dense-member formulas are performed. Of these potential blocks, Essbase writes to the database only blocks that contain values; blocks resulting in only #MISSING are not written to the database. The creation of #MISSING blocks resulting from sparse-member formulas is governed by the Create Block on Equations setting. (SET CREATEBLOCKONEQ) The SET CREATENONMISSINGBLK ON command ensures that only non-empty blocks are created, regardless of the Create Block on Equations setting. We will discuss the SET CREATEBLOCKONEQ command next.
When SET CREATENONMISSINGBLK is ON, all sparse-member formulas are executed in top-down mode. Dense member formulas are flagged for top-down calculation when they contain the following:
1) Sparse members
2) Constants (Account = 1;)
3) The @VAR function
4) The @XREF function
If Essbase encounters the @CALCMODE(BOTTOMUP) in a member formula, it ignores the @CALCMODE command. SET CREATENONMISSINGBLK affects only creation of new blocks. If existing blocks become #MISSING after formula execution, they are not deleted.
Remember, working with potential blocks can affect calculation performance.
Generally, we use
SET CREATENONMISSINGBLK OFF;
9. SET CREATEBLOCKONEQ
SET CREATEBLOCKONEQ ON|OFF;
This command controls, within a calculation script, whether new blocks are created when a calculation formula assigns anything other than a constant to a member of a sparse dimension. SET CREATEBLOCKONEQ overrides the Create Block on Equation setting for the database.
When the Create Blocks on Equation setting is ON, Essbase uses the top-down calculation method to calculate each sparse member. We can use multiple SET CREATEBLOCKONEQ commands in the calculation script to define the Create Blocks on Equation setting value for the calculations following each command. The value set by the SET CREATEBLOCKONEQ command stays in effect until the next SET CREATEBLOCKONEQ command is processed or the calculation script is finished. Also, as we mentioned earlier, the Create Blocks on Equation setting is overridden by SET CREATENONMISSINGBLK ON.
Let’s check a quick example.
FIX(Budget)
SET CREATEBLOCKONEQ OFF;
“Massachusetts” = “California” + 100;
SET CREATEBLOCKONEQ ON;
“New York” = “California” + 100;
ENDFIX
In this case, Essbase creates blocks for new values of “New York”, but not for “Massachusetts”.
Remember, sometimes, new blocks are not desired. Creation and processing of unneeded blocks can increase processing time and storage requirements.
Generally, we use
SET CREATEBLOCKONEQ ON;
10. SET FRMLBOTTOMUP
SET FRMLBOTTOMUP ON|OFF;
This command optimizes the calculation of complex formulas on sparse dimensions in large database outlines. This command tells Essbase to perform a bottom-up calculation on formulas that would otherwise require a top-down calculation.
Forcing a bottom-up calculation on a formula may produce results that are inconsistent with a top-down calculation if:
1)The formula contains complex functions (range functions, cross dim, etc.)
2)The formula’s dependencies are not straightforward
Generally, we use
SET FRMLBOTTOMUP ON;
11. SET FRMLRTDYNAMIC
SET FRMLRTDYNAMIC ON | OFF;
This command enables us to turn off calculation of all dense Dynamic Calc members during batch calculation if runtime dependent functions are included in formulas on stored members. This command improves batch calculation performance by removing the overhead of calculating all Dynamic Calc members.
Runtime-dependent functions include:
@ANCEST
@SANCEST
@PARENT
@SPARENT
@CURRMBR
If we set FRMLRTDYNAMIC OFF, and if a stored member formula includes a runtime-dependent function on a Dynamic Calc member, it may get #MISSING as the result instead of the expected value after executing the formula on the Dynamic Calc member.
Generally, we use
SET FRMLRTDYNAMIC OFF;
12. SET EMPTYMEMBERSETS
SET EMPTYMEMBERSETS ON|OFF;
This command stops the calculation within a FIX…ENDFIX command if the FIX evaluates to an empty member set.
Let’s check an example,
SET EMPTYMEMBERSETS ON;
FIX(@CHILDREN(“California”))
Calc Dim(Year);
ENDFIX
This calculation script does not calculate Calc Dim(Year) within the FIX command. Because California has no children and therefore the FIX statement evaluates to an empty member set.
Generally, we use
SET EMPTYMEMBERSETS ON;
13. SET UPDATECALC
SET UPDATECALC ON | OFF;
This command turns Intelligent Calculation on or off. Using Intelligent Calculation, Essbase calculates only dirty blocks, such as updated data blocks and their dependent parents.
As we have mentioned in the Intelligent Calculation section that, Intelligent Calculation is bad when we are running a calc script. Because for most cases, we would like the script to operate on both clean as dirty. So, for most cases, we SET UPDATECALC OFF.
Generally, we use
SET UPDATECALC OFF;
Hopefully, this housekeeping post can give you some idea on the SET commands. Wisely using these SET commands can reduce the processing time and help us tune the scripts and application in a better way.