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:

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

 

Leave a Reply

Your email address will not be published. Required fields are marked *