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

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

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.