Dimensional Modeling: Fact & Dimension Table

In the world of data management and analytics, having a well-structured data warehouse is paramount. It serves as the backbone for organizations to store, manage, and analyze vast amounts of data efficiently. To understand the core of data warehousing, let’s understand the key components that make it function seamlessly. In this article, we will explore two fundamental elements of data warehousing: Fact Tables and Dimension Tables.

What is a Data Warehouse?
A data warehouse is a central repository that stores a large volume of structured data from various sources within an organization. It is specifically designed for query and analysis, making it an invaluable asset for businesses seeking insights to support decision-making processes.
Data in Data Warehouse is stored in a tabular format which can be normalized or denormalized.

Normalized vs. Denormalized Form

In normalized form, data is stored in multiple tables, joined and referenced by primary-foreign keys. This helps in reducing data redundancy and inconsistency. In the denormalized form, data is stored in a limited number of tables (maybe a single table) to reduce querying time

What is Dimensional Modeling

The data model used to store data in the denormalized form is called Dimensional Modeling. It is the technique of storing data in a Data Warehouse in such a way that enables fast query performance and easy access to its business users. It involves creating a set of dimensional tables that are designed to support business intelligence and reporting needs.

The core concept of dimensional modeling is the creation of a star schema. It is called so as the tables are arranged in the form of a star.

Dimensional modeling includes facts and dimensions. Let’s have a basic idea of what Facts and Dimensions are.

Fact Tables

Fact tables are the heart of a data warehouse. They contain quantitative data, often referred to as measures or metrics, and are the focus of most data analysis. These tables store data related to business transactions and events, such as sales figures, revenue, or quantities sold. In essence, fact tables provide the “what” in data analysis.

Dimension Tables

Dimension tables, on the other hand, offer context to the data stored in fact tables. They provide descriptive information that helps users understand the “who,” “where,” and “when” aspects of the data.

Relationship Between Fact and Dimension Tables

Fact and dimension tables are interdependent. Fact tables include foreign keys that link to primary keys in dimension tables. These relationships enable analysts to associate quantitative data with the relevant descriptive attributes, providing a complete view of the data.

Real-case scenarios of Dimensional Modeling

Scenario: The e-Commerce industry
The e-commerce industry is widely known for selling and buying goods over the Internet. We are going to create a model for the below three business processes:

• products bought by the customers.
• capturing the delivery status of the product.
• maintaining the inventory so that items don’t run out of stock.

A grain is a business process at a specified level. The grain should be as granular (at the lowest level) as possible. We can have the grain as the products purchased by the customer, i.e., each row of the fact table will represent all the products checked out by the customer from the cart. But suppose a customer orders 100 products, if this will be represented as a single row, imagine how complex it will become to query such data. So we must choose a grain as granular as possible. Therefore, our grains will be :

• individual product ordered by a customer
• status of an individual product shipped from the warehouse
• daily inventory for each product in each store

Our dimensional model will have the following dimensions:

Date Dimension: This dimension table is used in almost every dimensional model as it helps monitor the business’s performance with time.

Product Dimension: This table will contain information regarding the product ordered.

Order Dimension: This detail will contain information regarding the order.

Customer Dimension: This dimension table will contain the customer’s information.

Promotion Dimension: This table covers the promotion condition under which the product was sold. The promotion conditions include temporary sales, reduction in price, discounts, etc.

Warehouse Dimension: This table will contain information about the different warehouses located across the country.

Fact Table 1: Individual product of the order per row.

Fact Table 2: Delivery Status of individual products in the order

Fact Table 3: Daily inventory for each product in each store.

If we combine all the three fact tables, our final model will look like this:
Our final model will look like this:

In conclusion, I believe that this article has provided you with a foundational grasp of dimensional modeling. This article has explained the creation of fact and dimension tables and the steps necessary for the successful implementation of a dimensional model.