Database Reference
In-Depth Information
FIX (Cola, Sales, Actual)
SET CREATEBLOCKONEQ OFF;
Nevada = NEW YORK" + 100;
ENDFIX
Placing a sparse member on the left side of the formula is not without its dangers.
Consider the following script:
FIX (Cola)
SET CREATEBLOCKONEQ OFF;
Budget = actual->Sales->"NEW YORK" + 100;
ENDFIX
Budget is a member of a sparse dimension and the formula readily creates blocks. In
fact, as shown in Figure 4.6, it might create more blocks than expected.
Because the market dimension was not restricted in the FIx, Cola blocks were created
for every market. When working with formulas that have a sparse member on the left of
the equal sign, it is a good idea to monitor the number of blocks that get created. once
blocks are created, they are difficult to remove.
4 .7. 6 .1
Data Copy
There are two advantages to using DAtACoPy to create blocks.
First, the number of blocks that will be created is known. The chances of exploding the
database with unneeded blocks are greatly reduced. Secondly, DAtACoPy is probably
faster than using either CrEAtEBLoCkonEQ or CrEAtEnonmISSIngBLk.
Creating blocks using DAtACoPy works best when there is another dimension to
use as a model. one example would be to use the Budget Scenario to create the blocks
for the Forecast Scenario. When using the DAtACoPy, consider this trick. Instead of
fixing on a dense block cell that has live data, set the FIx on a dummy member that
is either #missing or has meaningless data. Even if the dense member being copied is
empty, Essbase will create the block. often times I see calculations that copy meaning-
ful data and then another pass to zero it out. This technique saves the second pass. The
caveat when creating completely empty blocks is that a dense restructure will delete
them. In many cases, this might not be a bad thing.
4 .7. 6 . 2
Using the CREATEBLOCKONEQ Statement
For those times when blocks can-
not be created by formulas on the sparse dimension or using data copies, Essbase has
special commands. These are very powerful commands, so use them with great care.
It is far too easy to create many more blocks than are needed. of the two commands,
CrEAtEBLoCkonEQ runs much faster, but it is a little tricky to get working.
Table 4.6
Block Explosion
Sales
Jan
Actual
Budget
Cola
Connecticut
#mi
101.1
California
#mi
101.1
oregon
#mi
101.1
nevada
#mi
101.1
California
#mi
101.1
n e w yo r k
1.1
101.1
