CLEARBLOCK

The CLEARBLOCK calculation command for Essbase sets cell values to #MISSING, and if all the cells are empty or #MISSING, removes the block. This command is useful when you need to clear old data values across blocks before loading new values.

CLEARBLOCK helps optimize database calculation speed. For example, if an initial calculation creates numerous consolidated level blocks, subsequent recalculations take longer, because Essbase must pass through the additional blocks. CLEARBLOCK clears blocks before a calculation occurs.

Another example: if a database to be copied contains a lot of empty blocks, copying the database also copies the empty blocks, resulting in a many more empty blocks. Using CLEARBLOCK EMPTY first makes the copy process more efficient.

If you use CLEARBLOCK within a FIX statement containing dense dimension members, Essbase clears only the cells within the fixed range, and not the entire block.

Syntax

CLEARBLOCK ALL | UPPER | NONINPUT | DYNAMIC | EMPTY;

Parameters

ALL: Clears and removes all blocks.
UPPER: Clears consolidated level blocks.
NONINPUT: Clears blocks containing derived values. Applies to blocks that are completely created by a calculation operation. Cannot be a block into which any values were loaded.
DYNAMIC: Clears blocks containing values derived from Dynamic Calc and Store member combinations.
EMPTY: Removes empty blocks (blocks where all values are #MISSING).

Notes

If you regularly enter data values directly into a consolidated level, the UPPER option overwrites your data. In this case, you should use the NONINPUT option, which only clears blocks containing calculated values.
If you use CLEARBLOCK EMPTY, the resulting, smaller database can be processed more efficiently; however, the CLEARBLOCK EMPTY process itself can take some time, depending on the size and density of the database.
If CLEARBLOCK is used within a FIX command on a dense dimension, the FIX statement is ignored and all blocks are scanned for missing cells.
In a FIX statement, blocks are cleared only if the entire CLEARBLOCK block is selected by the FIX (no dense dimensions in the FIX), and the block is update-able (it is not a replicated-partition target region). If you wish to retain empty blocks, then in the FIX statement, set the blocks to #MISSING, instead of using CLEARBLOCK.

For example, the following command block clears East data and removes the block (because Market is sparse):
```
FIX("East")
    CLEARBLOCK ALL;
ENDFIX
```
The following command block sets New York data values to #MISSING without removing the blocks:
```
FIX("East")
    "New York" = #Missing;
ENDFIX
```
To use this command with parallel calculation, use FIXPARALLEL...ENDFIXPARALLEL instead of SET CALCPARALLEL.

Example

CLEARBLOCK ALL;
CLEARBLOCK UPPER;
CLEARBLOCK NONINPUT;
CLEARBLOCK DYNAMIC;
CLEARBLOCK EMPTY;