- October 14, 2021
- Posted by: Paul Hoch
- Categories: Hyperion, Oracle
Block Creation in Essbase – Part 1
Oracle Hyperion Essbase: Demystifying Block Creation
This post is the first 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.
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. In some circumstances, we’ll find that a calculation that we wrote that seemed to work correctly in testing somehow doesn’t produce the same results when you start with a fresh set of data. Or, we need to change a dimension from Sparse dimension type to Dense dimension type (or vice versa), and suddenly, your calculation fails. The calculations may run successfully, but we don’t see the expected data when looking at the results. The most common culprit in these scenarios is a problem with block creation; we haven’t correctly created the needed blocks. 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.
Essbase was created initially by Arbor Software. Arbor and Hyperion Solutions merged in 1998. Oracle acquired Hyperion in 2007. Hyperion Planning was developed using Essbase as its data repository around 2001. Oracle PBCS (Planning and Budgeting Cloud Services) was launched as a cloud version of the original Hyperion Planning tool and is now named Oracle Cloud EPM Planning. OAC (Oracle Analytics Cloud) was originally going to be called Essbase Cloud Service (EssCS) but was enhanced and changed to OAC. Additionally, FCCS (Financial Close and Consolidation Cloud Services) was also written to leverage the Essbase/Planning/PBCS frameworks. Regardless of the tool being used — native, on-premises Essbase, Oracle Hyperion Planning, IPBCS, FCCS, or OAC — the storage, calculation engine, and block creation in Essbase are factors in all of them. You may see references to Essbase, PBCS, Planning calc scripts, and Calc Manager Rules within this post. These can be used interchangeably for this discussion since we reference the underlying technology and storage/calculation engine.
Block Creation Background
You might wonder why this is an issue at all. Why doesn’t Essbase just create all the blocks? If we take a recent client database (which is pretty small) as an example, their dimensions were set up like this:
Based upon the Sparse/Dense configuration seen here, we can review the statistic information and see that the block size and the potential number of blocks are:
Block size (B): 675072
Potential number of blocks: 312554617159680
If you multiply out the size of each block times the number of potential blocks, we get 2.11E+20 maximum bytes required. This translates to over 191,000,000 terabytes or 183+ Exabytes! If Essbase just created all of the potential blocks it might ever need, you would need a huge server just to generate the database even before you loaded anything into it.
Another major factor to consider is that multidimensional databases are inherently very sparsely populated with data. Most production Essbase databases use far less than one percent of the potential blocks. It would be pretty inefficient to create a database of that size only to use up a fractional percentage of it. For this reason, Essbase will only create blocks in specific cases: as it needs them.
How and when blocks are created
Now that we know why Essbase doesn’t create all of the blocks let’s explore how blocks get created. When I was first learning Essbase, I was taught that blocks are created in the following instances:
-
- Data loading: If you load data to an intersection in the database and the block doesn’t already exist, Essbase will create the block it needs. This goes for batch data loading and user submissions using Smart View and via Planning web forms.
- DATACOPY: If in a calc script, you use the DATACOPY command, it will create blocks. If I say DATACOPY “Actual” to “Budget”; and the Budget blocks don’t exist, Essbase creates them.
- Assignment of a constant: If you explicitly assign a value in a calc, it will create blocks. Saying “New York”=100; will create blocks. This can be dangerous if you haven’t constrained the focus of your calculation; you could inadvertently create blocks for every intersection of “New York” and the other dimensions, assigning their values to 100. If you assign a constant value to a sparse member, make sure you enclose the assignment within a very tight FIX statement.
- Sparse Aggregations: Rollups within your sparse dimensions will create parent blocks as aggregations occur. For example, “New York” adds with “Connecticut” to form a new block at the total “East.”
- SET CREATEBLOCKONEQ: You can use this setting within a calc that will tell Essbase to create blocks on equations. This is another potentially dangerous setting in that you can end up creating tons of blocks that you might not mean to create. I have also found that this setting can be a bit hit or miss in terms of where it works. For more information on this setting, refer to the Oracle Tech Reference.
- @ALLOCATE and @MDALLOCATE: These functions will create blocks during an allocation calculation. These functions can be helpful, but their performance of can be slow. It is often worth testing, however.
Those are the only ways that I originally learned that blocks are created. In later versions of Essbase, a new SET command – SET CREATENONMISSINGBLK was released, which also impacts here. More on that in a bit.
Testing it out
To do some testing of these methods, I set up a small example test case. I took the existing Sample Basic cube and changed scenario to be a Sparse dimension type. This is important if you are following along! After making that change, I loaded up the data using the Calcdat.txt file in the database directory.
Next, I wrote a quick clear calculation to clear out the Budget data like this:
FIX ("Budget")
CLEARBLOCK ALL;
ENDFIX
I now set up a test spreadsheet in Smart View that looks like this:
Now that we have set up our test environment with some sample data and some settings established, we will write a few different calculations to populate the Budget. We’ll focus just on Marketing expenses for now. Ultimately, we would like the Marketing expenses for our Budget to be 5% above the actual amounts. It seems like a reasonable request, right?
The first script is written with the following lines:
/* Establish the baseline Fix – Focus on Budget */
FIX("Budget")
/* Further isolate the data to our test area of the East States */
FIX(@CHILDREN("East"))
/* Set the Marketing dollars to be 5 pct more than Actual */
"Marketing" = ("Marketing"->"Actual") * 1.05;
ENDFIX
ENDFIX
From a logical perspective, the code makes sense. Focus on the Budget and the States I’m working with, then assign the Marketing value to be Actual * 1.05. However, this doesn’t produce the desired result. If we run this code as a calc script or calc manager rule, we will continue to get #Missing as the result. Why? Because the blocks for Budget don’t exist and the code as written doesn’t meet one of the previously identified methods for creating blocks. So, let’s revisit the code and see if we can get it to work.
A method you might be tempted to use is “SET CREATENONMISSINGBLK ON;” at the top of the script. If we modify our code to look like this:
SET CREATENONMISSINGBLK ON;
FIX("Budget")
FIX(@CHILDREN("East"))
"Marketing" = ("Marketing"->"Actual") * 1.05;
ENDFIX
ENDFIX
And then execute the calc; we’ll get this as a result:
FIX("Marketing")
FIX(@CHILDREN("East"))
DATACOPY "Actual" to "Budget";
"Budget" = "Budget" * 1.05;
ENDFIX
ENDFIX
This code will run quickly and will produce the expected results. Whenever possible, this is our preferred approach. We can control the number of blocks created using a FIX statement, and DATACOPY reliably creates just the blocks we need.
What’s next?
In an upcoming post, we will discuss some additional alternative ways we can accomplish block creation. Leave a comment on your preferred methods for creating blocks in Essbase. If you have questions or need any assistance, please contact the PARC team to see how we can help.
1 Comment
Leave a Reply
-
Oracle EPM Cloud Update – Jan’22 (22.01)
These Oracle EPM updates are scheduled to take place during the first nightly maintenance period on or after 2AM ET on Saturday Jan 8, 2022 (test environments) and on or after 2AM ET on Saturday, Jan 22, 2022 (production environments)
January 7, 2022 -
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 2
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.
October 22, 2021
I am still facing an issue on creating block .
Please find the code I have created .In fix “AS_PJ_107016” and “Oct” are dense members and others are Sparse.
FIX(“OU_603″,”OO_81430″,”CC_81430″,”PD_178″,”USD”,”CO_10501″,”OEP_Working”,”OEP_Forecast”,”FY22″,”Oct”,”PJ_Test Project1″,”AS_PJ_107016″,”DS_PJ_Calculated”)
SET CREATEBLOCKONEQ ON;
“ET_619000” = 100;
SET CREATEBLOCKONEQ OFF;
ENDFIX;