What is Fact Table?

Fact Table

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.

 "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.

•    A fact table might contain either detail level facts or facts that have been aggregated (fact tables that contain aggregated facts are often instead called summary tables).

•      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).

•          for a product in a year within a location sold or serviced by an employee