In This Section:
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 Essbase Server outline.
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.
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 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.
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.
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.
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 37, 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.
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 37, Outline Showing Base and Attribute Dimensions, the Product dimension is the base dimension for the Caffeinated, Ounces, and Pkg Type attribute dimensions.
Attribute dimensions and members are Dynamic Calc, so Essbase 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 37, Outline Showing Base and Attribute Dimensions, 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.
Attribute dimensions have a type setting—text, numeric, Boolean, or date. Text is the default setting. Although assigned at the dimension level, the type applies only to the level 0 members of the dimension. For more information, see Understanding Attribute Types.
If you remove the attribute tag from a dimension, Essbase removes prefixes or suffixes from its member names. Prefixes and suffixes are not visible in the outline. For more information, see Setting Prefix and Suffix Formats for Member Names of Attribute Dimensions.
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. Tracking size-related information for the Market dimension requires another attribute dimension with a different name, for example, MarketSize, with the MarketSize attribute dimension associated with the Market dimension.
You cannot associate multiple members from the same attribute dimension with the same base dimension member. For example, the Bottle and Can package types cannot both be associated with the product 100-30.
You can associate members from different attribute dimensions with the same member of a base dimension. For example, a decaffeinated cola product (100-30) sold in 16 ounce bottles has three attributes—Caffeinated:False; Ounces:16; and Pkg Type:Bottle.
For example, in Figure 38, Association of Attributes with the Same Level Members of the Market Dimension, 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.
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.
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.
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 there is more than one Boolean attribute dimension, 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.
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.
Table 12. Differences Between Attribute and Standard Dimensions
If a member formula contains a run-time 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. Run-time dependent functions include the following: @CURRMBR, @PARENT, @PARENTVAL, @SPARENTVAL, @MDPARENTVAL, @ANCEST, @ANCESTVAL, @SANCESTVAL, and @MDANCESTVAL.
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.
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.
Table 13. Comparing Attributes and 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 just those members.
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 specific 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.
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.
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 and 6 million, and so on.
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.
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.
For more information about attribute features, see Understanding Attributes.
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 very large if the categories repeat a lot of members.
Standard dimensions and members. Additional standard dimensions provide flexibility, but they add storage requirements and complexity to a database. For guidelines on evaluating the impact of additional dimensions, see Analyzing and Planning.
Table 14 describes situations where you might consider one of these alternative approaches for managing attribute data in a database.
Table 14. Considering Alternatives to Attribute Dimensions
Members of separate, standard dimensions. For example, to track product maintenance costs over a period of 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.
Outline layout and content can affect attribute calculation and query performance. For general outline design guidelines, see Designing an Outline to Optimize Performance.
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, the base dimensions are the most queried dimensions.
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 view the dimension, attribute value and attribute type of a specific attribute member, use a tool:
When you use the attribute feature, Essbase establishes some default member names; for example, the system-defined True and False would preclude other member names of True and False. You can change these system-defined names for the database. Date attributes and numeric attributes can also 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 specific members, such as when you define partitions or select information to be retrieved.
If 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.
If you have more than one date attribute dimension, some member names in both dimensions could be the same. For example, the date that a store opens in a certain market could be the same as the date a product was introduced.
12 can be the attribute value for the size of a product and 12 could also be the value for the number of packing units for a product. This example results in two members with the same 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. As the prefix or suffix you can choose to 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.
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.
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.
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-2004 member exists and you change the format to dd-mm-2004, outline verification will find this member invalid. If you change the date format, you must rebuild the date attribute dimensions.
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.
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, 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.
Numeric attribute dimension member names are recommended to contain no more than six decimal positions. Otherwise, because of precision adjustments, an outline may not pass verification.
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.
Essbase calculates attribute data dynamically at retrieval time, using members from a system-defined dimension created specifically 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.
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.
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. For a discussion of calculation types, 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. For more information on the label only dimension property, 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.
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:
For example, as shown in Figure 40, 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):
For information on accessing calculated attribute data, see Accessing Attribute Calculations Members Using the Spreadsheet.
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 or not they have data values, use the @COUNT function in combination with the @ATTRIBUTE function. For more information, see the Essbase Technical Reference.
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.
Table 15. Sample Attribute Data
You can access members from the Attribute Calculations dimension in Essbase Spreadsheet Add-in for Excel. From the spreadsheet, users can view Attribute Calculations dimension members using any of the following methods:
The calculation order for attribute calculations is the same as the order for dynamic calculations. For an outline of calculation order, see Calculation Order for Dynamic Calculation.
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 Essbase Spreadsheet Add-in for Excel, 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 could group all 8-ounce products. Grouping members by attribute may decrease retrieval time.
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.
Some restrictions apply when using attributes in formulas associated with two-pass members. See the rows about two-pass calculations in Table 12, Differences Between Attribute and Standard Dimensions .
Generate a list of all base dimension members associated with attributes that satisfy the conditions that you specify. For example, you can generate a list of products that are greater than or equal to 20 ounces, and then increase the price for those products.
For syntax information and examples for these functions, see the Essbase 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 stored 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.