Optimization techniques Of Essbase Application

 


Optimization techniques  Of Essbase Application

Dimension building won't take much time. We need to optimize loading data and calculation process and retrieval process.

Rules for the optimization:

• We need to choose sparse and dense dimensions carefully.
• We need to arrange the sparse and dense dimensions in hour glass model. That is most dense dimensions in the top and less dense dimensions, less sparse in the middle and most sparse in the last.
• The block size must be between 8k and 100k

Optimizing Data Loading:

Apart from the above, we need to follow the below steps to optimize the performance.

• Arrange the source data in the same order of sparse dimensions as mentioned in the outline, because the index is arranged in the same order as they appear in the sparse dimensions.
• Group all sparse members together, so that the data related to a block will be together. We can use order by clause in the select statement.

Optimizing Calculations:

Apart from the above, we need to follow the below steps to optimize the calculations.

• Use intelligence calculation.
• Use label only whenever the parent member don't need any calculations.
• Use parallel calculations when possible.
• Use Dynamic calculations on some members, if we further want to reduce the calc time. But use dynamic calculations limited.

Optimizing data retrieval:

• Limit the usage of dynamic calc i.e. We have to always balance calculation and time. More dynamic calculations will slow down the retrieval performance.
• Increase the retrieval buffer size, by default it is set to 10 KB. We can set this up to 100 KB