Create Blocks Explained

missing data blocks in BSo can be perplexing. For anyone new to Essbase, the concept of missing blocks is hard to grasp. Even experienced developers and administrators find block creation confusing. I expect that most of the readers have experienced situations when blocks must be explicitly created. For those who have not, this section might not make a lot of sense, but eventually you will need these tips.

The most important thing to remember when creating blocks is that the goal is to create the appropriate block without creating extra blocks. As mentioned repeatedly in this chapter, the more blocks that are processed, the slower the database. It is for this reason that the more experienced developer’s tend to shy away from the statements CrEAtEBLoCkonEQ and CrEAtEnonmISSIngBLk. While these statements are powerful, they can needlessly increase the number of blocks if not used correctly.

The commands CrEAtEBLoCkonEQ and CrEAtEnonmISSIngBLk have not always been available in Essbase. Prior to their introduction, blocks were created by rearranging the formula so that the result is placed into a sparse member. The following script will not create a block because Sales is a member of a dense dimension.

This script assumes that the block for Cola->nevada->Actual did not exist prior to running the script, as shown in table 4.4.

FIX (Cola, Nevada, Actual) SET CREATEBLOCKONEQ OFF;

Sales = "NEW YORK" + 100;

ENDFIX

rewriting the formula so that the result is placed in nevada, which is sparse, creates the necessary block, as shown in table 4.5.

Table 4.4 Data Results

Sales Jan

Actual Budget

Cola nevada #mi #mi

new york 25 #mi

Table 4.5 Data Results after Change

Sales Jan

Actual Budget

Cola nevada 125 #mi

new york 25 #mi

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

The first warning is to make sure that the FIx tightly controls the number of blocks that will be processed. I have already shown what happens when a large dimension is not included in the fix.

The rules for CrEAtEBLoCkonEQ include:

•  The result of the formula must be a member of a sparse dimension.

•  Do not use cross-dimensional formulas.

•  CrEAtEBLoCkonEQ statement forces a top-down calculation.

Below is an example of a cross-dimensional formula. measures and year are dense. All other dimensions are sparse. The database is completely empty except for Cola->new york->Actual->Sales->Jan.

FIX ("Cola")

SET CREATEBLOCKONEQ OFF;

"Nevada" = "Actual"->"New York" + 100;

ENDFIX

note that CrEAtEBLoCkonEQ is oFF. table 4.7 shows the results of this adjusted formula.

Even though CrEAtEBLoCkonEQ is oFF, the budget block for nevada is created.

When there is a cross-dimensional formula, CrEAtEBLoCkonEQ does not influence the result.

The next script is exactly the same as the prior script except that the cross-dimension reference has been removed. CrEAtEBLoCkonEQ is still set to oFF.

FIX ("Cola")

SET CREATEBLOCKONEQ OFF;

Nevada = "NEW YORK" + 100;

ENDFIX

As can be seen in table 4.8, blocks were created for Actual, but not Budget for nevada.

In this next example, CrEAtEBLoCkonEQ is on and the cross-dimensional for-mula has been removed.

FIX ("Cola")

SET CREATEBLOCKONEQ OFF;

Nevada = "NEW YORK" + 100;

ENDFIX

Table 4.7 Block Explosion Eliminated

Sales Jan

Actual Budget

Cola Connecticut #mi #mi

California #mi #mi

oregon #mi #mi

nevada 101.1 101.1

California #mi #mi

new york 1.1 #mi

The blocks for nevada were created for Actual and Budget as shown in table 4.9. This example is how CrEAtEBLoCkonEQ should work.

4.7.6.3 CREATENONMISSINGBLK When Nothing Else Works the CrEAtEnon mISSIngBLk statement is the “last resort” option. not only can this statement cre-ate more blocks than you need, it also crecre-ates them slowly. This command follows the outline processing the “potential” blocks according to the FIx statement. Each potential block is loaded into memory. When all of the blocks have been consumed by the fix, Essbase then writes any block that has a value. Blocks that are entirely empty are dis-carded, hence, the name CrEAtEnonmISSIngBLk. Essbase will then log a message specifying the number of missing blocks that were not written back. use this feature only in FIx statements that tightly control the number of blocks being processed.

one might ask why use a command that has so many negative side effects? The answer is that sometime there is no other way. table 4.10 shows the results of the script below. note that the formula result is a dense member and the formula itself has cross-dimensional references.

FIX (Budget);

SET CREATENONMISSINGBLK ON;

(Sales=Sales->Actual * 1.1;

Misc = Misc->Actual * .95 ;) ENDFIX

Budget data blocks were created where actual data existed. Blocks where the formula resolved to #missing did not get created. The following log message specified the num-ber of blocks that were calculated in memory, but were not written.

Total #Missing Blocks Not Written Back: [4.5000e+002] Blocks Table 4.8 Without Cross-Dimensional Formula

Table 4.9 Final Block Creation Results

Sales Jan