Dynamically Calculating Data Values

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 Designing Hyperion Essbase Applications.

Understanding Dynamic Calculations

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 Hyperion Essbase, you can define a member to have a dynamic calculation. This definition tells Hyperion Essbase to calculate a data value for the member "on-the-fly" 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 Hyperion 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:

Understanding Dynamic Calc Members

For a member that is tagged as Dynamic Calc, Hyperion Essbase does not calculate its data value during the regular database calculation; for example, during a CALC ALL. Instead, Hyperion Essbase calculates the data value upon retrieval; for example, when you retrieve the data into Hyperion Essbase Spreadsheet Add-in.

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

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

Understanding Dynamic Calc And Store Members

Hyperion 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, Hyperion Essbase stores the data value that is calculated dynamically. Subsequent retrievals of that data value do not require recalculation, unless Hyperion Essbase detects that the value needs recalculating.

Recalculation of Data

When Hyperion 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.

Hyperion Essbase places the indicator on the data block containing the value and not on the data value itself. In other words, Hyperion 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, Hyperion Essbase detects the need and places an indicator on the data block when any of the following occur:

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

Effect of Updated Values on Recalculation

Hyperion 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, Hyperion 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 online Technical Reference in the DOCS directory.

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, Hyperion Essbase must dynamically calculate the child member combinations before calculating the parent value. Hyperion 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, Hyperion Essbase calculates East and West, even though you have not specifically retrieved them. However, Hyperion Essbase does not store the values of East or West.

Benefitting from Dynamic Calculations

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

When you tell Hyperion Essbase to calculate some data values dynamically, you achieve the following advantages:

Data values that Hyperion 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 Calculations

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 Which Values to Calculate Dynamically.

Outline Editor in Hyperion Essbase Application Manager shows which members are Dynamic Calc and which members are Dynamic Calc And Store.

In Hyperion Essbase Spreadsheet Add-in, users can display visual cues to distinguish dynamically calculated values. For more information, see the Hyperion 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 Hyperion Essbase does not dynamically calculate and store values while test spreadsheets are being built.

Choosing Which Values to Calculate Dynamically

To decide when to calculate data values dynamically, consider your needs for the following:

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.

Tagging Dense Dimension Members

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

Tagging Sparse Dimension Members

Tagging Two-Pass Members

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.

Calc Scripts and Dynamic Calculations

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

You cannot do a calc script calculation of a Dynamic Calc or Dynamic Calc And Store member. If you want to use a calc script to calculate a member explicitly, do not tag the member as Dynamic Calc. For example, the following calc script is valid only if Qtr1 is not a Dynamic Calc member:

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 could place the following formula on Qtr1 in the database outline:

You cannot make a dynamically calculated member the target of a formula calculation in a calc script; Hyperion 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, Hyperion Essbase displays a syntax error if you include the following formula in a calc script:

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 calc script:

This formula is valid because Hyperion Essbase is not assigning a value to the dynamically calculated member.

Dynamically Calculated Children of Regular Members

If the calculation of a regular member depends on the calculation of Dynamic Calc or Dynamic Calc And Store child members, then Hyperion 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.

Jan, Feb, and Mar are regular members, Qtr1 is a Dynamic Calc member, and Year is a regular member. When Hyperion Essbase calculates Year during the regular database calculation, it has to calculate Qtr1 in order to consolidate it up to Year. Hyperion Essbase calculates Qtr1 even though Qtr1 is a Dynamic Calc member.

Choosing Between Dynamic Calc and Dynamic Calc And Store

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.

Recommendations for Sparse Dimension Members

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 Hyperion Essbase calculates data values for a member combination that includes a Dynamic Calc member, Hyperion Essbase calculates only the requested values of the relevant data block. These values can be a subset of the data block.

However, when Hyperion Essbase calculates data values for a member combination that includes a Dynamic Calc And Store member, Hyperion 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.

Hyperion Essbase stores only the data blocks that contain the requested data values. If Hyperion 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, Hyperion Essbase calculates and stores the Market->Cola data block. In order to calculate and store Market->Cola, Hyperion Essbase calculates the intermediate data blocks: East->Cola, West->Cola, South->Cola, and Central->Cola. Hyperion 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:

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 29-3: Sample Basic Outline Showing Market as 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 regular calculation time. In addition, database size (disk usage) does not decrease significantly because Hyperion Essbase reserves space for the member's data values in the data block.

Recommendations for Data with Many Concurrent Users

Use Dynamic Calc members for data with concurrent users. If many users are concurrently retrieving Hyperion 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.

Considering the Effects of Dynamic Calculations

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

Calculation Order for Dynamic Calculations

When Hyperion 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, Hyperion Essbase calculates the database in the following order:

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

Calculation Order for Dynamically Calculating Two-Pass Members

Consider the following information to ensure that Hyperion 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 Optimizing Calculations.

If more than one Dynamic Calc or Dynamic Calc And Store dense dimension member is tagged as two-pass, Hyperion Essbase performs the regular dynamic calculation and then calculates the two-pass members in the following order:

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

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

This calculation order does not produce the required result because Hyperion 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 Hyperion Essbase then still calculates Margin% first.

Calculation Order for Asymmetric Data

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

*Table 29-1: 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

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, you add the values for Jan, Feb, and Mar: 50+100+150=300. Calculating along the accounts dimension, you subtract Qtr1->COGS from Qtr1->Sales: 600-300=300.

*Table 29-2: 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

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, you add the values for New York, Florida, and Connecticut: 50+100+100=250, which is correct. Calculating along the accounts dimension, you multiply the value East->Price by the value East->UnitsSold: 15 * 50=750, which is incorrect.

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

If East and Sales are tagged as Dynamic Calc, then Hyperion 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, Hyperion Essbase produces the correct result by calculating as follows:

If East and Sales are Dynamic Calc members, Hyperion 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 as Dynamic Calc And Store.

Reducing the Impact on Retrieval Time

When you dynamically calculate a member of a dense dimension, the increase in retrieval time is not significant unless the member contains a complex formula. See Choosing Which Values to Calculate Dynamically.

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

Displaying a Retrieval Factor

To help you estimate any increase in retrieval time, Hyperion Essbase calculates a retrieval factor for a database outline when you save the outline. Hyperion Essbase calculates this retrieval factor based on the dynamically calculated data block that is the most expensive for Hyperion 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 Hyperion 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.

Hyperion Essbase displays the retrieval factor value in the application event log file.

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

Displaying a Summary of Dynamically Calculated Members

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

Figure 29-6: Event Log Summary of Dynamic Calc and Dynamic Calc And Store Members

This message tells you that there are two Dynamic Calc members in the first dimension in the database outline and four in the fourth dimension in the database outline. There are no Dynamic Calc And Store members.

Hyperion Essbase includes Dynamic Time Series members in these numbers. For more information, see Calculating Time Series Data.

Increasing the Retrieval Buffer Size

When you retrieve data into Hyperion Essbase Spreadsheet Add-in or use Report Writer to retrieve data, Hyperion Essbase uses the retrieval buffer to optimize the retrieval. Hyperion Essbase processes the data in sections. Increasing the retrieval buffer size can significantly reduce retrieval time because Hyperion 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.

Using Dynamic Calculator Caches

By default, when Hyperion 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 Hyperion Essbase writes these blocks into the dynamic calculator cache, it expands them to include all Dynamic Calc members in the dense dimensions.

Using Hyperion Essbase's 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.

Sizing Dynamic Calculator Caches

Hyperion 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. Hyperion 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 machine, the configuration of the databases, and the nature of user queries. Table 29-3 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.

*Table 29-3: ESSBASE.CFG Settings for Dynamic Calculator Caches*
DYNCALCCACHEMAXSIZE
Description	This setting specifies the maximum size Hyperion Essbase can allocate to each dynamic calculator cache on the server.
Recommended Setting	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 machine. 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 Hyperion Essbase not to use dynamic calculator caches. The default size is 20 megabytes (20,971,520 bytes).
DYNCALCCACHEONLY
Description	For situations when the dynamic calculator cache has no more room, this setting specifies whether or not Hyperion Essbase may perform Dynamic Calc calculations in memory outside the dynamic calculator cache.
Recommended Setting	Setting value = FALSE (The default value). Only set this value to TRUE for one or more of the following circumstances: The operating system is not properly reclaiming memory outside the dynamic calculator cache. A severe memory shortage exists. Tighter control is required over memory usage for dynamic calculations.
DYNCALCCACHEWAITFORBLK
Description	If Hyperion Essbase uses up the maximum area for a dynamic calculator cache, this setting tells Hyperion 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 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.
Recommended Setting	Setting value = FALSE (The default value). If you cannot add physical memory to the server machine, 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
Description	If Hyperion Essbase is to wait for available space in the dynamic calculator cache, this setting defines how long it will wait.
Recommended Setting	Setting value = WT / B 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 29-7.
DYNCALCCACHEBLKRELEASE
Description	Hyperion Essbase uses this setting when it has waited the specified time and space is still not available in the dynamic calculator cache. This setting tells Hyperion Essbase whether to write and calculate the blocks immediately outside the dynamic calculator cache or to use space in the dynamic calculator cache that it makes available by swapping out blocks and temporarily compressing them in a dynamic calculator cache compressed-block buffer.
Recommended Setting	Setting value = FALSE (The default value). Set to TRUE only if you are experiencing severe memory shortage problems.
DYNCALCCACHECOMPRBLKBUFSIZE
Description	This setting is the size of the dynamic calculator cache compressed-block buffer to be used if Hyperion Essbase has waited the specified wait time and the DYNCALCCACHEBLKRELEASE setting is TRUE.
Recommended Setting	Setting value = (C * S) / 2 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 machine. Calculate S as described for the DYNCALCCACHEMAXSIZE setting.

For more information about dynamic calculator cache settings, see the online Technical Reference in the DOCS directory.

Reviewing Dynamic Calculator Cache Usage

Hyperion Essbase writes two messages to the application event log for each data retrieval. As shown in the example in Figure 29-7, the first message describes the total amount of time required for the retrieval.

Figure 29-7: Application Event 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.

Using Dynamic Calculations with Standard Procedures

The following sections discuss the effects of using dynamic calculations with standard Hyperion Essbase procedures.

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 Hyperion 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, Hyperion Essbase does not load data into member combinations that contain a Dynamic Calc or Dynamic Calc And Store member. Hyperion Essbase skips these members during data load. Hyperion 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 Hyperion Essbase recalculates Dynamic Calc And Store members. To do this, see Effect of Updated Values on Recalculation.

Exporting Data

Hyperion Essbase does not calculate dynamically calculated values before exporting data. Hyperion Essbase does not export values for Dynamic Calc members. Hyperion 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

Hyperion 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 online Technical Reference in the DOCS directory.

Including Dynamic Calc and Dynamic Calc And Store Members in Calc Scripts

When calculating a database, Hyperion Essbase skips the calculation of any Dynamic Calc or Dynamic Calc And Store members. Hyperion 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 calc script. For more information, see Calc Scripts and Dynamic Calculations.

Creating Dynamic Calc and Dynamic Calc And Store Members

In Hyperion Essbase 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.

Building Dimensions with Dynamic Calc Members

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.

Restructuring a Database

When you add a Dynamic Calc member to a dense dimension, Hyperion Essbase does not reserve space in the data block for the member's values. Therefore, Hyperion Essbase does not need to restructure the database. However, when you add a Dynamic Calc And Store member to a dense dimension, Hyperion 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, Hyperion Essbase updates the index, but does not change the relevant data blocks. For more information on the database index, see Introducing the Hyperion Essbase Kernel.

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

In the following cases, Hyperion Essbase does not restructure the database. Hyperion Essbase only has to save the database outline, which is very fast.

Hyperion Essbase does not restructure the database or change the index when you do any of the following:

In the following cases, Hyperion Essbase does not restructure the database, but does have to restructure the database index. Restructuring the index is significantly faster than restructuring the database.

Hyperion Essbase restructures only the database index when you do either of the following:

However, Hyperion Essbase does restructure your database when you do any of the following:

Dynamically Calculating Data in Partitions

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 Hyperion 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, Hyperion 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, Hyperion Essbase does not retrieve the child blocks from the remote database, and therefore the impact on retrieval time is not great.