Use Calculation Commands

Essbase calculation commands help you flexibly calculate the data in your block storage cube. You can design scripts to calculate data based on the outline, calculate iteratively or in parallel, restrict to subsets, and declare variables. You can customize the behavior of different features from within the calc script.

The topics in this section discuss calculation commands, grouped by functionality.

Calculate the Essbase Outline

The following Essbase calculation commands calculate a block storage cube based on the structure and formulas in its outline.

Table 23-1 List of Commands for Calculating the Cube Outline

Command	Calculation
CALC ALL	The entire cube, based on the outline
CALC DIM	A specified dimension or dimensions
CALC TWOPASS	All members tagged as two-pass on the dimension tagged as accounts Note: Do not use two-pass calculation with hybrid mode cubes. Only use solve order.
membername	The formula applied to a member in the cube outline, where membername is the name of the member to which the formula is applied
CALC AVERAGE	All members tagged as Average on the dimension tagged as accounts
CALC FIRST	All members tagged as First on the dimension tagged as accounts
CALC LAST	All members tagged as Last on the dimension tagged as accounts
CCONV	Currency conversions

Control the Flow of Calculations

Control flow commands in block storage Essbase calculation scripts help you iterate calculations, calculate in parallel, or restrict calculations to a subset of the cube.

The following commands manipulate the flow of calculations:

Table 23-2 List of Commands to Control the Flow of Calculations

Command	Calculation
FIX...ENDFIX	Calculate a subset of a database by inclusion
EXCLUDE...ENDEXCLUDE	Calculate a subset of a database by exclusion
LOOP...ENDLOOP	Specify the number of times that commands are iterated
FIXPARALLEL...ENDFIXPARALLEL	Enable parallel calculation on a block of commands

You can also use the IF and ENDIF commands to specify conditional calculations.

Note:

Essbase does not allow branching from one calculation script to another calculation script.

Declare Data Variables During Calculation

Use data declaration commands in Essbase block storage calculation scripts to declare and set the initial values of temporary variables, which store the results of intermediate calculations.

You can also use substitution variables in a calculation script. See Substitution Variables in Calculation Scripts.

Table 23-3 List of Commands for Declaring Data Variables

Command	Calculation
ARRAY	Declare one-dimensional array variables
VAR	Declare a temporary variable that contains a single value
THREADVAR	Declare a temporary variable that can be used within a FIXPARALLEL...ENDFIXPARALLEL block.

Values stored in temporary variables exist only while the calculation script is running. You cannot report on the values of temporary variables.

Variable and array names are character strings that contain any of the following characters:

Letters a–z
Numerals 0–9
Special characters: $ (dollar sign), # (pound sign), and _ (underscore)

Typically, arrays are used to store variables as part of a member formula. The size of the array variable is determined by the number of members in the corresponding dimension. For example, if the Scenario dimension has four members, the following command creates an array called Discount with four entries:

ARRAY Discount[Scenario];

You can use multiple arrays at a time.

Specify Global Settings for a Calculation

SET commands for Essbase calculation scripts help define behavior of different functional aspects of block storage calculations. The first occurrence of a SET command stays in effect until the next occurrence of the same SET command in the script.

The following commands define calculation behavior:

Table 23-4 List of Commands for Defining Calculation Behavior

Command	Calculation
SET AGGMISSG	Specify how Essbase treats #MISSING values during a calculation.
SET CACHE	Adjust the default calculator cache size. Refer to Essbase Caches and Calc Performance.
SET CALCPARALLEL	Enable parallel calculation. Refer to Enable CALCPARALLEL Parallel Calculation.
SET CALCTASKDIMS	Increase the number of dimensions used to identify tasks for parallel calculation. Refer to Identify Additional Tasks for Parallel Calculation.
SET CLEARUPDATESTATUS	Control how Essbase marks data blocks for Intelligent Calculation. Refer to The SET CLEARUPDATESTATUS Command.
SET CREATEBLOCKONEQ	Turn on and turn off the Create Blocks on Equation setting, which controls the creation of blocks when you assign nonconstant values to members of a sparse dimension. Refer to Nonconstant Values Assigned to Members in a Sparse Dimension.
SET CREATENONMISSINGBLK	Enable calculations on potential data blocks and save these blocks when the result is not #MISSING.
SET FRMLBOTTOMUP	Optimize the calculation of sparse dimension formulas in large database outlines. Refer to Formulas on Sparse Dimensions in Large Outlines.
SET MSG SET NOTICE SET TRACE	Display messages to monitor and trace a calculation.
SET RUNTIMESUBVARS	Declare runtime substitution variables that are used in a calculation script. Refer to Runtime Substitution Variables in Calculation Scripts Run in Essbase and Runtime Substitution Variables in Calculation Scripts Run in Smart View
SET UPDATECALC	Turn Intelligent Calculation On or Off.

In the following calculation script, Essbase displays messages at the detail level (SET MSG DETAIL;) when calculating the Year dimension and displays messages at the summary level (SET MSG SUMMARY;) when calculating the Measures dimension:

SET MSG DETAIL;
CALC DIM(Year);

SET MSG SUMMARY;

CALC DIM(Measures);

Some SET calculation commands trigger additional passes through the database.

In the following calculation script, Essbase calculates member combinations for Qtr1 with SET AGGMISSG turned on, and then does a second calculation pass through the database and calculates member combinations for East with SET AGGMISSG turned off:

SET AGGMISSG ON;
Qtr1;
SET AGGMISSG OFF;
East;