Dimensions 10-3 In spite of the benefits of dimensions, you must not create dimensions in any schema that does not fully satisfy the dimensional relationships described in this chapter.
Incorrect results can be returned from queries otherwise.
Creating Dimensions
Before you can create a dimension object, the dimension tables must exist in the database possibly containing the dimension data. For example, if you create a customer dimension, one or more tables must exist that contain the city, state, and country information. In a star schema data warehouse, these dimension tables already exist. It is therefore a simple task to identify which ones will be used.
Now you can draw the hierarchies of a dimension as shown in Figure 10–1. For example, city is a child of state (because you can aggregate city-level data up to state), and country. This hierarchical information will be stored in the database object dimension.
In the case of normalized or partially normalized dimension representation (a dimension that is stored in more than one table), identify how these tables are joined.
Note whether the joins between the dimension tables can guarantee that each child-side row joins with one and only one parent-side row. In the case of denormalized dimensions, determine whether the child-side columns uniquely determine the parent-side (or attribute) columns. If you use constraints to represent these relationships, they can be enabled with the NOVALIDATE and RELY clauses if the relationships represented by the constraints are guaranteed by other means.
You may want the capability to skip NULL levels in a dimension. An example of this is with Puerto Rico. You may want Puerto Rico to be included within a region of North America, but not include it within the state category. If you want this capability, use the SKIPWHENNULL clause. See the sample dimension later in this section for more information and Oracle Database SQL Reference for syntax and restrictions.
You create a dimension using either the CREATEDIMENSION statement or the Dimension Wizard in Oracle Enterprise Manager. Within the CREATEDIMENSION statement, use the LEVEL clause to identify the names of the dimension levels.
This customer dimension contains a single hierarchy with a geographical rollup, with arrows drawn from the child level to the parent level, as shown in Figure 10–1 on page 10-2.
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 violate hierarchical integrity. Also, you must use the SKIP WHEN NULL clause if you want to include cities that do not belong to a state, such as Washington D.C. Hierarchical integrity is necessary for the correct operation of management functions for materialized views that include aggregates.
For example, you can declare a dimension products_dim, which contains levels product, subcategory, and category:
See Also: Chapter 17, "Basic Query Rewrite" for further details regarding query rewrite and the Oracle Database Performance Tuning Guide for further details regarding the SQL Access Advisor
See Also: Oracle Database SQL Reference for a complete description of the CREATEDIMENSION statement
Creating Dimensions
10-4 Oracle Database Data Warehousing Guide CREATE DIMENSION products_dim
LEVEL product IS (products.prod_id)
LEVEL subcategory IS (products.prod_subcategory) LEVEL category IS (products.prod_category) ...
Each level in the dimension must correspond to one or more columns in a table in the database. Thus, level product 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 Tables" on page 10-8 shows how to create a dimension customers_dim that has a normalized schema design using the JOINKEY clause.
The next step is to declare the relationship between the levels with the HIERARCHY statement and give that hierarchy a name. A hierarchical relationship is a functional dependency from one level of a hierarchy to the next level in the hierarchy. Using the level names defined previously, the CHILDOF relationship denotes that each 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 hierarchical 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_month_name, and days_in_fiscal_month. Their relationship is defined as follows:
LEVEL 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 determine a specific fiscal_month_desc based on the fiscal_month_name, which is November for every fiscal year.
In this example, suppose a query were issued that queried by fiscal_month_name instead of fiscal_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)
See Also: Chapter 17, "Basic Query Rewrite" for further details of using dimensional information
Creating Dimensions
Dimensions 10-5 LEVEL subcategory IS (products.prod_subcategory) [SKIP WHEN NULL]
LEVEL category IS (products.prod_category) HIERARCHY prod_rollup (
product CHILD OF subcategory 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 DETERMINES
(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_category)
ATTRIBUTE product_info LEVEL 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 DETERMINES
(prod_subcategory, prod_subcategory_desc) ATTRIBUTE category DETERMINES
(prod_category, prod_category_desc);
The design, creation, and maintenance of dimensions is part of the design, creation, and maintenance of your data warehouse schema. Once the dimension has been created, verify 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 column fiscal_
month_desc, then a possible attribute relationship would be fiscal_month_
desc to fiscal_month_name. For skip NULL levels, if a row of the relation of a skip level has a NULL value for the level column, then that row must have a NULL value for the attribute-relationship column, too.
■ 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 the parent table unless you use the SKIPWHENNULL clause. This relationship is stronger than referential integrity alone, because it requires that the child join key must be non-null, that referential integrity must be maintained from the child join key to the parent join key, and that the parent join key must be unique.
Creating Dimensions
10-6 Oracle Database Data Warehousing Guide
■ You must ensure (using database constraints if necessary) that the columns of each hierarchy level are non-null unless you use the SKIP WHEN NULL clause and that hierarchical integrity is maintained.
■ An optional join key is a join key that connects the immediate non-skip child (if such a level exists), CHILDLEV, of a skip level to the nearest non-skip ancestor (again, if such a level exists), ANCLEV, of the skip level in the hierarchy. Also, this joinkey is allowed only when CHILDLEV and ANCLEV are defined over different relations.
■ 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 dimension.
■ Join relationships that form cycles in the dimension graph are not supported. For example, a hierarchy level cannot be joined to itself either directly or indirectly.