SET CREATEBLOCKONEQ

The value of this calculation command takes precedence over the value of the Create Block on Equation setting defined for the database.

Syntax

SET CREATEBLOCKONEQ ON|OFF;

Parameters

ON: When a formula assigns a non-constant value to a sparse dimension member for which no block exists, Essbase creates a block.
OFF: When a formula assigns a non-constant value to a sparse dimension member for which no block exists, Essbase does not create a block.

Notes

If calculations result in a value for a sparse dimension member for which no block exists, Essbase creates a block. Sometimes, new blocks are not desired; for example, when they contain no other values. In large databases, creation and processing of unneeded blocks can increase processing time and storage requirements.

The Create Blocks on Equation setting is designed for situations when blocks would be created as a result of assigning something other than a constant to a member of a sparse dimension. For example, when Create Blocks on Equation is ON and West is assigned a value where it did not have a value before, new blocks are created. When this setting is OFF, blocks are not created.

Create Blocks on Equation setting is a database property. Its initial value is OFF; no blocks are created when something other than a constant is assigned to a sparse dimension member.

For more specific control, you can use the SET CREATEBLOCKONEQ calculation command within a calculation script to control creation of blocks at the time the command is encountered in the script. Use of SET CREATEBLOCKONEQ has the following characteristics:

When Essbase encounters SET CREATEBLOCKONEQ within a calculation script, the database-level setting is ignored.
You 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.
The Create Blocks on Equation setting is overridden by SET CREATENONMISSINGBLK ON.
The SET CREATEBLOCKONEQ command does not change the database-level Create Blocks on Equation property.
If no SET CREATEBLOCKONEQ command is encountered, Essbase uses the database-level setting to determine whether to create blocks.

When the Create Blocks on Equation setting is ON, Essbase uses the top-down calculation method to calculate each sparse member.

The Create Blocks on Equation setting is not consulted when Essbase assigns constants to members of sparse dimensions. The following table shows examples of sparse member calculations where constants or non-constants are assigned to them.

Table 3-4 Examples of Constant and Non-constant Assignments on Sparse Member Calculations

Assigned Value	Sparse Member Formula Example	New Block Created?
Constant	`West = 350`	Yes
Non-constant	`West = California + 120`	Yes, if the Create Blocks on Equation setting is ON. Otherwise, no.
Non-constant	`West = California * 1.05`	Yes, if the Create Blocks on Equation setting is ON. Otherwise, no.

For a tip on controlling creation of blocks when you work with non-constants and sparse dimensions, see Nonconstant Values Assigned to Members in a Sparse Dimension.

Example

Example 1

The following example is based on Sample.Basic. Data is loaded for only one block: (“100-10”, “New York”).

SET MSG SUMMARY;
SET CREATEBLOCKONEQ OFF;

"300-10" = "100-10" + 100000;

This calculation creates the block ("300-10", "New York"). Upon export, the database exports two blocks: the loaded block, and the new block. The calculation runs bottom-up.

Example 2

The following example is based on Sample.Basic. Data is loaded for only one block: (“100-10”, “New York”).

SET MSG SUMMARY;
SET CREATEBLOCKONEQ ON;

"300-10" = "100-10" + 100000;

This calculation creates 25 new blocks: 300-10 crossed with 25 stored members from the Market dimension. Upon export, the database exports 26 blocks: the loaded block, and the 25 new blocks. The calculation runs top-down.

Comparison of Example 1 and Example 2

In Example 1, the calculation script writer may have hoped to turn block creation OFF by using this line:

SET CREATEBLOCKONEQ OFF;

However, the calculation script has to create at least the one dependent block, to be able to execute the assignment statement.

SET CREATEBLOCKONEQ OFF does not mute block creation in the case where dependent blocks are needed for the calculation; however, it mutes extraneous block creation.

In the case of Example 1, Essbase avoids creating blocks crossing the Product dimension with the Market dimension. In Example 2, those extra blocks are created.