Fact Table

Fact Table | Star Schema:

The centralized table in a star schema is called as FACT table. A fact table typically has two types of columns: those that contain facts and those that are foreign keys to dimension tables. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys.

In the example, “Sales Dollar” is a fact (measure) and  it can be added across several dimensions. Fact tables store different types of measures like additive, non additive and semi additive measures.

Measure Types:

• Additive – Measures that can be added across all dimensions.
• Non Additive – Measures that cannot be added across all dimensions.
• Semi Additive – Measures that can be added across few dimensions and not with others.

In the real world, it is possible to have a fact table that contains no measures or facts. These tables are called as Factless Fact tables.

Steps in designing Fact Table:

• 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, region name).
• Determine the lowest level of summary in a fact table(sales dollar).

Example of a Fact Table with an Additive Measure in Star Schema: