By the time my eyes and my brain could not take anymore I decided to put this calculation script away for the day. It was syntactically correct, mathematically correct, syntactically correct (just double checking) and could run to completion in less than a second. This was a mystery that I would have to solve tomorrow.

I was missing something... but so was Essbase. No matter how many times I ran my calculation script, it would not store any results. Retrieving data from Essbase in Excel would produce #Missing values every time.

Milk Carton Block

“Have you seen me?” (Missing Block)

The next day, I created a simplified example to help figure out what was going on.

/* 
Just a reminder to myself that I should test small sections of calculation
script before running them as part of larger calcs.
*/
FIX("Budget")
"Sales"
(
    "Actual" * 1.15;
);
ENDFIX

Missing Blocks

If you have experienced this issue, don’t sweat it, so have (or so will) most of us. Essbase does not discriminate. If you are brand new to Essbase or have many years of experience, this issue can snare us along the way. My calculation script was trying to assign values to data blocks that did not exist. To Essbase, the results of my calculation were to be stored in a MISSING block. Essbase BSO (Block Storage Option) stores results of data loads and calculations into a data structure called a “block”. If a block is missing, Essbase cannot store data there. Essentially missing blocks are like a black hole for data (perhaps Essbase is paying tribute to /dev/null in Linux).

Missing Block Diagram

Missing Blocks. a.k.a “Block Hole” (Sorry for the pun).

By default, if Essbase calculations try to write values into the cells of a missing block they will be discarded. (Cells within a block are defined by the combinations of dense dimensions in a BSO database.)

Solving The Problem

To fix this specific problem (Missing Blocks) you must ensure that your calculation script is writing to data blocks that exist, or that those blocks are created at some point during the calculation. Once a data block has been created by Essbase, it can be used to store values.

Create Block Diagram

Pro-Tip: Create blocks to store values in Essbase.

Let’s have a quick look at some ways you can create blocks in Essbase. This list is not all inclusive but here are several methods for creating data blocks in Essbase, including:

  • Perform a data load (or lock and send)
  • Execute a data copy calculation (to copy values from one block to another)
  • Aggregations (summarizing along stored sparse dimension rollups)
  • Assignment of constant values to a sparse dimension members
  • Calculation commands that change the default behavior of Essbase
    • CREATEBLOCKONEQ
    • CREATENONMISSINGBLK
  • Calculation functions that create blocks such as @CREATEBLOCK

This issue is often seen when working on calculations that use values from intersections within different data blocks. Assume that ‘Years’ is sparse, for example, when calculating values in a current or future year based on the values of a prior year, if the destination blocks do not exist then your calculations may not produce any results. In this case, a “DATACOPY” calculation with a well-defined FIX block is a common solution.

In Summary

If you find that you are unable to get Essbase to store values when running your calculation scripts, check to make sure that your target data blocks exist. Before you alter your calculation script, use Excel or the “Data Preview Grid” in EAS to submit a value to your destination block in Essbase. This will confirm that block creation may indeed be your problem. If your calculation script “just starts working” that is usually a good indicator as well. Also, be careful about using the last two methods noted above. Creating blocks with the calculation engine is potentially dangerous since you have the ability to create combinations of blocks that should not exist or to create too many blocks.

On a separate but related note, there may be some issues with the way Essbase handles block creation as compared to the behavior documented in the Tech-Ref. I will do some more research and see if I can replicate the issue I have seen or figure out if I am just missing something obvious. (The problem I see is that SET CREATEBLOCKONEQ OFF; does not seem to be working correctly on one of my DEV boxes. (Linux with Essbase 11.1.2.3) Instead of preventing the creation of blocks, Essbase seems to be stuck at a default setting of ON. Again, I could be missing something very obvious but I will continue down this path just in case there is a problem.

Cheers!

Edit: I received some feedback from a reader who rightfully noted that calculations will skip data blocks that exist but are not considered when intelligent calculation is enabled because the blocks are not marked as “dirty”. In your endeavor to identify why your calculations are not working correctly you may also find that you need to consider disabling intelligent calculations by adding the following to your calculation script: “SET UPDATECALC OFF;”.

Edit: I received some feedback from a reader about the diagrams shown above and based on that feedback I want to clarify the context of my diagrams. They do not represent multiple cells within a data block. (As is common for these types of diagrams and something I took for granted.) The diagrams instead depict a group of many potential blocks, one of which was yet to be created. Year, Market and Product are assumed to be sparse dimensions and the dense dimensions are not explicitly defined. They are implicitly part of each smaller block. (Perhaps imagine something like Account and Period as a possible dense dimension combination.)

Thanks for the great feedback and keep it coming!