Saturday, July 17, 2010

Block creation Issue

Hi All,

  Many times while writing Essbase calc scripts you must have encountered an issue of block creation i.e. you are not able to see any results for your simple calculations !...

 Essbase normally create blocks when data is loaded, during aggregation of sparse dimensions
and as a result of a DATACOPY command.

Essbase does not create blocks as a result of most dense member assignment calculations. For
example based on the Sample.Basic outline, assume that “Sales” is a dense dimension member
and actual is a sparse dimension member. The following formula will fail because it will not
create new blocks for Budget.
FIX (budget)
"Sales"="Sales"->"Actual"*1.1;
ENDFIX

 So, in short if you are performing some basic arithmatic calculations, essbase won't create new block for sparse dimensions.. This is done by essbase to improve it's calculation performance but  it is a headache for developer..

 Ways to tackle this issue are:

1.DATACOPY into Sparse Member:
You can use the data copy command to create blocks in the same pattern as the actual
blocks. In above example you can do DATACOPY ACTUAL TO BUDGET prior to writing calc. command... It will first create a block for Budget and then will overwrite a calculated value into it.. You can also fix some combination and always enter some dummy data into it as a part of your essbase process and can use this combination for block creation.

e.g. You can always enter '1' in Actual->Test_Acc and can use it for all block creation datacopy commands..



2. CALCULATION COMMANDS TO ENABLE BLOCK CREATION
You can also use  SET CREATEBLOCKONEQ and SET CREATENONMISSINGBLK commands for block creation. But these commands are bit heavy on your calculation and degrades calc script performance so for big calc scripts it is always advisable to use DATACOPY approach...

 Cheerz!
--CJ

3 comments:

Arijit said...

Hey Dude,
essbase creates blocks if you put any sparse equation under IF condition. So that can also be put as one of the techniques of creating blocks.
----- Arijit Sinha

Sandip Joshi said...
This comment has been removed by the author.
Vikram S said...

Hey Dude,

Can you please confirm on your statement.

"Essbase creates blocks if you put any sparse equation under IF condition. So that can also be put as one of the techniques of creating blocks. "

Is this true? If so, what should be the other limitations. Because I could not see any blocks created like this.