El levantamiento a través de la bibliografía

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 or Dynamic Calc and Store.

The following sections discuss ways you can analyze and manage the effect of Dynamic Calc members on a database:

• Displaying a Retrieval Factor

• Displaying a Summary of Dynamically Calculated Members

• Increasing Retrieval Buffer Size

• Using Dynamic Calculator Caches

• Reviewing Dynamic Calculator Cache Usage Note:

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

Displaying 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 disk or from the database in order to calculate the most expensive block. If the database has Dynamic Calc or Dynamic Calc and Store members in dense dimensions only (no Dynamic Calc or Dynamic Calc and Store 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.

To view an estimated retrieval factor, see Viewing the Essbase Server and Application Logs.

A message similar to this sample indicates a retrieval factor:

[Wed Sep 20 20:04:13 2000] Local/Sample///Info (1012710) 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 in order to calculate the most expensive dynamically calculated data block.

Displaying a Summary of Dynamically Calculated Members

When you add Dynamic Calc or Dynamic Calc and Store members to a database outline and save the outline, Essbase provides a summary of how many members are tagged as Dynamic Calc and Dynamic Calc and Store. Essbase displays the summary in the

application log.

To view a summary of dynamically calculated members, see Viewing the Essbase Server and Application Logs.

A message similar to this sample is displayed:

[Wed Sep 20 20:04:13 2000]Local/Sample///Info(1007125) The number of Dynamic Calc Non-Store Members = [ 8 6 0 0 2]

[Wed Sep 20 20:04:13 2000]Local/Sample///Info(1007126) The number of Dynamic Calc Store Members = [ 0 0 0 0 0]

This message tells you that there are eight Dynamic Calc members in the first dimension of the database outline, six in the second dimension, and two in the fifth dimension.

Dynamic Time Series members are included in this count.

This example does not include Dynamic Calc and Store members.

Increasing Retrieval Buffer Size

When you retrieve data into Essbase Spreadsheet Add-in for Excel or use Report Writer to retrieve data, Essbase uses the retrieval buffer to optimize the retrieval. Essbase

processes the data in sections. Increasing the retrieval buffer size can significantly reduce retrieval time because Essbase can process larger sections of data at one time.

By default, the retrieval buffer size is 10 KB. However, you may speed up retrieval time if you set the retrieval buffer size greater than 10 KB. See Setting the Retrieval Buffer Size.

To set the retrieval buffer size, use a tool:

Tool Topic Location Administration

Services Setting the Size of Retrieval

Buffers Essbase Administration Services

Online Help

MaxL alter database Essbase Technical Reference

ESSCMD SETDBSTATEITEM Essbase Technical Reference

Using 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.

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.

See Sizing the Calculator Cache.

Reviewing 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 amount of time required for the retrieval:

[Thu Aug 03 14:33:00 2005]Local/Sample/Basic/aspen/Info(1001065) Regular Extractor Elapsed Time : [0.531] seconds

[Thu Aug 03 14:33:00 2005]Local/Sample/Basic/aspen/Info(1001401) 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 if the dynamic calculator cache is being used effectively, review both of these messages and consider what your settings are in the essbase.cfg file. For example, if the message indicates that blocks were calculated outside and in a dynamic calculator cache, you may need to 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 GETPERFSTATS command in ESSCMD to view a summary of dynamic calculator cache activity. See the Essbase Technical Reference.