Reduce Dynamic Calc Impact on Retrieval Time

The increase in retrieval time when you dynamically calculate a member of a dense dimension is not significant unless the member contains a complex formula. The increase in retrieval time may be significant when you tag members of sparse dimensions as Dynamic Calc.

You can analyze and manage the effect of Dynamic Calc members on an Essbase database by paying attention to retrieval factor, retrieval buffer size, and calculator caches.

Note:

For a list of functions that have the most significant effect on query retrieval, see Member Set Functions and Performance.

Display a Retrieval Factor

To help you estimate any increase in retrieval time, Essbase calculates a retrieval factor for a database outline when you save the outline. Essbase calculates this retrieval factor based on the dynamically calculated data block that is the most expensive for Essbase to calculate. The retrieval factor takes into account only aggregations. It does not consider the retrieval impact of formulas.

The retrieval factor is the number of data blocks that Essbase must retrieve from the disk or from the database to calculate the most expensive block. If the database has Dynamic Calc members in dense dimensions only (no Dynamic Calc members in sparse dimensions), the retrieval factor is 1.

An outline with a high retrieval factor (for example, greater than 2000) can cause long delays when users retrieve data. However, the actual impact on retrieval time also depends on how many dynamically calculated data values a user retrieves. The retrieval factor is only an indicator. In some applications, using Dynamic Calc members may reduce retrieval time because the database size and index size are reduced.

Essbase displays the retrieval factor value in the application log.

A message similar to this sample indicates a retrieval factor:


Essbase needs to retrieve [1] Essbase kernel blocks in order
to calculate the top dynamically-calculated block.

This message tells you that Essbase needs to retrieve one block to calculate the most expensive dynamically calculated data block.

Increase Retrieval Buffer Size

When users query from Smart View, MDX, or Report Writer, Essbase uses the retrieval buffer to optimize the query. Increasing the retrieval buffer size can improve query performance, as Essbase can process larger sections of data simultaneously.

By default, the retrieval buffer size is 20 KB. However, you may speed retrieval time if you set the retrieval buffer size greater than 20 KB.

To increase the retrieval buffer size, you can use the MaxL alter database statement. For example:

alter database Sample.Basic set retrieve_buffer_size 20kb;

Or you can change the buffer size in the database customization settings, using the Essbase web interface.

Use Dynamic Calculator Caches

By default, when Essbase calculates a Dynamic Calc member in a dense dimension (for example, for a query), it writes all blocks needed for the calculation into an area in memory called the dynamic calculator cache. When Essbase writes these blocks into the dynamic calculator cache, it expands them to include all Dynamic Calc members in the dense dimensions.

If a query includes a two-pass calculation member in a dense dimension, the query needs one dynamic calculator cache for each block retrieved.

Using the Essbase dynamic calculator cache enables centralized control of memory usage for dynamic calculations. Managing data blocks in the dynamic calculator cache also reduces the overall memory space requirement and can improve performance by reducing the number of calls to the operating system to do memory allocations.

Note:

The dynamic calculator cache and the calculator cache use different approaches to optimizing calculation performance.

Refer also to: Size the Dynamic Calculator Caches and Size the Calculator Cache.

Review Dynamic Calculator Cache Usage

Essbase writes two messages to the application log for each data retrieval. In the following example, the first message describes the total time required for the retrieval:


Regular Extractor Elapsed Time : [0.531] seconds
Regular Extractor Big Blocks Allocs -- Dyn.Calc.Cache : [30] non-Dyn.Calc.Cache : [0]

If a dynamic calculator cache is used, a second message displays the number of blocks calculated within the data calculator cache (Dyn.Calc.Cache: [n]) and the number of blocks calculated in memory outside dynamic calculator cache (non-Dyn.Calc.Cache: [n]).

To determine whether the dynamic calculator cache is being used effectively, review both messages and consider your configuration settings. For example, if the message indicates that blocks were calculated outside and in a dynamic calculator cache, you may increase the DYNCALCCACHEMAXSIZE setting.

If the specified maximum size is all that you can afford for all dynamic calculator caches on the server, and if using memory outside the calculator cache to complete dynamically calculated retrievals results in unacceptable delays (for example, because of swapping or paging activity), set DYNCALCCACHEWAITFORBLK to TRUE.

You can use the query database MaxL statement with the performance statistics grammar to view a summary of dynamic calculator cache activity.