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.
- 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.
- Identify measures or facts.
- Identify dimensions for facts.
- List the columns that describe each dimension.
- Determine the lowest level of summary in a fact table.