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.
|Number of bytes||8192||102400|
|Number of cells||1024||12800|
So if you have 2 dense dimensions with m and n members each, then
|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.