Dynamically Calculating Data Values

In This Section:

Understanding Dynamic Calculation

Benefitting from Dynamic Calculation

Using Dynamic Calculation

Choosing Values to Calculate Dynamically

Choosing Between Dynamic Calc and Dynamic Calc and Store

Understanding How Dynamic Calculation Changes Calculation Order

Reducing the Impact on Retrieval Time

Using Dynamic Calculations with Standard Procedures

Creating Dynamic Calc and Dynamic Calc and Store Members

Restructuring Databases

Dynamically Calculating Data in Partitions

The information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases. Also see Comparison of Aggregate and Block Storage.

Understanding Dynamic Calculation

When you design the overall database calculation, it may be more efficient to calculate some member combinations when you retrieve their data, instead of precalculating the member combinations during a batch database calculation. Dynamically calculating some values in a database can significantly improve the performance of an overall database calculation.

In Essbase, you can define a member to have a dynamic calculation. This definition tells Essbase to calculate a data value for the member as users request it. Dynamic calculation shortens batch database calculation time, but may increase retrieval time for the dynamically calculated data values. See Reducing the Impact on Retrieval Time.

In Essbase you specify dynamic calculations on a per-member basis. You can define a member in the database outline as one of two types of a dynamically calculated member:

  • Dynamic Calc

  • Dynamic Calc and Store

Understanding Dynamic Calc Members

For a member tagged as Dynamic Calc, Essbase does not calculate its data value during a batch database calculation (for example, during a CALC ALL). Instead, Essbase calculates the data value upon retrieval (for example, when you retrieve the data into Spreadsheet Add-in or Smart View.)

Specifically, Essbase calculates a data value dynamically when you request the data value in either of two ways:

  • By retrieving the data value into Spreadsheet Add-in or Smart View

  • By running a report script that displays the data value

Essbase does not store the calculated value; it recalculates the value for each subsequent retrieval.

Understanding Dynamic Calc and Store Members

Essbase calculates the data value for a member tagged as Dynamic Calc and Store when you retrieve the data, in the same way as for a Dynamic Calc member. For a Dynamic Calc and Store member, however, Essbase stores the data value that is calculated dynamically. Subsequent retrievals of that data value do not require recalculation, unless Essbase detects that the value needs recalculating.

Recalculation of Data

When Essbase detects that the data value for a Dynamic Calc and Store member needs recalculating, it places an indicator on the data block that contains the value, so that Essbase knows to recalculate the block on the next retrieval of the data value.

Essbase places the indicator on the data block containing the value and not on the data value itself, meaning that Essbase tracks Dynamic Calc and Store members at the data block level. See Data Blocks and the Index System.

If the data block needs recalculating, Essbase detects the need and places an indicator on the data block when any of the following situations occur:

  • You perform a batch calculation.

  • You restructure the database.

  • You use the CLEARBLOCK DYNAMIC calculation command.

    See the Oracle Essbase Technical Reference.

Essbase recalculates the indicated data blocks when you next retrieve the data value.

Effect of Updated Values on Recalculation

Because Essbase does not detect that a data block needs recalculating and does not place an indicator on the data block when you update the data, updated blocks are recalculated only during the next batch calculation. Consider these scenarios:

  • You do a data load.

  • You do a Lock and Send from Spreadsheet Add-in.

If you load data into the children of a Dynamic Calc and Store member, and the member is a consolidation of its child members, Essbase does not know to recalculate the Dynamic Calc and Store member during the next retrieval. The parent member is recalculated only during the next batch calculation.

After loading data, you must perform a batch calculation of the database or use the CLEARBLOCK DYNAMIC calculation command to ensure that the Dynamic Calc and Store members are recalculated. See the Oracle Essbase Technical Reference.

Retrieving the Parent Value of Dynamically Calculated Child Values

If you retrieve a parent value that is calculated from Dynamic Calc or Dynamic Calc and Store child members, Essbase must dynamically calculate the child member combinations before calculating the parent value. Essbase does not store the child values, even if they are Dynamic Calc and Store members.

For example, assume that Market is a parent member and that East and West are Dynamic Calc and Store child members that consolidate up to Market. When you retrieve a data value for Market, Essbase calculates East and West, even though you have not specifically retrieved them. However, Essbase does not store the values of East or West.

Benefitting from Dynamic Calculation

Dynamically calculating some database values can significantly improve the performance of an overall database calculation.

By calculating some data values dynamically, you reduce:

  • Batch calculation time of the database, because Essbase has fewer member combinations to calculate.

  • Disk usage, because Essbase stores fewer calculated data values. Database size and index size are also reduced.

  • Database restructure time. For example, adding or deleting a Dynamic Calc member in a dense dimension does not change the data block size, so Essbase does not need to restructure the database. See Restructuring Databases.

  • Time required to back up the database. Because database size is reduced, Essbase takes less time to perform a backup.

Data values that Essbase calculates dynamically can take longer to retrieve. You can estimate the retrieval time for dynamically calculated members. See Reducing the Impact on Retrieval Time.

Using Dynamic Calculation

You can tag any member as Dynamic Calc or Dynamic Calc and Store, except the following members:

  • Level 0 members that do not have a formula

  • Label-only members

  • Shared members

Which members you choose to calculate dynamically depends on the database structure and on the balance between (1) the need for reduced calculation time and disk usage and (2) the need for speedy data retrieval for users. See Choosing Values to Calculate Dynamically.

In Outline Editor, you can see which members are Dynamic Calc and which are Dynamic Calc and Store. Figure 126, Sample.Basic Outline Showing Dynamic Calc Members shows Dynamic Calc members.

Figure 126. Sample.Basic Outline Showing Dynamic Calc Members

This image shows an outline with Dynamic Calc members.

In Spreadsheet Add-in or Smart View, users can display visual cues to distinguish dynamically calculated values. See the Oracle Essbase Spreadsheet Add-in User's Guide and the Oracle Hyperion Smart View for Office User's Guide Help.

When developing spreadsheets that include dynamically calculated values, spreadsheet designers may want to use the spreadsheet Navigate Without Data option, so that Essbase does not dynamically calculate and store values while test spreadsheets are built.

Choosing Values to Calculate Dynamically

Dynamically calculating some data values decreases calculation time and disk usage and reduces database restructure time but increases retrieval time for dynamically calculated data values.

Use the guidelines described in the following sections when deciding which members to calculate dynamically.

Dense Members and Dynamic Calculation

Consider making the following changes to members of dense dimensions:

  • Tag upper-level members of dense dimensions as Dynamic Calc.

  • Try tagging level 0 members of dense dimensions with simple formulas as Dynamic Calc, and assess the increase in retrieval time.

    Simple formulas do not require Essbase to perform an expensive calculation. Formulas containing financial functions or cross-dimensional operators (->) are complex formulas.

  • Do not tag members of dense dimensions as Dynamic Calc and Store.

Sparse Members and Dynamic Calculation

Consider making the following changes to members of sparse dimensions:

  • Tag some upper-level members of sparse dimensions that have six or fewer children as Dynamic Calc or Dynamic Calc and Store.

  • Tag sparse-dimension members with complex formulas as Dynamic Calc or Dynamic Calc and Store.

    A complex formula requires Essbase to perform an expensive calculation. For example, any formula that contains a financial function is a complex formula. See Using Complex Formulas.

  • Tag upper-level members in a dimension that you frequently restructure as Dynamic Calc or Dynamic Calc and Store.

  • Do not tag upper-level, sparse-dimension members that have 20 or more descendants as Dynamic Calc or Dynamic Calc and Store.

See Choosing Between Dynamic Calc and Dynamic Calc and Store.

Two-Pass Members and Dynamic Calculation

To reduce the time needed to perform batch calculations, tag two-pass members as Dynamic Calc. You can tag any Dynamic Calc or Dynamic Calc and Store member as two-pass, even if it is not on an accounts dimension. See Using Two-Pass Calculation.

For information about the interaction of members tagged as two-pass and attribute members, see Comparing Attribute and Standard Dimensions.

For information about how querying on a two-pass member in a dense dimension impacts the dynamic calculator cache, see Two-Pass Members and Dynamic Calculation.

Parent-Child Relationships and Dynamic Calculation

If a parent member has one child member, and you tag the child as Dynamic Calc, you must also tag the parent as Dynamic Calc. Similarly, if you tag the child as Dynamic Calc and Store, you must also tag the parent as Dynamic Calc and Store. However, if a parent member has one child member, and the parent is a Dynamic Calc or Dynamic Calc and Store member, you do not have to tag the child as Dynamic Calc or Dynamic Calc and Store.

Calculation Scripts and Dynamic Calculation

When Essbase calculates a CALC ALL or CALC DIM statement in a calculation script, it bypasses the calculation of Dynamic Calc and Dynamic Calc and Store members.

Similarly, if a member set function (for example, @CHILDREN or @SIBLINGS) is used to specify the list of members to calculate, Essbase bypasses the calculation of any Dynamic Calc or Dynamic Calc and Store members in the resulting list.

If you specify a Dynamic Calc or Dynamic Calc and Store member explicitly in a calculation script, the calculation script fails. You cannot do a calculation script calculation of a Dynamic Calc or Dynamic Calc and Store member. To use a calculation script to calculate a member explicitly, do not tag the member as Dynamic Calc.

For example, the following calculation script is valid only if Qtr1 is not a Dynamic Calc member:

FIX (East, Colas)
   Qtr1;
ENDFIX

Formulas and Dynamically Calculated Members

You can include a dynamically calculated member in a formula when you apply the formula to the database outline. For example, if Qtr1 is a Dynamic Calc member, you can place the following formula on Qtr1 in the database outline:

Qtr1 = Jan + Feb;

You cannot make a dynamically calculated member the target of a formula calculation in a calculation script; Essbase does not reserve memory for a dynamically calculated value and, therefore, cannot assign a value to it. For example, if Qtr1 is a Dynamic Calc or Dynamic Calc and Store member, Essbase displays a syntax error if you include the following formula in a calculation script:

Qtr1 = Jan + Feb;

If Qtr1 is a Dynamic Calc or Dynamic Calc and Store member and Year is neither Dynamic Calc nor Dynamic Calc and Store, you can use the following formula in a calculation script:

Year = Qtr1 + Qtr2;

This formula is valid because Essbase does not assign a value to the dynamically calculated member.

Note:

When you reference a dynamically calculated member in a formula in the database outline or in a calculation script, Essbase interrupts the regular calculation to do the dynamic calculation. This interruption can significantly reduce calculation performance.

Scripts and Dynamically Calculated Members

The preprocessing phase of a calculation script cannot determine whether an outline contains dense Dynamic Calc members. If a script contains runtime-dependent formulas, Essbase must calculate all dense Dynamic Calc members when the script is executed. Using the SET FRMLRTDYNAMIC OFF calculation command improves performance by stopping calculation of these Dynamic Calc members. See the Oracle Essbase Technical Reference.

Dynamically Calculated Children

If the calculation of a member depends on the calculation of Dynamic Calc or Dynamic Calc and Store child members, Essbase must calculate the child members first during the batch database calculation in order to calculate the parent. Therefore, regular calculation time is not reduced. This requirement applies to members of sparse dimensions and members of dense dimensions.

For example, in Figure 127, Sample.Basic Outline, Showing Qtr1 as a Dynamic Calc Member, Qtr1 is a Dynamic Calc member. Its children, Jan, Feb, and Mar, are not dynamic members. Its parent, Year, is not a dynamic member. When Essbase calculates Year during a batch database calculation, it must consolidate the values of its children, including Qtr1. Therefore, it must take the additional time to calculate Qtr1, although Qtr1 is a Dynamic Calc member.

Figure 127. Sample.Basic Outline, Showing Qtr1 as a Dynamic Calc Member

This image shows an outline in which Qrt1 is a Dynamic Calc member.

Choosing Between Dynamic Calc and Dynamic Calc and Store

In most cases, you can optimize calculation performance and reduce disk usage by using Dynamic Calc members instead of Dynamic Calc and Store members. However, in specific situations, using Dynamic Calc and Store members is optimal.

Recommendations for Sparse Dimension Members

In most cases, to calculate a sparse dimension member dynamically, tag the member as Dynamic Calc instead of Dynamic Calc and Store. When Essbase calculates data values for a member combination that includes a Dynamic Calc member, Essbase calculates only the requested values of the relevant data block. These values can be a subset of the data block.

However, when Essbase calculates data values for a member combination that includes a Dynamic Calc and Store member, Essbase must calculate and store the whole data block, even if the requested data values are a subset of the data block. Thus, the calculation takes longer and the initial retrieval time is greater.

Essbase stores only the data blocks that contain the requested data values. If Essbase must calculate intermediate data blocks to calculate the requested data blocks, it does not store the intermediate blocks.

Calculating the intermediate data blocks can significantly increase the initial retrieval time. For example, in the Sample.Basic database, Market and Product are the sparse dimensions. Assume that Market and the children of Market are Dynamic Calc and Store members. When a user retrieves the data value for the member combination Market -> Cola -> Jan -> Actual -> Sales, Essbase calculates and stores the Market -> Cola data block. To calculate and store Market -> Cola, Essbase calculates the intermediate data blocks—East -> Cola, West -> Cola, South -> Cola, and Central -> Cola. Essbase does not store these intermediate data blocks.

Recommendations for Members with Specific Characteristics

Using Dynamic Calc and Store may slow initial retrieval; however, subsequent retrievals are faster than for Dynamic Calc members. Use Dynamic Calc and Store instead of Dynamic Calc for the following members:

  • An upper-level sparse dimension member with children on a remote database.

    Essbase must retrieve the value from the remote database, which increases retrieval time. See Dynamically Calculating Data in Partitions.

  • A sparse dimension member with a complex formula.

    A complex formula requires Essbase to perform an expensive calculation. Any formula that contains a financial function or a cross-dimensional member is a complex formula.

  • If users frequently retrieve an upper-level member of a sparse dimension, speedy retrieval is important.

For example, in the Sample.Basic database, if most users retrieve data at the Market level, you probably want to tag Market as Dynamic Calc and Store and its children as Dynamic Calc.

Figure 128. Sample.Basic Outline, Market is Dynamic Calc and Store Member

This image shows an outline in which Market is a Dynamic Calc and Store member.

Recommendations for Dense Dimension Members

Use Dynamic Calc members for dense dimension members. Defining members as Dynamic Calc and Store on a dense dimension provides only a small decrease in retrieval time and in batch calculation time. In addition, database size (disk usage) does not decrease significantly because Essbase reserves space in the data block for the data values of the members.

Recommendations for Data with Many Concurrent Users

Use Dynamic Calc members for data with concurrent users. If many users are concurrently retrieving Essbase data, the initial retrieval time for Dynamic Calc and Store members can be significantly longer than for Dynamic Calc members.

Dynamic Calc and Store member retrieval time increases as the number of concurrent user retrievals increases. However, Dynamic Calc member retrieval time does not increase as concurrent user retrievals increase.

If many users are concurrently accessing data, you may see significantly faster retrieval times if you use Dynamic Calc members instead of Dynamic Calc and Store members.

Understanding How Dynamic Calculation Changes Calculation Order

Using dynamically calculated data values changes the order in which Essbase calculates the values and can have implications for how you administer a database.

Calculation Order for Dynamic Calculation

When Essbase dynamically calculates data values, it calculates the data in an order different from the batch database calculation order.

During batch calculations, Essbase calculates the database in the following order:

  1. Dimension tagged as accounts

  2. Dimension tagged as time

  3. Other dense dimensions (in the order in which they appear in the database outline)

  4. Other sparse dimensions (in the order in which they appear in the database outline)

  5. Two-pass calculations

See Defining Calculation Order.

For dynamically calculated values, on retrieval, Essbase calculates the values by calculating the database in the following order:

  1. Sparse dimensions

    • If the dimension tagged as time is sparse and the database outline uses time series data, Essbase bases the sparse calculation on the time dimension.

    • Otherwise, Essbase bases the calculation on the dimension that it normally uses for a batch calculation.

  2. Dense dimensions

    1. Dimension tagged as accounts, if dense

    2. Dimension tagged as time, if dense

    3. Time series calculations

    4. Remaining dense dimensions

    5. Two-pass calculations

    6. Attributes

If your data retrieval uses attribute members, the last step in the calculation order is the summation of the attributes. Attribute calculation performs on-the-fly aggregation on data blocks that match the attribute members specified in the query. When the query contains two-pass calculation members, attribute calculation applies the two-pass calculation member formula after all the aggregated values are collected. This two-pass calculation uses the data values from the attribute calculation, not the values in a real data block.

The use of attribute members in your query causes Essbase to disregard the value of the Time Balance member in the dynamic calculations. During retrievals that do not use attributes, the value of the Time Balance member is applied to the calculations. The difference in calculation procedure between the use and nonuse of attribute members generates different results for any upper-level time members that are dynamically calculated.

During retrievals that do not use attributes, these dynamically calculated members are calculated in the last step and, therefore, apply the time balance functionality properly. However, during retrievals that do use attributes, the summation of the attribute is the last step applied. The difference in calculation order produces two different, predictable results for upper-level time members that are dynamically calculated.

Calculation Order for Dynamically Calculating Two-Pass Members

Consider the following information to ensure that Essbase produces the required calculation result when it dynamically calculates data values for members tagged as two-pass (see Using Two-Pass Calculation).

If multiple Dynamic Calc or Dynamic Calc and Store dense dimension member are tagged as two-pass, Essbase performs the dynamic calculation in the first pass, and then calculates the two-pass members in this order:

  1. Two-pass members in the accounts dimension, if any exist

  2. Two-pass members in the time dimension, if any exist

  3. Two-pass members in the remaining dense dimensions in the order in which the dimensions appear in the outline

For example, in the Sample.Basic database, assume the following:

  • Margin% in the dense Measures dimension (the dimension tagged as accounts) is tagged as Dynamic Calc and two-pass.

  • Variance in the dense Scenario dimension is tagged as Dynamic Calc and two-pass.

Essbase calculates the accounts dimension member first. So, Essbase calculates Margin% (from the Measures dimension) and then calculates Variance (from the Scenario dimension).

If Scenario is a sparse dimension, Essbase calculates Variance first, following the regular calculation order for dynamic calculations. Essbase then calculates Margin%. See Calculation Order for Dynamic Calculation.

This calculation order does not produce the required result, because Essbase needs to calculate Margin % -> Variance using the formula on Margin %, and not the formula on Variance. You can avoid this problem by making Scenario a dense dimension. This problem does not occur if the Measures dimension (the accounts dimension) is sparse, because Essbase still calculates Margin% first.

Calculation Order for Asymmetric Data

Because the calculation order used for dynamic calculations differs from the calculation order used for batch database calculations, in some database outlines, you may get different calculation results if you tag certain members as Dynamic Calc or Dynamic Calc and Store. These differences happen when Essbase dynamically calculates asymmetric data.

Symmetric data calculations produce the same results no matter which dimension is calculated.

Using the data set in Table 75, the calculation for Qtr1-> Profit produces the same result whether you calculate along the dimension tagged as time or the dimension tagged as accounts. Calculating along the time dimension, add the values for Jan, Feb, and Mar:

50+100+150=300 

Calculating along the accounts dimension, subtract Qtr1 -> COGS from Qtr1 -> Sales:

600–300=300 

Table 75. Example of a Symmetric Calculation

Time -> Accounts

Jan

Feb

Mar

Qtr1

Sales

100

200

300

600

COGS

50

100

150

300

Profit (Sales – COGS)

50

100

150

300

Asymmetric data calculations calculate differently along different dimensions.

Using the data set in Table 76, the calculation for East -> Sales produces the correct result when you calculate along the Market dimension, but produces an incorrect result when you calculate along the accounts dimension. Calculating along the Market dimension, adding the values for New York, Florida, and Connecticut produces the correct results:

50 + 100 + 100 = 250

Calculating along the accounts dimension, multiplying the value East -> Price by the value East -> UnitsSold produces incorrect results:

15 * 50 = 750

Table 76. Example of an Asymmetric Calculation

Market -> Accounts

New York

Florida

Connecticut

East

UnitsSold

10

20

20

50

Price

5

5

5

15

Sales (Price * UnitsSold)

50

100

100

250

In the following outline, East is a sparse dimension, and Accounts is a dense dimension:

This image shows an outline in which East is a sparse dimension, and Accounts is a dense dimension.

If East and Sales are tagged as Dynamic Calc, Essbase calculates a different result than it does if East and Sales are not tagged as Dynamic Calc.

If East and Sales are not Dynamic Calc members, Essbase produces the correct result by calculating these dimensions:

  1. Dense Accounts dimension—calculating the values for UnitsSold, Price, and Sales for New York, Florida, and Connecticut

  2. Sparse East dimension—aggregating the calculated values for UnitsSold, Price, and Sales for New York, Florida, and Connecticut to obtain the Sales values for East

If East and Sales are Dynamic Calc members, Essbase produces an incorrect result by calculating these dimensions:

  1. Sparse East dimension—aggregating the values for UnitsSold, Price, and Sales for New York, Florida, and Connecticut to obtain the values for East

  2. Values for East -> Sales—taking the aggregated values in the East data blocks and performing a formula calculation with these values to obtain the value for Sales

To avoid this problem and ensure that you obtain the required results, do not tag the Sales member as Dynamic Calc or Dynamic Calc and Store.

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

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

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 the disk or from the database 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 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 Spreadsheet Add-in 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 simultaneously.

By default, the retrieval buffer size is 10 KB. However, you may speed 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

Oracle Essbase Administration Services Online Help

MaxL

alter database

Oracle Essbase Technical Reference

ESSCMD

SETDBSTATEITEM

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

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. 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 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 whether the dynamic calculator cache is being used effectively, review both messages and consider your essbase.cfg 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. See the Oracle Essbase Technical Reference.

Using Dynamic Calculations with Standard Procedures

Using dynamic calculations with standard Essbase procedures affects these processes:

  • Clearing data and data blocks

    You can use the CLEARBLOCK DYNAMIC command to remove data blocks for Dynamic Calc and Store member combinations.

    You can use the CLEARDATA command to mark Dynamic Calc and Store data blocks, so that Essbase knows to recalculate the blocks. The CLEARDATA command has no effect on data values for Dynamic Calc members.

  • Copying data

    You cannot copy data to a dynamically calculated data value. You cannot specify a Dynamic Calc or Dynamic Calc and Store member as the target for the DATACOPY calculation command.

  • Converting currencies

    You cannot specify a Dynamic Calc or Dynamic Calc and Store member as the target for the CCONV command.

  • Loading data

    When you load data, Essbase does not load data into member combinations that contain a Dynamic Calc or Dynamic Calc and Store member. Essbase skips these members during data load and does not display an error message.

    To place data into Dynamic Calc and Dynamic Calc and Store members, after loading data, ensure that Essbase recalculates Dynamic Calc and Store members. See Effect of Updated Values on Recalculation.

  • Exporting data

    Essbase does not calculate dynamically calculated values before exporting data. Essbase does not export values for Dynamic Calc members. Essbase exports values for Dynamic Calc and Store members only if a calculated value exists in the database from a previous user retrieval of the data.

  • Reporting data

    Essbase cannot use the SPARSE data extraction method for dynamically calculated members. The SPARSE data extraction method optimizes performance when a high proportion of the reported data rows are #MISSING. See the <SPARSE command in the Oracle Essbase Technical Reference.

  • Including dynamic members in calculation scripts

    When calculating a database, Essbase skips the calculation of any Dynamic Calc or Dynamic Calc and Store members. Essbase displays an error message if you attempt to do a member calculation of a Dynamic Calc or Dynamic Calc and Store member in a calculation script. See Calculation Scripts and Dynamic Calculation.

Creating Dynamic Calc and Dynamic Calc and Store Members

  To create Dynamic Calc and Dynamic Calc and Store members using Outline Editor, see “Setting Member Storage Properties” in the Oracle Essbase Administration Services Online Help.

  To create Dynamic Calc and Dynamic Calc and Store members during a dimension build, in the dimension build data file, use the property X for Dynamic Calc and the property V for Dynamic Calc and Store. See Using the Data Source to Work with Member Properties.

Restructuring Databases

When you add a Dynamic Calc member to a dense dimension, Essbase does not reserve space in the data block for the member’s values. Therefore, Essbase does not need to restructure the database. However, when you add a Dynamic Calc and Store member to a dense dimension, Essbase does reserve space in the relevant data blocks for the member’s values and, therefore, must restructure the database.

When you add a Dynamic Calc or a Dynamic Calc and Store member to a sparse dimension, Essbase updates the index but does not change the relevant data blocks. See Index Manager.

Essbase can save changes to the database outline significantly faster if it does not have to restructure the database.

In the following cases, Essbase does not restructure the database or change the index (Essbase saves only the database outline, which is very fast):

  • Add, delete, or move a dense dimension Dynamic Calc member.

    Essbase does restructure the database if the member is Dynamic Calc and Store.

  • Change the storage property of a dense dimension member from Dynamic Calc and Store member to a nondynamic storage property.

  • Change the storage property of a sparse dimension Dynamic Calc or Dynamic Calc and Store member to a nondynamic storage property.

  • Rename any Dynamic Calc or Dynamic Calc and Store member.

In the following cases, Essbase does not restructure the database but does restructure the database index, which is significantly faster:

  • Add, delete, or move sparse dimension Dynamic Calc or Dynamic Calc and Store members.

  • Change the storage property of a dense dimension member from a nondynamic value to Dynamic Calc and Store.

In the following cases, Essbase restructures the database:

  • Add, delete, or move a dense dimension Dynamic Calc and Store member.

    Essbase does not restructure the database if the member is Dynamic Calc.

  • Change a dense dimension Dynamic Calc and Store member to a Dynamic Calc member.

  • Change a dense dimension Dynamic Calc member to a Dynamic Calc and Store member.

  • Change the storage property of a nondynamic member in a dense dimension to Dynamic Calc.

  • Change the storage property of a dense dimension from Dynamic Calc member to a nondynamic value.

  • Change the storage property of a nondynamic member in a sparse dimension Dynamic Calc or Dynamic Calc and Store.

See Types of Database Restructuring.

Dynamically Calculating Data in Partitions

You can define Dynamic Calc and Dynamic Calc and Store members in transparent, replicated, or linked regions of the partitions. See Designing Partitioned Applications.

For example, if you tag an upper-level, sparse dimension member with children that are on a remote database (transparent database partition) as Dynamic Calc and Store, because Essbase retrieves child values from the other database, retrieval time is increased. You can use Dynamic Calc instead of Dynamic Calc and Store; however, the impact on subsequent retrieval time might be too great.

For example, assume that the local database is the Corporate database, which has transparent partitions to the regional data for East, West, South, and Central. You can tag the parent member Market as Dynamic Calc and Store.

In a transparent partition, the definition on the remote database takes precedence over any definition on the local database. For example, if a member is tagged as Dynamic Calc in the local database but not in the remote database, Essbase retrieves the value from the remote database and does not do the local calculation.

If you are using a replicated partition, consider using Dynamic Calc members instead of Dynamic Calc and Store members. When calculating replicated data, Essbase does not retrieve the child blocks from the remote database; therefore, the impact on retrieval time is not great.

Note:

When Essbase replicates data, it checks the time stamp on each source data block and each corresponding target data block. If the source data block is more recent, Essbase replicates the data in the data block. However, for dynamically calculated data, data blocks and time stamps do not exist. Therefore, Essbase always replicates dynamically calculated data.