10 Dimensions

This customer dimension contains a single hierarchy with a geographical r ollup, with arrows drawn from the child level to the parent level, as shown in Figure 10-1.

Each arrow in this graph indicates that for any child there is one and only one parent. For example, each city must be contained in exactly one state and each state must be contained in exactly one country. States that belong to more than one country, or that belong to no cou ntry, violate hierarchical integrity. Hierarchical integrity is necessary for the correct operation of management functions for mater ialized views that include aggregates.

For example, you can declare a dimension products_dim, which contains leve ls product, subcategory, and category:

CREATE DIMENSION products_dim
       LEVEL product           IS
(products.prod_id)
       LEVEL subcategory       IS (products.prod_subcategory)
       LEVEL category          IS (products.prod_cat
egory) ...

Each level in the dimension must correspond to one or more columns in a table in the database. Thus, level pro duct is identified by the column prod_id in the products table and level subcategory is identified by a column called prod_subcategory in the same table.

In this example, the database tables are denormalized and all the columns exist in the same table. However, this is not a prerequisite for creating dimensions. "Using Normalized Dimension Tabl es" shows how to create a dimension customers_dim that has a normalized schema design using the JOIN KEY clause.

The next step is to declare the relationship between the levels with the HIERARCHY stateme nt and give that hierarchy a name. A hierarchical relationship is a functional dependency from one level of a hierarchy to the next l evel in the hierarchy. Using the level names defined previously, the CHILD OF relationship denotes that eac h child's level value is associated with one and only one parent level value. The following statement declares a hierarchy prod _rollup and defines the relationship between products, subcategory, and category.

HIERARCHY prod
_rollup 
 (product         CHILD OF
  subcategory     CHILD OF
  category)

In addition to the 1:n hierarchic al relationships, dimensions also include 1:1 attribute relationships between the hierarchy levels and their dependent, determined dimension attributes. For example, the dimension times_dim, as defined in Oracle Database Sample Schemas, has columns fiscal_month_desc, fiscal_m onth_name, and days_in_fiscal_month. Their relationship is defined as follows:

LEVE
L fis_month   IS TIMES.FISCAL_MONTH_DESC
...
ATTRIBUTE fis_month DETERMINES
      (fiscal_month_name, days_in_fiscal_month)

The ATTRIBUTE ... DETERMINES clause relates fis_month to fiscal_month_name and days_in_fiscal_month. Note that this is a unidirectional determination. It is only guaranteed , that for a specific fiscal_month, for example, 1999-11, you will find exactly one matching values for fiscal_month_name, for example, November and days_in_fiscal_month, for example, 28. You cannot de termine a specific fiscal_month_desc based on the fiscal_month_name, which is November for eve ry fiscal year.

In this example, suppose a query were issued that queried by fiscal_month_name instead of f iscal_month_desc. Because this 1:1 relationship exists between the attribute and the level, an already aggregated materialized view containing fiscal_month_desc can be joined back to the dimension information and used to identify the data.

A sample dimension definition follows:

CREATE
DIMENSION products_dim
    LEVEL product           IS (products.prod_id)
    LEVEL subcategory       IS (products.prod_subcategory)
   LEVEL category          IS (products.prod_category)
    HIERARCHY prod_rollup (
         product         CHILD OF
         subcate
gory     CHILD OF
         category)
    ATTRIBUTE product DETERMINES
     (products.prod_name, products.prod_desc,
      prod_weight
_class, prod_unit_of_measure,
      prod_pack_size, prod_status, prod_list_price, prod_min_price)
    ATTRIBUTE subcategory DETERMINE
S
     (prod_subcategory, prod_subcategory_desc)
    ATTRIBUTE category DETERMINES
     (prod_category, prod_category_desc);

Alternatively, the extended_attribute_clause could have been used instead of the attribute_clause , as shown in the following example:

CREATE DIMENSION products_dim
    LEVEL product
    IS (products.prod_id)
    LEVEL subcategory       IS (products.prod_subcategory)
    LEVEL category          IS (products.prod_ca
tegory)
    HIERARCHY prod_rollup (
         product         CHILD OF
         subcategory     CHILD OF
         category
        )
   ATTRIBUTE product_info LEVEL product DETERMINES
     (products.prod_name, products.prod_desc,
      prod_weight_class, prod_unit_o
f_measure,
      prod_pack_size, prod_status, prod_list_price, prod_min_price)
    ATTRIBUTE subcategory DETERMINES
     (prod_subcat
egory, prod_subcategory_desc)
    ATTRIBUTE category DETERMINES
     (prod_category, prod_category_desc);

The design, cre ation, and maintenance of dimensions is part of the design, creation, and maintenance of your data warehouse schema. Once the dimensi on has been created, check that it meets these requirements:

• There must be a 1:n relationship between a parent and children. A parent can have one or more children, but a child can have only one parent.

• There must be a 1:1 attribute relationship between hierarchy levels and their dependent dimension attributes. For example, if there is a c olumn fiscal_month_desc, then a possible attribute relationship would be fiscal_month_desc to fiscal_ month_name.

• If the columns of a parent level and child level are in different relations, then the connection between them also requires a 1:n join relationship. Each row of the child table must join with one and only one row of th e parent table. This relationship is stronger than referential integrity alone, because it requires that the child join key must be n on-null, that referential integrity must be maintained from the child join key to the parent join key, and that the parent join key m ust be unique.

• You must ensure (using database constraints if necessary) that the columns of each hierar chy level are non-null and that hierarchical integrity is maintained.

• The hierarchies of a dimension can overlap or be disconnected from each other. However, the columns of a hierarchy level cannot be associated with more than one dimens ion.

• Join relationships that form cycles in the dimension graph are not supported. For example, a hierar chy level cannot be joined to itself either directly or indirectly.