Top >
Calculation Commands >
SET CREATENONMISSINGBLK
SET CREATENONMISSINGBLK
Controls whether or not potential blocks are created in memory for calculation
purposes and whether or not #MISSING blocks are stored.
Syntax
SET CREATENONMISSINGBLK ON|OFF;
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. |
Description
The SET CREATENONMISSINGBLK command affects
the results of calculations on both sparse and dense dimensions.
By default, Analytic Services applies dense-member formulas only to existing data blocks.
The SET CREATENONMISSINGBLK ON command enables Analytic Services to create potential
blocks in memory where the dense-member formulas are performed. Of these potential blocks, Analytic Services
writes to the database only blocks that contain actual values; blocks resulting
in only #MISSING values are not written to the database.
The creation of #MISSING blocks resulting from sparse-member
formulas is governed by the Create Block on Equations setting. (See SET
CREATEBLOCKONEQ.) The SET CREATENONMISSINGBLK ON command 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
Analytic Services 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)
- The @VAR function
- The @XREF function
- If Analytic Services encounters the @CALCMODE(BOTTOMUP)
in a member formula, it ignores the @CALCMODE command. A message about the member is written in the application log saying that the command is being ignored.
- If a batch calculation contains top-down formulas and SET CREATENONMISSINGBLK
is set to 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/ENDFIX regions. As a warning, when the potential number of blocks exceeds
20 million blocks, Analytic Services writes an entry to the application log
showing the number of blocks to be calculated and recommending using FIX/ENDFIX.
- Where needed in the calculation script, you can use multiple SET CREATENONMISSINGBLK
commands to affect the calculations that follow each command. However, consider
that each time a SET CREATENONMISSINGBLK command is encountered within a set
of FIX and ENDFIX statements, the calculator cycles through the database,
potentially affecting calculation performance.
Notes
- SET CREATENONMISSINGBLK affects only creation of new blocks. If existing
blocks become #MISSING blocks after formula execution, they are not deleted.
- The value set by the SET CREATENONMISSINGBLK command stays in affect until
the next SET CREATENONMISSINGBLK command is processed or the calculation script
is finished.
- When the calculation script includes both SET CREATENONMISSINGBLK ON and
SET MSG DETAIL, any #MISSING block that is not stored is indicated in the
application log.
- If SET MSG is set to SUMMARY, when SET CREATENONMISSINGBLK is set to ON,
Analytic Services writes an entry to the application log saying 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, Analytic Services 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 the Sample Basic database. 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
See Also
Database Administration Guide
SET CREATEBLOCKONEQ