Business Rule - How to create blocks different techniques (@CreateBlock) - Part 1

Hello,

We have so many articles, docs and blog on "How to create blocks in Essbase" and this post is a small contribution to this topic.

As we blog through google we find that there are many ways to create blocks.

1. Data Load -

        Forcefully creating blocks by loading data at the desired intersection

2. DataCopy - 

       Create blocks ondemand using DATACOPY commands
     
       DATACOPY mbr1 ot mbr2;

3. Sparse Calculation - 

      Convert the scripts to have sparse calculation

      FIX(Sparse1, Sparse2, Dense1)
          Dense2 = 1;
      ENDFIX

          TO

      FIX(Sparse1, Dense2 , Dense1)
          Sparse2 = 1;
      ENDFIX

4. @Allocate command

      Create blocks using @ALLOCATE command

5. SET Commands (CREATEBLOCKONEQ, CREATENONMISSINGBLK)

      Create blocks using CREATBLOCKONEQ and CREATENONMISSINGBLK set commands

6. @CREATEBLOCK

     With the latest version Oracle has introduced a new function @CREATEBLOCK which creates empty blocks where cells are set to #Missing, here we will discuss on this with example. Note:- POST, INP, OTH are members from sparse dimension.

SET UPDATECALC OFF;
FIX (PC_0001 PC_0002 FY20 )
    FIX ("INP")
        A_UOC_Volume (
            A_UOC_Volume->"OTH";
        )
    ENDFIX
ENDFIX

When I execute this script and try to see the result, data is not copied to INP

Now let us change the script a bit

SET UPDATECALC OFF;
FIX (PC_0001 PC_0002 FY20 )
POST (
       @CREATEBLOCK("INP");
    )
    FIX ("INP")
        A_UOC_Volume (
            A_UOC_Volume->"OTH";
        )
    ENDFIX
ENDFIX

Note:- We need to have sparse member block and it should be different from that of which the block needs to be created.

Now lets see the result

Values are copied to INP

But, this will create blocks for all PC's, now lets try to restrict the block creation

SET UPDATECALC OFF;
FIX (PC_0001 PC_0002 FY20 )
POST (
IF(A_UOC_Volume->OTH == 1)
       @CREATEBLOCK("INP");
   ENDIF
    )
    FIX ("INP")
        A_UOC_Volume (
            A_UOC_Volume->"OTH";
        )
    ENDFIX
ENDFIX

Now lets see the result

As you can see it copied data to INP only where OTH has 1

Conclusion : - @CREATEBLOCK can be used within IF condition to check and create block based on condition :) .

Comments are appreciated