It's all about Enterprise Performance Management

If you have already been a Hyperion Planning consultant long enough, you should have met this issue sometimes: “Data cache is full. Please increase the data cache size for database…”

Of course our first thought to solve this problem is to increase the data cache size for database like what the instruction said. And below is how:

OK, but how much the data cache should be for best practice? You can read this http://docs.oracle.com/cd/E12825_01/epm.111/esb_dbag/dstcache.htm#dstcache1010002

By default, it’s just 3072 KB. So if you keep increasing the cache but you still have this issue, it’s time for you to think about your business rule or even your design. Because the more memory you allocate for data cache, the more RAM essbase will take over permanently (never release even the rule has already finished) unless you restart the Essbase app/cube.

Once Essbase pulls a block into memory, it won’t release it unless the app is stopped, or there is no additional memory available, and it has to release the block so it can go get another block. So Essbase won’t really “release” the memory. This is one reason Essbase has a reputation for not playing nicely with other applications on the same server (like relational databases).

http://www.network54.com/Forum/58296/thread/1167930858/Releasing+memory

What need a lot of Data Cache usually are those calculation need the participating of a lot of member/data such as aggregation. For example:

FIX (Year1, Year2, Year3)

CALC DIM (Entity, Product, Customer, Currency, Period).

// To aggregate the parent, it’s needed to load all the children to Cache to perform the aggregation

ENDFIX

The above rule aggregates most of dimensions so it may need the whole cube load into Cache to perform calculation. To solve this issue either you can try to break the rule in to parts:

FIX(Year1)

CALC DIM (Entity, Product, Customer, Currency, Period).

ENDFIX

FIX(Year2)

CALC DIM (Entity, Product, Customer, Currency, Period).

ENDFIX

…

Or thinking about a way to make your block size smaller…. depend on specific situation.