Few Optimization Techniques in Essbase

With the essential features available in Essbase you can load the huge data to the Essbase cubes, Run the reports and you can perform the complex calculations also,

As you keep on adding the different features to your application the performance will get reduce. As i said Essbase came up with different features along with the different performance tuning techniques which makes the application best optimized.

The optimization can be done at many places such as

Outline Optimization

Data Load Optimization

Report Script Optimization

Calculation Script Optimization

Outline Optimization:

1) Arrange the dimension in "Hour Glass Model"

The Outline should starts with dense dimension with highest stored members and it keep going till the dense dimension with least stored members and then starts with sparse dimension with least stored members and it keep going till the sparse dimension with highest stored members.

2) Use the member storage properties efficiently.

If the dimension is to just host the different types of data such as scenarios, here there is no point in rolling up the lower values to higher level, in this situation you can tag the dimension as "Label Only" and assign the no consolidation operator to the members under it.

Some calculations really not required to stored the results in database at this point of time tag the concern members with "Dynamic Calc" property.

Data Load Optimization:

1) In data file, the fields should starts with sparse dimension members and then dense dimension members and then the data field.

2) If the same field is repeating in all the records in the data file, then try to ignore that field from fetching itself and keep that member in the "Header Definition", why means to save the buffer memory and it will increase data load process.

Report Script Optimization:

1) In the report script first specify the sparse dimensions and then dense dimensions, why means :Sparse dimension creates the data blocks within which the data cells are available, so specifying the dense first does not make sense. So to speed up the process specify the data blocks first(Sparse dimension) and then data cells (Dense dimensions).

2) The dimensions which are not required to display in the report put them in the page.

3) Use the special commands to increase the report performance

SUPMISSINGROWS : To Suppress the data missing rows.

SUPHEADING : To Suppress the headings.

SUPBRACKETS: To Suppress the brackets around the negative values.

SUPEMPTYROWS: To Suppress the empty rows.

Calculation Script Optimization:

1) Use the set commands to increase the calculation performance.

SET MSG SUMMARY : Set the message level to summary.

SET AGGMISSG ON : To avoid the aggregation of #missing values.

SET CACHE HIGH : To increase the bugger size.

SET NOTICE LOW : To set the notices to low.

2) Perform the calculations on only required part of database using FIX command.

Happy learning and thanks for all who supported me to write this post :)