A data block size of 8 Kb to 100 Kb provides optimal performance in most cases.

If data blocks are much smaller than 8 KB, the index is usually very large, forcing Essbase to write to and retrieve the index from disk. This process slows calculation.

If data blocks are much larger than 100 KB, Intelligent Calculation does not work effectively. Refer to Intelligent Calculation for Block Storage Cubes.

To optimize calculation performance and data storage, you may need to balance data block density and data block size by rearranging the dense and sparse dimension configuration of the database. Keep these suggestions in mind:

• Keep data block size between 8 KB and 100 KB with as high a block density as possible.

• Run test calculations of the most promising configurations of a cube that contains representative data. Check results to determine the configuration that produces the best calculation performance.

• Consider using hybrid mode. In a hybrid mode cube, you can easily customize the solve order instead of rearranging dimensional order.

View information about a cube, including the potential and actual number of data blocks and the data block size, by using the Essbase web interface or MaxL.

The equivalent statement for viewing block statistics using MaxL is:

query database appname.dbname get dbstats data_block;

Alternatively, consider using hybrid mode. In a hybrid mode cube, you can easily customize the solve order instead of rearranging dimensional order.

You may improve calculation performance by changing the order of standard (non attribute) sparse dimensions in the outline. Order standard sparse dimensions by the number of members they contain, placing the dimension that contains the fewest members first. This arrangement can improve:

• The function of the calculator cache can have ~10% performance improvement for an outline with a large dimension (for example, one containing 1000 members).

• Parallel calculation, if enabled, more likely will be used if the standard sparse dimension with the most members is the last standard sparse dimension in the outline.

Many companies load data incrementally. For example, a company may load data each month for that month.

To optimize calculation performance when you load data incrementally, make the dimension tagged as time a sparse dimension. If the time dimension is sparse, the database contains a data block for each time period. When you load data by time period, Essbase accesses fewer data blocks because fewer blocks contain the relevant time period. Thus, if you have Intelligent Calculation enabled, only the data blocks marked as dirty are recalculated. For example, if you load data for March, only the data blocks for March and the dependent parents of March are updated.

However, making the time dimension sparse when it is naturally dense may significantly increase the size of the index, creating possibly slower performance due to more physical I/O activity to accommodate the large index.

If the dimension tagged as time is dense, you still receive some benefit from Intelligent Calculation when you do a partial data load for a sparse dimension. For example, if Product is sparse and you load data for one product, Essbase recalculates only the blocks affected by the partial load, although time is dense and Intelligent Calculation is enabled.

Calculation performance may be affected if a cube outline has multiple flat dimensions. A flat dimension has very few parents, and each parent has many thousands of children; in other words, flat dimensions have many members and few levels.

You can improve performance for outlines with multiple flat dimensions by adding intermediate levels to the outline.

You may achieve significant improvements in calculation performance by carefully grouping formulas and dimensions in a calculation script. In this way, you can ensure that Essbase cycles through the data blocks in the cube as few times as possible during a calculation.

Order commands in calculation scripts to make the database calculation as simple as possible. Consider applying all formulas to the outline and using a default calculation (CALC ALL).

See Develop Calculation Scripts for Block Storage Cubes and Calculation Passes.