9
Dimensions

The following sections will help you create and manage a data warehouse:

• What are Dimensions?
• Creating Dimensions
• Viewing Dimensions
• Using Dimensions with Constraints
• Validating Dimensions
• Altering Dimensions
• Deleting Dimensions
• Using the Dimension Wizard

What are Dimensions?

A dimension is a structure that categorizes data in order to enable users to answer business questions. Commonly used dimensions are customers, products, and time. For example, each sales channel of a clothing retailer might gather and store data regarding sales and reclamations of their Cloth assortment. The retail chain management can build a data warehouse to analyze the sales of its products across all stores over time and help answer questions such as:

• What is the effect of promoting one product on the sale of a related product that is not promoted?
• What are the sales of a product before and after a promotion?
• How does a promotion affect the various distribution channels?

The data in the retailer's data warehouse system has two important components: dimensions and facts. The dimensions are products, customers, promotions, channels, and time. One approach for identifying your dimensions is to review your reference tables, such as a product table that contains everything about a product, or a promotion table containing all information about promotions. The facts are sales (units sold) and profits. A data warehouse contains facts about the sales of each product at on a daily basis.

A typical relational implementation for such a data warehouse is a Star Schema. The fact information is stored in the so-called fact table, whereas the dimensional information is stored in the so-called dimension tables. In our example, each sales transaction record is uniquely defined as for each customer, for each product, for each sales channel, for each promotion, and for each day (time).

In Oracle9i, the dimensional information itself is stored in a dimension table. In addition, the database object dimension helps to organize and group dimensional information into hierarchies. This represents natural 1:n relationships between columns or column groups (the levels of a hierarchy) that cannot be represented with constraint conditions. Going up a level in the hierarchy is called rolling up the data and going down a level in the hierarchy is called drilling down the data. In the retailer example:

• Within the time dimension, months roll up to quarters, quarters roll up to years, and years roll up to all years.
• Within the product dimension, products roll up to subcategories, subcategories roll up to categories, and categories roll up to all products.
• Within the customer dimension, customers roll up to city. Then cities rolls up to state. Then states roll up to country. Then countries roll up to subregion. Finally, subregions roll up to region, as shown in Figure 9-1.

Figure 9-1 Sample Rollup for a Customer Dimension

Text description of the illustration dwhsg072.gif

Data analysis typically starts at higher levels in the dimensional hierarchy and gradually drills down if the situation warrants such analysis.

Dimensions do not have to be defined, but spending time creating them can yield significant benefits, because they help query rewrite perform more complex types of rewrite. They are mandatory if you use the Summary Advisor (a GUI tool for materialized view management) to recommend which materialized views to create, drop, or retain.

You must not create dimensions in any schema that does not satisfy these relationships. 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, 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 9-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. These constraints can be enabled with the NOVALIDATE and RELY clauses if the relationships represented by the constraints are guaranteed by other means.

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, as shown in Figure 9-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 country, violate hierarchical integrity. 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" 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 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 statements declare a hierarchy prod_rollup and define 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 Oracle9i 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)
        LEVEL subcategory       IS (products.prod_subcategory)
        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_subcat_desc)
        ATTRIBUTE category DETERMINES
        (prod_category, prod_cat_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, 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 column 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 the parent table. 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.
• You must ensure (using database constraints if necessary) that the columns of each hierarchy 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 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.

  Note: The information stored with a dimension objects is only declarative. The previously discussed relationships are not enforced with the creation of a dimension object. You should validate any dimension definition with the DBMS_MVIEW.VALIDATE_DIMENSION procedure, as discussed on "Validating Dimensions".

Multiple Hierarchies

A single dimension definition can contain multiple hierarchies. Suppose our retailer wants to track the sales of certain items over time. The first step is to define the time dimension over which sales will be tracked. Figure 9-2 illustrates a dimension times_dim with two time hierarchies.

Figure 9-2 times_dim Dimension with Two Time Hierarchies

Text description of the illustration dwhsg075.gif

From the illustration, you can construct the hierarchy of the denormalized time_dim dimension's CREATE DIMENSION statement as follows. The complete CREATE DIMENSION statement as well as the CREATE TABLE statement are shown in Oracle9i Sample Schemas.

CREATE DIMENSION times_dim
   LEVEL day         IS TIMES.TIME_ID
   LEVEL month       IS TIMES.CALENDAR_MONTH_DESC
   LEVEL quarter     IS TIMES.CALENDAR_QUARTER_DESC
   LEVEL year        IS TIMES.CALENDAR_YEAR
   LEVEL fis_week    IS TIMES.WEEK_ENDING_DAY
   LEVEL fis_month   IS TIMES.FISCAL_MONTH_DESC
   LEVEL fis_quarter IS TIMES.FISCAL_QUARTER_DESC
   LEVEL fis_year    IS TIMES.FISCAL_YEAR
   HIERARCHY cal_rollup    (
             day     CHILD OF
             month   CHILD OF
             quarter CHILD OF
             year
   )
   HIERARCHY fis_rollup    (
             day         CHILD OF
             fis_week    CHILD OF
             fis_month   CHILD OF
             fis_quarter CHILD OF
             fis_year
   ) <attribute determination clauses>...

Using Normalized Dimension Tables

The tables used to define a dimension may be normalized or denormalized and the individual hierarchies can be normalized or denormalized. If the levels of a hierarchy come from the same table, it is called a fully denormalized hierarchy. For example, cal_rollup in the times_dim dimension is a denormalized hierarchy. If levels of a hierarchy come from different tables, such a hierarchy is either a fully or partially normalized hierarchy. This section shows how to define a normalized hierarchy.

Suppose the tracking of a customer's location is done by city, state, and country. This data is stored in the tables customers and countries. The customer dimension customers_dim is partially normalized because the data entities cust_id and country_id are taken from different tables. The clause JOIN KEY within the dimension definition specifies how to join together the levels in the hierarchy. The dimension statement is partially shown in the following. The complete CREATE DIMENSION statement as well as the CREATE TABLE statement are shown in Oracle9i Sample Schemas.

CREATE DIMENSION customers_dim
       LEVEL customer  IS (customers.cust_id)
       LEVEL city      IS (customers.cust_city)
       LEVEL state     IS (customers.cust_state_province)
       LEVEL country   IS (countries.country_id)
       LEVEL subregion IS (countries.country_subregion)
       LEVEL region IS (countries.country_region)
       HIERARCHY geog_rollup (
                 customer        CHILD OF
                 city            CHILD OF
                 state           CHILD OF
                 country         CHILD OF
                 subregion       CHILD OF
                 region
       JOIN KEY (customers.country_id) REFERENCES country
        ) ...attribute determination clause;

Viewing Dimensions

Dimensions can be viewed through one of two methods:

• Using The DEMO_DIM Package
• Using Oracle Enterprise Manager

Using The DEMO_DIM Package

Two procedures allow you to display the dimensions that have been defined. First, the file smdim.sql, located under $ORACLE_HOME/rdbms/demo, must be executed to provide the DEMO_DIM package, which includes:

• DEMO_DIM.PRINT_DIM to print a specific dimension
• DEMO_DIM.PRINT_ALLDIMS to print all dimensions accessible to a user

The DEMO_DIM.PRINT_DIM procedure has only one parameter: the name of the dimension to display. The following example shows how to display the dimension TIMES_DIM.

SET SERVEROUTPUT ON;
EXECUTE DEMO_DIM.PRINT_DIM ('TIMES_DIM');

To display all of the dimensions that have been defined, call the procedure DEMO_DIM.PRINT_ALLDIMS without any parameters is illustrated as follows.

EXECUTE DBMS_OUTPUT.ENABLE(10000);
EXECUTE DEMO_DIM.PRINT_ALLDIMS;

Regardless of which procedure is called, the output format is identical. A sample display is shown here.

DIMENSION SH.PROMO_DIM
LEVEL CATEGORY IS SH.PROMOTIONS.PROMO_CATEGORY
LEVEL PROMO IS SH.PROMOTIONS.PROMO_ID
LEVEL SUBCATEGORY IS SH.PROMOTIONS.PROMO_SUBCATEGORY
HIERARCHY PROMO_ROLLUP ( PROMO
CHILD OF SUBCATEGORY
CHILD OF CATEGORY)
ATTRIBUTE CATEGORY DETERMINES SH.PROMOTIONS.PROMO_CATEGORY
ATTRIBUTE PROMO DETERMINES SH.PROMOTIONS.PROMO_BEGIN_DATE
ATTRIBUTE PROMO DETERMINES SH.PROMOTIONS.PROMO_COST
ATTRIBUTE PROMO DETERMINES SH.PROMOTIONS.PROMO_END_DATE
ATTRIBUTE PROMO DETERMINES SH.PROMOTIONS.PROMO_NAME
ATTRIBUTE SUBCATEGORY DETERMINES SH.PROMOTIONS.PROMO_SUBCATEGORY

Using Oracle Enterprise Manager

All of the dimensions that exist in the data warehouse can be viewed using Oracle Enterprise Manager. Select the Dimension object from within the Schema icon to display all of the dimensions. Select a specific dimension to graphically display its hierarchy, levels, and any attributes that have been defined.

Using Dimensions with Constraints

Constraints play an important role with dimensions. Full referential integrity is sometimes enabled in data warehouses, but not always. This is because operational databases normally have full referential integrity and you can ensure that the data flowing into your warehouse never violates the already established integrity rules.

Oracle recommends that constraints be enabled and, if validation time is a concern, then the NOVALIDATE clause should be used as follows:

ENABLE NOVALIDATE CONSTRAINT pk_time;

Primary and foreign keys should be implemented also. Referential integrity constraints and NOT NULL constraints on the fact tables provide information that query rewrite can use to extend the usefulness of materialized views.

In addition, you should use the RELY clause to inform query rewrite that it can rely upon the constraints being correct as follows:

ALTER TABLE time MODIFY CONSTRAINT pk_time RELY;

This information is also used for query rewrite.

Validating Dimensions

The information of a dimension object is declarative only and not enforced by the database. If the relationships described by the dimensions are incorrect, incorrect results could occur. Therefore, you should verify the relationships specified by CREATE DIMENSION using the DBMS_OLAP.VALIDATE_DIMENSION procedure periodically.

This procedure is easy to use and has only five parameters:

• Dimension name
• Owner name
• Set to TRUE to check only the new rows for tables of this dimension
• Set to TRUE to verify that all columns are not null
• Unique run ID obtained by calling the DBMS_OLAP.CREATE_ID procedure. The ID is used to identify the result of each run

The following example validates the dimension TIME_FN in the grocery schema

VARIABLE RID NUMBER;
EXECUTE DBMS_OLAP.CREATE_ID(:RID);
EXECUTE DBMS_OLAP.VALIDATE_DIMENSION ('TIME_FN', 'GROCERY', \
FALSE, TRUE, :RID);

If the VALIDATE_DIMENSION procedure encounters any errors, they are placed in a system table. The table can be accessed from the view SYSTEM.MVIEW_EXCEPTIONS. Querying this view will identify the exceptions that were found. For example:

SELECT * FROM SYSTEM.MVIEW_EXCEPTIONS
WHERE RUNID = :RID;
RUNID OWNER    TABLE_NAME  DIMENSION_NAME RELATIONSHIP BAD_ROWID
----- -------- ----------- -------------- ------------ ---------
678   GROCERY  MONTH       TIME_FN        FOREIGN KEY  AAAAuwAAJAAAARwAAA

However, rather than query this view, it may be better to query the rowid of the invalid row to retrieve the actual row that has violated the constraint. In this example, the dimension TIME_FN is checking a table called month. It has found a row that violates the constraints. Using the rowid, you can see exactly which row in the month table is causing the problem, as in the following:

SELECT * FROM month
WHERE rowid IN (SELECT bad_rowid 
                FROM SYSTEM.MVIEW_EXCEPTIONS 
                WHERE RUNID = :RID);

MONTH    QUARTER FISCAL_QTR YEAR FULL_MONTH_NAME MONTH_NUMB
-------- ------- ---------- ---- --------------- ----------
  199903   19981      19981 1998 March                    3

Finally, to remove results from the system table for the current run:

EXECUTE DBMS_OLAP.PURGE_RESULTS(:RID);

Altering Dimensions

You can modify the dimension using the ALTER DIMENSION statement. You can add or drop a level, hierarchy, or attribute from the dimension using this command.

Referring to the time dimension in Figure 9-2, you can remove the attribute fis_year, drop the hierarchy fis_rollup, or remove the level fiscal_year. In addition, you can add a new level called foyer as in the following:

ALTER DIMENSION times_dim DROP ATTRIBUTE fis_year;
ALTER DIMENSION times_dim DROP HIERARCHY fis_rollup;
ALTER DIMENSION times_dim DROP LEVEL fis_year;
ALTER DIMENSION times_dim ADD LEVEL f_year IS times.fiscal_year;

If you try to remove anything with further dependencies inside the dimension, Oracle rejects the altering of the dimension. A dimension becomes invalid if you change any schema object that the dimension is referencing. For example, if the table on which the dimension is defined is altered, the dimension becomes invalid.

To check the status of a dimension, view the contents of the column invalid in the ALL_DIMENSIONS data dictionary view.

To revalidate the dimension, use the COMPILE option as follows:

ALTER DIMENSION times_dim COMPILE;

Dimensions can also be modified using Oracle Enterprise Manager.

Deleting Dimensions

A dimension is removed using the DROP DIMENSION statement. For example:

DROP DIMENSION times_dim;

Dimensions can also be deleted using Oracle Enterprise Manager.

Using the Dimension Wizard

An alternative method for creating and viewing dimensions is to use Oracle Enterprise Manager, which graphically displays the dimension definition, thus making it easier to see the hierarchy and a dimension wizard is provided to facilitate easy definition of the dimension object.

The Dimension Wizard is automatically invoked whenever a request is made to create a dimension object in Oracle Enterprise Manager. You are then guided step by step through the information required for a dimension.

A dimension created using the Wizard can contain any of the attributes described in "Creating Dimensions", such as join keys, multiple hierarchies, and attributes. You might prefer to use the Wizard because it graphically displays the hierarchical relationships as they are being constructed. When it is time to describe the hierarchy, the Wizard automatically displays a default hierarchy based on the column values, which you can subsequently amend.

Managing the Dimension Object

The dimension object is located within the Warehouse section for a database. Selecting a specific dimension results in 5 sheets of information becoming available. The General Property sheet shown in Figure 9-3 displays the dimension definition in a graphical form.

Figure 9-3 Dimension General Property Sheet

Text description of the illustration dimgen.gif

The levels in the dimension can either be shown on the General Property sheet, or by selecting the Levels property sheet, levels can be deleted, displayed or new ones defined for this dimension as illustrated in Figure 9-4.

Figure 9-4 Dimension Levels Property Sheet

Text description of the illustration dimlevel.gif

By selecting the level name from the list on the left of the property sheet, the columns used for this level are displayed in the Selected Columns window in the lower half of the property sheet.

Levels can be added or removed by pressing the New or Delete buttons but they cannot be modified.

A similar property sheet to that for Levels is provided for the attributes in the dimension and is selected by clicking on the Attributes tab.

One of the main advantages of using Oracle Enterprise Manager to define the dimension is that the hierarchies can be easily displayed. Figure 9-5 illustrates the Hierarchy property sheet.

Figure 9-5 Dimension Hierarchy Property Sheet

Text description of the illustration dimhier2.gif

In Figure 9-5, you can see that the hierarchy called CAL_ROLLUP contains four levels where the top level is year, followed by quarter, month, and day.

You can add or remove hierarchies by pressing the New or Delete buttons but they cannot be modified.

Creating a Dimension

An alternative to writing the CREATE DIMENSION statement is to invoke the Dimension wizard, which guides you through 6 steps to create a dimension.

Step 1

First, you must define which type of dimension object is to be defined. If a time dimension is required, selecting the time dimension type ensures that your dimension is recognized as a time dimension that has specific types of hierarchies and attributes.

Step 2

Specify the name of your dimension and into which schema it should reside by selecting from the drop down list of schemas.

Step 3

The levels in the dimension are defined in Step 3 as shown in Figure 9-6.

Figure 9-6 Dimension Wizard: Define Levels

Text description of the illustration dimlevea.gif

First, give the level a name and then select the table from where the columns which define this level are located. Now, select one or more columns from the available list and using the > key move them into the Selected Columns area. Your level will now appear in the list on the left side of the property sheet.

To define another level, click the New button, or, if all the levels have been defined, click the Next button to proceed to the next step. If a mistake is made when defining a level, simply click the Delete button to remove it and start again.

Step 4

The levels in the dimension can also have attributes. Give the attribute a name and then select the level on which this attribute is to be defined and using the > button move it into the Selected Levels column. Now choose the column from the drop down list for this attribute.

Levels can be added or removed by pressing the New or Delete buttons but they cannot be modified.

Step 5

A hierarchy is defined as illustrated in Figure 9-7.

Figure 9-7 Dimension Wizard: Define Hierarchies

Text description of the illustration dimhierw.gif

First, give the hierarchy a name and then select the levels to be used in this hierarchy and move them to the Selected Levels column using the > button.

The level name at the top of the list defines the top of the hierarchy. Use the up and down buttons to move the levels into the required order. Note that each level will indent so you can see the relationships between the levels.

Step 6

Finally, the Summary screen is displayed as shown in Figure 9-8 where a graphical representation of the dimension is shown on the left side of the property sheet and on the right side the CREATE DIMENSION statement is shown. Clicking on the Finish button will create the dimension.

Figure 9-8 Dimension Wizard: Summary Screen

Text description of the illustration dimwizsa.gif