This chapter explains how you calculate data values dynamically and how you benefit from doing so. Dynamically calculating some of the values in a database can significantly improve the performance of an overall database calculation.
The information in this chapter assumes that you are familiar with the concepts of member combinations, dense and sparse dimensions, and data blocks. For this information, see Understanding Hyperion Essbase.
This chapter includes the following sections:
When you design your overall database calculation, it may be more efficient to calculate some member combinations when you retrieve their data, instead of pre-calculating the member combinations during the regular 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 regular database calculation time, but may increase retrieval time for the dynamically calculated data values. See Reducing the Impact on Retrieval Time for more information.
In Essbase you specify dynamic calculations on a per-member basis. You can define a member in your database outline as one of two types of a dynamically calculated member:
Use the rest of this section to gain a basic understanding of dynamic calculation:
For a member that is tagged as Dynamic Calc, Essbase does not calculate its data value during the regular 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.
Specifically, Essbase calculates a data value dynamically when you request the data value in either of two ways:
Essbase does not store the calculated value; it recalculates the value for any subsequent retrieval.
Essbase calculates the data value for a member that is 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 it 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. In other words, Essbase tracks Dynamic Calc And Store members at the data block level. For more information on data blocks, see Basic Architectural Elements.
If the data block needs recalculating, Essbase detects the need and places an indicator on the data block when any of the following occur:
Essbase recalculates the indicated data blocks when you next retrieve the data value.
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; that is, updated blocks are recalculated only during the next batch calculation. Consider the following two 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 need to do a regular batch calculation of the database or use the CLEARBLOCK DYNAMIC calculation command to ensure that the Dynamic Calc And Store members are recalculated. For more information on CLEARBLOCK DYNAMIC, see Clearing data and data blocks and the Technical Reference in the docs directory.
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.
Dynamically calculating some database values can significantly improve the performance of an overall database calculation.
When you tell Essbase to calculate some data values dynamically, you achieve the following advantages:
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.
You can tag any member as Dynamic Calc or Dynamic Calc And Store, except the following:
Which members you choose to calculate dynamically depends on your database structure and on the balance between (1) your need for reduced calculation time and disk usage and (2) your need for speedy data retrieval for users. See Choosing Values to Calculate Dynamically.
Outline Editor in Application Manager shows which members are Dynamic Calc and which members are Dynamic Calc And Store.
Figure 413: Sample Basic Outline Showing Dynamic Calc Members
In Spreadsheet Add-in, users can display visual cues to distinguish dynamically calculated values. For more information, see the Essbase Spreadsheet Add-in User's Guide.
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 being built.
Dynamically calculating some data values decreases calculation time, lowers 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:
Consider making the following changes to members of dense dimensions:
Simple formulas are formulas that do not require Essbase to perform an expensive calculation. Formulas containing, for example, financial functions or cross-dimensional operators (->) are complex formulas.
Consider making the following changes to members of sparse dimensions:
For more information, see Choosing Between Dynamic Calc and Dynamic Calc And Store.
To reduce the amount of 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 the member is not on an accounts dimension.
For more information about two-pass calculation, see Using Two-Pass Calculation. For details about the interaction of members tagged as two-pass and attribute members, see Table 14.
If a parent member has a single child member and you tag the child as Dynamic Calc, you must tag the parent as Dynamic Calc. Similarly, if you tag the child as Dynamic Calc And Store, you must tag the parent as Dynamic Calc And Store. However, if a parent member has a single 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.
When Essbase calculates, for example, 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 you specify a Dynamic Calc or Dynamic Calc and Store member explicitly in a calculation script, the calculation script will fail. You cannot do a calculation script calculation of a Dynamic Calc or Dynamic Calc And Store member. If you want 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 could 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 space 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;
However, if Qtr1 is a Dynamic Calc or Dynamic Calc And Store member and Year is a regular member, you can use the following formula in a calculation script:
Year = Qtr1 + Qtr2;
This formula is valid because Essbase is not assigning 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 lower calculation performance.
If the calculation of a regular member depends on the calculation of Dynamic Calc or Dynamic Calc And Store child members, then Essbase has to calculate the child members during the regular database calculation in order to calculate the parent. Therefore, there is no reduction in regular calculation time. This requirement applies to members of sparse dimensions and members of dense dimensions.
For example, consider the following outline:
Figure 414: Sample Basic Outline Showing Qtr1 as a Dynamic Calc Member
Jan, Feb, and Mar are regular members, Qtr1 is a Dynamic Calc member, and Year is a regular member. When Essbase calculates Year during the regular database calculation, it has to calculate Qtr1 in order to consolidate it up to Year. Essbase calculates Qtr1 even though Qtr1 is a Dynamic Calc member.
In most cases you can optimize calculation performance and lower 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, when you want 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 needs to 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 needs to calculate any intermediate data blocks in order 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. In order 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:
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 415: Sample Basic Outline, Market is Dynamic Calc And Store Member
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 regular calculation time. In addition, database size (disk usage) does not decrease significantly because Essbase reserves space for the member's data values in the data block.
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 higher 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 lower retrieval times if you use Dynamic Calc members instead of Dynamic Calc And Store members.
Using dynamically calculated data values changes the order in which Essbase calculates the values and can have implications for the way you administer a database:
When Essbase dynamically calculates data values, it calculates the data in an order that is different from the regular database calculation order. For detailed information on calculation order, see Defining the Calculation Order.
During regular calculations, Essbase calculates the database in this order:
For dynamically calculated values, on retrieval, Essbase calculates the values by calculating the database in the following order:
Consider the following information to ensure that Essbase produces the required calculation result when it dynamically calculates data values for members that are tagged as two-pass. For more information on two-pass calculations, 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 regular dynamic calculation and then calculates the two-pass members in the following order:
For example, in the Sample Basic database, assume the following:
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. See Calculation Order for Dynamic Calculations. Essbase then calculates Margin%.
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 then still calculates Margin% first.
Because the calculation order used for dynamic calculations differs from the calculation order used for regular 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. Asymmetric data calculations calculate differently along different dimensions.
Time -> Accounts |
Jan |
Feb |
Mar |
Qtr1 |
---|---|---|---|---|
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:
Calculating along the accounts dimension, subtract Qtr1 -> COGS from Qtr1 -> Sales:
Market -> Accounts |
New York |
Florida |
Connecticut |
East |
---|---|---|---|---|
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:
Calculating along the accounts dimension, multiplying the value East -> Price by the value East -> UnitsSold produces incorrect results:
In this outline, East is a sparse dimension, and Accounts is a dense dimension:
If East and Sales are tagged as Dynamic Calc, then 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 as follows:
If East and Sales are Dynamic Calc members, Essbase produces an incorrect result by calculating as follows:
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.
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.
Note: 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 for details.
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 in Application Manager:
Essbase displays the Log Viewer window for the database you selected.
A message similar to this sample indicates a retrieval factor:
This message tells you that Essbase needs to retrieve one block in order to calculate the most expensive dynamically calculated data block.
Tip: You can also use the View Log dialog box in Administration Services to view logs. For more information, see Essbase Administration Services Online Help.
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 in Application Manager:
Essbase displays the Log Viewer window for the database you selected.
A message similar to this sample is displayed:
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.
Essbase includes Dynamic Time Series members in these numbers. For more information, see Calculating Time Series Data.
When you retrieve data into Essbase 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 can speed up retrieval time if you set the retrieval buffer size greater than 10 KB.
To set the retrieval buffer size in Application Manager:
Essbase displays the Database Settings dialog box.
Tip: You can set the size of the retrieval buffer outside the Essbase Application Manager.
Tool |
Instructions |
For More Information |
---|---|---|
For more information on sizing the retrieval buffer, 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.
Note: The dynamic calculator cache uses a different approach to optimizing calculation performance than the calculator cache. For information about the calculator cache, see Managing Caches to Improve Performance.
Essbase uses a separate dynamic calculator cache for each open database. The single DYNCALCCACHEMAXSIZE setting in the configuration file, essbase.cfg, specifies the maximum size for each dynamic calculator cache on the server. By default, the maximum size is 20MB. Essbase allocates area in a dynamic calculator cache for data blocks, as needed, until it has allocated the maximum memory area specified by this setting.
Five additional configuration file settings are relevant to dynamic calculator caches. The optimum settings for the dynamic calculator cache depend on the amount of memory on the server computers, the configuration of the databases, and the nature of user queries. Table 38 describes each setting and includes recommendations on how to determine values for your system. To fit your situation, you may need to test and adjust the settings.
DYNCALCCACHEMAXSIZE |
|
This setting specifies the maximum size Essbase can allocate to each dynamic calculator cache on the server. |
|
Recommended Setting value = C * S * U C is the value of the appropriate CALCLOCKBLOCK setting in the essbase.cfg file. (The SET LOCKBLOCK command specifies which CALCLOCKBLOCK setting to use.) S is the size of the largest expanded block across all databases on the computer. To calculate the expanded block size, multiply the number of members (including Dynamic Calc members) in each dense dimension together, then multiply the result by the size of each member cell, 8 bytes. For example, for the dense dimensions in Sample Basic, 12 (Year) * 8 (Measures) * 3 (Scenario) * 8 bytes = 2304 bytes. U is the maximum number of expected concurrent users on the database that has the largest number of concurrent users. Assigning the value 0 (zero) to DYNCALCACHEMAXSIZE tells Essbase not to use dynamic calculator caches. |
|
DYNCALCCACHEONLY |
|
For situations when the dynamic calculator cache has no more room, this setting specifies whether or not Essbase may perform Dynamic Calc calculations in memory outside the dynamic calculator cache. |
|
Setting value = FALSE (The default value). Only set this value to TRUE for one or more of the following circumstances: |
|
DYNCALCCACHEWAITFORBLK |
|
If Essbase uses up the maximum area for a dynamic calculator cache, this setting tells Essbase whether to wait until space becomes available in the cache or to immediately write and calculate the blocks in memory outside the dynamic calculator cache. If DYNCALCCACHEONLY is set to TRUE, instead of using memory outside the dynamic calculator cache, Essbase generates an error message. If the dynamic calculator cache is too small, it is possible for more than one thread to be in queue, each thread waiting to calculate its data blocks. |
|
Setting value = FALSE (The default value). If you cannot add physical memory to the server computer, before setting to TRUE, iteratively increase the value of DYNCALCCACHEMAXSIZE and test until you determine that you cannot spare more memory for the dynamic calculator cache. |
|
DYNCALCCACHEBLKTIMEOUT |
|
If Essbase is to wait for available space in the dynamic calculator cache, this setting defines how long it will wait. |
|
WT is the maximum tolerable wait time for a query; for example, 5 seconds. B is the total number of logical blocks accessed in the largest query. To determine the value of B, check the messages in the application log for the largest number of Dyn.Calc.Cache "Big Block Allocs" for a query, as shown in Figure 416. |
|
DYNCALCCACHEBLKRELEASE |
|
Essbase uses this setting when it has waited the specified time and space is still not available in the dynamic calculator cache. A TRUE setting tells Essbase to use space in the dynamic calculator cache that is made available by swapping out blocks and temporarily compressing them in a dynamic calculator cache compressed-block buffer. A FALSE setting tells Essbase to write and calculate the blocks immediately outside the dynamic calculator cache. If DYNCALCCACHEONLY is set to TRUE, instead of using memory outside the dynamic calculator cache, Essbase generates an error message. |
|
Setting value = FALSE (The default value). Set to TRUE only if you are experiencing severe memory shortage problems. |
|
DYNCALCCACHECOMPRBLKBUFSIZE |
|
This setting is the size of the dynamic calculator cache compressed-block buffer to be used if Essbase has waited the specified wait time and the DYNCALCCACHEBLKRELEASE setting is TRUE. |
|
C is the value of the current CALCLOCKBLOCK setting in the essbase.cfg file. The SET LOCKBLOCK command specifies which CALCLOCKBLOCK configuration setting is current. S is the size of the largest expanded block across all databases on the computer. Calculate S as described for the DYNCALCCACHEMAXSIZE setting. |
Note: For the new values to take effect, after changing any parameter in the essbase.cfg file, you must close and restart Essbase.
For more information about dynamic calculator cache settings, see the Technical Reference in the docs directory.
Essbase writes two messages to the application log for each data retrieval. As shown in the example in Figure 416, the first message describes the total amount of time required for the retrieval.
Figure 416: Application Log Example of Memory Usage for Data Blocks Containing Dynamic Calc Members
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 as well as 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 regular memory 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 Technical Reference in the docs directory for information about this command. See Automating the Production Environment for information about ESSCMD
Using dynamic calculations with standard Essbase procedures affects these processes:
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.
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.
You cannot specify a Dynamic Calc or Dynamic Calc And Store member as the target for the CCONV command.
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. Essbase does not display an error message.
To place data into Dynamic Calc and Dynamic Calc And Store members, after loading data, you need to ensure that Essbase recalculates Dynamic Calc And Store members. To do this, 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. For more information, see the <SPARSE command in the Technical Reference in the docs directory.
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. For more information, see Calculation Scripts and Dynamic Calculations.
In Application Manager Outline Editor, you can tag members as Dynamic Calc or as Dynamic Calc And Store. When you build a dimension, you can use properties to indicate Dynamic Calc and Dynamic Calc And Store members.
To tag a member as Dynamic Calc using Application Manager:
Alternatively, in the Outline Editor menu, select Edit > Properties. Then, in the Data Storage group of the Member Specification dialog box, select Dynamic Calc.
To tag a member as Dynamic Calc And Store using Application Manager:
Alternatively, in the Outline Editor menu, select Edit > Properties. Then, in the Data Storage group of the Member Specification dialog box, select Dynamic Calc And Store.
To remove a Dynamic Calc or Dynamic Calc And Store tag using Application Manager:
Alternatively, in the Outline Editor menu, select Edit > Properties. Then, in the Data Storage group of the Member Specification dialog box, select Store Data.
You can build dimensions with Dynamic Calc and Dynamic Calc And Store members. In the dimension build data file, use the property X for Dynamic Calc and the property V for Dynamic Calc And Store. For more information, see Setting 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 needs to 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. For more information on the database index, see Managing Essbase Kernel Settings.
Essbase can save changes to your database outline significantly faster if it does not have to restructure the database.
In the following cases, Essbase does not restructure the database. Essbase only has to save the database outline, which is very fast.
Essbase does not restructure the database or change the index when you do any of the following:
In the following cases, Essbase does not restructure the database, but does have to restructure the database index. Restructuring the index is significantly faster than restructuring the database.
Essbase restructures only the database index when you do either of the following:
However, Essbase does restructure your database when you do any of the following:
For detailed information on the types of database restructuring, see Understanding Database Restructuring.
You can define Dynamic Calc and Dynamic Calc And Store members in transparent, replicated, or linked regions of your partitions. For more information on partitions, see Designing Partitioned Applications.
For example, you might want to 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 needs to retrieve the child values from the other database, retrieval time is increased. You could use Dynamic Calc instead of Dynamic Calc And Store; however, the impact on subsequent retrieval time might be too great.
For example, assume that your local database is the Corporate database, which has transparent partitions to the regional data for East, West, South, and Central. You could 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, then you might want to use 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, and 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.
![]() © 2002 Hyperion Solutions Corporation. All rights reserved. http://www.hyperion.com |