Ten Steps to Optimization

Hi,

These are the optimization Steps in Essabase. 

Step 1: Model Analysis

1. Minimize the number of dimensions. Do not ask for everything in one model
2. Minimize complexity of individual dimensions. Consider UDAs and Attribute Dimensions in order to reduce the size of  some of the dimensions
3. Examine the level of granularity in the dimensions.

 Step 2: Order The Outline

 

1. Dense dimensions from largest to smallest. Small and large is measured simply by counting the number of Stored members in a dimension. The effect of sparse dimension ordering is much greater than dense dimension ordering.
2. Sparse dimensions from smallest to largest. This relates directly to how the calculator cache functions.

Step 3: Evaluate Dense/Sparse Settings

 

Finding the optimal configuration for the Dense/sparse settings is the most

important step in tuning a database.

Optimize the block size. This varies per operating system, but in choosing the best Dense/sparse configuration keep in mind that blocks over 100k tend to yield poorer performance. In general, Analytic Services runs optimally with smaller block sizes  

Step 4: System Tuning

 

System tuning is dependent on the type of hardware and operating

1. Keep memory size higher.
2. Ensure there is no conflict for resources with other application 

 

Step 5: Cache Settings

 

1. The actual cache settings recommended is strongly dependent on your specific situation.
2. To measure the effectiveness of the cache settings, keep track of the time taken to do a calculation and examine the hit ratio statistics in your database information.  

 

Step 6: Optimize Data Loads

 

1. Know your database configuration settings (which dimensions are dense and sparse).
2. Organize the data file so that it is sorted on sparse dimensions. The most effective data load is one which makes the fewest passes on the database. Hence, by sorting on sparse dimensions, you are loading a block fully before moving to the next one.
3. Load data locally on the server. If you are loading from a raw data file dump, make sure the data file is on the server. If it is on the client, you may bottleneck on the network

Step 7: Optimize Retrievals

 

1. Increase the Retrieval Buffer size. This helps if retrievals are affected due to dynamic calculations and attribute dimensions.
2. Increase the Retrieval Sort Buffer size if you are performing queries involving sorting or ranking.
3. Smaller block sizes tend to give better retrieval performance. Logically, this makes sense because it usually implies less I/O.
4. Smaller reports retrieve faster.
5. Attribute may impact the calculation performance which usually has a higher importance from a performance standpoint.
6. If you have a lot of dynamic calculations or attribute dimensions
7. Higher Index cache settings may help performance since blocks are found quicker

 Step 8: Optimize Calculations

1. Unary calculations are the fastest. Try to put everything in the outline and perform a Calc All when possible. You should FIX on sparse dimensions, IF on dense dimensions. FIX statements on sparse dimensions only brings into memory blocks with those sparse combinations which the calc has focused on. If statements on dense dimensions operate on blocks as they are brought into memory. Use the Two Pass Calculation tag. Try to avoid multiple passes on the database. In the case where the calculation is a CALC  
2. Use Intelligent Calc in the case of simple calc scripts.

 Step 9: Defragmentation

Fragmentation occurs over time as data blocks are updated. As the data blocks are updated, they grow (assuming you are using compression) and the updated blocks are appended to the page file. This tends to leave small free space gaps in the page file.

1. Time - The longer you run your database without clearing and reloading the more likely it is that it has become fragmented.
2. Incremental Loads - This usually leads to lots of updates for blocks.
3. Many Calculations/Many Passes On The Database - Incremental calculations or calculations that pass through the data blocks multiple times leads to fragmentation.

 Step 10: Partition

By breaking up one large database into smaller pieces, calculation performance may be optimized. Because this adds a significant layer of complexity to administration, this is the last of the optimization steps we list. However, this does not mean that has the least impact.

Regards,

K. Suneel