Quick BSO Tuning Tips
Here are some quick tips to check when tuning your BSO application:
1. Dimension Order
♦ Use the hourglass model:
Largest dense to smallest dense, smallest sparse to largest sparse
♦ Attribute dimensions are always last.
2. Dense/Sparse Settings
♦ Typically, your accounts and period dimensions are classified as dense.
3. Member Properties
♦ Tag all upper-level members on your accounts and period dimension as dynamic calc. This not only will impact the size of the database which affects performance, but also reduces calculation times.
4. Block Size
♦ The recommended block size is 8-100KB.
♦ Be sure to check your Average Clustering Ratio (Right click database, Edit, Properties, Statistics). An Average Cluster Ratio of 1 is ideal in Essbase. You can achieve this number through a database restructure or by clearing your database and reloading your export file.
♦Be sure you have set configuration settings in the server environment.
♦ Right click on the database and click restructure. There are three different types of restructures:
♦ Your Index Cache should be equal to the size of the index file. If it is higher, you will not see improvement in performance.
♦ Use the set command SET CACHE to improve calculation performance. This command specifies the size of the calculator cache.
8. Calc commands
♦ It is recommended to If on dense members, and fix on sparse members.
♦ Use SET Commands as necessary to improve performance.
♦ When designing your calc script, keep in mind to minimize the number of passes on the database.
9. Avoid loading zeros to databases. It is best practice to replace zeros with #missings as a blank cell uses no memory as opposed to a zero.