Table L.E.17.1 - A warehouse

The single key figure in this example is the "inventory level".

Please draw the Classic Star Schema for this example, create the dimension table "product", then build the Fact Constellation Schema.

Then you should draw this example's dimension tables partitioned and normalized.

Figure L.E.17.1 - The Classic Star Schema

To create dimension table "product" we have to make up some products and categories, each being a record in the table. Products are assigned to product categories by entering the categories in the corresponding fields of each "product"-record. The "category"-records contain a N/A sign (*) in the "product"-fields, the record representing the total aggregation has this sign in all fields, save the key and the level attribute:

Table L.E.17.2 - The content of dimension table "product"

The Fact Constellation Schema's dimension tables are similar to those of the Classic Star, only the level attribute is omitted. The quantity of fact tables (including the base fact table) can be calculated by multiplying the number of hierarchy levels of each dimension, neglecting the total aggregation. Therefore this example's Fact Constellation Schema consists of 18 fact tables (2x3x3):

Figure L.E.17.2 - The Fact Constellation Schema

Partitioning the Classic Star's dimension tables by the level attribute results in one table for each hierarchy level (in each dimension). All tables include the fields of the higher levels, the level attribute itself is omitted:

Figure L.E.17.3 - DT partitioned (left) and normalized (right)

Normalizing the dimension tables completely means that only the key of the next higher level is kept in each table:

Please note that the tables for the total aggregation level have been neglected in Figure L.E.17.3.