Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Organizing classification attributes for Oracle data warehouses

Oracle Tips by Burleson Consulting
Jan 17, 2001

When building an Oracle data warehouse, it is important to properly organize all of the classification information about the facts in the warehouse. This article will examine the basic techniques for identifying and organizing classification attributes for any Oracle warehouse.

Basic data warehouse table for Oracle8i

In a basic data warehouse, the goal is to capture information about some relevant fact. These facts are commonly kept according to a time dimension.

Examples of commonly used facts for a warehouse include:

  • The number of units sold each day.
  • The average sale price per month.
  • The number of widgets manufactured per day.

In addition to the basic facts and dates, the data warehouse will also collect information about attributes of the facts. It is these attributes that make the data warehouse function to provide end users with insights about patterns in the warehouse.

Figure A shows a sample fact table.

Figure A

A fact table is created with the basic fact and all keys relating to the classification of the fact. From this fact table, we see the basic fact is number_of_units_sold. In addition, we see keys to other information about the sale, namely the date, the key to the store where the sales were made, and the SKU (stock keeping unit) number of the items. By itself, the fact table does not provide much useful information about the sale. However, when combined with additional attributes, we can see a great deal of information about a sale.

Let's begin this discussion with a basic review of attribute classification techniques.

Handling attribute classification
One of the foremost problems in a data warehouse is the creation and maintenance of the nonfact data attributes. A nonfact attribute is a table column that serves to describe the fact. In all cases, attributes will either be choose-one, finite value, or hierarchical. Let's discuss each of these data attributes and the design techniques you can use to implement them within a data warehouse.

Choose-one attribute
You implement a choose-one attribute in the case of mutually exclusive attributes, such as gender, level of education, category of income, and so on. In most cases, a flag column is used to represent these data attributes, and Oracle check constraints are used to enforce the valid values for the flag. The check constraint validates incoming columns at row insert time. For example, rather than having an application verify that all occurrences of region are North, South, East, or West, you can add a check constraint to the table definition to ensure the validity of the region column.

Finite value attributes
Finite value attributes contain specific values that can't be categorized by range values. Examples include last_name, street_address, and phone_number.

Hierarchical attributes
Hierarchical attributes are the most challenging to represent in a data warehouse. Within a classification tree, each branch may have many levels, and once an entity has been classified, it should automatically participate in all other classes it participates in.

A practical example of attribute classification
To illustrate, let's take an example of a data warehouse for a supermarket.

In a supermarket warehouse, buyers are encouraged to get "VIP cards" in order to get discounts on products. When they apply for the VIP card, the supermarket collects their demographic information, including their age, income, education, and housing data.

At sale time, the VIP card cross-references the purchases with the demographics of the person who made the purchase. After recording the sales of the items and the person who purchased the items, we're ready to store and analyze the data in our warehouse.

The main purpose of supermarket data warehouses is to target promotions and mail advertising. By knowing the propensity of a specific class of buyers to purchase a specific class of product, the supermarket can target potential customers with great accuracy. But how do we get started?

Let's assume that I go to the supermarket and buy a jar of prune juice. By itself, this sale is not terribly interesting, but when we view the sale in the context of the attribute data, we see that there are many opportunities for summarizing the data. In addition to the single attribute items, such as age and gender, we also see data warehouse attributes that can be used to create a hierarchical structure:

  1. Person attribute hierarchy:
  • Education:
    College education
    Bachelor's degree
    Master's degree
  • Job:
    Computer Science
  1. Item hierarchy:
  • Edible items
    Bottled juices

Classification hierarchies are very important for the Oracle data warehouse because they allow the correlation of distinct classes of data. It is quite easy to tabulate the prune juice buying habits for people with master's degrees, but what if we want to make global correlations on buying behaviors for entire classes of items?

For example, with this hierarchy, we could perform the following analysis:

  1. Cross-reference buying preferences for fruit products by the job description of the buyer
  2. Cross-reference the buying preferences for juices by the education of the buyer

From these examples, it should be clear that just knowing the basic attributes for a fact is not enough—we must be able to classify the facts into a hierarchy.

Returning to our example from the supermarket, we remember that while the customer entity has many attributes, each attribute is distinct and can be represented independently of other attributes.

The Oracle table definition for the fact table might include the following customer attributes:

. . .
highest_education number(2)
CONSTRAINT highest_education_check
CHECK (highest_education between 0 and 20);
size_of_immediate_family number(2)
CONSTRAINT family_check
CHECK (size_of_immediate_family between 0 and 20);
number_of_children number(2)
CONSTRAINT children_check
CHECK (number_of_children between 0 and 20);
own_or_rent_home char(1)
CONSTRAINT own_check
CHECK (own_or_rent in 'U','O','R');
yearly_income_class number(1)
CONSTRAINT income_check
CHECK (yearly_income_class between 1 and 5);

Here, we've used numeric abbreviations to reduce space in the fact table. In this case, a total of 8 bytes would be added to the fact table to provide fast reference to information about the type of customer who made the transaction. Also note that there are provisions for unknown values for the customer, as would be the case when a customer did not use a VIP card—a "U" for unknown character values and a zero for unknown numeric values.

Now, queries from end users desiring transaction information according to the type of customer are easy to respond to because the fact table does not need to be joined with the customer table. But how can we identify the product classifications? Because the product classifications are hierarchical in nature, we need to develop another mechanism for representing them in our fact table.



If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.