SET CREATEBLOCKONEQ

Controls, within a calculation script, whether or not 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.

Syntax

SET CREATEBLOCKONEQ ON|OFF;

ON When a calculation formula assigns a non-constant value to a member of a sparse dimension for which a block does not exist, Analytic Services creates a new block.
OFF When a calculation formula assigns a non-constant value to a member of a sparse dimension for which a block does not exist, Analytic Services does not create a new block.

Description

If calculations result in a value for a sparse dimension member for which no block exists, Analytic Services creates a new block. Sometimes, the 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 as well as the storage requirement for a database.

For the situation when new blocks would be created as a result of assigning something other than a constant to a member of a sparse dimension, Analytic Services provides the Create Blocks on Equation setting. For example, when the Create Blocks on Equation setting 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, new blocks are not created.

The Create Blocks on Equation setting is a database property. The initial value for the Create Blocks on Equation setting is OFF; no new blocks are created when something other than a constant is assigned to a sparse dimension member. You can use Administration Services or MaxL to set the Create Blocks on Equation setting to ON at the database-level. For more information about enabling the Create Blocks on Equation property for a database, see MaxL documentation in the Technical Reference or Administration Services online help.

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

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

The Create Blocks on Equation setting is not consulted when Analytic Services 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.

Description Sparse Member Formula Example New Block Created?
Constant is assigned as the value West = 350 Yes
Non-constant is assigned as the value West = California + 120 Yes, if the Create Blocks on Equation setting is ON. Otherwise, no.
Non-constant is assigned as the value 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, in the Database Administrator's Guide, Volume III, check for information about improving performance for non-constants assigned to members in sparse dimensions.

Example

The following example is based on the Sample Basic database. West and East are members of the Markets dimension, which is sparse.

FIX (Colas);
SET CREATEBLOCKONEQ OFF
West = California + 120;                                         
SET CREATEBLOCKONEQ ON
East = "New York" + 100;                                         
ENDFIX

Because of the preceding SET CREATEBLOCKONEQ OFF command, Analytic Services does not create new blocks for new values of West. Because the setting has been reversed to ON in the next command, Analytic Services creates new blocks for new values of East.

See Also

Database Administrator's Guide, Volume III
Administration Services online help
MaxL Reference (alter database) in the Technical Reference
SET CREATENONMISSINGBLK