Tutorials -> Dimensional Modeling Tutorial

Dimension Tables - Key elements of a Dimension Table

Dimensional modeling allows only one table per dimension. But your OLTP data spans across multiple tables as described.  So we need de-normalize the OLTP schema and export into your Dimension Tables.

For example, for the location dimension, you achieve this by joining the three OLTP tables and inserting the data into the single Location table.

Your Location Table will look like this:
 

Location Dimension Table Schema
 

Field Name	Type
Dim_Id	INTEGER (4)
Loc_Code	VARCHAR (4)
Name	VARCHAR (50)
State_Name	VARCHAR (20)
Country_Name	VARCHAR (20)

All Dimension tables contain a key column called the dimension key. In this example Dim_Id is our dimension Id. This is the unique key into our Location dimension table.

The actual data in your Location Table may look like this

Location Dimension Table Data

Dim_Id	Loc_Code	Name	State_Name	Country_Name
1001	IL01	Chicago Loop	Illinois	USA
1002	IL02	Arlington Hts	Illinois	USA
1003	NY01	Brooklyn	New York	USA
1004	TO01	Toronto	Ontario	Canada
1005	MX01	Mexico City	Distrito Federal	Mexico

You may notice that some of the information is repeated in the above dimension table. The State Name and Country Name are repeated through out the table. You may feel that this is waste of data space and against the normalization principles. But in dimensional modeling this type of design makes the querying very optimized and reduces the query times. Also we will learn later that in a typical data warehouse, the dimension tables make up only 10 to 15 % of the storage as the fact table is by far the largest table and takes up the rest of the storage allocation.

Next>> Time Dimension