An attribute dimension specifies a data source, attributes, and levels; a hierarchy organizes the levels hierarchically.

An attribute dimension specifies the data source it is using and specifies columns of that source as its attributes. It specifies levels for some or all of the attributes and determines attribute relationships between levels.

A hierarchy defines the hierarchical relationships between the levels of an attribute dimension. Attribute dimensions and hierarchies provide the dimension members for analytic view objects.

Most metadata related to dimensions and hierarchies is defined in the attribute dimension. A hierarchy inherits all of the metadata of the attribute dimension it uses. This allows the metadata for attributes and levels to be reused in many hierarchies, promoting consistency and simplifying the definition of the hierarchy.

CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
DIMENSION TYPE TIME  -- TYPE TIME enables time series calculations
USING time_dim       -- References the TIME_DIM table
ATTRIBUTES           -- A list of table columns to be used as attributes
 (year_id,
  quarter_id,
  month_id)
LEVEL MONTH          -- A level
  LEVEL TYPE MONTHS  -- The level type
  KEY month_id       -- Attribute with unique values
LEVEL QUARTER
  LEVEL TYPE QUARTERS
  KEY quarter_id
LEVEL YEAR
  LEVEL TYPE YEARS
  KEY year_id;

Example 25-2 A Simple Hierarchy

CREATE OR REPLACE HIERARCHY time_hier  -- Hierarchy name
USING time_attr_dim      -- Refers to the TIME_ATTR_DIM attribute dimension
 (month CHILD OF         -- Levels in the attribute dimension
  quarter CHILD OF
  year);

The hierarchy has columns for each attribute of the attribute dimension and for its hierarchical attributes.

SELECT column_name from ALL_HIER_COLUMNS WHERE HIER_NAME = 'TIME_HIER';

Description of the illustration time_hier_columns.png

The following selects the attribute columns and some of the hierarchical columns from TIME_HIER when TIME_ATTR_DIM is the attribute dimension defined in Example 25-1.

SELECT year_id, quarter_id, month_id,
       member_name, member_unique_name
       member_caption, member_description
  FROM time_hier
  ORDER BY hier_order;

An excerpt from the query results are:

Description of the illustration time_hier_col_subset.png

Attribute dimension attributes typically reference columns from a source table or view. Hierarchical attributes provide information about the members of a hierarchy.

In an attribute dimension, attributes specify the columns of the source table or view to reference. The default name of the attribute is the name of the table column. You may provide a different name for an attribute by using syntax similar to SQL SELECT clause aliases. You define levels using attributes and you define the relationships between attributes using levels. Attributes appear as columns in hierarchies, depending on the levels that the hierarchy includes and on the defined attribute relationships of the levels.

The hierarchical attributes are the following:

• DEPTH is the level depth of the hierarchy member; the ALL level is at depth 0 (zero)

• HIER_ORDER is the order of the member in the hierarchy

• IS_LEAF is a boolean value that indicates whether the member is at the lowest (leaf) level of the hierarchy

• LEVEL_NAME is the name of the level in the definition of the attribute dimension

• MEMBER_NAME is the name of the member in the definition of the attribute dimension

• MEMBER_CAPTION is NULL unless you specify values for it in the definition of the attribute dimension or the hierarchy

• MEMBER_DESCRIPTION is NULL unless you specify values for it in the definition of the attribute dimension or the hierarchy

• MEMBER_UNIQUE_NAME is a name that is guaranteed to be unique in the hierarchy; it is a concatenation of level name, ancestors, and key attribute values

• PARENT_LEVEL_NAME is the name of level that is the parent of the current member

• PARENT_UNIQUE_NAME is the MEMBER_UNIQUE_NAME of the parent of the current member

The hierarchical attribute value is composed of the level and the lineage. The lineage includes the member’s key value. Each component of the lineage is enclosed in square brackets, and the components are separated by periods. If a component value contains a right square bracket, it is represented using two right square brackets.

Example 25-3 Providing Values for Some Hierarchical Attributes

This is the excerpt from the results of the query of the hierarchy based on the simple attribute dimension in About Attribute Dimensions and Hierarchies.

Description of the illustration time_hier_col_subset.png

While the hierarchy is functional, it lacks some important features. Note that the MEMBER_NAME column might not be easily readable, and the MEMBER_CAPTION and MEMBER_DESCRIPTION columns do not return data.

This new definition of the time_attr_dim attribute dimension includes the _NAME columns from the TIME_DIM table. In the definitions of the levels, it specifies attributes that contain values for the hierarchical attributes MEMBER_NAME, MEMBER_CAPTION, and MEMBER_DESCRIPTION. This definition provides a hierarchy that uses the attribute dimension with descriptive values for the level members.

CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
DIMENSION TYPE TIME
USING time_dim
ATTRIBUTES
 (year_id,
  year_name,
  quarter_id,
  quarter_name,
  month_id,
  month_name,
  month_long_name)
LEVEL MONTH
  LEVEL TYPE MONTHS
  KEY month_id
  MEMBER NAME month_name
  MEMBER CAPTION month_name
  MEMBER DESCRIPTION month_long_name
LEVEL QUARTER
  LEVEL TYPE QUARTERS
  KEY quarter_id
  MEMBER NAME quarter_name
  MEMBER CAPTION quarter_name
  MEMBER DESCRIPTION quarter_name
LEVEL YEAR
  LEVEL TYPE YEARS
  KEY year_id
  MEMBER NAME year_name
  MEMBER CAPTION year_name
  MEMBER DESCRIPTION year_name;

This statement selects the attribute columns and some of the hierarchical columns from the TIME_HIER hierarchy.

SELECT year_id, quarter_id, month_id,
       member_name, member_unique_name,
       member_caption, member_description
  FROM time_hier
  ORDER BY hier_order;

An excerpt from the query results are:

Description of the illustration time_hier_col_subset_hier_attrs.png

The ordering of time periods is not yet correct for reporting on time series calculations; for example, February comes before January. For an example of specifying a sort order for a level, see Order Levels.

You can specify the order of attribute dimension level members.

You may use the ORDER BY clause of an attribute dimension level definition to specify an order for members of the level. By default, values of an attribute dimension level are sorted alphabetically by the MEMBER_NAME value. If you do not specify a member name, the level is ordered by its KEY attribute value.

The ORDER BY clause also specifies whether NULL values are first or last in the order. You may specify MIN or MAX expression if the attribute is not determined by the level, with the default being MIN.

Example 25-4 Add End Dates

This example adds end date attributes to the definition of the time_attr_dim attribute dimension.

CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
DIMENSION TYPE TIME
USING time_dim
ATTRIBUTES
 (year_id,
  year_name,
  year_end_date,
  quarter_id,
  quarter_name,
  quarter_end_date,
  month_id,
  month_name,
  month_long_name,
  month_end_date)
LEVEL MONTH
  KEY month_id
  MEMBER NAME month_name
  MEMBER CAPTION month_name
  MEMBER DESCRIPTION month_long_name
  ORDER BY month_end_date
LEVEL QUARTER
  KEY quarter_id
  MEMBER NAME quarter_name
  MEMBER CAPTION quarter_name
  MEMBER DESCRIPTION quarter_name
  ORDER BY quarter_end_date
LEVEL YEAR
  KEY year_id
  MEMBER NAME year_name
  MEMBER CAPTION year_name
  MEMBER DESCRIPTION year_name
  ORDER BY year_end_date;

This is the definition of the time_hier hierarchy.

CREATE OR REPLACE HIERARCHY time_hier
USING time_attr_dim
 (month CHILD OF
  quarter CHILD OF
  year);

This query includes the hierarchy order attribute.

SELECT year_id,
  quarter_id,
  month_id,
  member_name,
  hier_order
FROM time_hier
ORDER BY hier_order;

This is an excerpt from the query results.

Description of the illustration time_hier_col_subset_orderby.png

The level members are now sorted by end dates.

A level key attribute specifies the data source of the level members.

An attribute dimension level specifies key and optional alternate key attributes that provide the members of the level.

A level must have a key, which is defined by a single attribute, or by multiple attributes for a compound key. Each distinct value for the key defines an attribute dimension member at that level.

A level can also have one or more alternate keys. An alternate key must have a one-to-one relationship with the level key: an attribute specified as an alternate key must have a unique value for every member of the level key attribute.

CREATE OR REPLACE ATTRIBUTE DIMENSION product_attr_dim
USING product_dim 
ATTRIBUTES
 (department_id,
  department_name,
  category_id,
  category_name)
LEVEL DEPARTMENT
  KEY department_id
  ALTERNATE KEY department_name
  MEMBER NAME department_name
  MEMBER CAPTION department_name
  ORDER BY department_name
LEVEL CATEGORY
  KEY category_id
  ALTERNATE KEY category_name
  MEMBER NAME category_name
  MEMBER CAPTION category_name
  ORDER BY category_name
  DETERMINES(department_id)
ALL MEMBER NAME 'ALL PRODUCTS';

You can specify that an attribute of a level determines the values of other attributes.

You can use the DETERMINES clause of an attribute dimension level definition to specify a relationship between the level key attribute and other attributes. When there is only one value of an attribute for each value of another attribute, the value of one attribute determines the value of another. For example, there is only one value of QUARTER_ID for each value of MONTH_ID; MONTH_ID determines QUARTER_ID.

An attribute determined by a level is included in a hierarchy that uses the attribute dimension. An attribute specified in a DETERMINES clause can have the same value for different level members. A level implicitly determines its key and alternate key attributes, although, unlike the attributes in a DETERMINES clause, those attributes must have unique values.

The relationships specified by a DETERMINES clause can do the following:

• Change the number of rows returned by a hierarchy

• Control whether certain attributes return data for certain rows

• Simplify the SQL that is generated when an analytic view is queried

Specifying determined attributes helps a hierarchy or analytic view to determine a unique value for a member. If an attribute is determined by a level, you do not need to explicitly specify in a query the attribute value that identifies the relationship of the determined attribute to the hierarchy member. For example, a QUALIFY calculation requires a uniquely identified hierarchy member. If you omit attributes from a DETERMINES clause, then in an analytic view measure that uses a QUALIFY calculation, you must explicitly specify those attributes to identify the unique member.

The relationship of determined attributes to key and alternate key attributes is not validated or enforced in an attribute dimension or in a hierarchy that uses the attribute dimension. To validate the relationship, use the PL/SQL procedure DBMS_HIERARCHY.VALIDATE_HIERARCHY, which inspects the data in the source table or view.

When using a DETERMINES clause, consider the following:

• Lower levels inherit the determined attributes of ancestor levels; therefore, it is a good practice to include the key attribute value of the parent level in the DETERMINES clause of a lower level whenever the key of the lower level determines the value of the parent level.

• Values of MEMBER NAME, MEMBER CAPTION, MEMBER DESCRIPTION and ORDER BY properties are assumed to be determined by the KEY attribute value. You do not need to include attributes for those properties in a DETERMINES clause. You should be sure, however, that the data for those attributes has only one value for each value of the KEY attribute.

Example 25-6 Add DETERMINES Clauses

This example adds the DETERMINES clause to the levels of time_attr_dim.

CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
DIMENSION TYPE TIME
USING time_dim
ATTRIBUTES
 (year_id,
  year_name,
  year_end_date,
  quarter_id,
  quarter_name,
  quarter_end_date,
  month_id,
  month_name,
  month_long_name,
  month_end_date)
LEVEL MONTH
  LEVEL TYPE MONTHS
  KEY month_id
  MEMBER NAME month_name
  MEMBER CAPTION month_name
  MEMBER DESCRIPTION month_long_name
  ORDER BY month_end_date
  DETERMINES (quarter_id)
LEVEL QUARTER
  LEVEL TYPE QUARTERS
  KEY quarter_id
  MEMBER NAME quarter_name
  MEMBER CAPTION quarter_name
  MEMBER DESCRIPTION quarter_name
  ORDER BY quarter_end_date
  DETERMINES (year_id)
LEVEL YEAR
  LEVEL TYPE YEARS
  KEY year_id
  MEMBER NAME year_name
  MEMBER CAPTION year_name
  MEMBER DESCRIPTION year_name
  ORDER BY year_end_date;

Select the LEVEL_NAME, _ID, and MEMBER_UNIQUE_NAME columns from the TIME_HIER hierarchy.

SELECT level_name,
  year_id,
  quarter_id,
  month_id,
  member_unique_name
FROM time_hier
ORDER BY hier_order;

The hierarchy now knows the relationship between the months, quarters, and years attributes, as shown in the following results of the preceding query. The MEMBER_UNIQUE_NAME values are now created from only the level name and the KEY attribute value; they no longer must include the full lineage as seen in Example 25-3.

Description of the illustration time_hier_col_subset_determines.png