Context : Following a functional requirement, administrator tries to implement new calculation in Hyperion Essbase script or Hyperion Planning Business rules, but after script set up, for some reason data do not get calculated. One possible reason could be block creation issue.
Objective: We’ll introduce in this section various work around to manage this recurrent issue in Hyperion EPM tools.
Frustration can raise when a correctly designed essbase script or planning rules does not work as required. Block creation issue can be one possible reason.
In essbase, data are loaded in a cell, and physically stored in a block, which is the level of granularity for data storage. To actually store data in the database, a block should exist. When it comes to calculate a new data, sometimes a block does not exist behind the target cell, and does not get created at calculation.
Note that block creation topic can only be raised during calculation. Direct data input (via smart view, excel add-in lock & send, data import) always creates the data storage block.
Below are proposed workarounds to manage block creation issues :
1.Block creation by writing on a sparse member:
Objective is to put in the last fix before calculation dense dimension member(s), and to write the calculation on a sparse target member. Choose a sparse dimension member part of the calculation perimeter, to which calculation formula will be applied, and try to follow syntax below:
2. Block creation using block calculation mode:
If solution 1 is unsuccessful, which can be possible depending on your database set up, then you should try the solution 2 introduced below.
Idea is to use the “block calculation” syntax proposed by essbase to naturally creates block at calculation.
The member to be calculated, dense or sparse, should be declared and the calculation formula should be in parenthesis, followed by a semi-column caracter to end calculation.
3. Block creation through datacopy function (initialization of blocks before calculation):
If solutions proposed above do not work or are impossible to implement due to calculation format, then a third workaround can be applied : the creation of block prior to calculation, on the calculation perimeter, using a DATACOPY function.
DATACOPY function is able to copy a data from a source perimeter to a target perimeter, and the datastorage blocks at source are copied to target.
So you can select a source perimeter where you know data are always loaded and so blocks are; (exchange rate input point of view, mandatory initialization flag, etc…) to copy it to the calculation perimeter you want blocks to be created, using DATACOPY function.
Warning : data will be copied as well as blocks, from source to target, so you’ll need to make sure target perimeter will be fully covered by target calculation, otherwise remanent data will still be present after calculation.
You can find an example below of the syntax to be applied:
Nota bene : make sure to apply DATACOPY only on levels of calculation, for performance purposes.
4. Block creation using a parallel point of view with block already created:
To avoid block creation issue, you can also use an alternate method, which applicability will depend on the calculation to implement.
The idea will to FIX a perimeter on which blocks are already created, and the calculation will refer to the calculated point of view using the cross dim command “->”.
Exemple:
You need to calculate account “A” on currency “French Franc”, on which blocks are not yet created. You know that you have blocks created on currency “Dollar of the United States of America”.
You will then FIX the currency “Dollar of the United States of America” and in the calculation, you will cross dim on the caculated currency “French Franc”
Ex:
FIX(“Dollar of the United States of America”)
“A”->”French Franc” =”A”*1,1. (considering you need 1,1 French franc for one dollar, which is the admitted exchange rate).
ENDFIX
5. Block creation using block creation function CREATEBLOCKONEQ :
As a last solution, user can try the native bloc creation function proposed in Essbase.
Activate block creation function by placing following statement between the last fix and the formula section:
SET CREATEBLOCKONEQ ON;
See example below:
This is supposed to create block on calculation perimeter.
This should be activated in the calculation script if not activated by default in the essbase database settings.
Use this function carefully not to create blocks on wider perimeter than required by calculation.
Also use it when calculation does not include cross dimensionnal members, otherwise function will have no impact.
