Knowledge

Types of Fact Tables in a Data Warehouse

In this article, Notitia’s Adelaide-based Analytics Consultant, Guilherme Matte, runs us through the ins and outs of >fact tables. What are fact tables? Why work with fact tables? And the different types of fact tables.

March 26, 2024

Photo of Notitia Analytics Consultant Guilherme Matte with team

What are fact tables? | Why work with fact tables? | Different types of fact tables

In this article, Notitia’s Adelaide-based Analytics Consultant, Guilherme Matte, runs us through the ins and outs of >fact tables. What are fact tables? Why work with fact tables? And the different types of fact tables.

First let’s start with some definitions.

A data warehouse is a centralised repository that collects, integrates, and stores large volumes of structured data from various sources within an organisation.

An integral component of a data warehouse are fact tables.

A fact table, in a data warehouse, is a central table that stores quantitative data, typically numeric measures. It forms the core of analytical processes by linking with dimension tables to provide context for comprehensive business intelligence

Let’s explore how data warehousing and fact tables work together to collect, integrate and store your data - enabling the organisation and analysis of complex datasets.

The end result? Effective and data-driven decision-making your organisation.

Why fact tables?

Working with fact tables is crucial for anyone who uses data analysis or business intelligence to make informed decisions, optimise business processes and plan strategically in an organisation.

Fact tables play a central role in organising and storing quantitative data and providing a foundation for analytical processes.

Fact tables and dimensional models

In data warehousing, a fact table is one of the key concepts in a dimensional model.

It is used to store quantitative data for analysis and often serves as the focal point of a data warehouse.

Fact tables typically contain numeric measures and are used in conjunction with dimension tables, which contain descriptive attributes to provide context to the data. Whenever developing a new solution for a client, it is essential to understand the data and correctly model the fact tables.

This is crucial for enabling further analytics within BI (Business Intelligence) tools.

1. Transactional fact tables: These tables record data related to specific events or transactions. Each row in a transactional fact table represents an individual event at a point in time.

For instance, in a retail context, each row could represent a sale, capturing data such as sale amount, date, and the IDs of related dimensions such as product, customer, and store.

One row is added per new transaction in a transactional fact table

2. Periodic snapshot fact tables: These tables capture data at regular intervals, summarising activities over a specified period.

They are useful for tracking data that accumulates over time, such as monthly sales totals or quarterly inventory levels. Each row represents a summary of the data for a particular period.

Filtering "Store 01" note how each row repeats itself for each quarter per item and in the periodic snapshot we can estimate the table growth, based on its periodicity.

Batches of new rows are added in periodic intervals.

Filtering "Store 01" note how each row repeats itself for each quarter per "Product ID". In the periodic snapshot, we can estimate the table growth based on its granularity and periodicity, in this case, each "Product ID" will generate a new row per quarter for each "Store".

3. Accumulating snapshot fact tables: These tables are used to show the activity of a process that has a clear beginning and end, such as an order fulfilment or manufacturing process.

Rows in an accumulating snapshot fact table are updated as the process moves through different stages. For example, an order might move from placed, to shipped, to delivered, and the corresponding row in the table would be updated at each stage.

Each process/transaction adds a new row that is updated as the process advances stages

Each fact table variant serves distinct analysis and reporting purposes

Transactional tables excel in providing granular, event-specific insights. Periodic snapshot tables are ideal for analysing trends over defined intervals while accumulating snapshot tables are adept at monitoring the stages of a process from start to finish. Often, in practice, these types of fact tables are employed together for the same business process. This allows them to complement each other, yielding more comprehensive and effective outcomes and enabling more in-depth analytics.

Guilherme Matte, Notitia's Adelaide-Based Data Analytics Consultant

Gui says it's the thrill of a challenge, new technologies and working with clients that makes what he does so rewarding.

With a degree in Mechanical Engineering, along with a Master of Business Administration and Master of Business Information Systems, he brings a wealth of experience to the team and passion to every project.

Read more about Gui here, or book in a chat with him directly via Notitia's team page.

Notitia's Data Quality Cake recipe