25 Attribute Dimension and Hierarchy Objects

Example 25-1 A Simple Attribute Dimension

An attribute dimension may be as simple as a list of attributes and levels defined only with key attributes. This example creates an attribute dimension that specifies as attributes only the YEAR_ID, QUARTER_ID, and MONTH_ID columns from the TIME_DIM table.

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;

For a description of the TIME_DIM table, see About the Data and Scripts for Examples.

Each of the _ID columns in the TIME_DIM table is included in the attribute list. By default, the name of the attribute is the name of the dimension table column name. You can provide a different name for the attribute by using the AS alias clause in the definition.

Levels are created for each attribute using the KEY property, which is the only required property for a level.

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';

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:

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.

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:

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.

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.

The level members are now sorted by end dates.

Example 25-5 Create the PRODUCT_ATTR_DIM Attribute Dimension

This example creates the product_attr_dim attribute dimension. The level clauses specify keys and alternate keys.

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';

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.