Working with Attributes

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 a Hyperion Essbase OLAP Server outline. It contains the following sections:

You can find other information about attributes in relevant sections of this book.


For information about...	See...
Defining attributes through dimension build	Introducing Dynamic Dimension Building Building Dimensions Using a Rules File
Using attributes in partitions	Designing Partitioned Applications Building and Maintaining Partitions
Using attributes in report writer	Developing Report Scripts Examples of Report Scripts

About Attributes

You can use the Hyperion 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 your 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 your 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.

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 Hyperion 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, Hyperion 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 calc 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. For example, the crosstab in Figure 10-1 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.

Figure 10-1: Crosstab Example

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

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

About 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 this 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 10-2 shows part of the Sample Basic outline featuring the Product dimension and three attribute dimensions, Caffeinated, Ounces, and Pkg Type.

Figure 10-2: Outline Showing Base and Attribute Dimensions

In the outline, to the right of the Product dimension, the terms Caffeinated, Ounces, and Pkg Type, enclosed in braces, 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 10-2, the Product dimension is the base dimension for the Caffeinated, Ounces, and Pkg Type attribute dimensions.

Note:

Attribute dimensions and members are Dynamic Calc. This means Hyperion Essbase calculates attribute information at retrieval time. Attribute data is not stored in the database.

Members of Attribute Dimensions

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 enclosed in braces next to a base dimension member shows the attributes of that member. In Figure 10-2, 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.

All base dimension members associated with members of a particular attribute dimension must be at the same level.
For example, in Figure 10-3, 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 10-3: Association of Attributes with the Same Level Members of the Market Dimension
The level 0 members of attribute dimensions are the only members that you can associate with base dimension 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.

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.
A base dimension member can have many attributes, but only one attribute from each particular attribute dimension.
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:

> (greater than)
>= (greater than or equal to)
< (less than)
<= (less than or equal to)
= = (equal to)
<> or != (not equal to)
IN

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, Hyperion 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 Assigning the Names of Members of Numeric Attribute Dimensions to 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. See Changing the Member Names for Boolean Attribute Dimensions.

You can use date attributes to specify the date format--month-day-year or day-month-year--and to sequence information accordingly. See Setting the Member Name Format of 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.

Hyperion Essbase supports date attributes from January 1, 1970 through January 1, 2038.

Differences Between Attribute and Standard Dimensions

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 10-1 describes major differences between attribute and standard dimensions and their members.

*Table 10-1: Differences Between Attribute and Standard Dimensions*
	Attribute Dimensions	Standard Dimensions
Storage	Must be sparse. Their base dimensions must also 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
Alias combinations	Cannot include attribute dimensions or members	Can include standard dimensions and members
Two-pass calculation member property	Not available	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 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.

Differences Between Attributes and UDAs

Attributes and UDAs both enable analysis based on characteristics of the data. Attributes provide much more capability than UDAs. Table 10-2 compares them. Checkmarks indicate the feature supports the corresponding capability.

*Table 10-2: Comparing Attributes and UDAs*
Capability	Attributes Feature	UDAs Feature
Data Storage
You can associate with sparse dimensions.
You can associate with dense dimensions.
Data Retrieval
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.	Simple	More difficult to implement, requiring additional calc 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 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.		More difficult to implement
You can create crosstab views displaying aggregate totals of attributes associated with the same base dimension.	You can show a crosstab of all values of each attribute dimension.	You can only retrieve totals based on specific UDA values.
You can use Boolean operators AND, OR, and NOT with attribute and UDA values to further refine a query. For example, you can select decaffeinated drinks from the 100 product group.
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-dimension 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.
Through the Attribute Calculations dimension, you can view aggregations of attribute values as sums, counts, minimums, maximums, and averages.
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.
You can retrieve specific base-dimension members using attribute-related information	Powerful conditional and value-based selections	Limited to text string matches only
Data Conversion
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.
Calc Scripts
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.
You can perform calculations on base-dimension members whose attribute value satisfies conditions that you specify. For example, you can calculate the Profit per Ounce of each base-dimension member.

Introducing the Attribute Calculations Dimension

When you create the first attribute dimension in the outline, Hyperion 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 the members of the Attribute Calculations dimension to retrieve calculated information; for example, the average sales of cola within different market population ranges.

The Attribute Calculations dimension is not visible in the outline. You can see it wherever you select dimension members, such as in the Member Selection dialog box and in the Hyperion Essbase Query Designer in the Hyperion Essbase Spreadsheet Add-in.

For more information about the Attribute Calculations dimension, see Attribute Calculations Dimension and Changing Member Names of the Attribute Calculations Dimension.

Attribute Design Considerations

Hyperion 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.

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 retrieval
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.
Additional calculation functionality
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.
Economy and simplicity
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 about attribute features, see About Attributes.

Using Alternative Design Approaches

In some situations, you should 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 Differences Between 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. See Analyzing Database Design.

Table 10-3 describes situations where you might consider one of these alternative approaches for managing attribute data in a database.

*Table 10-3: Considering Alternatives to Attribute Dimensions*
If you want to...	Consider using...
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 as 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 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.
Minimize retrieval time with large numbers of base-dimension members	Batch calculation with shared members or members of separate, standard dimensions.

Outline Performance Considerations

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, the base dimensions are the most queried dimensions.

For information on optimizing calculation of outlines containing attributes, see Calculation and Retrieval Performance Considerations.

Working with the Names of Members of Attribute Dimensions

All member names in an outline must be unique. When you use the attribute feature, Hyperion Essbase establishes some default member names in some places. These default names might duplicate names that already exist in the outline. Use the Settings > Attribute Member Names command to change these system-defined names for the database. You can also use this command to establish other settings for members of attribute dimensions in the database.

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 source and target outlines.

You can use the GETATTRIBUTESPECS command in ESSCMD to view the attribute member name format specifications for the database. For example:

GETATTRIBUTESPECS Sample Basic

See the online Technical Reference in the DOCS directory for information about this command. See Performing Interactive and Batch Operations Using ESSCMD for information about ESSCMD.

Defining a Prefix or Suffix Format for the Names of Members of 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 example--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.
Date example--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.
Numeric example--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 would result in two members with the same name: 12.

Because Hyperion Essbase does not allow duplicate member names, Application Manager helps you to 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, Hyperion Essbase assumes that no prefix or suffix is attached to the names of members of attribute dimensions.

Note:

The convention that you select applies to the level 0 member names of all numeric, Boolean, and date attribute dimensions in the outline.

For the examples in the following instructions, consider the attribute dimension shown in Figure 10-4.

Figure 10-4: The Population Attribute Dimension and Members

To define prefixes or suffixes for the names of members of Boolean, date, and numeric attribute dimensions:

Select Settings > Attribute Member Names.
The Sample box shows the format of member names in numeric, Boolean, and date attribute dimensions.
In the Value option group, select the source of the value that Hyperion Essbase attaches to the attribute member name in the outline.
- None uses the member name in the outline as the full name. Nothing is attached; for example, 3000000.
- Parent attaches the immediate parent; for example, Small_3000000.
- Grandparent attaches the parent's parent; for example, Population_3000000.
- Ancestor attaches all generations; for example, Population_Small_3000000.
- Dimension attaches the attribute dimension name; for example, Population_3000000.
  
  Note: If you select a value other than None, Hyperion Essbase assumes the other format options are Prefix and "_(underscore)."
In the Separator option group, select the separator character to insert between the attribute member name in the outline and the attached value.
- _(underscore) inserts an underscore between each value in the prefix or suffix; for example, Population_Small_3000000.
- | (pipe) inserts a pipe between each value in the prefix or suffix; for example, Population|Small|3000000.
- ^ (caret) inserts a caret between each value in the prefix or suffix; for example, Population^Small^3000000.
In the Prefix/Suffix option group, select whether to attach a prefix or a suffix to the attribute member name in the outline.
- Prefix attaches the value selected in the Value option group before the attribute member name; for example, Population_3000000.
- Suffix attaches the value selected in the Value option group after the attribute member name; for example, 3000000_Population.
Click OK.

Note:

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.

You can use the GETATTRINFO command in ESSCMD to view the dimension, attribute value and attribute type of a specific attribute member. For example, to view attribute name information for the Caffeinated_True attribute:

GETATTRINFO "Caffeinated_True"

See the online Technical Reference in the DOCS directory for information about this command. See Performing Interactive and Batch Operations Using ESSCMD for information about ESSCMD.

Changing 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. For more information about the Attribute Calculations dimension, see 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 Attribute Calculations dimension and its members:

Select Settings > Attribute Member Names and select the Attribute Calculations tab.
Figure 10-5: Attribute Calculations Page in the Attribute Member
Names Dialog Box

In the Attribute Calculations Member Names option group, type the new member name for each member name that you want to change. Follow the member naming rules described in Rules for Naming Dimensions and Members.

The default names are:


Text Box Label	Default Name
Dimension Name	Attribute Calculations
Sum Member Name	Sum
Count Member Name	Count
Min Member Name	Min
Max Member Name	Max
Avg Member Name	Avg

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. For more information about each member, see Default Attribute Calculations Members.

Click OK.

Changing the Member Names for Boolean Attribute Dimensions

All Boolean attribute dimensions in a single database have two, level 0 members with the same names, by default, True and False.

Hyperion Essbase enables you to change the names of members of Boolean attribute dimensions; for example, to Yes and No.

Note:

Changing the Boolean member-name setting does not retroactively change the names of members of existing Boolean attribute dimensions. You must manually change the names of existing Boolean members to the names specified in the Boolean member names setting.

When you set an attribute dimension type as Boolean, Hyperion Essbase automatically creates two members with the names specified in the setting. If other members exist in the Boolean attribute dimension, you must remove them.

To change the member names setting of Boolean attribute dimensions in a database:

Select Settings > Attribute Member Names and select the Boolean and Date tab.
Figure 10-6: Boolean and Date Page in the Attribute Member
Names Dialog Box
In the Boolean Member Names option group, type the new member name for each member name that you want to change. Follow the member naming rules described in Rules for Naming Dimensions and Members.
The default names are:

Text Box Label Default Name

True Member Name True

False Member Name False

Text Box Label	Default Name
True Member Name	True
False Member Name	False

Click OK.

Note:

If you have more than one Boolean attribute dimension, you must specify a prefix or suffix member name format to ensure unique member names; for example, Caffeinated_True and Caffeinated_False. See Defining a Prefix or Suffix Format for the Names of Members of Attribute Dimensions.

Setting the Member Name Format of Date Attribute Dimensions

To change the format of members of date attribute dimensions at the database level:

Select Settings > Attribute Member Names and click the Boolean and Date tab.
In the Date Member Names group, select a format:
- mm-dd-yyyy displays the month before the day; for example, October 18, 1999 is displayed as 10-18-1999.
- dd-mm-yyyy displays the day before the month; for example, October 18, 1999 is displayed as 18-10-1999.

Click OK.

Note:

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.

Assigning the Names of Members of Numeric Attribute Dimensions to Ranges of Values

Members of numeric attribute dimensions can represent single numeric values. For 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.

Members of numeric attribute dimensions can represent ranges of values. Consider, for example, the Population attribute dimension shown in Figure 10-7.

Figure 10-7: 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, Hyperion Essbase automatically associates members of the base dimension with the appropriate attribute range. For example, if numeric members represent the tops of ranges, Hyperion 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, you 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. See Working With Numeric Ranges.

To define the rule for assigning numeric attribute member names to ranges of values:

Select Settings > Attribute Member Names and select the Numeric Ranges tab.
Figure 10-8: Numeric Ranges Page in the Attribute Member
Names Dialog Box
Select the option that sets whether numeric attribute values define the tops or bottoms of the ranges that they represent.
- Tops of ranges. For example, Hyperion Essbase associates the 6000000 attribute with all markets in a population area greater than the value of the next lower member of the attribute dimension and less than or equal to 6,000,000. This is the default setting.
- Bottoms of ranges. For example, Hyperion Essbase associates the 6000000 attribute with all markets in a population area equal to or greater than 6000000 and less than the value of the next higher member of the attribute dimension.
Click OK.

Defining Attributes Manually

When manually working with attributes, use the Outline Editor in Hyperion Essbase Application Manager to perform the following dimension and member-related tasks:

Create the attribute dimensions. See Adding Dimensions to Outlines. In the outline, position the attribute dimensions after all standard dimensions.
Tag the dimensions as attribute dimensions. See Tagging an Attribute Dimension.
Set each attribute dimension type as text, numeric, Boolean, or date. See Setting the Attribute Type.
Add members to the attribute dimensions. See Adding Members to Dimensions.
Associate base dimensions with the attribute dimensions. See Associating Base Dimensions with Attribute Dimensions.
Associate base dimension members with members of the attribute dimensions. See Associating Base Dimension Members with Attributes.

Associating Base Dimensions with Attribute Dimensions

When you associate an attribute dimension with a standard dimension, the standard dimension is known as the base dimension for that attribute dimension.

You can only associate attribute dimensions with sparse standard dimensions.
A standard dimension can be a base dimension for more than one attribute dimension.
An attribute dimension can be associated with only one base 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.

To associate attribute dimensions with a standard dimension:

Select the base dimension, for example Product.
Click the Data Dictionary button, , press the Enter key, or select Edit > Properties to open the Dimension Properties dialog box.
Figure 10-9: Attributes Page in the Dimension Properties Dialog Box

The Attributes page displays the name of the selected base dimension and contains the following items:
- Available Attribute Dimensions list box, which displays the names of attribute dimensions that are not associated with a base dimension
- Associated Attribute Dimensions list box, which displays the names of the attribute dimensions associated with the selected base dimension
- Add button, which moves attribute dimensions from the Available Attribute Dimensions list box to the Associated Attribute Dimensions list box
- Delete button, which removes attribute dimensions from the Associated Attribute Dimensions list box, putting them back in the Available Attribute Dimensions list box
Use the Add and Delete buttons to move selected attribute dimension names in and out of the Associated Attribute Dimensions list box.
When the Associated Attribute Dimensions list box contains the names of all attribute dimensions to be associated with the base dimension, click OK.

Associating Base Dimension Members with Attributes

Attribute associations must follow these rules:

You cannot associate multiple members from the same attribute dimension with the same base dimension member. For example, the same product cannot be associated with both bottle and can package types.
You can associate only level-0 members of attribute dimensions.
The base dimension members to which you associate members from a specific attribute dimension must all be at the same level.

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.

After attributes are associated with base dimension members, if you cut or copy and paste base dimension members to another location in the outline, the attribute associations are lost.

To associate a member of a base dimension with members of attribute dimensions:

Select the base dimension member with which you want to associate the attributes, for example 100-10.
Click the Data Dictionary button, , press the Enter key, or select Edit > Properties to open the Member Properties dialog box.
Select the Member Attributes tab.
Figure 10-10: Member Attributes Page in the Member Properties Dialog Box

The Member Attributes page displays the name of the selected member of the base dimension and contains the following items:
- Available Member Attributes list box, which lists attribute dimensions associated with the base dimension to which the selected member belongs
- Associated Attributes list box, which lists attributes associated with the selected member of the base dimension
- Delete button, which deletes attribute associations from the Associated Attributes list box
- Attribute Member Name box, which displays the full member name of the selected attribute dimension member
Click the plus symbol by the name of an attribute dimension or member to display its children.
Repeat until member names display without plus or minus symbols next to them. Attribute values are the level 0 members in an attribute dimension.
Double-click the attribute value that you want to associate with the selected base-dimension member.
The Associated Attributes list box displays the name of the selected attribute dimension and the associated attribute value.
Repeat for other attribute dimensions, until the Associated Attributes list box lists the correct attribute values to be associated with the base dimension member.

Note: Hyperion Essbase does not require that each member of a base dimension be associated with a member of an attribute dimension.
Click OK.

Calculating Attribute Data

Hyperion Essbase calculates attribute data dynamically at retrieval time, using members from a system-defined dimension created specifically by Hyperion 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 Hyperion Essbase calculations, including dynamic calculations. For more information, see Calculating Data

This section includes the following:

Attribute Calculations Dimension

The Attribute Calculations dimension contains five members. 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. This dimension has the following properties:

System-defined. When you create the first attribute dimension in an application, Hyperion 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 more information, see 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 Defining Dimension and Member 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. For more information on dynamic calculations, see Dynamically Calculating Data Values
Not displayed in Outline Editor. The Attribute Calculations dimension is not displayed in the 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, Hyperion Essbase:

Finds the base-dimension members that are associated with the specified 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: Hyperion Essbase excludes #MISSING values when calculating attribute data.

For example, as shown in Figure 10-11, 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, Hyperion Essbase dynamically calculates the average sales values of all products associated with these attributes for the current member combination (Actual->Sales->East->Qtr1):

Figure 10-11: Retrieving an Attribute Calculations Member

For more information on accessing calculated attribute data, see Accessing Attribute Calculations Members Using the Spreadsheet.

Default Attribute Calculations Members

The Attribute Calculations dimension contains five members used to calculate and report attribute data. These members are:

Sum: calculates a sum, or total, of the values for a member with an attribute or combination of attributes. Supports non-additive consolidations such as multiplication, and two-pass measures.
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 online Technical Reference in the DOCS directory.
Avg: calculates a mathematical mean, or average, of the non-missing 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 using Application Manager, subject to the same naming conventions as standard members. For more information on changing Attribute Calculations member names, see Changing Member Names of the Attribute Calculations Dimension.

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, Hyperion Essbase retrieves the sum for the specified attribute or combination of attributes. For example, in the following spreadsheet view, 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 10-12: Retrieving the Default Attribute Calculations Member

An Attribute Calculation Example

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

*Table 10-4: 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

A spreadsheet report showing calculated attribute data might look like this:

Figure 10-13: 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.

Accessing Attribute Calculations Members Using the Spreadsheet

You can access members from the Attribute Calculations dimension in Hyperion Essbase Spreadsheet Add-in. From the spreadsheet, users can view Attribute Calculations dimension members by:

Entering members directly into a sheet
Selecting members from the Hyperion Essbase Query Designer
Selecting members from the Hyperion Essbase Member Selection dialog box
Entering members as an EssCell parameter

For more information on accessing calculated attribute data from the spreadsheet, see the Hyperion Essbase Spreadsheet Add-in User's Guide.

Calculation and Retrieval Performance Considerations

Keep in mind the following calculation and retrieval performance considerations:

The calculation order for attribute calculations is the same as the order for dynamic calculations. For more information, see Calculation Order for Dynamic Calculations.
Since Hyperion 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:
- Configure the outline as described in Outline Performance Considerations.
- Drill down to the lowest level of base dimensions before retrieving data. For example, in Hyperion Essbase 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 could group all 8-ounce products. Grouping members by attribute may decrease retrieval time.

Using Attributes in Calculation Formulas

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.

Note:

You cannot associate formulas with members of attribute dimensions.

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


To do this...	Use this function...
Generate a list of all base-dimension members with a specific attribute. For example, you can generate a list of members that have the Bottle attribute, and then increase the price for those members.	@ATTRIBUTE
Return the numeric value of the attribute from a specific numeric or date attribute dimension that is associated with the base-dimension member being calculated. For example, you can return the numeric value of a size attribute (for example, 12 for the member 12 under Ounces) for the base-dimension member being calculated (for example, Cola).	@ATTRIBUTEVAL
Return the Boolean value (TRUE or FALSE) of the attribute from a specific Boolean attribute dimension that is associated with the base-dimension member being calculated. For example, if you specify @ATTRIBUTEBVAL(Caffeinated), when the product 100-10 is being calculated, Hyperion Essbase returns TRUE. True is the value of the Caffeinated attribute that is associated with the Product member 100-10.	@ATTRIBUTEBVAL
Return the text value of the attribute from a text attribute dimension that is associated with the base-dimension member being calculated. For example, if you specify @ATTRIBUTESVAL(Pkg Type), when the product 100-10 is being calculated, Hyperion Essbase returns the value: Can. Can is the value of the Pkg Type attribute that is associated with the Product member 100-10.	@ATTRIBUTESVAL
Convert a date string to numbers for a calculation. For example, you can use @TODATE in combination with the @ATTRIBUTEVAL function to increase overhead costs for stores opened after a certain date.	@TODATE
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.	@WITHATTR

Note: For syntax information and examples for these functions, see the online Technical Reference in the DOCS directory. For an additional example using @ATTRIBUTEVAL in a formula, see Calculating an Attribute Formula.