SET CREATENONMISSINGBLK

The SET CREATENONMISSINGBLK calculation command for Essbase controls whether potential blocks are created in memory for calculation purposes, and whether #MISSING blocks are stored.

This command affects the results of calculations on sparse and dense dimensions.

By default, Essbase applies dense-member formulas only to existing data blocks. SET CREATENONMISSINGBLK ON enables Essbase to create potential blocks in memory where the dense-member formulas are performed. Of these potential blocks, Essbase writes to the database only blocks that contain values; blocks resulting in only #MISSING are not written to the database.

The creation of #MISSING blocks resulting from sparse-member formulas is governed by SET CREATEBLOCKONEQ. SET CREATENONMISSINGBLK ON ensures that only non-empty blocks are created, regardless of the Create Block on Equations setting.

In order to create new blocks, setting SET CREATENONMISSINGBLK to ON requires Essbase to anticipate the blocks that will be created. Working with potential blocks can affect calculation performance. Consider the following situations carefully:

When SET CREATENONMISSINGBLK is ON, all sparse-member formulas are executed in top-down mode. Dense member formulas are flagged for top-down calculation when they contain the following:
- Sparse members
- Constants (for example, Sales = 100,000)
- @VAR
- @XREF
If Essbase encounters @CALCMODE(BOTTOMUP) in a member formula, it ignores @CALCMODE.
If a batch calculation contains top-down formulas and SET CREATENONMISSINGBLK is ON, Intelligent Calculation is turned off. Within the scope of the calculation script, all blocks are calculated, regardless if they are marked clean or dirty.
To reduce the number of blocks to be calculated, use this command within FIX...ENDFIXregions. As a warning, when the potential number of blocks exceeds 20 million, Essbase writes an entry to the application log showing the number of blocks to be calculated and recommending using FIX/ENDFIX.
You can use multiple SET CREATENONMISSINGBLK commands in a calc script, each affecting calculations that follow. However, consider that each time SET CREATENONMISSINGBLK is encountered within a set of FIX and ENDFIX statements, the calculator cycles through the database, potentially affecting calculation performance.

Syntax

SET CREATENONMISSINGBLK ON|OFF;

Parameters

ON: Calculations are performed on potential blocks as well as existing blocks. If the result of the calculation is not #MISSING, the block is stored. The Create Blocks on Equations setting is ignored.
OFF: Calculations are performed only on existing blocks. This is the default setting.

Notes

SET CREATENONMISSINGBLK affects only creation of new blocks. If existing blocks become #MISSING after formula execution, they are not deleted.
The value set by SET CREATENONMISSINGBLK stays in effect until the next SET CREATENONMISSINGBLK is processed, or the calculation script terminates.
When the calculation script includes both SET CREATENONMISSINGBLK ON and SET MSG DETAIL, any non-stored #MISSING block is indicated in the application log.
If SET MSG is set to SUMMARY, when SET CREATENONMISSINGBLK is set to ON, Essbase writes an entry to the application log stating that Create Non #MISSING Blocks is enabled.
If SET MSG is set to SUMMARY, and SET CREATENONMISSINGBLK is set to ON, at the end of the calculation, Essbase writes an entry to the application log showing the total number of #MISSING blocks that were not created.

Example

The following example is based on a variation of Sample Basic. Assume that the Scenario dimension, of which Actual is a member, is sparse. "Jan Rolling YTD Est" is a member of the dense time dimension, Year.

FIX (Budget)
  SET MSG DETAIL;
  SET CREATENONMISSINGBLK ON;
  "Jan Rolling YTD Est"= (Jan->Actual+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov+Dec);
ENDFIX