Dimensional Modeling - Part 1 of 2: An Overview

I believe that the data model is the most important part of any data driven solution. Because I work in the Business Intelligence and Data Warehousing field, the dimensional data model is the standard for how to relate and store data. In this two part series, I will explain the concept of dimensional modeling, compare dimensional modeling with transactional modeling, and provide tips for how best to create a dimensional model. For part one, I will begin with the overview and comparison:

Introduction to Dimensional Modeling (OLAP)

The premise of data warehousing is that data is stored in a dimensional model, which allows for complex, online analytical processing (OLAP) queries to be generated and returned in a reasonable amount of time. To achieve this, data is stored in a denormalized manner which stores redundant data, and thus reducing the number of joins a relational database management system (RDBMS) would need to perform. Denormalized, dimensional models are made up of dimensions and facts and the resulting model is often referred to as a star model. This is because the fact is central to the dimensions and resembles a star (figure 1).

Figure 1: Sample Sales Dimensional (Star) Model

Dimensions typically contain attributes or textual information that describes entities. Examples could be customer name, address, employee name, or product name and description. Facts contain metrics or items that need to be calculated or summed. Common examples are sales order quantity, sales order amount, or discount amount.

Comparison with Transactional Modeling (OLTP)

Dimensional models differ from online transactional processing (OLTP) systems in that transactional models are highly normalized. Normalized data models try to remove redundant data stored within tables, and thus necessitates more joins. The purpose of transactional databases is to quickly store and retrieve a small number of records (figure 2).  

Figure 2: Sample Sales Transactional Model

A sample transactional request could be: 

Return a listing of products for open orders placed by John Smith

This query is fairly simple for the database to return because you are looking for a small number of records. Only the products pertaining to John Smith will be retrieved. Even if you have millions of records, the database can quickly locate, and return those specific records.

A sample analytical request could be: 

Retrieve East Region revenue for products sold to customers living in Washington DC

In a transactional environment, this is a much more difficult question to answer. Looking at the sample transactional model (figure 2), you would need to join at least 10 tables together, and there may be thousands, if not millions of orders for a particular location. With the sample dimensional model (figure 1), you only need four tables to answer the same question. You can easily add or remove dimensions to your request and the results would still come back in a short amount of time; seconds not minutes. 

Benefits of Dimensional Modeling

The reason this works so well is that many tables from the transactional system are combined into a singular dimension. Doing this creates redundant data, but allows for much faster retrieval of large volumes of data. All metrics are stored in the fact and can easily be summed or aggregated to the desired level by bringing in or removing attributes from the associated dimensions. Another reason it works well is because executing a complex, analytical query in the data warehouse reduces the burden on the transactional system and allows it to handle only transactional requests.

Think of your favorite online retailer. When you are placing an order, viewing order history, or updating your customer information, you are storing and retrieving data from a transactional data model. Business intelligence, on the other hand, allows business users to use their data to create trends, find correlations, and make informed decisions. To do this, data must be able to be summarized, retrieved quickly, and have the ability to drill into the details. This is where a robust dimensional model comes into play.  

In the next post, I will discuss some best practice tips to consider when creating a dimensional model. Stay tuned…

The views expressed in this post are entirely my own, and don't represent that of my employer or any of its employees.