Dynamically Calculating Data Values Skip Navigation
Essbase® Analytic Services Database Administrator's Guide | Update Contents | Previous | Next | Print | ? |
Information Map

Dynamically Calculating Data Values


This chapter explains how you calculate data values dynamically in block storage databases 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. This chapter is not relevant to aggregate storage databases.

The information in this chapter assumes that you are familiar with the concepts of member combinations, dense and sparse dimensions, and data blocks. For a comprehensive discussion of these concepts, see Understanding Multidimensional Databases.

This chapter includes the following sections:

Understanding Dynamic Calculation

When you design the overall database calculation, it may be more efficient to calculate some member combinations when you retrieve their data, instead of pre-calculating the member combinations during a batch database calculation.

In Analytic Services, you can define a member to have a dynamic calculation. This definition tells Analytic Services 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 information on how to analyze and manage the effect of dynamic calculation.

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

Use the rest of this section to gain a basic understanding of dynamic calculation:

Understanding Dynamic Calc Members

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

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

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

Understanding Dynamic Calc and Store Members

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

Recalculation of Data

When Analytic Services 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 Analytic Services knows to recalculate the block on the next retrieval of the data value.

Analytic Services places the indicator on the data block containing the value and not on the data value itself. In other words, Analytic Services tracks Dynamic Calc and Store members at the data block level. For detailed information on data blocks, see Data Blocks and the Index System.

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

Analytic Services recalculates the indicated data blocks when you next retrieve the data value.

Effect of Updated Values on Recalculation

Analytic Services 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, Analytic Services 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 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. For more information on the CLEARBLOCK command, see the Technical Reference.

Retrieving the Parent Value of Dynamically Calculated Child Values

If you retrieve a parent value that is calculated from Dynamic Calc or Dynamic Calc and Store child members, Analytic Services must dynamically calculate the child member combinations before calculating the parent value. Analytic Services 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, Analytic Services calculates East and West, even though you have not specifically retrieved them. However, Analytic Services does not store the values of East or West.

Benefitting from Dynamic Calculation

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

Calculating some data values dynamically, achieves the following advantages:

Data values that Analytic Services calculates dynamically can take longer to retrieve. You can estimate the retrieval time for dynamically calculated members. For information on how to analyze and manage the effect of dynamic calculation, see Reducing the Impact on Retrieval Time.

Using Dynamic Calculation

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

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. For guidelines on choosing which members to calculate dynamically, see Choosing Values to Calculate Dynamically.

Outline Editor shows which members are Dynamic Calc and which members are Dynamic Calc and Store.

Figure 189: Sample Basic Outline Showing Dynamic Calc Members

In Spreadsheet Add-in or Spreadsheet Services, users can display visual cues to distinguish dynamically calculated values. For more information, see the Spreadsheet online help.

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

Choosing Values to Calculate Dynamically

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:

Dense Members and Dynamic Calculation

Consider making the following changes to members of dense dimensions:

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

Sparse Members and Dynamic Calculation

Consider making the following changes to members of sparse dimensions:

For recommendations in regard to use of Dynamic Calc and Dynamic Calc And Store, see Choosing Between Dynamic Calc and Dynamic Calc and Store.

Two-Pass Members and Dynamic Calculation

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 a comprehensive discussion of two-pass calculation, see Using Two-Pass Calculation. For details about the interaction of members tagged as two-pass and attribute members, see Comparing Attribute and Standard Dimensions.

Parent-Child Relationships and Dynamic Calculation

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.

Calculation Scripts and Dynamic Calculation

When Analytic Services 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, Analytic Services 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. 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 
 

Formulas and Dynamically Calculated Members

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

Qtr1 = Jan + Feb; 
 

You cannot make a dynamically calculated member the target of a formula calculation in a calculation script; Analytic Services 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, Analytic Services 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 neither Dynamic Calc nor Dynamic Calc and Store, you can use the following formula in a calculation script:

Year = Qtr1 + Qtr2; 
 

This formula is valid because Analytic Services 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, Analytic Services interrupts the regular calculation to do the dynamic calculation. This interruption can significantly lower calculation performance.

Dynamically Calculated Children

If the calculation of a member depends on the calculation of Dynamic Calc or Dynamic Calc and Store child members, Analytic Services has to calculate the child members first during the batch 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 190: 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, and its parent, Year, is not a dynamic member. When Analytic Services 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 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 Analytic Services calculates data values for a member combination that includes a Dynamic Calc member, Analytic Services calculates only the requested values of the relevant data block. These values can be a subset of the data block.

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

Analytic Services stores only the data blocks that contain the requested data values. If Analytic Services 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, Analytic Services calculates and stores the Market -> Cola data block. In order to calculate and store Market -> Cola, Analytic Services calculates the intermediate data blocks-East -> Cola, West -> Cola, South -> Cola, and Central -> Cola. Analytic Services 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 191: Sample Basic Outline, Market is Dynamic Calc and Store Member

Recommendations for Dense Dimension Members

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

Recommendations for Data with Many Concurrent Users

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

Understanding How Dynamic Calculation Changes Calculation Order

Using dynamically calculated data values changes the order in which Analytic Services calculates the values and can have implications for the way you administer a database:

Calculation Order for Dynamic Calculation

When Analytic Services dynamically calculates data values, it calculates the data in an order that is different from the batch database calculation order. For detailed information on calculation order when dynamic calculation is not used, see Defining Calculation Order.

During batch calculations, Analytic Services calculates the database in this order:

  1. Dimension tagged as accounts

  2. Dimension tagged as time

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

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

  5. Two-pass calculations

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

  1. Sparse dimensions

  2. Dense dimensions

    1. Dimension tagged as accounts, if dense

    2. Dimension tagged as time, if dense

    3. Time series calculations

    4. Remaining dense dimensions

    5. Two-pass calculations

    6. Attributes

      Note: 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 Analytic Services 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 non-use of attribute members generates different results for any upper level time members that are dynamically calculated.

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

Calculation Order for Dynamically Calculating Two-Pass Members

Consider the following information to ensure that Analytic Services produces the required calculation result when it dynamically calculates data values for members that are tagged as two-pass. For a comprehensive discussion of 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, Analytic Services performs the dynamic calculation in the first pass, and then calculates the two-pass members in the following order:

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

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

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

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

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

If Scenario is a sparse dimension, Analytic Services calculates Variance first, following the regular calculation order for dynamic calculations. For a description of calculation order for dynamic calculations, see Calculation Order for Dynamic Calculation. Analytic Services then calculates Margin%.

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

Calculation Order for Asymmetric Data

Because the calculation order used for dynamic calculations differs from the calculation order used for batch database calculations, in some database outlines you may get different calculation results if you tag certain members as Dynamic Calc or Dynamic Calc and Store. These differences happen when Analytic Services 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 25: 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, add the values for Jan, Feb, and Mar:

50+100+150=300  
 

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

600-300=300  
 


Table 26: 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, 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 
 

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

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

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

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

If East and Sales are Dynamic Calc members, Analytic Services produces an incorrect result by calculating as follows:

  1. The sparse East dimension, by aggregating the values for UnitsSold, Price, and Sales for New York, Florida, and Connecticut to obtain the values for East.

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

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

Reducing the Impact on Retrieval Time

The increase in retrieval time when you dynamically calculate a member of a dense dimension is not significant unless the member contains a complex formula. The increase in retrieval time may be significant when you tag members of sparse dimensions as Dynamic Calc or Dynamic Calc and Store.

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

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

Displaying a Retrieval Factor

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

Analytic Services displays the retrieval factor value in the application log.

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

A message similar to this sample indicates a retrieval factor:

[Wed Sep 20 20:04:13 2000] Local/Sample///Info (1012710)
Essbase needs to retrieve [1] Essbase kernel blocks in order
to calculate the top dynamically-calculated block. 
 

This message tells you that Analytic Services 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, Analytic Services provides a summary of how many members are tagged as Dynamic Calc and Dynamic Calc and Store. Analytic Services displays the summary in the application log.

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

A message similar to this sample is displayed:

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

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

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

This example does not include Dynamic Calc and Store members.

Increasing Retrieval Buffer Size

When you retrieve data into Spreadsheet Services or use Report Writer to retrieve data, Analytic Services uses the retrieval buffer to optimize the retrieval. Analytic Services processes the data in sections. Increasing the retrieval buffer size can significantly reduce retrieval time because Analytic Services can process larger sections of data at one time.

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

Use any of the following methods to set the retrieval buffer size:


Tool
Topic
Location

Administration Services

Setting the Size of Retrieval Buffers

Essbase Administration Services Online Help

MaxL

alter database

Technical Reference

ESSCMD

SETDBSTATEITEM

Technical Reference



Using Dynamic Calculator Caches

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

Using the Analytic Services dynamic calculator cache enables centralized control of memory usage for dynamic calculations. Managing data blocks in the dynamic calculator cache also reduces the overall memory space requirement and can improve performance by reducing the number of calls to the operating system to do memory allocations.

Note: The dynamic calculator cache and the calculator cache use different approaches to optimizing calculation performance.

For details about sizing and reviewing dynamic calculator cache usage, see Sizing the Calculator Cache.

Reviewing Dynamic Calculator Cache Usage

Analytic Services writes two messages to the application log for each data retrieval. As shown in the example in Figure 192, the first message describes the total amount of time required for the retrieval.

Figure 192: Application Log Example of Memory Usage for Data Blocks Containing Dynamic Calc Members

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

[Thu Aug 03 14:33:00 2000]Local/Sample/Basic/aspen/Info(1001401)
Regular Extractor Big Blocks Allocs -- Dyn.Calc.Cache : [30] non-Dyn.Calc.Cache : [0] 
 

If a dynamic calculator cache is used, a second message displays the number of blocks calculated within the data calculator cache (Dyn.Calc.Cache: [n]) and the number of blocks calculated in memory outside dynamic calculator cache (non-Dyn.Calc.Cache: [n]).

To determine if the dynamic calculator cache is being used effectively, review both of these messages and consider what your settings are in the essbase.cfg file. For example, if the message indicates that blocks were calculated outside 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 memory outside the calculator cache to complete dynamically calculated retrievals results in unacceptable delays (for example, because of swapping or paging activity), set DYNCALCCACHEWAITFORBLK to TRUE.

You can use the GETPERFSTATS command in ESSCMD to view a summary of dynamic calculator cache activity. See the Technical Reference for information about GETPERFSTATS.

Using Dynamic Calculations with Standard Procedures

Using dynamic calculations with standard Analytic Services procedures affects these processes:

Creating Dynamic Calc and Dynamic Calc and Store Members

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

To create Dynamic Calc and Dynamic Calc and Store members during a dimension build, in the dimension build data file, use the property X for Dynamic Calc and the property V for Dynamic Calc and Store. For information on how to place X and V in the data source, see Using the Data Source to Set Member Properties.

Restructuring Databases

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

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

In the following cases, Analytic Services does not restructure the database. Analytic Services only has to save the database outline, which is very fast. Analytic Services does not restructure the database or change the index when you do any of the following:

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

Analytic Services restructures only the database index when you do either of the following:

However, Analytic Services does restructure the database when you do any of the following:

For detailed information on the types of database restructuring, see Types of Database Restructuring.

Dynamically Calculating Data in Partitions

You can define Dynamic Calc and Dynamic Calc and Store members in transparent, replicated, or linked regions of the partitions. For a comprehensive discussion of 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 Analytic Services needs to retrieve the 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, Analytic Services 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, Analytic Services does not retrieve the child blocks from the remote database, and therefore the impact on retrieval time is not great.

Note: When Analytic Services 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, Analytic Services replicates the data in the data block. However, for dynamically calculated data, data blocks and time stamps do not exist. Therefore Analytic Services always replicates dynamically calculated data.



Hyperion Solutions Corporation link