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.

Calculate the Essbase Outline

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

Table 24-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 24-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 24-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 24-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 Enabling CALCPARALLEL Parallel Calculation.

SET CALCTASKDIMS

Increase the number of dimensions used to identify tasks for parallel calculation. Refer to Identifying Additional Tasks for Parallel Calculation.

SET CLEARUPDATESTATUS

Control how Essbase marks data blocks for Intelligent Calculation. Refer to Using 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;

See Also

SET Commands