Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
Attributes describe characteristics of data such as the size and color of products. 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 Analytic Server outline.
Note: The information in this chapter is designed for block storage databases. Some of the information is not relevant to aggregate storage databases. For detailed information on the differences between aggregate and block storage, see Comparison of Aggregate and Block Storage. For information on creating aggregate storage applications, see Aggregate Storage Applications, Databases, and Outlines.
This chapter contains the following topics:
You can find other information about attributes in relevant sections of this book.
Information Needed |
More Information |
---|---|
When working with attributes in Outline Editor perform the following tasks:
You can use the Analytic Services 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 size of each market region.
Such an analysis could tell you that decaffeinated drinks sold in cans in small (less than 6,000,000-population) markets 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.
Here are a few ways analysis by attribute provides depth and perspective, supporting better-informed decisions:
An attribute crosstab is a report or spreadsheet showing data consolidations across attributes of the same dimension. For example, the crosstab in Figure 59 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 |
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 60 shows part of the Sample Basic outline featuring the Product dimension and three attribute dimensions, Caffeinated, Ounces, and Pkg Type.
Figure 60: Outline Showing Base and Attribute Dimensions
In the outline, to the right of the Product dimension, the terms Caffeinated, Ounces, and Pkg Type show that these attribute dimensions are associated with the Product dimension.
A standard dimension is any dimension that is not an attribute dimension. When an attribute dimension is associated with a standard dimension, the standard dimension is the base dimension for that attribute dimension. In the outline in Figure 60, the Product dimension is the base dimension for the Caffeinated, Ounces, and Pkg Type attribute dimensions.
Note: Attribute dimensions and members are Dynamic Calc, so Analytic Services calculates attribute information at retrieval time. Attribute data is not stored in the database.
Members of an attribute dimension are potential attributes of the members of the associated base dimension. After you associate a base dimension with an attribute dimension, you associate members of the base dimension with members of the associated attribute dimension. The Market dimension member Connecticut is associated with the 6000000 member of the Population attribute dimension. That makes 6000000 an attribute of Connecticut.
In the outline, the information next to a base dimension member shows the attributes of that member. In Figure 60, next to product "100-10, Caffeinated:True, Ounces:12, Pkg Type:Can" shows that product 100-10 has three attributes-product 100-10 has caffeine, it is sold in 12-ounce containers, and the containers are cans.
There are several important rules regarding members of attribute dimensions and their base dimensions.
For example, product 100-10 can have size and packaging attributes, but only one size and only one type of packaging.
You can use attribute values in calculations in the following comparisons:
When you associate an attribute dimension with a standard dimension, the standard dimension is known as the base dimension for that attribute dimension.
For example, you might have a Size attribute dimension with members Small, Medium, and Large. If you associate the Size attribute dimension with the Product dimension, you cannot also associate the Size attribute dimension with the Market dimension. If you also want to track size-related information for the Market dimension, you must create another attribute dimension with a different name, for example, MarketSize, and associate the MarketSize attribute dimension with the Market dimension.
When you associate a member of an attribute dimension with a member of a base dimension, follow these rules:
For example, in Figure 61, all Market dimension members that have Population attributes are at level 0. You cannot associate East, which is a level 1 member, with a Population attribute since the other members of the Market dimension that have Population attributes are level 0 members.
Figure 61: Association of Attributes with the Same Level Members of the Market Dimension
For example, in the Population attribute dimension, you can associate only level 0 members such as 3000000, 6000000, and 9000000, with members of the Market dimension. You cannot associate a level 1 member such as Small.
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.
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.
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.
Analytic Services supports date attributes from January 1, 1970 through January 1, 2038.
In general, attribute dimensions and their members are similar to standard dimensions and 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 you can on standard dimensions and members; for example, to analyze data from different perspectives, you can retrieve, pivot, and drill down in the spreadsheet.
Table 12 describes major differences between attribute and standard dimensions and their members.
Attributes and UDAs both enable analysis based on characteristics of the data. Attributes provide much more capability than UDAs. Table 13 compares them. Checkmarks indicate the feature supports the corresponding capability.
Analytic Services 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.
For the most flexibility and functionality, use attribute dimensions to define attribute data. Using attribute dimensions provides the following features:
You 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 just the data you want to see.
Not only can you perform calculations on the names of members of attribute dimensions to define members of standard dimensions, you can also access five different types of consolidations of attribute data-sums, counts, averages, minimums, and maximums.
Because 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.
For more information about attribute features, see Understanding Attributes.
In some situations, consider one of the following approaches:
Table 14 describes situations where you might consider one of these alternative approaches for managing attribute data in a database.
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:
For information on optimizing calculation of outlines containing attributes, see Optimizing Calculation and Retrieval Performance.
To build an attribute dimension, first tag the dimension as 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 Essbase Administration Services Online Help.
To view the dimension, attribute value and attribute type of a specific attribute member, use any of the following methods:
Tool |
Topic |
Location |
---|---|---|
All member names in an outline must be unique. When you use the attribute feature, Analytic Services establishes some default member names. These default names might duplicate names that already exist in the outline. You can change these system-defined names for the database and can establish other settings for members of attribute dimensions in the database. The outline does not show the full attribute names. You can see and use 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.
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.
Because Analytic Services does not allow duplicate member names, you can define unique names by attaching a prefix or suffix to member names in Boolean, date, and numeric attribute dimensions in the outline. 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, Analytic Services 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 wish 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 Essbase Administration Services Online Help.
When you set the dimension type of an attribute dimension as Boolean, Analytic Services 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. If you want to change these default names, for example, to Yes and No, you must define the member names for Boolean attribute dimensions before you create any 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 Essbase Administration Services Online Help.
You can change the format of members of date attribute dimensions. For example, you can use the following date formats:
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-1999 member exists and you change the format to dd-mm-yyyy, outline verification will find this member invalid. If you change the date format, you must rebuild the date attribute dimensions.
To change the names of the members in date attribute dimensions, see "Setting the Member Name Format of Date Attribute Dimensions" in the Essbase Administration Services Online Help.
Members of numeric attribute dimensions can represent single numeric values or ranges of values:
Figure 62: 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. Each range includes values greater than the name of the preceding member up to and including the member value itself. 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, Analytic Services automatically associates members of the base dimension with the appropriate attribute range. For example, if numeric members represent the tops of ranges, Analytic Services 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 set up ranges in numeric attribute dimensions, see "Assigning Member Names to Ranges of Values" in the Essbase Administration Services Online Help.
To avoid duplicating names in an outline, you may need to change the name of the Attribute Calculations dimension or its members. For more information about this dimension, 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, no matter what you name it.
To change the names of the members in the Attribute Calculations dimension, see "Changing Member Names of Attribute Calculations Dimensions" in the Essbase Administration Services Online Help.
Analytic Services calculates attribute data dynamically at retrieval time, using members from a system-defined dimension created specifically by Analytic Services. 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 Analytic Services calculations, including dynamic calculations.
This section includes the following sections:
When you create the first attribute dimension in the outline, Analytic Services 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:
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, Analytic Services performs the following tasks:
Note: Analytic Services excludes #MISSING values when calculating attribute data.
For example, as shown in Figure 63, 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, Analytic Services dynamically calculates the average sales values of all products associated with these attributes for the current member combination (Actual -> Sales -> East -> Qtr1):
Figure 63: Retrieving an Attribute Calculations Member
For information on accessing calculated attribute data, see Accessing Attribute Calculations Members Using the Spreadsheet.
The Attribute Calculations dimension contains five members used to calculate and report attribute data. These members are as follows:
Note: The Sum member totals members based on their consolidation property or formula. For example, the Sum member uses the following formula to consolidate the profit percentages of 12-ounce products:
This calculation is not the sum of all percentages for all base-dimension members with the Ounces attribute 12.
The default calculation for attributes is Sum. If a spreadsheet user specifies a member of an attribute dimension in a spreadsheet but does not specify a member of the Attribute Calculations dimension, Analytic Services retrieves the sum for the specified attribute or combination of attributes. For example, in the spreadsheet view in Figure 64, the value in cell C4 represents the sum of sales values for the attributes Ounces_16 and Bottle for the current member combination (Actual -> Sales -> East -> Qtr1), even though the Sum member is not displayed in the sheet.
Figure 64: Retrieving the Default Attribute Calculations Member
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.
As an example of how Analytic Services calculates attribute data, consider the following yearly sales data for the East:
Base-Dimension Member |
Associated Attributes |
Sales Value for Attribute-Member Combination |
---|---|---|
A spreadsheet report showing calculated attribute data might look like the following illustration:
Figure 65: Sample Spreadsheet with Attribute Data
As shown in the figure above, you can retrieve multiple Attribute Calculations members for attributes. For example, you can calculate Sum, Count, Avg, Min, and Max for 32-ounce bottles and cans.
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:
For more information on accessing calculated attribute data from the spreadsheet, see the Essbase Spreadsheet Add-in User's Guide.
To optimize attribute calculation and retrieval performance, consider the following considerations:
In addition to using the Attribute Calculations dimension to calculate attribute data, you can also 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. For details, see the rows about two-pass calculations in Table 12.
You can use the following functions to perform specific calculations on attributes:
Note: For syntax information and examples for these functions, see the Technical Reference. For an additional example using @ATTRIBUTEVAL in a formula, see Calculating an Attribute Formula.
Attribute calculations start at level 0 and stop at the first stored member. Therefore, if your outline has placed a real member in between two shared members in a an outline hierarchy, the calculation results may not include the higher shared member.
Member 1 (stored) Member A (stored) Member 2 (shared) Member B (stored) Member 1 (shared member whose stored member is Member 1 above)
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.
Avoid mixing shared and stored members to avoid unexpected results with attribute calculation. 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.
![]() |