In This Section:
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.
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.
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.)
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.
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.
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:
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.
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.
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.
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.
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 128, Sample.Basic Outline Showing Dynamic Calc Members shows 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 Online Help and the Oracle Hyperion Smart View for Office Online 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.
Simple formulas do not require Essbase to perform an expensive calculation. Formulas containing financial functions or cross-dimensional operators (->) are complex formulas.
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.
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.
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.
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
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;
Year = Qtr1 + Qtr2;
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.
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 129, 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.
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.
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.
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:
Essbase must retrieve the value from the remote database, which increases retrieval time. See Dynamically Calculating Data in Partitions.
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.
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.
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 your data retrieval uses attribute members, the last step in the calculation order is the summation of the attributes. However, 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.
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 more than one Dynamic Calc or Dynamic Calc and Store dense dimension member is tagged as two-pass, Essbase performs the dynamic calculation in the first pass, and then calculates the two-pass members in this order:
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.
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.
Using the data set in Table 48, 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:
Table 48. Example of a Symmetric Calculation
Asymmetric data calculations calculate differently along different dimensions.
Using the data set in Table 49, 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 49. Example of an Asymmetric Calculation
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.
For a list of functions that have the most significant effect on query retrieval, see Choosing Between Member Set Functions and Performance.
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.
To view an estimated retrieval factor, see Viewing the Essbase Server and Application Logs.
[Wed Sep 20 20:04:13 2000] Local/Sample///Info (1012710) Essbase needs to retrieve  Essbase kernel blocks in order to calculate the top dynamically-calculated block.
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.
[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.
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 at one time.
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.
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.
The dynamic calculator cache and the calculator cache use different approaches to optimizing calculation performance.
[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 :  non-Dyn.Calc.Cache : 
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 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.
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.
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.
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.
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.
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.
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 does restructure the database if the member is Dynamic Calc and Store.
Essbase does not restructure the database if the member is Dynamic Calc.
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.
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.