Mastering Fact Table Design: From Basics to Advanced Strategies

1. Fact Table Basics

Every data warehouse contains one or more fact tables that store numeric data (facts) which can be aggregated to provide historical metrics for business units. Each fact table includes an index composed of foreign keys referencing dimension tables that describe the characteristics of the facts.

1.1 Fact Table Features

Fact tables revolve around the business process and capture measures that describe that process. The level of detail represented by a row is called the grain . Measures can be additive , semi‑additive , or non‑additive .

Additive facts can be summed across any dimension.

Semi‑additive facts can be summed only across specific dimensions (e.g., inventory by location and product).

Non‑additive facts cannot be summed directly (e.g., ratios) and should be decomposed into additive components.

1.2 Fact Tables with Facts

Fact tables that contain measurable data fall into three categories:

Transaction fact tables

Periodic snapshot fact tables

Cumulative snapshot fact tables

1.3 Fact Tables without Facts

These tables track events that may not have a numeric measure, such as student attendance records. They include well‑defined foreign keys (date, student, teacher, location, course) and can be counted across dimensions.

2. Fact Table Design Rules

Include all facts related to the business process.

Select only facts relevant to the process.

Decompose non‑additive facts into additive components.

Declare the grain before choosing dimensions and facts.

Do not mix different grains within the same fact table.

Maintain consistent units across facts.

Handle null values (e.g., replace with zero for aggregation).

Use degenerate dimensions to simplify queries.

3. Fact Table Design Methodology

Kimball’s four‑step dimensional modeling method:

Choose the business process and determine the fact table type.

Declare the grain.

Identify the dimensions that describe the process.

Determine the facts (measures) that answer “what is measured?” and ensure they match the declared grain.

4. Types of Fact Tables

4.1 Transaction Fact Tables

One fact table per business process, providing detailed, flexible analysis. Advantages: fine‑grained tracking; disadvantages: many tables to manage.

4.2 Periodic Snapshot Fact Tables

Sample the state of an entity at regular intervals, useful for studying metrics without aggregating long‑term transaction history. They are semi‑additive and can be dense (a row per entity each period).

4.3 Cumulative Snapshot Fact Tables

Designed for processes with clear start and end dates (e.g., order lifecycle). They capture multiple business‑process dates to calculate intervals and often store full historical states.

5. Fact Tables without Facts

These tables support business processes without measurable facts. Common types are event‑type tables (e.g., logs) and condition/range tables that record many‑to‑many relationships such as customer‑salesperson assignments.

6. Aggregate Fact Tables

Aggregates improve query performance by summarizing detailed data. They reside in the DWS layer and provide consistent results with the detailed layer while reducing data volume.

6.1 Basic Principles

Consistency with detailed queries.

Avoid storing multiple aggregation levels in a single table.

Aggregation granularity can differ from the source granularity.

6.2 Basic Steps

Determine aggregation dimensions.

Define consistent drill‑down paths.

Select aggregation facts.

6.3 Common Aggregate Fact Tables

Typical aggregates include daily, periodic, and historical summaries used for BI reports, feature extraction, and user profiling.