When I started my career, and started learning and working on Essbase and Hyperion, I was told that knowing about how the Essbase works, how the blocks work can differentiate a knowledgeable and a terrible Essbase admin. At the beginning, I didn’t think that was a big deal. Until days later, some block related problems came up regularly and issues I got didn’t make any sense. I double checked the syntax, logic for the codes, and issue was still there. I was very frustrated. Finally, I realized that Essbase had specific ways to create block. There are certain regulations when working with blocks.

 

A very common block issue is, each month end, quarter end or year end, when we work on Hyperion or PBCS to generate reports, after submitting drivers and assumptions data, after running business rules, there is still no data in the smart view reports or financial reports. Or Current year has data, but when it comes to the next fiscal year, there is no data at all. If you have experienced this issue, then I could tell you that the issue is about the Missing Blocks. Your business rules or scripts were trying to store data into a block which did not exist. If the data block is missing, then Essbase will not store that data.

 

I remember when I started learning how the block works, my mentor told me that thinking about you have a box in your hand, then he threw me a marker. That marker would be stored in the box. But what about if there is no box in your hand? Then there will be nowhere to store the maker. Similarly for Essbase, but the box is block. To store the data, we will need to create block first.

 

Now we understand the issue and let’s focus on how to create blocks. Once the blocks are ready, then Essbase could store data inside the blocks.

 

There several ways to create block:

Data Load:

Blocks will be created when data load process been triggered.  The data load processes including

Smart View, Essbase Load Rule, and FDM.

All of these three ways will create blocks for the sparse members which don’t have blocks.

 

Aggregation:

Blocks will be created when sparse members are rolled up to the parent level by using AGG, CALC ALL, CALC DIM.

 

Data Copy:

Blocks will be created when DATACOPY command is being used to copy values from one block to another.

 

Equation / Formula:

Blocks may be created when we have an equation or a formula in the scripts. When we assign a constant value to sparse dimension members, the block will be created. But other situations may cause some issues. We will discuss more detail down below.

 

Calculation Commands / Functions

Some of the commands will change the default setting of Essbase which will create blocks.

SET CREATENONMISSINGBLK ON;

SET CREATEBLOCKONEQ ON;

@CREATEBLOCK

 

The issue that we were discussing is when working on calculations, use values from different data blocks and the destination blocks do not exist, then the calculation may produce no results.

 

The Data Load, Aggregation and Data Copy will create necessary blocks without any issues. But the last two ways, using equation and commands may cause some issues. Now let’s take a look at them and do some testing.

Firstly, we are going to create a standard application.

 

For account and entity dimensions, we will create some sample members for testing purpose.

 

Then go to Calculation Manager, and check the Database Properties.

We notice that as of now, there is 0 block in the application.

 

Let’s start our block testing.

 

Assign a constant value to a sparse dimension member

We will use California as an example. California is in a sparse Entity dimension.

 

Assign a constant value to California.

 

After running the rule, we notice that the data has been stored to California.

 

Also, the existing level 0 blocks changed to 1.

 

So Essbase automatically creates all possible data blocks for California and the value 1000 to all data cells.

 

Assign a constant value to a dense dimension member

Before we do this testing, we need to use CLEARBLOCK ALL to clear out all the blocks.

This time we will assign a constant value for a dense dimension member. We will use account member, Revenue, as an example.

 

We notice that if we assign a constant value to a dense dimension member, then no blocks will be created. Because there is no block for that sparse member, California. To solve this, we will need to use some Essbase Commands and we will discuss this later.

 

Assign a constant value is straight forward, the confusion part always happened when we assign a non-constant value to a sparse dimension member.

 

Assign a non-constant value and a constant value to a sparse dimension member

Prepopulate data for California, then we will assign value to another sparse dimension member, Massachusetts.

 

Then assign non-constant value to Massachusetts.

 

Then check the results, we see data has been copied to Massachusetts and blocks have been created as well.

 

Assign a non-constant value and a constant value to a dense dimension member

Again, we will clear all the blocks first. Then submit value to Revenue, California.

 

Then assign value to Cost of Sales.

 

Since in this case, the block already existed, so there won’t be any creating block issue.

 

Assign 2 non-constant values to a sparse dimension member

Prepopulate data for California and Massachusetts, then we will assign value to another sparse dimension member New York.

 

Check the blocks before running the business rule.

 

Assign value to New York.

 

Check the results. We see the block for New York has been created.

 

Re-write rules with dense member

 

So far, we don’t encounter any major block issues. Let’s re-write the business rule with dense dimension members.

We will pre-populate data for California.

 

Use cross dimension sign in the rule.

 

Check the blocks before running the rule.

 

After running the rule, we notice that no data has been calculated for Massachusetts.

 

By default, Essbase applies dense member formulas only to existing data blocks. We were facing this issue when assigning a constant value to a dense dimension member as well. As there are no blocks for Massachusetts, then no data will be calculated.

 

To fix this, we could use Essbase command: SET CREATENONMISSINGBLK ON.

 

SET CREATENONMISSINGBLK

 

Update the business rule.

 

Run the rule and check the result. We noticed that the data has been calculated and the blocks have been created as well.

 

By default, Essbase applies dense-member formulas only to existing data blocks. SET CREATENONMISSINGBLK ON enables Essbase to create potential blocks in memory where the dense-member formulas are performed. Of these potential blocks, Essbase writes to the database only blocks that contain values; blocks resulting in only #MISSING are not written to the database.

 

The creation of #MISSING blocks resulting from sparse-member formulas is governed by the Create Block on Equations setting (SET CREATEBLOCKONEQ).  The SET CREATENONMISSINGBLK ON command ensures that only non-empty blocks are created, regardless of the Create Block on Equations setting.

 

In order to create new blocks, setting SET CREATENONMISSINGBLK to ON requires Essbase to anticipate the blocks that will be created. Working with potential blocks can affect calculation performance. It will create potential blocks in memory even though it will write and store only the non-missing blocks after calculation. With this being said, the better way is to rewrite this rule as a sparse member formula.

 

SET CREATEBLOCKONEQ

 

SET CREATENONMISSINGBLK is another Essbase command which can create blocks. It will check whether new blocks are created when a calculation formula assigns anything other than a constant to a member of a sparse dimension. SET CREATEBLOCKONEQ overrides the Create Block on Equation setting for the database.

 

To test it, clear all blocks again.

 

Without using SET CREATENONMISSINGBLK, or SET CREATENONMISSINGBLK OFF, will give the same result. No data will be calculated and no blocks will be created. Essbase will not create a block when a formula assigns a non-constant value to a sparse dimension member for which no block exists with SET CREATEBLOCKONEQ OFF.

 

Now let’s use SET CREATEBLOCKONEQ ON.

 

Check the result and we see that the data has been calculated and related blocks have been created.

 

Using SET CREATEBLOCKONEQ ON, if calculations result in a value for a sparse dimension member for which no block exists, Essbase creates a block. Sometimes, new blocks are not desired; for example, when they contain no other values. The Create Blocks on Equation setting is designed for situations when blocks would be created as a result of assigning something other than a constant to a member of a sparse dimension.

 

SET FRMLBOTTOMUP

 

Another command that is related to SET CREATEBLOCKONEQ is SET FRMLBOTTOMUP.

Essbase uses one of two calculation methods to do a full calculation of a database outline: bottom-up calculation or top-down calculation. The default setting is OFF.

 

When we use SET CREATEBLOCKONEQ ON, and SET FRMLBOTTOMUP ON, then no block will be created in this case.

 

When SET FRMLBOTTOMUP OFF, then the block will be created.

 

So creating Blocks on Equations should be enabled for the specific calculation having sparse member formula, when the calculation is globally forcing the formula bottom up.

 

SET CREATEBLOCKONEQ and SET CREATENONMISSINGBLK, both of these settings have the performance impact because of the processing overhead. CREATEBLOCKONEQ is better than CREATENONMISSINGBLK. Since CREATEBLOCKONEQ creates only the required blocks, but CREATENONMISSINGBLK creates potential blocks while calculating and store only the non-missing blocks.  CREATENONMISSINGBLK will have much more impact on the performance. SET CREATENONMISSINGBLK command will always be the last option for us.

 

Hopefully, until now we have a better understanding of missing blocks and creating blocks. Remember, always be careful about using the Essbase commands and functions to create blocks, since we have the ability to create combinations of blocks that should not exist or to create too many blocks. I will discuss more about the blocks in the future posts. Till next time.