Process for Creating Attributes
Also see:
For other information about using attributes, see:
Such an analysis could tell you that decaffeinated drinks sold in cans in small markets (populations less than 6,000,000) are less profitable than you anticipated. For more details, you can filter the analysis by specific attribute criteria, including minimum or maximum sales and profits of different products in similar market segments.
A few ways analysis by attribute provides depth and perspective, supporting better-informed decisions:
Understanding Attributes
Understanding Attribute Dimensions
Designing Attribute Dimensions
Building Attribute Dimensions
Setting Member Names in Attribute Dimensions
Calculating Attribute Data
Varying Attributes
Some information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases.Also see:
- Comparison of Aggregate and Block Storage
- Aggregate Storage Applications, Databases, and Outlines
- Building Attribute Dimensions and Associating Attributes
- Designing Partitioned Applications
- Creating and Maintaining Partitions
- Developing Report Scripts
Attributes describe characteristics of
data such as product size and color. Through attributes, you can group
and analyze members of dimensions based on their characteristics. This
chapter describes how to create and manage attributes in an Essbase
Server outline.
When working with attributes in Outline Editor, perform the following tasks:
You can use the Essbase attribute feature
to retrieve and analyze data not only from the perspective of
dimensions, but also in terms of characteristics, or attributes, of
those dimensions. For example, you can analyze product profitability
based on size or packaging, and you can make more effective conclusions
by incorporating into the analysis market attributes such as the
population of each market region.When working with attributes in Outline Editor, perform the following tasks:
- Create a dimension.See Adding Dimensions and Members to an Outline. In the outline, position the attribute dimensions after all standard dimensions.
- Tag the dimension as an attribute dimension and set attribute dimension type as text, numeric, Boolean, or date.See Creating Attribute Dimensions.
- Add members to the attribute dimension.See Adding Dimensions and Members to an Outline.
- Associate a base dimension with the attribute dimension.See Understanding the Rules for Attribute Dimension Association.
- Associate members of the base dimension with members of the attribute dimension.See Understanding the Rules for Attribute Member Association.
- If necessary, set up the attribute calculations.See Calculating Attribute Data.
Such an analysis could tell you that decaffeinated drinks sold in cans in small markets (populations less than 6,000,000) are less profitable than you anticipated. For more details, you can filter the analysis by specific attribute criteria, including minimum or maximum sales and profits of different products in similar market segments.
A few ways analysis by attribute provides depth and perspective, supporting better-informed decisions:
- You can select, aggregate, and report on data based on common features (attributes).
- By defining attributes as having a text, numeric, Boolean, or date type, you can filter (select) data using type-related functions such as AND, OR, and NOT operators and <, >, and = comparisons.
- You can use the numeric attribute type to group statistical values by attribute ranges; for example, population groupings such as <500,000, 500,000–1,000,000, and >1,000,000.
- Through the Attribute Calculations dimension automatically created by Essbase, you can view sums, counts, minimum or maximum values, and average values of attribute data. For example, when you enter Avg and Bottle into a spreadsheet, Essbase retrieves calculated values for average sales in bottles for all the column and row intersections on the sheet.
- You can perform calculations using numeric attribute values in calculation scripts and member formulas; for example, to determine profitability by ounce for products sized by the ounce.
- You can create crosstabs of attribute
data for the same dimension, and you can pivot and drill down for detail
data in spreadsheets.An attribute crosstab
is a report or spreadsheet showing data consolidations across
attributes of the same dimension. The crosstab example below displays
product packaging as columns and the product size in ounces as rows. At
their intersections, you see the profit for each combination of package
type and size.
From this information, you can see which size-packaging combinations were most profitable in the Florida market.
Product Year Florida Profit Actual Bottle Can Pkg Type ========= ========= ========= 32 946 N/A 946 20 791 N/A 791 16 714 N/A 714 12 241 2,383 2,624 Ounces 2,692 2,383 5,075Understanding Attribute Dimensions
In the Sample.Basic database, products have attributes that are characteristics of the products. For example, products have an attribute that describes their packaging. In the outline, you see these characteristics as two dimensions, the Products dimension, and the Pkg Type attribute dimension that is associated with it. An attribute dimension has the word Attribute next to its name in the outline.
Figure 39, Outline Showing Base and Attribute Dimensions shows part of the Sample.Basic outline featuring the Product dimension and three attribute dimensions, Caffeinated, Ounces, and Pkg Type.
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 39, Outline Showing Base and Attribute Dimensions, the Product dimension is the base dimension for the Caffeinated, Ounces, and Pkg Type attribute dimensions.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.
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. Figure 39, Outline Showing Base and Attribute Dimensions, for example, shows that product 100-10 has three attributes—it has caffeine, is sold in 12-ounce containers, and is sold in cans.
Rules regarding members of attribute dimensions and their base dimensions.
- You can tag only sparse dimensions as attribute dimensions.
- Before you can save an outline to the server, each attribute dimension must be associated with a standard, sparse dimension as its base dimension.
- Attribute dimensions must be the last dimensions in the outline.
- 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. 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. See Setting Prefix and Suffix Formats for Member Names of Attribute Dimensions.
- A base dimension member can have many attributes, but only one attribute from each attribute dimension.For example, product 100-10 can have size and packaging attributes, but only one size and only one type of packaging.
- You cannot associate an attribute with an implied shared member, the child of which is tagged as shared.
- Essbase does not support attributes for Hybrid Analysis-enabled members.
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 the base dimension for that attribute dimension.When you associate a member of an attribute dimension with a member of a base dimension, follow these rules:
- An attribute dimension must be associated with a sparse standard dimension.
- 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. 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.
- After attributes are associated with base dimension members, if you cut or copy and paste base dimension members to another outline location, the attribute associations are lost.
- Essbase does not require that each member of a base dimension be associated with a member of an attribute dimension.
- All base dimension members associated with members of a particular attribute dimension must be at the same level.For
example, in Figure 40, 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, because the other
members of the Market dimension that have Population attributes are
level 0 members.
Figure 40. 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.
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.In general, attribute dimensions and their members are similar to standard dimensions and their members. You can provide aliases and member comments for attributes. Attribute dimensions can include hierarchies, and you can name generations and levels. You can perform the same spreadsheet operations on attribute dimensions and members as on standard dimensions and members; for example, to analyze data from different perspectives, you can retrieve, pivot, and drill down in the spreadsheet.
- The default attribute type is text. Text attributes enable the basic attribute member selection and attribute comparisons in calculations. When you perform such comparisons, Essbase compares characters. For example, the package type Bottle is less than the package type Can, because B precedes C in the alphabet. In Sample.Basic, Pkg Type is an example of a text attribute dimension.
- The names of level 0 members of numeric attribute dimensions are numeric values. You can include the names (values) of numeric attribute dimension members in calculations. For example, you can use the number of ounces specified in the Ounces attribute to calculate profit per ounce for each product.You can also associate numeric attributes with ranges of base dimension values; for example, to analyze product sales by market population groupings—states with 3,000,000 population or less in one group, states with a population between 3,000,001 and 6 million in another group, and so on. See Setting Up Member Names Representing Ranges of Values.
- All Boolean attribute dimensions in a database contain only two members. The member names must match the settings for the database; for example, True and False. If more than one Boolean attribute dimension exists, specify a prefix or suffix member name format to ensure unique member names; for example, Caffeinated_True and Caffeinated_False. For a discussion of how to change Boolean names, see Setting Boolean Attribute Member Names.
- You can use date attributes to specify the date format—month-day-year or day-month-year—and to sequence information accordingly. For a discussion of how to change date formats, see Changing the Member Names in Date Attribute Dimensions. You can use date attributes in calculations. For example, you can compare dates in a calculation that selects product sales from markets established since 10-12-1999.Essbase supports date attributes from January 1, 1970, through January 1, 2038.
Table 18 describes major differences between attribute and standard dimensions and their members.
Table 18. Differences Between Attribute and Standard Dimensions
Comparing Attributes and UDAs
Attributes and UDAs enable analysis based on characteristics of the data. Attributes provide greater capability than UDAs.
The tables in this topic describe the differences between attributes and UDAs in these areas of functionality:
- Data storage (Table 19, Data Storage—Comparing Attributes and UDAs)
- Data retrieval (Table 20, Data Retrieval—Comparing Attributes and UDAs)
- Data conversion (Table 21, Data Conversion—Comparing Attributes and UDAs)
- Calculation scripts (Table 22, Calculation Scripts—Comparing Attributes and UDAs)
Table 19. Data Storage—Comparing Attributes and UDAsData storage Attributes UDAs You can associate with sparse dimensions. Supported Supported You can associate with dense dimensions. Not supported Supported Table 20. Data Retrieval—Comparing Attributes and UDAsTable 21. Data Conversion—Comparing Attributes and UDAsData Conversion Attributes UDAs Based on the value of a UDA, you can change the sign of the data as it is loaded into the database. For example, you can reverse the sign of all members with the UDA Debit. Not supported Supported Table 22. Calculation Scripts—Comparing Attributes and UDAsCalculation Scripts Attributes UDAs You can perform calculations on a member if its attribute or UDA value matches a specific value. For example, you can increase the price by 10% of all products with the attribute or UDA of Bottle. Supported Supported You can perform calculations on base members whose attribute value satisfies conditions that you specify. For example, you can calculate the Profit per Ounce of each base member. Supported Not supported 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.
For the most flexibility and functionality, use attribute dimensions to define attribute data. Using attribute dimensions provides the following features:
See Understanding Attributes.- Sophisticated, flexible data retrievalYou can view attribute data only when you want to; you can create meaningful summaries through crosstabs; and, using type-based comparisons, you can selectively view only the data that you want to see.
- Additional calculation functionalityNot only can you perform calculations on the names of members of attribute dimensions to define members of standard dimensions, you can also access five types of consolidations of attribute data—sums, counts, averages, minimums, and maximums.
- Economy and simplicityBecause attribute dimensions are sparse, Dynamic Calc, they are not stored as data. Compared to using shared members, outlines using attribute dimensions contain fewer members and are easier to read.
In some situations, consider one of the following approaches:
- UDAs. Although UDAs provide less flexibility than attributes, you can use them to group and retrieve data based on its characteristics. See Comparing Attributes and UDAs.
- Shared members. For example, to include a seasonal analysis in the Year dimension, repeat the months as shared members under the appropriate season; Winter: Jan (shared member), Feb (shared member), and so on. A major disadvantage of using shared members is that the outline becomes large if the categories repeat many members.
- Standard dimensions and members. Additional standard dimensions provide flexibility but add storage requirements and complexity to a database. For guidelines on evaluating the impact of additional dimensions, see Analyzing and Planning.
Table 23 describes situations in which you might consider an alternative approach to managing attribute data in a database.
Table 23. Considering Alternatives to Attribute DimensionsOutline 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:
See Optimizing Calculation and Retrieval Performance.- Ensure that attribute dimensions are the only sparse Dynamic Calc dimensions in the outline.
- Locate sparse dimensions after dense dimensions in the outline. Place the most-queried dimensions at the beginning of the sparse dimensions and attribute dimensions at the end of the outline. In most situations, base dimensions are queried most.
To build an attribute dimension, tag the dimension as an attribute and assign the dimension a type. Then associate the attribute dimension with a base dimension. Finally, associate each level 0 member of the attribute dimension with a member of the associated base dimension.
To build an attribute dimension, see “Defining Attributes” in the Oracle Essbase Administration Services Online Help.When you use the attribute feature, Essbase establishes default member names; for example, the system-defined True and False precludes other member names of True and False. You can change these system-defined names for the database. Date attributes and numeric attributes also can be duplicated. To avoid duplicate name confusion, you can establish settings for qualifying member names in attribute dimensions. The outline does not show the fully qualified attribute names, but you can see the full attribute names anywhere you select members, such as when you define partitions or select information to be retrieved.
Define the member name settings before you define or build the attribute dimensions. Changing the settings after the attribute dimensions and members are defined could result in invalid member names.
The following sections describe how to work with the names of members of attribute dimensions:
Note:If you partition on outlines containing attribute dimensions, the name format settings of members described in this section must be identical in the source and target outlines.
The information in this section does not apply to duplicate member attribute dimensions.
The names of members of Boolean, date, and numeric attribute dimensions are values. It is possible to encounter duplicate attribute values in different attribute dimensions.
You can define unique names by attaching a prefix or suffix to member names in Boolean, date, and numeric attribute dimensions in the outline. You can affix the dimension, parent, grandparent, or all ancestors to the attribute name. For example, by setting member names of attribute dimensions to include the dimension name as the suffix, attached by an underscore, the member value 12 in the Ounces attribute dimension assumes the unique, full attribute member name 12_Ounces.- Boolean exampleIf you have more than one Boolean attribute dimension in an outline, the two members of each of those dimensions have the same names, by default, True and False.
- Date exampleIf you have more than one date attribute dimension, some member names in both dimensions could be the same. For example, the date on which a store opens in a certain market could be the same as the date on which a product was introduced.
- Numeric exampleThe attribute value for the size of a product could be 12, and 12 also could be the value for the number of packing units for a product. This example results in two members with the name 12.
By default, Essbase assumes that no prefix or suffix is attached to the names of members of attribute dimensions.
The convention that you select applies to the level 0 member names of all numeric, Boolean, and date attribute dimensions in the outline. You can define aliases for these names if you want to display shorter names in retrievals.
When you set the dimension type of an attribute dimension as Boolean, Essbase automatically creates two level 0 members with the names specified for the Boolean attribute settings. The initial Boolean member names in a database are set as True and False. To change these default names, for example, to Yes and No, define the member names for Boolean attribute dimensions before you create Boolean attribute dimensions in the database.
Before you can set an attribute dimension type as Boolean, you must delete all existing members in the dimension.
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-2007 member exists, and you change the format to dd-mm-2007, outline verification will find this member invalid. If you change the date format, you must rebuild the date attribute dimensions.
Members of numeric attribute dimensions can represent single numeric values or ranges of values:
- Single-value example: the member 12 in the Ounces attribute dimension represents the single numeric value 12; you associate this attribute with all 12-ounce products. The outline includes a separate member for each size; for example, 16, 20, and 32.
- Range of values example: the Population attribute dimension, as shown in Figure 41, Population Attribute Dimension and Members:
- In
this outline, the members of the Population attribute dimension
represent ranges of population values in the associated Market
dimension. The 3000000 member represents populations from zero through
3,000,000; the 6000000 member represents populations from 3,000,001
through 6,000,000; and so on. A setting for the outline establishes that
each numeric member represents the top of its range.
You can also define this outline setting
so that members of numeric attribute dimensions are the bottoms of the
ranges that they represent. For example, if numeric members are set to
define the bottoms of the ranges, the 3000000 member represents
populations from 3,000,000 through 5,999,999, and the 6000000 member
represents populations from 6,000,000 through 8,999,999.
When you build the base dimension, Essbase automatically associates members of the base dimension with the appropriate attribute range. For example, if numeric members represent the tops of ranges, Essbase automatically associates the Connecticut market, with a population of 3,269,858, with the 6000000 member of the Population attribute dimension.
In the dimension build rules file, specify the size of the range for each member of the numeric attribute dimension. In the above example, each attribute represents a range of 3,000,000.
Note:Oracle recommends that numeric attribute dimension member names contain no more than six decimal positions. Otherwise, because of precision adjustments, an outline may not pass verification.
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 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.
Regardless of the name that you use for a member, its function remains the same. For example, the second (Count) member always counts.
The following information assumes that you understand the concepts of attribute dimensions and Essbase calculations, including dynamic calculations. See the following sections.
When you create the first attribute dimension in the outline, Essbase also creates the Attribute Calculations dimension comprising five members with the default names Sum, Count, Min (minimum), Max (maximum), and Avg (average). You can use these members in spreadsheets or in reports to dynamically calculate and report on attribute data, such as the average yearly sales of 12-ounce bottles of cola in the West.
The Attribute Calculations dimension is not visible in the outline. You can see it wherever you select dimension members, such as in the Spreadsheet Add-in.
The attribute calculation dimension has the following properties:
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.- System-definedWhen you create the first attribute dimension in an application, Essbase creates the Attribute Calculations dimension and its members (Sum, Count, Min, Max, and Avg). Each member represents a type of calculation to be performed for attributes. See Understanding the Default Attribute Calculations Members.
- Label onlyLike all label only dimensions, the Attribute Calculations dimension shares the value of its first child, Sum. See Member Storage Properties.
- Dynamic Calc.The data in the Attribute Calculations dimension is calculated when a user requests it and is then discarded. You cannot store calculated attribute data in a database. See Dynamically Calculating Data Values.
- Not displayed in Outline Editor.The Attribute Calculations dimension is not displayed in Outline Editor. Members from this dimension can be viewed in spreadsheets and in reports.
To calculate attribute data at retrieval time, Essbase performs the following tasks:
- Finds the base-dimension members associated with the attribute-dimension members present in the current query
- Dynamically calculates the sum, count, minimum, maximum, or average for the attribute-member combination for the current query
- Displays the results in the spreadsheet or report
- Discards the calculated values—that is, the values are not stored in the database
Note: Essbase excludes #MISSING values when calculating attribute data.
For example, as shown in Figure 42, Retrieving an Attribute Calculations Member, a spreadsheet user specifies two members of attribute dimensions (Ounces_16 and Bottle) and an Attribute Calculations member (Avg) in a spreadsheet report. Upon retrieval, Essbase dynamically calculates the average sales values of all products associated with these attributes for the current member combination (Actual -> Sales -> East -> Qtr1):
Figure 42. Retrieving an Attribute Calculations MemberSee Accessing Attribute Calculations Members Using the Spreadsheet.
The Attribute Calculations dimension contains five members used to calculate and report attribute data:As an example of how Essbase calculates attribute data, consider the following yearly sales data for the East:
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.- Sum calculates a sum, or total, of the values for a member with an attribute or combination of attributes.
- Count calculates the number of members with the specified attribute or combination of attributes, for which a data value exists. Count includes only those members that have data blocks in existence. To calculate a count of all members with certain attributes, regardless of whether they have data values, use the @COUNT function in combination with the @ATTRIBUTE function. See the Oracle Essbase Technical Reference.
- Avg calculates a mathematical mean, or average, of the nonmissing values for an specified attribute or combination of attributes (Sum divided by Count).
- Min calculates the minimum data value for a specified attribute or combination of attributes.
- Max calculates the maximum data value for a specified attribute or combination of attributes.
Note: Each of these calculations excludes #MISSING values.
Table 24. Sample Attribute Data
Figure 43, Sample Spreadsheet with Attribute Data shows how calculated attribute data might look in a spreadsheet report. You can retrieve multiple Attribute Calculations members for attributes. For example, you can calculate Sum, Count, Avg, Min, and Max for bottles and cans.
Figure 43. Sample Spreadsheet with Attribute Data
Accessing Attribute Calculations Members Using the Spreadsheet
You can access members from the Attribute Calculations dimension in Spreadsheet Add-in. From the spreadsheet, users can view Attribute Calculations dimension members using any of the following methods:
See the Oracle Essbase Spreadsheet Add-in User’s Guide.
To optimize attribute calculation and retrieval performance, consider the following:
- The calculation order for attribute calculations is the same as for dynamic calculations. For an outline, see Calculation Order for Dynamic Calculation.
- Because Essbase calculates attribute data dynamically at retrieval time, attribute calculations do not affect the performance of the overall (batch) database calculation.
- Tagging base-dimension members as Dynamic Calc may increase retrieval time.
- When a query includes the Sum member and an attribute-dimension member whose associated base member is tagged as two-pass, retrieval time may be slow.
- To maximize attribute retrieval performance, use any of the following techniques:
- Configure the outline using the tips in Optimizing Outline Performance.
- Drill down to the lowest level of base dimensions before retrieving data. For example, in Spreadsheet Add-in, turn on the Navigate Without Data feature, drill down to the lowest level of the base dimensions included in the report, and then retrieve data.
- When the members of a base dimension are associated with several attribute dimensions, consider grouping the members of the base dimension according to their attributes. For example, in the Sample.Basic database, you can group all 8-ounce products.
In addition to using the Attribute Calculations dimension to calculate attribute data, you can use calculation formulas on members of standard or base dimensions to perform specific calculations on members of attribute dimensions; for example, to determine profitability by ounce for products sized by the ounce.
You cannot associate formulas with members of attribute dimensions.
Table 25 lists functions you can use to perform specific calculations on attributes:Note:Some restrictions apply when using attributes in formulas associated with two-pass members. See the rows about two-pass calculations in Table 18, Differences Between Attribute and Standard Dimensions.
Table 25. Functions That Calculate On AttributesNote:For syntax information and examples for these functions, see the Oracle Essbase Technical Reference. For an additional example using @ATTRIBUTEVAL in a formula, see Calculating an Attribute Formula.
Attribute calculations start at level 0 and stop at the first stored member. Therefore, if your outline has placed a stored member between two shared members in a an outline hierarchy, the calculation results may not include the higher shared member.
For example:
In this example, when an attribute calculation is performed, the calculation starts with level 0 Member 2 and stops when it encounters the first stored member, Member A. Therefore, Member 1 would not be included in the calculation.Member 1 (stored) Member A (stored) Member 2 (shared) Member B (stored) Member 1 (shared member whose stored member is Member 1 above)
To avoid unexpected results with attribute calculation, avoid mixing shared and stored members. For this example, if Member 2 were not shared, or Member 1 did not have a corresponding shared member elsewhere in the outline, calculation results would be as expected.
A product typically has attributes that describe or define the product. For example, a product could have an attribute describing the size of the product in ounces and an attribute describing the flavor of the product. In such a scenario, Product would be a base dimension while Ounces and Flavor would be attribute dimensions.
A varying attribute enables you to track two values in relation to a third dimension called an independent dimension. You could, for example, track your product in eight ounces over a year. In this scenario, Time is the independent dimension. The value of this third factor can vary (hence the name).
For example, you could track your product over a year, a quarter, or a month.
Note:There are two types of independent dimensions: continuous and discrete. The members in a continuous dimension reflect continuity. For example, week, month, and quarter reflect the continuity in a time dimension. The members in a discrete dimension do not imply continuity. For example, California, Texas, and Ohio in a market dimension do not have a relationship based on continuity.
As another example, consider this scenario: The sales representative for a client changes in midyear. Customer sales totals and sales representative assignments over six months are as follows:
Table 26. Varying Attribute Example: Sales Representative Changing Over TimeMarch April May June July August 4000 6000 2000 1000 1000 7000 Jones Jones Jones Smith Smith Smith
In this example, Sales Representative is the varying attribute. Data retrievals show that the sales representative Jones sold the customer a total of $12,000 worth of products from March through May and the sales representative Smith then sold a total of $9,000 worth of products to the customer from June through August. Without the use of the varying attribute, the only known sales representative would be the current representative Smith to whom all sales ($21,000) would be credited.
Varying attributes offer alternate ways of grouping your members. For example, you can use color to group SKUs. In this scenario, the attribute dimension “Color” is associated with SUBSKU:
Product_H | |__Family | | | |__SKU | | | |__SUBSKU | |_Color | |__SUBSKU
When Color is set as a varying attribute, the retrieval results would be similar to the following table:
Varying attributes must meet the following guidelines:
You can enable an outline to support varying attributes. You can define attribute dimensions to function as varying attributes. You can also edit varying attributes to reflect the type of information you need.- They must have multiple chains.
- Leaf levels must match.
Varying attributes are supported for aggregate and block storage databases. You implement varying attributes at the database level.
Use the following workflow to enable and use varying attributes:
- In the outline properties, enable varying attributes.
- In the member properties for the base dimension, go to the Attributes tab and identify the independent dimension (the dimension upon which varying attributes depend).For example, if the sales representative attribute association for Customer A gets changed in May, then Year would be the independent dimension.
- Specify the type of independent dimension: continuous or discrete. An example of a continuous independent dimension is one that is based on time. A discrete independent dimension has no continuity; for example, in a Market dimension, California, Texas, and Ohio do not have a relationship based on continuity.
- Associate the independent dimension with a varying attribute. Optionally select a range and an association mode.A range can be assigned for which the attribute association is true. For example, you can assign a time range for which the attribute association applies: Jane is an Engineer from July 2007–June 2008. An association mode tells Essbase how to handle conflicting associations of a varying attribute with its independent dimension. The following association modes are available: Overwrite, NoOverwrite, and Extend.
- Save and restructure the outline.
- Perform the following maintenance tasks as needed:
- Add new varying attribute associations to independent members (for example, add a new job title for an employee).
- Remove independent member associations.
- View existing independent dimension member associations (for example, see for which Months the company had a replacement sales manager).
- Disassociate attribute dimensions from base dimensions.
The following Report Writer functions are designed to work with varying attributes.
For more information, see the Oracle Essbase Technical Reference.- <PERSPECTIVE
- <WITHATTREX
- <ATTRIBUTEVA
The following MDX functions are designed to work with varying attributes.
For more information, see the Oracle Essbase Technical Reference.- AttributeEx
- WithAttrEx
- The WITH PERSPECTIVE keywords
Continuous independent dimensions must act as a single dimension, for example Year and Month. Unconnected continuous independent dimensions are not supported.
Continuous independent dimensions and members must be specified last.
Independent members must be stored, level 0 members.
Great
ReplyDelete