Limitations on size of dense dimensions for optimum performance

Designing an Essbase DB comes with a lot of practice and experience. There are however some basic tips & tricks that we should be aware of. One common question I’ve come across in my inbox relates to the ideal block size of an Essbase DB.

The recommended block size for an Essbase DB is from 8KB to 100KB.
Each cell in a block needs exactly 8 bytes.

Minimum Maximum
Block Size 8KB 100KB
Number of bytes 8192 102400
Number of cells 1024 12800

So if you have 2 dense dimensions with m and n members each, then

Minimum Maximum
m*n 1024 12800
m (when n=13) 78 984
m (when n=21) 48 609

In the above table I have assumed that one of the dimensions is the Period dimension (which makes sense from a design point of view). I have assumed two cases wherein the Period dimension has 13 members (Jan:Dec, BegBalance) and the other case in which there are 21 members (Jan:Dec, Q1:Q4, H1:H2, YearTotal, BegBalance).

In most cases the Account dimension is also taken as a dense dimension thus allowing it to have a maximum of 984 members in the first case to 609 in the second case. Usually the Period dimension is taken with a Quarterly, Half-Yearly and Yearly hierarchy this allowing for 609 members in the Account dimension. This may seem like a small amount, but ideally this should be more than enough for a Planning and Budgeting application. Straying above the allowed limit is alright and there isn’t normally a huge performance impact that I would suggest against the practice. However, it is best to stay within the advised range.

Advertisements

One thought on “Limitations on size of dense dimensions for optimum performance

  1. Hi Shezad

    Learnt something from this stuff. So what I am understanding from your topic that most of the time typically the Hyperion application will have more number of Sparse then Dense dimensions? Is this true ? If you have more Dense dimensions your Block Size would be high , It may go beyond 100 KB , and resulting performance issues ? Please clarify

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s