#MISSING Values

If no data value exists for a combination of block storage dimension members, Essbase gives the combination a value of #MISSING. #MISSING values are different from zero (0) values. By default, #MISSING data is not consolidated, unless you enable a setting. You can remove #MISSING blocks to improve performance.

Consolidation of #MISSING

The following shows how Essbase calculates #MISSING values. In this table, X represents any number:

Table 35-5 How Essbase Treats #MISSING Values

Calculation/Operation	Result
X + #MISSING	X
X – #MISSING #MISSING – X	X -X
X * #MISSING	#MISSING
X / #MISSING #MISSING / X X / 0	#MISSING #MISSING #MISSING
X % #MISSING #MISSING % X X % 0	#MISSING #MISSING #MISSING
X == #MISSING	FALSE, unless X is #MISSING
X != #MISSING X < > #MISSING	TRUE, unless X is #MISSING TRUE, unless X is #MISSING
X <= #MISSING	(X <= 0)
X >= #MISSING	(X >= 0) or (X == #MISSING)
X > #MISSING	(X > 0)
X < #MISSING	(X < 0)
X AND #MISSING: Y AND #MISSING, where Y represents any nonzero value 0 AND #MISSING #MISSING AND #MISSING	#MISSING 0 #MISSING
X OR #MISSING: Y OR #MISSING, where Y represents any nonzero value 0 OR #MISSING #MISSING OR #MISSING	Y #MISSING #MISSING
IF (#MISSING)	IF (0)
f (#MISSING)	#MISSING for any Essbase function of one variable
f (X)	#MISSING for any X not in the domain of f and any EssbaseEssbase function of multiple variables (except where specifically noted)

By default, Essbase does not roll up #MISSING values. However, if you always load data at level 0 and never at parent levels, you should enable the setting for consolidating #MISSING values. This setting provides a calculation performance improvement of 1%–30%. The performance improvement varies, depending on cube size and configuration.

Caution:

The default, not consolidating #MISSING values, must be in effect if you load data at parent, rather than child, levels, if any child member combinations have #MISSING values. If all child member combinations have any other values, including zero (0), Essbase rolls up the child values and overwrites the parent values correctly, so you can safely change the default.

To consolidate #MISSING values, use one of the following methods. The degree of performance improvement you achieve depends on the ratio between upper-level blocks and input blocks in the database.

To change how #MISSING values are consolidated, you can use one of these methods:

• Select the Aggregate missing values cube option in the Essbase web interface.

  See Setting Advanced Cube Properties.

• In a calculation script, use the SET AGGMISSG calculation command.

• Use the alter database enable aggregate_missing MaxL statement.

If you enable consolidation of #MISSING values in the cube properties, the cell calculation order within a data block changes.

When the setting for consolidating #MISSING values is disabled, note that the performance overhead is particularly high in the following situations:

• When the ratio of calculated data blocks to input data blocks is low

• When you load many data values at parent levels on sparse dimensions

In these situations, the performance overhead is 10%–30%. If calculation performance is critical, you may want to reconsider the database configuration or how you load data.

Removing #MISSING Blocks

You can use the CLEARDATA command to change the value of cells in a block to #MISSING. It does not remove the data blocks. These extra blocks can slow retrieval and calculation performance.

If the #MISSING blocks are slowing performance, perform either action:

• Use the CLEARBLOCK command to remove the data blocks.

• Export the data and re-import it.

Removing empty blocks improves performance when data values already have been loaded. However, data load process time increases if new values require that blocks be created.