Process for Creating Attributes
Understanding Attributes
Understanding Attribute Dimensions
Designing Attribute Dimensions
Building Attribute Dimensions
Setting Member Names in Attribute Dimensions
Calculating Attribute Data
Varying Attributes
Some information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases.
Also see:
- Comparison of Aggregate and Block Storage
- Aggregate Storage Applications, Databases, and Outlines
For other information about using attributes, see:
- Building Attribute Dimensions and Associating Attributes
- Designing Partitioned Applications
- Creating and Maintaining Partitions
- Developing Report Scripts
Process for Creating Attributes
Attributes describe characteristics of
data such as product size and color. Through attributes, you can group
and analyze members of dimensions based on their characteristics. This
chapter describes how to create and manage attributes in an Essbase
Server outline.
When working with attributes in Outline Editor, perform the following tasks:
- Create a dimension.See Adding Dimensions
and Members to an Outline. In the outline, position the attribute
dimensions after all standard dimensions.
- Tag the dimension as an attribute
dimension and set attribute dimension type as text, numeric, Boolean, or
date.See Creating Attribute Dimensions.
- Add members to the attribute dimension.See Adding Dimensions and Members to an Outline.
- Associate a base dimension with the attribute dimension.See Understanding the Rules for Attribute Dimension Association.
- Associate members of the base dimension
with members of the attribute dimension.See Understanding the Rules for
Attribute Member Association.
- If necessary, set up the attribute calculations.See Calculating Attribute Data.
You can use the Essbase attribute feature
to retrieve and analyze data not only from the perspective of
dimensions, but also in terms of characteristics, or attributes, of
those dimensions. For example, you can analyze product profitability
based on size or packaging, and you can make more effective conclusions
by incorporating into the analysis market attributes such as the
population of each market region.
Such an analysis could tell you that
decaffeinated drinks sold in cans in small markets (populations less
than 6,000,000) are less profitable than you anticipated. For more
details, you can filter the analysis by specific attribute criteria,
including minimum or maximum sales and profits of different products in
similar market segments.
A few ways analysis by attribute provides depth and perspective, supporting better-informed decisions:
- You can select, aggregate, and report on data based on common features (attributes).
- By defining attributes as having a text,
numeric, Boolean, or date type, you can filter (select) data using
type-related functions such as AND, OR, and NOT operators and <,
>, and = comparisons.
- You can use the numeric attribute type
to group statistical values by attribute ranges; for example, population
groupings such as <500,000, 500,000–1,000,000, and >1,000,000.
- Through the Attribute Calculations
dimension automatically created by Essbase, you can view sums, counts,
minimum or maximum values, and average values of attribute data. For
example, when you enter Avg and Bottle into a spreadsheet, Essbase
retrieves calculated values for average sales in bottles for all the
column and row intersections on the sheet.
- You can perform calculations using
numeric attribute values in calculation scripts and member formulas; for
example, to determine profitability by ounce for products sized by the
ounce.
- You can create crosstabs of attribute
data for the same dimension, and you can pivot and drill down for detail
data in spreadsheets.An attribute crosstab
is a report or spreadsheet showing data consolidations across
attributes of the same dimension. The crosstab example below displays
product packaging as columns and the product size in ounces as rows. At
their intersections, you see the profit for each combination of package
type and size.
From this information, you can see which size-packaging combinations were most profitable in the Florida market.
Product Year Florida Profit Actual
Bottle Can Pkg Type
========= ========= =========
32 946 N/A 946
20 791 N/A 791
16 714 N/A 714
12 241 2,383 2,624
Ounces 2,692 2,383 5,075
Understanding Attribute Dimensions
In
the Sample.Basic database, products have attributes that are
characteristics of the products. For example, products have an attribute
that describes their packaging. In the outline, you see these
characteristics as two dimensions, the Products dimension, and the Pkg
Type attribute dimension that is associated with it. An attribute dimension has the word Attribute next to its name in the outline.
Figure 39, Outline Showing Base and Attribute Dimensions
shows part of the Sample.Basic outline featuring the Product dimension
and three attribute dimensions, Caffeinated, Ounces, and Pkg Type.
The name of the level 0 member of an attribute dimension is the attribute value. The only members of attribute dimensions that have attribute values are level 0 members.
You can use the higher-level members of
attribute dimensions to select and group data. For example, you can use
Small, the level 1 member of the Population attribute dimension, to
retrieve sales in both the 3000000 and 6000000 population categories.
Understanding Attribute Types
Attribute dimensions have a
text,
numeric, Boolean, or date type that enables different functions for
grouping, selecting, or calculating data. Although assigned at the
dimension level, the attribute type applies only to level 0 members of
the attribute dimension.
- The default attribute type is text.
Text attributes enable the basic attribute member selection and
attribute comparisons in calculations. When you perform such
comparisons, Essbase compares characters. For example, the package type
Bottle is less than the package type Can, because B precedes C in the
alphabet. In Sample.Basic, Pkg Type is an example of a text attribute
dimension.
- The names of level 0 members of numeric
attribute dimensions are numeric values. You can include the names
(values) of numeric attribute dimension members in calculations. For
example, you can use the number of ounces specified in the Ounces
attribute to calculate profit per ounce for each product.You can also associate numeric attributes with ranges
of base dimension values; for example, to analyze product sales by
market population groupings—states with 3,000,000 population or less in
one group, states with a population between 3,000,001 and 6 million in
another group, and so on. See Setting Up Member Names Representing
Ranges of Values.
- All Boolean
attribute dimensions in a database contain only two members. The member
names must match the settings for the database; for example, True and
False. If more than one Boolean attribute dimension exists, specify a
prefix or suffix member name format to ensure unique member names; for
example, Caffeinated_True and Caffeinated_False. For a discussion of how
to change Boolean names, see Setting Boolean Attribute Member Names.
- You can use date
attributes to specify the date format—month-day-year or
day-month-year—and to sequence information accordingly. For a discussion
of how to change date formats, see Changing the Member Names in Date
Attribute Dimensions. You can use date attributes in calculations. For
example, you can compare dates in a calculation that selects product
sales from markets established since 10-12-1999.Essbase supports date attributes from January 1, 1970, through January 1, 2038.
Comparing Attribute and Standard Dimensions
In general, attribute dimensions and
their members are similar to standard dimensions and their members. You
can provide aliases and member comments for attributes. Attribute
dimensions can include hierarchies, and you can name generations and
levels. You can perform the same spreadsheet operations on attribute
dimensions and members as on standard dimensions and members; for
example, to analyze data from different perspectives, you can retrieve,
pivot, and drill down in the spreadsheet.
Table 18 describes major differences between attribute and standard dimensions and their members.
Table 18. Differences Between Attribute and Standard Dimensions
| Functionality |
Attribute Dimensions |
Standard Dimensions |
|---|
| Storage |
Sparse. Their base dimensions also must be sparse. |
Can be dense or sparse |
| Storage property |
Dynamic Calc only, therefore not stored in the database. The outline does not display this property. |
Can be Store Data, Dynamic Calc and Store, Dynamic Calc, Never Share, or Label Only |
| Position in outline |
Must be the last dimensions in the outline |
Must be ahead of all attribute dimensions in the outline |
| Partitions |
Cannot be defined along attribute dimensions, but you can use attributes to define a partition on a base dimension. |
Can be defined along standard dimensions. |
| Formulas (on members) |
Cannot be associated |
Can be associated |
| Shared members |
Not allowed |
Allowed |
| Two-pass calculation member property |
Not available |
Available |
| Two-pass calculation with runtime formula |
If a member formula contains a
runtime-dependent function associated with an attribute member name, and
the member with the formula is tagged as two-pass, calculation skips
the member and issues a warning message. Runtime-dependent functions
include: @CURRMBR, @PARENT, @PARENTVAL, @SPARENTVAL, @MDPARENTVAL,
@ANCEST, @ANCESTVAL, @SANCESTVAL, and @MDANCESTVAL. |
Calculation is performed on standard members with runtime formulas and tagged two-pass. |
| Two-pass, multiple dimensions: Calculation order |
Order of calculation of members tagged two-pass depends on order in outline. The last dimension is calculated last. |
Calculation result is not dependent on outline order for members tagged two-pass in more than one dimension. |
| Two-pass calculation with no member formula |
Calculation skipped, warning message
issued. Therefore, member intersection of two-pass tagged members and
upper-level members may return different results from calculation on
standard dimensions. |
Available |
| Dense Dynamic Calc members in nonexisting stored blocks |
Calculations skip dense dimensions if
they are on nonexisting stored blocks. To identify nonexisting stored
blocks, export the database or run query to find out whether block has
data. |
Available |
| UDAs on members |
Not allowed |
Allowed |
| Consolidations |
For all members, calculated through the
Attribute Calculations dimension members: Sum, Count, Min, Max, and Avg.
Consolidation operators in the outline are ignored during attribute
calculations. |
Consolidation operation indicated by assigning the desired consolidation symbol to each member |
| Member selection facilitated by Level 0 member typing |
Available types include text, numeric, Boolean, and date. |
All members treated as text |
| Associations |
Must be associated with a base dimension |
N/A |
| Spreadsheet drill-downs |
List the base dimension data associated
with the selected attribute. For example, drilling down on the attribute
Glass displays sales for each product packaged in glass, where Product
is the base dimension for the Pkg Type attribute dimension. |
List lower or sibling levels of detail
in the standard dimensions. For example, drilling down on QTR1 displays a
list of products and their sales for that quarter. |
Comparing Attributes and UDAs
Attributes and UDAs enable analysis based
on characteristics of the data. Attributes provide greater capability
than UDAs.
The tables in this topic describe the differences between
attributes and UDAs in these areas of functionality:
- Data storage (Table 19, Data Storage—Comparing Attributes and UDAs)
- Data retrieval (Table 20, Data Retrieval—Comparing Attributes and UDAs)
- Data conversion (Table 21, Data Conversion—Comparing Attributes and UDAs)
- Calculation scripts (Table 22, Calculation Scripts—Comparing Attributes and UDAs)
Table 19. Data Storage—Comparing Attributes and UDAs
| Data storage |
Attributes |
UDAs |
| You can associate with sparse dimensions. |
Supported |
Supported |
| You can associate with dense dimensions. |
Not supported |
Supported |
Table 20. Data Retrieval—Comparing Attributes and UDAs
| Data Retrieval |
Attributes |
UDAs |
| You can group and retrieve consolidated
totals by attribute or UDA value. For example, associate the value High
Focus Item to various members of the Product dimension and use that term
to retrieve totals and details for only those members. |
Supported
Simple |
Supported
More difficult to implement, requiring additional calculation scripts or commands |
| You can categorize attributes in a
hierarchy and retrieve consolidated totals by higher levels in the
attribute hierarchy; for example, if each product has a size attribute
such as 8, 12, 16, or 32, and the sizes are categorized as small,
medium, and large. You can view the total sales of small products. |
Supported |
Supported
More difficult to implement |
| You can create crosstab views displaying aggregate totals of attributes associated with the same base dimension. |
Supported
You can show a crosstab of all values of each attribute dimension. |
Supported
You can retrieve only totals based on specific UDA values. |
| You can use Boolean operators AND, OR,
and NOT with attribute and UDA values to refine a query. For example,
you can select decaffeinated drinks from the 100 product group. |
Supported |
Supported |
| Because attributes have a text, Boolean,
date, or numeric type, you can use appropriate operators and functions
to work with and display attribute data. For example, you can view sales
totals of all products introduced after a specific date. |
Supported |
Not supported |
| You can group numeric attributes into
ranges of values and let the dimension building process automatically
associate the base member with the appropriate range. For example, you
can group sales in various regions based on ranges of their
populations—less than 3 million, between 3 million and 6 million, and so
on. |
Supported |
Not supported |
| Through the Attribute Calculations
dimension, you can view aggregations of attribute values as sums,
counts, minimums, maximums, and averages. |
Supported |
Not supported |
| You can use an attribute in a
calculation that defines a member. For example, you can use the weight
of a product in ounces to define the profit per ounce member of the
Measures dimension. |
Supported |
Not supported |
| You can retrieve specific base members using attribute-related information. |
Supported
Powerful conditional and value-based selections |
Supported
Limited to text string matches only |
Table 21. Data Conversion—Comparing Attributes and UDAs
| Data Conversion |
Attributes |
UDAs |
| Based on the value of a UDA, you can change the sign of the data as
it is loaded into the database. For example, you can reverse the sign of
all members with the UDA Debit. |
Not supported |
Supported |
Table 22. Calculation Scripts—Comparing Attributes and UDAs
| Calculation Scripts |
Attributes |
UDAs |
| You can perform calculations on a member if its attribute or UDA
value matches a specific value. For example, you can increase the price
by 10% of all products with the attribute or UDA of Bottle. |
Supported |
Supported |
| You can perform calculations on base members whose attribute value
satisfies conditions that you specify. For example, you can calculate
the Profit per Ounce of each base member. |
Supported |
Not supported |
Designing Attribute Dimensions
Essbase provides more than one way to
design attribute information into a database. Most often, defining
characteristics of the data through attribute dimensions and their
members is the best approach. The following sections discuss when to use
attribute dimensions, when to use other features, and how to optimize
performance when using attributes.
Using Attribute Dimensions
For the most flexibility and
functionality, use attribute dimensions to define attribute data. Using
attribute dimensions provides the following features:
- Sophisticated, flexible data retrievalYou
can view attribute data only when you want to; you can create
meaningful summaries through crosstabs; and, using type-based
comparisons, you can selectively view only the data that you want to
see.
- Additional calculation functionalityNot
only can you perform calculations on the names of members of attribute
dimensions to define members of standard dimensions, you can also access
five types of consolidations of attribute data—sums, counts, averages,
minimums, and maximums.
- Economy and simplicityBecause
attribute dimensions are sparse, Dynamic Calc, they are not stored as
data. Compared to using shared members, outlines using attribute
dimensions contain fewer members and are easier to read.
See Understanding Attributes.
Using Alternative Design Approaches
In some situations, consider one of the following approaches:
- UDAs.
Although UDAs provide less flexibility than attributes, you can use
them to group and retrieve data based on its characteristics. See
Comparing Attributes and UDAs.
- Shared
members. For example, to include a seasonal analysis in the Year
dimension, repeat the months as shared members under the appropriate
season; Winter: Jan (shared member), Feb (shared member), and so on. A
major disadvantage of using shared members is that the outline becomes
large if the categories repeat many members.
- Standard dimensions
and members. Additional standard dimensions provide flexibility but add
storage requirements and complexity to a database. For guidelines on
evaluating the impact of additional dimensions, see Analyzing and
Planning.
Table 23 describes situations in which you might consider an alternative approach to managing attribute data in a database.
Table 23. Considering Alternatives to Attribute Dimensions
| Situation |
Alternative |
| Analyze attributes of dense dimensions |
UDAs or shared members |
| Perform batch calculation of data |
Shared members or members of separate, standard dimensions |
| Define the name of a member of an attribute dimension as a value that results from a formula |
Shared members or members of separate, standard dimensions |
| Define attributes that vary over time |
Members of separate, standard
dimensions. For example, to track product maintenance costs over time,
the age of the product at the time of maintenance is important. However,
using the attribute feature, you could associate only one age with the
product. You need multiple members in a separate dimension for each time
period that you want to track. |
| Minimize retrieval time with large numbers of base-dimension members |
Batch calculation with shared members or members of separate, standard dimensions. |
Optimizing Outline Performance
Outline
layout
and content can affect attribute calculation and query performance. For
general outline design guidelines, see Designing an Outline to Optimize
Performance.
To optimize attribute query performance, consider the following design tips:
- Ensure that attribute dimensions are the only sparse Dynamic Calc dimensions in the outline.
- Locate sparse dimensions after dense
dimensions in the outline. Place the most-queried dimensions at the
beginning of the sparse dimensions and attribute dimensions at the end
of the outline. In most situations, base dimensions are queried most.
See Optimizing Calculation and Retrieval Performance.
Building Attribute Dimensions
To build an attribute dimension, tag the
dimension as an attribute and assign the dimension a type. Then
associate the attribute dimension with a base dimension. Finally,
associate each level 0 member of the attribute dimension with a member
of the associated base dimension.
To build an attribute dimension, see “Defining Attributes” in the Oracle Essbase Administration Services Online Help.
To view the dimension, attribute value, and attribute type of a specific attribute member, use a tool:
Setting Member Names in Attribute Dimensions
When you use the attribute feature,
Essbase establishes default member names; for example, the
system-defined True and False precludes other member names of True and
False. You can change these system-defined names for the database. Date
attributes and numeric attributes also can be duplicated. To avoid
duplicate name confusion, you can establish settings for qualifying
member names in attribute dimensions. The outline does not show the
fully qualified attribute names, but you can see the full attribute
names anywhere you select members, such as when you define partitions or
select information to be retrieved.
Define the member name settings before
you define or build the attribute dimensions. Changing the settings
after the attribute dimensions and members are defined could result in
invalid member names.
The following sections describe how to work with the names of members of attribute dimensions:
Note:
If you partition on outlines containing attribute dimensions, the
name format settings of members described in this section must be
identical in the source and target outlines.
Setting Prefix and Suffix Formats for Member Names of Attribute Dimensions
The information in this section does not apply to duplicate member attribute dimensions.
The
names of members of Boolean, date, and numeric attribute dimensions are
values. It is possible to encounter duplicate attribute values in
different attribute dimensions.
- Boolean
exampleIf you have more than one Boolean attribute dimension in an
outline, the two members of each of those dimensions have the same
names, by default, True and False.
- Date
exampleIf you have more than one date attribute dimension, some member
names in both dimensions could be the same. For example, the date on
which a store opens in a certain market could be the same as the date on
which a product was introduced.
- Numeric
exampleThe attribute value for the size of a product could be 12, and
12 also could be the value for the number of packing units for a
product. This example results in two members with the name 12.
You can define unique names by attaching a
prefix or suffix to member names in Boolean, date, and numeric
attribute dimensions in the outline. You can affix the dimension,
parent, grandparent, or all ancestors to the attribute name. For
example, by setting member names of attribute dimensions to include the
dimension name as the suffix, attached by an underscore, the member
value 12 in the Ounces attribute dimension assumes the unique, full
attribute member name 12_Ounces.
By default, Essbase assumes that no prefix or suffix is attached to the names of members of attribute dimensions.
The convention that you select applies to
the level 0 member names of all numeric, Boolean, and date attribute
dimensions in the outline. You can define aliases for these names if you
want to display shorter names in retrievals.
To define prefix and suffix formats, see “Defining a Prefix or Suffix Format for Members of Attribute Dimensions” in the
Oracle Essbase Administration Services Online Help.
Setting Boolean Attribute Member Names
When
you set the dimension type of an attribute dimension as Boolean,
Essbase automatically creates two level 0 members with the names
specified for the Boolean attribute settings. The initial Boolean member
names in a database are set as True and False. To change these default
names, for example, to Yes and No, define the member names for Boolean
attribute dimensions before you create Boolean attribute dimensions in
the database.
Before you can set an attribute dimension type as Boolean, you must delete all existing members in the dimension.
To define the database setting for the names of members of Boolean
attribute dimensions, see “Setting Member Names for Boolean Attribute
Dimensions” in the
Oracle Essbase Administration Services Online Help.
Changing the Member Names in Date Attribute Dimensions
You can change the format of members of date attribute dimensions.
For example, you can use the following date formats:
- mm-dd-yyyy: October 18, 2007 is displayed as 10-18-2007.
- dd-mm-yyyy: October 18, 2007 is displayed as 18-10-2007.
If you change the date member name
format, the names of existing members of date attribute dimensions may
be invalid. For example, if the 10-18-2007 member exists, and you change
the format to dd-mm-2007, outline verification will find this member
invalid. If you change the date format, you must rebuild the date
attribute dimensions.
To change member names in date attribute dimensions, see “Setting the Member Name Format of Date Attribute Dimensions” in the
Oracle Essbase Administration Services Online Help.
Setting Up Member Names Representing Ranges of Values
Members of numeric attribute dimensions can represent single numeric values or ranges of values:
- Single-value example: the member 12 in
the Ounces attribute dimension represents the single numeric value 12;
you associate this attribute with all 12-ounce products. The outline
includes a separate member for each size; for example, 16, 20, and 32.
- Range of values example: the Population attribute dimension, as shown in Figure 41, Population Attribute Dimension and Members:
- In
this outline, the members of the Population attribute dimension
represent ranges of population values in the associated Market
dimension. The 3000000 member represents populations from zero through
3,000,000; the 6000000 member represents populations from 3,000,001
through 6,000,000; and so on. A setting for the outline establishes that
each numeric member represents the top of its range.
You can also define this outline setting
so that members of numeric attribute dimensions are the bottoms of the
ranges that they represent. For example, if numeric members are set to
define the bottoms of the ranges, the 3000000 member represents
populations from 3,000,000 through 5,999,999, and the 6000000 member
represents populations from 6,000,000 through 8,999,999.
When you build the base dimension,
Essbase automatically associates members of the base dimension with the
appropriate attribute range. For example, if numeric members represent
the tops of ranges, Essbase automatically associates the Connecticut
market, with a population of 3,269,858, with the 6000000 member of the
Population attribute dimension.
In the dimension build rules file,
specify the size of the range for each member of the numeric attribute
dimension. In the above example, each attribute represents a range of
3,000,000.
To define ranges in numeric attribute dimensions, see “Assigning Member Names to Ranges of Values” in the
Oracle Essbase Administration Services Online Help.
Note:
Oracle recommends that numeric attribute dimension member names
contain no more than six decimal positions. Otherwise, because of
precision adjustments, an outline may not pass verification.
Changing the Member Names of the Attribute Calculations Dimension
To avoid
duplicating names in an outline, you may need to change the name of the
Attribute Calculations dimension or its members. See Understanding the
Attribute Calculations Dimension.
Regardless of the name that you use for a
member, its function remains the same. For example, the second (Count)
member always counts.
To change member names in the Attribute Calculations dimension, see
“Changing Member Names of Attribute Calculations Dimensions” in the
Oracle Essbase Administration Services Online Help.
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
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 the Spreadsheet
Add-in.
The attribute calculation dimension has the following properties:
- System-definedWhen
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 onlyLike 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:
- Finds the base-dimension members associated with the attribute-dimension members present in the current query
- Dynamically calculates the sum, count, minimum, maximum, or average for the attribute-member combination for the current query
- Displays the results in the spreadsheet or report
- 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 42,
Retrieving an Attribute Calculations Member, 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 42. Retrieving an Attribute Calculations Member
See Accessing Attribute Calculations Members Using the Spreadsheet.
Understanding the Default Attribute Calculations Members
The Attribute Calculations dimension contains five members 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. See the Oracle Essbase Technical Reference.
- 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. For a
discussion of Attribute Calculations member names, 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 24. 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 43, Sample Spreadsheet with Attribute Data
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 43. Sample Spreadsheet with Attribute Data
Accessing Attribute Calculations Members Using the Spreadsheet
You
can access members from the Attribute Calculations dimension in
Spreadsheet Add-in. 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 the Oracle Essbase Spreadsheet Add-in User’s Guide.
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 Spreadsheet Add-in,
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
Table 18, Differences Between Attribute and Standard Dimensions.
Table 25 lists functions you can use to perform specific calculations on attributes:
Table 25. Functions That Calculate On Attributes
| Type of Calculation |
Function to Use |
| 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. |
@ATTRIBUTE |
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). |
@ATTRIBUTEVAL
@ATTRIBUTEBVAL
@ATTRIBUTESVAL |
| 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. |
@TODATE |
| 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. |
@WITHATTR |
Note:
For syntax information and examples for these functions, see the
Oracle Essbase Technical Reference. For an additional example using @ATTRIBUTEVAL in a formula, see Calculating an Attribute Formula.
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 a an outline
hierarchy, the calculation results may not include the higher shared
member.
For example:
In this example, when an attribute
calculation is performed, the calculation starts with level 0 Member 2
and stops when it encounters the first stored member, Member A.
Therefore, Member 1 would not be included in the calculation.
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 be as expected.
A product typically has
attributes that describe or define the product. For example, a product
could have an attribute describing the size of the product in ounces and
an attribute describing the flavor of the product. In such a scenario,
Product would be a base dimension while Ounces and Flavor would be
attribute dimensions.
A varying attribute enables you to track two
values in relation to a third dimension called an independent dimension.
You could, for example, track your product in eight ounces over a year.
In this scenario, Time is the independent dimension. The value of this
third factor can vary (hence the name).
For example, you could track
your product over a year, a quarter, or a month.
Note:
There are two types of
independent dimensions: continuous and discrete. The members in a
continuous dimension reflect continuity. For example, week, month, and
quarter reflect the continuity in a time dimension. The members in a
discrete dimension do not imply continuity. For example, California,
Texas, and Ohio in a market dimension do not have a relationship based
on continuity.
As another example, consider this scenario:
The sales representative for a client changes in midyear. Customer sales
totals and sales representative assignments over six months are as
follows:
Table 26. Varying Attribute Example: Sales Representative Changing Over Time
| March |
April |
May |
June |
July |
August |
| 4000 |
6000 |
2000 |
1000 |
1000 |
7000 |
| Jones |
Jones |
Jones |
Smith |
Smith |
Smith |
In this example, Sales Representative is the varying attribute. Data
retrievals show that the sales representative Jones sold the customer a
total of $12,000 worth of products from March through May and the sales
representative Smith then sold a total of $9,000 worth of products to
the customer from June through August. Without the use of the varying
attribute, the only known sales representative would be the current
representative Smith to whom all sales ($21,000) would be credited.
Varying attributes offer alternate ways of grouping your members. For
example, you can use color to group SKUs. In this scenario, the
attribute dimension “Color” is associated with SUBSKU:
Product_H
|
|__Family
| |
| |__SKU
| |
| |__SUBSKU
|
|_Color
|
|__SUBSKU
When Color is set as a varying attribute, the retrieval results would be similar to the following table:
Table 27.
| SUBSKU |
SKU |
| Red |
100 |
| White |
400 |
| White |
600 |
| Black |
200 |
| Black |
300 |
| Silver |
500 |
Varying attributes must meet the following guidelines:
- They must have multiple chains.
- Leaf levels must match.
You can enable an outline to support varying attributes. You can
define attribute dimensions to function as varying attributes. You can
also edit varying attributes to reflect the type of information you
need.
Implementing Varying Attributes
Varying attributes are supported for aggregate and block storage
databases. You implement varying attributes at the database level.
Use the following workflow to enable and use varying attributes:
- In the outline properties, enable varying attributes.
- In the member properties for the base dimension, go to the
Attributes tab and identify the independent dimension (the dimension
upon which varying attributes depend).For example, if the sales
representative attribute association for Customer A gets changed in May,
then Year would be the independent dimension.
- Specify the type of independent dimension: continuous or discrete.
An example of a continuous independent dimension is one that is based on
time. A discrete independent dimension has no continuity; for example,
in a Market dimension, California, Texas, and Ohio do not have a
relationship based on continuity.
- Associate the independent dimension with a varying attribute. Optionally select a range and an association mode.A
range can be assigned for which the attribute association is true. For
example, you can assign a time range for which the attribute association
applies: Jane is an Engineer from July 2007–June 2008.
An association mode tells Essbase how to handle conflicting
associations of a varying attribute with its independent dimension. The
following association modes are available: Overwrite, NoOverwrite, and
Extend.
- Save and restructure the outline.
- Perform the following maintenance tasks as needed:
- Add new varying attribute associations to independent members (for example, add a new job title for an employee).
- Remove independent member associations.
- View existing independent dimension member associations (for
example, see for which Months the company had a replacement sales
manager).
- Disassociate attribute dimensions from base dimensions.
Functions Supporting Varying Attributes
The following Report Writer functions are designed to work with varying attributes.
- <PERSPECTIVE
- <WITHATTREX
- <ATTRIBUTEVA
For more information, see the
Oracle Essbase Technical Reference.
The following MDX functions are designed to work with varying attributes.
- AttributeEx
- WithAttrEx
- The WITH PERSPECTIVE keywords
For more information, see the
Oracle Essbase Technical Reference.
Limitations of Varying Attributes
Continuous independent dimensions must act as a single dimension, for
example Year and Month. Unconnected continuous independent dimensions
are not supported.
Continuous independent dimensions and members must be specified last.
Independent members must be stored, level 0 members.
