A Complete Guide to Dimensional Modeling

Dimensional modeling involves using fact and dimension tables to maintain a record of historical data in data warehouses.

Different applications require different dimensional modeling techniques. Normalized Entity-Relationship models (ER models) are designed to eliminate data redundancy, quickly perform the Insert, Update, and Delete operations, and get the data inside a database.

In contrast, dimensional models or Kimball dimensional data models, data models based on the technique developed by Ralph Kimball, are denormalized structures designed to retrieve data from a data warehouse. They are optimized to perform the Select operation and are used in the basic design framework to build highly optimized and functional data warehouses.

This article covers the basics of dimensional modeling and its concepts. It also discusses different tools and implementation methods for designing successful dimensional data models.

• Benefits of Dimensional Modeling
• Elements of a Dimensional Data Model
• Designing a Dimensional Data Model
• Automation – A Game Changer for Designing Dimensional Models

Benefits of Dimensional Modeling

Dimensional modeling is still the most commonly used data modeling technique for designing enterprise data warehouses because of the benefits it yields. These include:

Faster Data Retrieval

Dimensional data modeling merges the tables in the model itself, which enables users to retrieve data faster from different data sources by running join queries. The denormalized schema of a dimensional model data warehouse, instead of the normalized one in the snowflake schema, is optimized to run ad hoc queries. As a result, it greatly complements an organization’s business intelligence (BI) goals.

Better Understanding of Business Processes

The principles of dimensional modeling are based on fact and dimension tables. We will cover facts and dimensions in the subsequent sections. This categorization of data into facts and dimensions, and the entity-relationship structure of a dimensional model presents complex business processes in an easy-to-understand manner to analysts.

Flexible to Change

A dimensional modeling framework makes the data warehousing process extensible. The design can be easily modified to incorporate new business requirements or make adjustments to the central repository. New entities can be added to the model or the layout of the existing ones can be changed to reflect modified business processes.

Automate manual design tasks and reduce dimensional model design time by 80%

Free 14-Day Trial

Elements Involved in Dimensional Modeling

Fact Tables or Business Measures

Fact tables store the numeric information about business measures and foreign keys to the dimensional tables. Business facts can be additive, semi-additive, or non-additive. Table 1 explains the three types of fact tables.

Table 1: Types of facts in a fact table

Fact Types Explained with a Dimensional Data Model

An apparel store maintains the following data in fact table rows for a sales transaction:

Table 2: Transactional table maintained by an apparel store

The columns containing numeric information about the business process are our business facts. In this example, Quantity, Unit Price, Sales Amount, Inventory, and Sales Tax are facts. And the rest of the entities (Date, Store, and Product Type) are dimensions.

Sales Amount can be added across all dimensions. Therefore, it is an additive fact. Moreover, adding Inventory information across the Store dimension provides useful business information. However, since this is just the snapshot of the number of goods at a certain point, adding it across the Date dimension does not give any helpful business insights. Since Inventory is additive across some dimensions and non-additive across others, it is a semi-additive fact. Now consider Sales Tax. Adding Sales Tax across any dimension will pose problems during analytical processing. Sales Tax is, therefore, a non-additive fact.

Discover the agile best practices to develop a data warehouse

Download Whitepaper

Dimension Tables

Dimension tables store descriptive information about the business facts to help understand and analyze the data better. In the example presented in Table 2, Date, Store Location, and Product Type are dimension entities, giving more information about the business facts. The total amount of Sales is an important measure to record, but without the dimensions, a business cannot assess which store location or product type yields more sales.

Figure 1: Star schema with fact and dimension tables

Primary Key

The primary key is a column in dimension tables that identifies unique records. The surrogate key will be the primary key for the slowly changing dimensions.

Foreign Key

Foreign keys join two tables (usually fact and dimension tables). The primary key in a dimension table is a foreign key in the related fact table and is used to reference that particular dimension.

Designing a Dimensional Data Model

To understand the process of designing dimensional models, let’s consider the example of an apparel line that sells two kinds of windbreakers – Nylon and Polyester in its two stores across California and Pennsylvania. The sample data for the example is shown in Table 2.

Step 1: Identify the Business Processes

Before modeling the data, you should find the types of dimensional modeling appropriate for your data model. The dimensional modeling process (or any data modeling) begins with the identification of the business process that you want to track. In this case, we want to track sales for the two types of windbreakers.

Step 2: Identify Facts and Dimensions in Your Dimensional Data Model

The information in a dimensional model is categorized into two table types – Facts and Dimensions. The next step is identifying the business facts you want to measure and their associated dimensions. In our example, windbreaker sale is the fact that we want to measure. Date, store location (California and Pennsylvania), and product type (Nylon windbreakers and Polyester windbreakers) are the dimensions that give us further insights into the sales process.

Step 3: Identify the Attributes for Dimensions

After you’ve identified the dimensions and facts for your business process, the next step is identifying attributes and creating a separate dimensional table for each dimension. There are different types of dimensional tables for each data type. Each record in the dimension table should have a unique key. This key will be used to identify the records in the dimension table and as the foreign key in the fact table to reference the particular dimension and join it with the fact table. Tables 3-5 show the different types of dimensions in a data warehouse in our apparel line example.

Table 3: Dimension table for Date

Table 4: Dimension table for Store

Table 5: Dimension table for Product

Step 4: Define the Granularity for Business Facts

Granularity refers to the level of information that is stored in any table. For instance, in our example, the sales amount is recorded daily; therefore, the granularity, in this case, is daily. The fact tables in a dimensional model should be consistent with the pre-defined granularity.

Step 5: Storing Historical Information (Slowly Changing Dimensions)

An important feature of dimensional models is that the dimensional attributes can be easily modified without changing the complete transaction information. For example, the apparel line decides to continue the Nylon windbreaker from Fall Collection into the Spring Collection and updates the name in the Collection attribute. Making the update is easy in the dimensional table, but we will lose our previous data with the update. If the goal of your data modeling and data warehouse is maintaining and storing history, this could be a problem. Dimensions that change slowly over time are called Slowly Changing Dimensions. In addition, the time dimension table in a data warehouse is automatically generated and captures the time at which different transactions occur. You can maintain and store historical data by tracking slowly changing dimensions.

Learn More About Designing An Automated Dimensional

Modeling With Our Step By Step Guide

Automation – A Game Changer for Dimensional Modeling

Designing dimensional models is an essential step in building the framework of an enterprise data warehouse. The process can be streamlined with the help of a robust data warehouse automation tool such as Astera Data Warehouse Builder.

Build Your Dimensional Model Data Warehouse From Scratch

Sign up for Demo

With Astera DW Builder, you can quickly build dimensional models in a visual code-free integrated development environment. Entities can be denormalized with simple drag-and-drop and merges. Entity roles (facts and dimensions) can be assigned in bulk, saving you valuable time when working with hundreds of entities. In addition, the product enables you to manage slowly changing dimensions with built-in support for SCD types 1, 2, 3, and 6.

Astera DW Builder is an end-to-end data warehouse automation platform with built-in dimensional data modeling capabilities, support for a wide range of databases and CRM applications, automated data mapping and data loading features, and native integration with business intelligence platforms such as Tableau and Power BI.

See Astera DW BuiBuilder’smo or sign up for a free trial to experience first-hand the power of data warehouse automation.

Reduce data warehouse development time by up to 80%