Block Creation in Essbase – Part 2

Block Creation in Essbase – Part 2

 

Oracle Hyperion Essbase: Demystifying Block Creation

This post is the second in a two-part series covering Essbase & OAC calculation script and Planning & PBCS Calculation Manager calculations and how the underlying engine creates and stores data blocks.

In my last post, we talked about creating blocks in Essbase and went through a few different approaches that can help us achieve the desired results. The previous post ended with a discussion on using DATACOPY to create blocks. I stated that it was my preferred method of making sure the right blocks were created for use. However, we may run into a situation requiring an “IF” test to determine when we create the blocks. In this situation, we cannot use DATACOPY as it is not syntactically valid inside an IF test.

 

Continuing with our previous example using a slightly modified version of the Sample Basic database, let’s set up the following scenario (for the specifics, please refer back to Post 1):

 

 

We had previously gotten our Marketing expenses to populate for Budget and increased by 5% via DATACOPY. Now we’d like to do the same thing but only when Actual Sales are greater than 150. Based on this dataset, New Hampshire will be excluded from the calculation. It might be tempting to say something like:

IF(“Sales”->”Actual” > 150)
DATACOPY "Actual" to "Budget";
“Budget” = “Budget” * 1.05;
ENDIF:

 

Referring back to the original rules of block creation that we learned we know that assigning a constant to a Sparse dimension member will create blocks. Saying “New York” = 10; in a calc script will create blocks. Let’s use this to our advantage by setting our Budget Marketing Expense to 0 and then clear it out after as follows:

FIX(@CHILDREN("East"))
FIX("Marketing") 
"Budget" = 0;
"Budget" = #MISSING;
ENDFIX

ENDFIX

 

We’ve enclosed the calc lines within a FIX on Marketing, and then we set the value to 0, which creates the block, and then we set the value to #MISSING which leaves the block in place but sets it up as an empty block. Now we have a set of blocks created that we can write further calculations against:

FIX("Budget")
"Marketing"
(
IF("Sales"->"Actual" > 150)
"Marketing" = ("Marketing"->"Actual") * 1.05;
ENDIF
)
ENDFIX

 

Note: This technique does create blocks for anything that meets the criteria for the FIX statement(s), so we need to be tight with what we fix on. If we aren’t careful, we can inadvertently create lots of extra blocks. When testing this out, refer to the Database Statistics, and the Number of Existing Blocks before and after you run the calculation to make sure that you haven’t blown up the size of the database. I have also included a “CLEARBLOCK EMPTY” statement to remove any blocks that I created that don’t have any numbers in them.

 

Finished Calculation

Putting it all together the finished calc looks like this:

FIX(@CHILDREN("East"))
FIX("Marketing") 
"Budget" = 0;
"Budget" = #MISSING;
ENDFIX
FIX("Budget")
"Marketing"
(
IF("Sales"->"Actual" > 150)
"Marketing" = ("Marketing"->"Actual") * 1.05;
ENDIF
)
ENDFIX
CLEARBLOCK EMPTY;
ENDFIX

 

Our finished calculation produces this set of data:

 

 

Which is precisely what we are looking for.

 

If you are having difficulty getting your calculations to work the way you need them to, or if you need some assistance with creating calculations, reach out to PARC Consulting to see how our experts can assist you with your environment.

2 Comments

Leave a Reply

  • Oracle EPM Cloud Update – Dec’21 (21.12)

    These Oracle EPM updates are scheduled to take place during the first nightly maintenance period on or after 2AM ET on Saturday Dec 3, 2021 (test environments) and on or after 2AM ET on Saturday, Dec 17, 2021 (production environments).

    December 2, 2021
  • Oracle EPM Cloud Update – Nov’21 (21.11)

    These Oracle EPM updates are scheduled to take place during the first nightly maintenance period on or after 2AM ET on Saturday Nov 5, 2021 (test environments) and on or after 2AM ET on Saturday, Nov 19, 2021 (production environments).

    November 3, 2021
  • Block Creation in Essbase – Part 1

    A data block is the fundamental unit of storage for a cube in Essbase or Planning (including Planning Cloud). When developing a new calculation or updating an existing calculation, inevitably, block creation will become an issue for us. When writing calculations, you need to be sure that the data blocks either already exist or that they get created as part of your calculation; otherwise, you’ll end up without the data you need.

    October 14, 2021
  • Oracle EPM Cloud Update – Oct’21 (21.10)

    The below Oracle updates are scheduled to take place during the first nightly maintenance period on or after 2AM ET on Saturday Oct 2, 2021 (test environments) and on or after 2AM ET on Saturday, Oct 16, 2021 (production environments).

    October 1, 2021