dense and sparse dimensions in Essbase

dense and sparse dimensions in Essbase, what is the best setting?

In “design guidelines” by Arthur van den Berg

What  should be the dense and sparse dimensions in Essbase for Hyperion Planning? A good question with a straightforward answer.

dense and sparse dimensions in Essbase

If you are new to Essbase, PBCS and/or Oracle’s Planning & Budgeting the terminology on “dense” will most likely mean nothing to you. I wish I could leave it at that, but unfortunately you do need to dive into this topic to become the Essbase expert for your organization. As a starter, I recommend that you read our article on Essbase dense vs sparse.

“Dense” and “sparse” are the values for the performance settings of the underlying data structures – called dimensions – involved in the Essbase databases – called cubes. The data that is available in Essbase is a mix of data that is stored on disk or in memory (pre-calculated) and data that will be calculated on-the-fly (dynamically calculated) when needed. Finding the right balance between stored data and data that is calculated when needed is the key to a smooth running Essbase engine.

data block vs index

The Essbase dimensions that are set to “dense” will become part of the structure that is called the Essbase “data block”, a matrix-like structure with slots available for each possible dense member combination. The Essbase data blocks are stored in the data base file, called “page file”.  The dimensions that are set to “sparse” will be part of the Essbase index (more on this, read here).

You need to define the sparse and dense dimensions in Essbase right in the beginning of your project; when you are defining the content and layout of your dimensions. Once you have made your choice and continue with your project, it is very difficult to change it down the line as these settings will influence how Essbase will behave in data forms, calculation logic (business rules), data load (FDMEE or transfers) and data storage. And please pay attention, because if you make a mistake on this topic now, your project will suffer from it in terms of performance and data storage; causing a lot of stress and anxiety in your team and among the stakeholders. I know from experience that it can be very costly to recover from.

there is just one right setting in Planning projects

I could be politically correct to say that the right “dense” and ”sparse” setting for a dimension is project specific and it all depends on the context of the business; but in reality, there is only just one right setting for a planning and budgeting project according to the Dutch School on Essbase Design (DSED), which is: set the Account,  Period and View dimensions to “dense”.

Consider the following theoretical planning set-up:

  cube 1 cube 2 cube 3
performance
setting
consolidation cube detail cube
(workforce/ capex)
detail cube
(sales/ production)
dense Account (sub set) Account (sub set) Account (sub set)
dense Period Period Period
dense View View View
sparse Year Year Year
sparse Scenario Scenario Scenario
sparse Version Version Version
sparse Entity Entity Entity
sparse <custom dimensions> <custom dimensions> <custom dimensions>

 

The Account dimension contains the measures to plan and report on, such as the general ledger items, calculated members, KPI’s, benchmarks and so on.

The Period dimension contains the months, with the quarters and year total; with optionally a beginning balance (BegBalance). Or, in case you want to have a weekly plan, all the weeks of the year.

The View dimension is a specific (custom) dimension that will assist you in implementing fancy calculations, both dynamically as via business logic (rules).

 

So, in short, set these dimensions always to “dense” and set all the other dimensions to “sparse” and you are on the right track applying the Essbase best practices for a well performing planning implementation.

-----
about the author
mm

Arthur van den Berg

LinkedIn

I love the calculation power of Essbase and the financial problems you can solve with it. I have developed, implemented and improved dozens of EPM solutions with Essbase at its core and I love sharing what I learned with professionals that need to build and/or support these solutions.

share this post