DEV EPM

Hey guys, how are you? Today we’ll going to talk about how to automatically administrate Essbase using ODI.

As you guys know, in order to have good performance in an Essbase cube, we must keep vigilance and follow up its growth and its data movements, so we can distribute caches and adjust the database parameters accordingly. But this is a very difficult task to achieve, since Essbase statistics are not temporal and only tells you the cube statistics is in that specific time frame.

The performance of an Essbase app has a direct relationship with the configuration setting that we set and must be based on the design and usage of each cube. To find out the optimal values for these configurations we need to look at the Essbase statistics.

The problem with this is that the Essbase statistics is a snapshot of the cube at that moment and every time we shut down a cube the run time statistics are cleared, meaning that these statistics are more or less accurate depending of the amount time the cube is online.

That also means that if we look in the statistics after a restart of the cube, we can get the wrong information and we can make a poor setting of caches and other things making the cube slow or even crashing them.

The best thing we can do to make this process more reliable is to create a historical DW with the Essbase statistics information, this way we can create a process to keep improving Essbase setting in a more pragmatically way extract, analyze, change setting and so on.

These are the most important caches that we can set, but we can do all kind of things with the information we’ll gathering from Essbase.

Cache Type	Description
Index	A buffer in memory that holds index pages (.ind files)
Data file	A buffer in memory that holds compressed data files (.pag files). Essbase allocates memory to the data file cache during data load, calculation, and retrieval operations, as needed. (only when direct I/O is in effect)
Data	A buffer in memory that holds uncompressed data blocks. Essbase allocates memory to the data cache during data load, calculation, and retrieval operations, as needed
Calculator	A buffer in memory that Essbase uses to create and track data blocks during calculation operations
Dynamic calculator	A buffer in memory that Essbase uses to store all of the blocks needed for a calculation of a Dynamic Calc member in a dense dimension

Let’s start with the Index cache. Index cache holds in memory the indexes that Essbase uses to find requested data.

Every time we retrieve or calculate any data from Essbase, first Essbase try to find that index in the index cache. If the index is not there, Essbase will find the index in the index files, get the data and store this index in the index cache memory to make it faster for the next time we need the same information. For each index that Essbase store there, it will take 112 bytes of memory.

Them to calculate the maximum size of this cache we just need to multiply the number of existing blocks by 112 bytes and that’s it.

Just to make sure everyone is in the same page: a Block is the combination of all store like members from all dense dimensions. Essbase creates a new Block when a new unique combination of data in the sparse dimensions is inserted/calculated.

This table shows the different ways to get the information we need from Essbase. As you can see, we don’t need to know java/C++ or any language to do extract this information, we can just use maxl, esscmd or even manually using EAS.

EAS	MAXL	ESSCMD	JAVA
Right click in a cube edit properties->Statistics	query database sample.basic get dbstats data_block;	select sample basic; getdbstats;	(IEssCube) cube.getPropertyNames();

One interesting thing about this cache (and almost every cache in Essbase) is that, as I said, it holds in memory the indexes that Essbase used at least once to find the requested data. That means, we will only find in the cache, indexes that we used at least once since the last time the cube starts.

Then, if we have a cube with historical data, the chances of these old data never hit the index are bigger then the new data. We need to take this in consideration when we set this cache.

In this case, 70% of the cube contains historical data that are rarely requested, meaning, we need just 30% of the maximum size for the index cache.

A way to figure out if the cache is well set is looking at the hit ratio in EAS, but we still have the issue that this info reset every time when the cube restarts.

If we are resuming what we just said about Index Cache, we have the follow:

• The index Cache is used to hold in memory all used indexes since the application is turned on.
• The maximum index cache size is calculated by multiplying the number of existing Blocks * 112 bytes
  • A Block is the combination of all store like members from all dense dimensions. Essbase creates a new Block when a new unique combination of data in the sparse dimensions is inserted/calculated
• The optimal index cache will vary depending of the design and use of the cube.
  • If the cube has a lot of historical data, the chances of these index never hit the cache are big
  • For that we can define a historical percentage that we can remove from the maximum index cache to find a more realistic cache.
  • This historical percentage can be obtained by analyzing the outline and see how many members are in use

That’s it for today. Next post we’ll talk about the Data File Cache. Stay safe and see you soon.

This entry was posted on February 1, 2021 at 10:23 am and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.