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.
Syntax
SET CREATEBLOCKONEQ ON|OFF;
| Parameter | Description |
|---|---|
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. Use Administration Services or MaxL to turn the setting ON at the database-level. For more information about enabling Create Blocks on Equation, see the MaxL documentation in the Oracle Essbase Technical Reference or the Oracle Essbase Administration Services Online Help.
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 (see SET CREATENONMISSINGBLK).
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.
| 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, in the Oracle Essbase Database Administrator's Guide check for information about improving performance for non-constants assigned to members in sparse dimensions.
Example
The following example is based on Sample.Basic. West and East are members of the sparse Markets dimension.
FIX (Colas); SET CREATEBLOCKONEQ OFF West = California + 120; SET CREATEBLOCKONEQ ON East = "New York" + 100; ENDFIX
Because of the preceding SET CREATEBLOCKONEQ OFF command, Essbase does not create blocks for new values of West. Because the setting has been reversed to ON in the next command, Essbase creates blocks for new values of East.
See Also