You select from several storage and calculation options for dimension members to optimize performance and disk usage. The following table describes these options:
StoreData Stores data values of members. This is the default value for all members.
DynamicCalcAndStore Calculates the data value the first time the member is retrieved and then saves the value.
DynamicCalc Calculates the data values each time the member is retrieved. The value is not stored in the database.
Share Data Allows two or more members in the same dimension to share data values.
Copyright © 2007, Oracle. All rights reserved.
Selecting Data Storage Options
Use storage options to optimize performance and disk usage.
Storage options
Lesson 3 Creating Dimensions Overview
DynamicCalcAndStore
With DynamicCalcAndStore, on the first retrieval, the data value is calculated and then stored. Subsequent retrievals use the stored value. The DynamicCalcAndStore option is used infrequently. In most cases, you can optimize your calculation and lower disk usage by using DynamicCalc when you calculate members of sparse dimensions. Use
DynamicCalcAndStore for members of sparse dimensions in the following situations:
• A sparse member with a complex formula
• A sparse member that users retrieve frequently
For members of dense dimensions, use Dynamic Calc for calculations. Using Dynamic Calc and Store increases retrieval time and regular calculation time. In addition, disk usage is not lowered significantly.
DynamicCalc
If a member is set to DynamicCalc, Essbase does not calculate its data value during regular database calculation; for example, when the CALC ALL function is executed, Essbase calculates the data value upon retrieval; a specific example of this is data being calculated when it is retrieved into a data form.
Advantages of DynamicCalc:
• Reduction in the regular calculation time of the database because Essbase has fewer member combinations to calculate.
• Reduction in disk usage because Essbase stores fewer data values. Database size and index size are reduced.
DynamicCalc is most useful for parent-level dense dimension members. Since values for DynamicCalc members are not stored, the data block size is reduced, which improves performance. However, retrieval time for the member is increased.
DynamicCalc should be used sparingly with sparse dimension members. Because each member of a sparse dimension references a separate data block, a dynamic calculation that includes many sparse members must retrieve data from a large number of data blocks. Retrieving data from many data blocks can reduce performance. As a rule of thumb, use DynamicCalc for upper-level members of sparse dimensions only if those upper-level members have six or fewer children.
NeverShare Prohibits two or more members in the same dimension from sharing a data value.
LabelOnly Has no data associated with the member.
Oracle Internal & Oracle Academy
Use Only
Module 2 Creating Application Structures
Lesson 3 Creating Dimensions Overview
Planning: Create and Manage Applications 3-19
DynamicCalc guidelines:
• Do not use DynamicCalc for base-level members for which users enter data. Data values are not saved for DynamicCalc members.
• Do not use DynamicCalc for a parent member if you enter data for that member in a target version. Parent members set to DynamicCalc are read-only in target versions.
• Do not set a parent member to Stored if its children are set to DynamicCalc. With this combination, when a user saves and refreshes a data form, the new total for the parent is not calculated.
ShareData and NeverShare
Use ShareData when you want to allow for alternate rollup structures. You can set members within the same dimension to share data values, enabling alternate rollup structures within the application. For shared members, you must set the data storage to Share Data so that the shared member is added when the Application View is deployed.
Use NeverShare when a parent has a single child that aggregates to the parent. In this case, set the parent to NeverShare. You can set appropriate security to the child
member. Typically, you set the currency, entity, version, and any custom dimensions to NeverShare. Members within the currency, entity, version, and any custom dimensions are not set to NeverShare, and typically are set to either StoreData or DynamicCalc.
LabelOnly Data Storage
LabelOnly members are virtual members; they are typically used for navigation and have no associated data. For example, you might have a parent member named Statistical that groups statistical accounts such as Headcount and Interest Rate. Because these accounts do not aggregate to a meaningful total, there is no need to calculate or store a value for the member Statistical.
Making a dimension member LabelOnly helps minimize database space by decreasing block size, which improves performance. LabelOnly members can also be used to group members or to navigate and report more easily from Financial Reporting or Essbase Smart View Provider.
LabelOnly guidelines:
• You cannot designate level 0 members as LabelOnly.
• Even though a Label Only member has no data associated with it, it can still display a value. A parent member set to LabelOnly displays the value of its first child member.
Oracle Internal & Oracle Academy
Use Only
Lesson 3 Creating Dimensions Overview
• In single-currency applications, LabelOnly is the default data storage type for all root dimensions. In multicurrency applications, LabelOnly is the default data storage type for HSP_Rates, Account, Period, Year, and Scenario.
• You cannot assign attributes to LabelOnly members.
• In multicurrency applications, you cannot apply the LabelOnly storage option to members of the following dimensions: Entity, Version, Currency, and user-defined dimensions. If you want to store exchange rates, you must set them to NeverShare.
• The data storage option for children of LabelOnly parents is set to StoreData by default.
Oracle Internal & Oracle Academy
Use Only
Module 2 Creating Application Structures
Lesson 3 Creating Dimensions Overview
Planning: Create and Manage Applications 3-21