Calculating Attribute Data

Essbase calculates attribute data dynamically at retrieval time, using members from a system-defined dimension created by Essbase. Using this dimension, you can apply different calculation functions, such as a sum or an average, to the same attribute. You can also perform specific calculations on members of attribute dimensions; for example, to determine profitability by ounce for products sized by the ounce.

The following information assumes that you understand the concepts of attribute dimensions and Essbase calculations, including dynamic calculations. See the following sections.

• Understanding the Attribute Calculations Dimension

• Understanding the Default Attribute Calculations Members

• Viewing an Attribute Calculation Example

• Accessing Attribute Calculations Members in Smart View

• Optimizing Calculation and Retrieval Performance

• Using Attributes in Calculation Formulas

• Understanding Attribute Calculation and Shared Members

• Differences Between Calculating Attribute Members and Non-Attribute (Stored and Dynamic Calc) Members

Understanding the Attribute Calculations Dimension

When you create the first attribute dimension in the outline, Essbase also creates the Attribute Calculations dimension comprising five members with the default names Sum, Count, Min (minimum), Max (maximum), and Avg (average). You can use these members in spreadsheets or in reports to dynamically calculate and report on attribute data, such as the average yearly sales of 12-ounce bottles of cola in the West.

The Attribute Calculations dimension is not visible in the outline. You can see it wherever you select dimension members, such as in Smart View.

The attribute calculation dimension has the following properties:

• System-defined

  When you create the first attribute dimension in an application, Essbase creates the Attribute Calculations dimension and its members (Sum, Count, Min, Max, and Avg). Each member represents a type of calculation to be performed for attributes.

  See Understanding the Default Attribute Calculations Members.

• Label only

  Like all label only dimensions, the Attribute Calculations dimension shares the value of its first child, Sum.

  See Member Storage Properties.

• Dynamic Calc

  The data in the Attribute Calculations dimension is calculated when a user requests it and is then discarded. You cannot store calculated attribute data in a database.

  See Dynamically Calculating Data Values.

• Not displayed in Outline Editor

  The Attribute Calculations dimension is not displayed in Outline Editor. Members from this dimension can be viewed in spreadsheets and in reports.

There is no consolidation along attribute dimensions. You cannot tag members from attribute dimensions with consolidation symbols (for example, + or -) or with member formulas in order to calculate attribute data. As Dynamic Calc members, attribute calculations do not affect the batch calculation in terms of time or calculation order.

To calculate attribute data at retrieval time, Essbase performs the following tasks:

1. Finds the base-dimension members associated with the attribute-dimension members present in the current query

2. Dynamically calculates the sum, count, minimum, maximum, or average for the attribute-member combination for the current query

3. Displays the results in the spreadsheet or report

4. Discards the calculated values—that is, the values are not stored in the database

   Note:

   Essbase excludes #MISSING values when calculating attribute data.

For example, as shown in Figure 7-2, a spreadsheet user specifies two members of attribute dimensions (Ounces_16 and Bottle) and an Attribute Calculations member (Avg) in a spreadsheet report. Upon retrieval, Essbase dynamically calculates the average sales values of all products associated with these attributes for the current member combination (Actual -> Sales -> East -> Qtr1):

Figure 7-2 Retrieving an Attribute Calculations Member

See Accessing Attribute Calculations Members in Smart View.

Understanding the Default Attribute Calculations Members

The Attribute Calculations dimension contains five members (Sum, Count, Min, Max, and Avg) that are used to calculate and report attribute data:

• Sum—Calculates a sum, or total, of the values for a member with an attribute or combination of attributes.

• Count—Calculates the number of members with the specified attribute or combination of attributes, for which a data value exists. Count includes only those members that have data blocks in existence. To calculate a count of all members with certain attributes, regardless of whether they have data values, use the @COUNT function in combination with the @ATTRIBUTE function.

• Avg—Calculates a mathematical mean, or average, of the nonmissing values for an specified attribute or combination of attributes (Sum divided by Count).

• Min—Calculates the minimum data value for a specified attribute or combination of attributes.

• Max—Calculates the maximum data value for a specified attribute or combination of attributes.

  Note:

  Each of these calculations excludes #MISSING values.

You can change these default member names, subject to the same naming conventions as standard members. See Changing the Member Names of the Attribute Calculations Dimension.

Viewing an Attribute Calculation Example

As an example of how Essbase calculates attribute data, consider the following yearly sales data for the East:

Table 7-7 Sample Attribute Data

Base-Dimension Member	Associated Attributes	Sales Value for Attribute-Member Combination
Cola	Ounces_12, Can	23205
Diet Cola	Ounces_12, Can	3068
Diet Cream	Ounces_12, Can	1074
Grape	Ounces_32, Bottle	6398
Orange	Ounces_32, Bottle	3183
Strawberry	Ounces_32, Bottle	5664

Figure 7-3 shows how calculated attribute data might look in a spreadsheet report. You can retrieve multiple Attribute Calculations members for attributes. For example, you can calculate Sum, Count, Avg, Min, and Max for bottles and cans.

Figure 7-3 Sample Spreadsheet with Attribute Data

Accessing Attribute Calculations Members in Smart View

You can access members from the Attribute Calculations dimension in Smart View. From the spreadsheet, users can view Attribute Calculations dimension members using any of the following methods:

• Entering members directly into a sheet

• Selecting members from the Query Designer

• Entering members as an EssCell parameter

See Working with Oracle Smart View for Office.

Optimizing Calculation and Retrieval Performance

To optimize attribute calculation and retrieval performance, consider the following:

• The calculation order for attribute calculations is the same as for dynamic calculations. For an outline, see Calculation Order for Dynamic Calculation.

• Because Essbase calculates attribute data dynamically at retrieval time, attribute calculations do not affect the performance of the overall (batch) database calculation.

• Tagging base-dimension members as Dynamic Calc may increase retrieval time.

• When a query includes the Sum member and an attribute-dimension member whose associated base member is tagged as two-pass, retrieval time may be slow.

• To maximize attribute retrieval performance, use any of the following techniques:

  • Configure the outline using the tips in Optimizing Outline Performance.

  • Drill down to the lowest level of base dimensions before retrieving data. For example, in Smart View, turn on the Navigate Without Data feature, drill down to the lowest level of the base dimensions included in the report, and then retrieve data.

  • When the members of a base dimension are associated with several attribute dimensions, consider grouping the members of the base dimension according to their attributes. For example, in the Sample.Basic database, you can group all 8-ounce products.

Using Attributes in Calculation Formulas

In addition to using the Attribute Calculations dimension to calculate attribute data, you can use calculation formulas on members of standard or base dimensions to perform specific calculations on members of attribute dimensions; for example, to determine profitability by ounce for products sized by the ounce.

You cannot associate formulas with members of attribute dimensions.

Note:

Some restrictions apply when using attributes in formulas associated with two-pass members. See the rows about two-pass calculations in Understanding Two-Pass Calculations on Attribute Dimensions.

You can use the following functions to perform specific calculations on attributes:

Table 7-8 Functions That Calculate On Attributes

Function	Type of Calculation
@ATTRIBUTE	Generate a list of all base members with a specific attribute. For example, generate a list of members that have the Bottle attribute, and then increase the price for those members.
@ATTRIBUTEVAL @ATTRIBUTEBVAL @ATTRIBUTESVAL	Return the value of the level 0 attribute member that is associated with the base member being calculated. From a numeric or date attribute dimension (using @ATTRIBUTEVAL) From a Boolean attribute dimension (using @ATTRIBUTEBVAL) From a text attribute dimension (using @ATTRIBUTESVAL) For example, return the numeric value of a size attribute (for example, 12 for the member 12 under Ounces) for the base member being calculated (for example, Cola). For an additional example using @ATTRIBUTEVAL in a formula, see Calculating an Attribute Formula.
@TODATE	Convert a date string to numbers for a calculation. For example, use @TODATE in combination with the @ATTRIBUTEVAL function to increase overhead costs for stores opened after a certain date.
@WITHATTR	Generate a list of base dimension members associated with attributes that satisfy the conditions that you specify. For example, generate a list of products that are greater than or equal to 20 ounces, and then increase the price for those products.

Understanding Attribute Calculation and Shared Members

Attribute calculations start at level 0 and stop at the first stored member. Therefore, if your outline has placed a stored member between two shared members in an outline hierarchy, the calculation results may not include the higher shared member.

In the following example, when an attribute calculation is performed, the calculation starts with level 0 Member 2 and stops when it encounters the first stored member, which is Member A. Therefore, Member 1 would not be included in the calculation.

Member 1 (stored)
   Member A (stored)
     Member 2 (shared)
Member B (stored)
   Member 1 (shared member whose prototype member is Member 1 above)

To avoid unexpected results with attribute calculation, avoid mixing shared and stored members. For this example, if Member 2 were not shared, or Member 1 did not have a corresponding shared member elsewhere in the outline, calculation results would not be as expected.

Differences Between Calculating Attribute Members and Non-Attribute (Stored and Dynamic Calc) Members

The calculation of attribute dimension members is designed to work differently than the calculation of stored or Dynamic Calc members in standard dimensions.

The basis of this difference:

• Members in standard dimensions: The value of a parent member is based on aggregating the values of the parent member's child members, whether the child members are level 0 or upper-level members. All child member values contribute to the value of the parent member.

• Members in attribute dimensions: For each parent member for which an attribute aggregation is requested, the parent member's descendant list is expanded to include the dependent level 0 data blocks that need to be aggregated to calculate the value of the parent member.

Given these differences, the result of aggregating attribute dimension members might differ from the result of aggregating standard dimension members, if shared members are involved and there are multiple aggregation paths.

To workaround this issue, remove duplicate shared members under the aggregating attribute hierarchy or remodel the outline so that there are not multiple aggregation paths.