What is a Dimension Table in the Dimensional Modeling?

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 OLTPschema 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.

<<< What is Online Transaction Processing (OLTP) Schema?Design of the Time dimension table in the dimensional modeling >>>
Copyright 2005-2016 KnowledgeHills. Privacy Policy. Contact .