Handling Attribute Classification Oracle Tips by Burleson Consulting

The Data Warehouse Development Life Cycle

Oracle Data Warehouse DesignHandling Attribute Classification

One of the foremost problems in a data warehouse is the design of the non-fact data attributes. 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 that can be used to implement them within a data warehouse.

* Choose-one Attributes--This would be the case with mutually exclusive attributes such as sex, level of education, category of income, and so on. In most cases, a flag column would be used to represent these data attributes, and Oracle check constraints would be 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, a check constraint can be added to the table definition to ensure the validity of the region column.

* Finite Value Attributes--These attributes contain specific values that cannot be categorized by range values. Examples include last_name, street_address, and phone_number.

* Hierarchical Attributes--This is 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 superclasses up the tree.

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.