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. 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 SKIP WHEN NULL 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 CREATE DIMENSION statement or the
Dimension Wizard in Oracle Enterprise Manager. Within the CREATE DIMENSION
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

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:

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 pageshows 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
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 CHILD OF 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)

Posted in: Dimension