User Tools

Site Tools


Fact Table

In data warehousing, a fact table consists of the measurements, metrics of facts of a business process.

The two basic types of tables in a data warehous are the dimension table1) and the fact table. The fact table describes the facts (numeric measurements), which means the basic business processes2). While the volume of the dimension table, consisting of so called dimensions (describing data in text fields) is comparatively small, the fact table can become quite huge with a lot of information.

The fact table in a data warehousing basic concept is often located in the center of a star schema or a snowflake schema surrounded by dimension tables.

The fact table is a normalized table and contains relevant characteristics and information of the process as well as foreign keys (FK) which refer to the primary keys (PK) of the respective dimension tables. The primary key of the fact table itself is a combination of all foreign keys of the dimension tables.

Between facts and dimensions can be different kinds of relations - the cardinality. It explains how the tables are linked to each other3):

  • 1:1-relation (one-to-one): in this relation every table has in the linked field a unique value which is the same in both fields. (p.e. one license plate for one car)
  • 1:n-relation (one-to-many): it is the most common relation type. It means a data record in one field of the first tabe has a relation to many records in a field of the second table (p.e. one customer ID for many different orders)
  • m:n-relation (many-to many): both data records are linked to more data records in the other table (is not allowed between fact and dimension tables; p.e. a student got more professors - a professor got more students)

It is important to use indices or comment columns to reduce the time for scanning of all tables for an analysis.

Types of Facts

There are three types of facts 4)5):

  • Additive: Additive facts are facts that can be summed up through all of the dimensions in the fact table.
  • Semi-Additive: Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.
  • Non-Additive: Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table

If a fact-table has no measures or facts, but just collecting and bunch information, it is called a “factless fact table” or “function table”.

Types of Fact Tables

Fact tables are often defined by their grain. The grain of a fact table represents the most atomic level by which the facts may be defined. All fact tables can be grouped into just three types, but often there is a mixture of two to get a complete picture of a business 6):

  • Transactional grain

A transactional table is the most basic and fundamental, it represents an atomic action that occurs at an instantaneous point of time. It is the only table that can answer detailed questions about timing (because of the accurate date/timestamp) and predictable, repetitive behavior (p. e. about customers). This kind of table can be enormous, with the largest containing many billions of records.

  • Periodic snapshots grain

The periodic snapshot, as the name implies, takes a “picture of the moment”, where the moment could be any regular defined period of time. It corresponds to a predefined span of time. It is the only table that can easily generate a regular view of the important measurements (p. e. revenue and costs). This kind of fact table can also become very large.

  • Accumulating snapshots grain

This type of fact table is used to show the activity of a process that has a well-defined beginning and end. The accumulating snapshot often combines the most recent volatile status with measures that accumulate from the beginning of history. It is used when an item with a finite lifetime is tracked (p.e. insurance policy). Accumulated snapshot records are revisited and overwritten as the process progresses through its steps from beginning to end. Accumulating snapshot fact tables generally are much smaller than the other two types because of this overwriting strategy.

Steps in designing a fact table

An example for designing a fact table 7):

  • Identify a business process for analysis (like sales).
  • Identify measures or facts (sales dollar).
  • Identify dimensions for facts (product dimension, location dimension, time dimension, organization dimension).
  • List the columns that describe each dimension (region name, branch name, business unit name).
  • Determine the lowest level of summary (granularity) in a fact table (e.g. sales dollar).
Holger Schrödl: Business Intelligence – mit Microsoft SQL Server 2005, Carl Hanser Verlag 2006, p. 27f
Brian Underdahl und Darlene Underdahl: Microsoft Office Access 2007 Formulare und Berichte für Dummies, Wiley-Vch Verlag 2008, p.139
Paulray Ponniah: Data Warehousing, Fundamentals for IT Professionals, 2. Ed, John Wiley & Sons 2010; p.237
design/fact_table.txt · Last modified: 2020/08/20 13:27 (external edit)