Dimensional Modeling tutorial – OLAP, data warehouse design

In this dimensional modeling tutorial, we intend to teach people with basic SQL and relational database design skills. In this tutorial we show you the dimensional modeling techniques developed by the legendary Ralph Kimball of the Kimball Group.

Dimensional Modeling – Definition

Many data warehouse designers use Dimensional modeling design concepts to build data warehouses. Dimensional model is the underlying data model used by many of the commercial OLAP products available today in the market. In this dimensional model, we store all data in just two types of tables. They are Fact Tables and Dimension Tables. The Fact table contains the main facts or measures. Fact table links to many dimension tables thru foreign keys. We call this resulting schema as star schema because it looks like a star. Because of these multiple dimension tables, all connecting to single fact table, this design concept is named dimensional modeling.

Dimensional Modeling

Fig 1: Dimensional Modeling Schema, resembles a Star and hence called Star Schema

Dimensional Modeling – Fact Table

In a Dimensional Model, Fact table contains the measurements or metrics or facts of your business processes. If your business process is Sales, then a  measurement of this business process such as “monthly sales number” is captured in the fact table. In addition to the measurements, the only other things a fact table contains are foreign keys for the dimension tables.

Dimensional Modeling – Dimension Table

In a Dimensional Model, context of the measurements are represented in dimension tables. You can also think of the context of a measurement as the characteristics such as who, what, where, when, how  of a measurement (subject ). In your business process Sales, the characteristics of the ‘monthly sales number’ measurement can be a Location (Where), Time (When), Product Sold (What).

The Dimension Attributes are the various columns in a dimension table. In the Location dimension, the attributes can be Location Code, State, Country, Zip code. Generally the Dimension Attributes are used in report labels, and query constraints such as where Country=’USA’. The dimension attributes also contain one or more hierarchical relationships. Before designing your data warehouse, you need to decide what this data warehouse contains. Say if you want to build a data warehouse containing monthly sales numbers across multiple store locations, across time and across products then your dimensions are:

  1. Location
  2. Time
  3. Product

Each dimension table contains data for one dimension. In the above example you get all your store location information and put that into one single table called Location. Your store location data may be spanning across multiple tables in your OLTP system (unlike OLAP). You need to de-normalize all that data into one single dimension table.

What is Online Transaction Processing (OLTP) Schema? >>>
Copyright 2005-2016 KnowledgeHills. Privacy Policy. Contact .