A fact table stores quantitative information for analysis and is often denormalized. A fact table holds the measures, metrics and other quantifiable information. The different types of fact tables are as explained below:
Read:
- Data Warehouse fact-less fact and Examples
- Slowly changing dimension
- Types of Dimension Tables in a Data Warehouse
Types of Facts
There are three types of facts:
Additive Facts
Additive facts can be used with any aggregation function like Sum(), Avg() etc.
Example is Quantity, sales amount etc.
Semi Additive Facts
Semi-additive facts are those where only a few of aggregation function can be applied.
For example, Consider bank account details. You cannot apply the Sum() on the bank balance that does not give useful results but min() and max() function may return useful information.
Non-Additive Facts
You cannot use numeric aggregation functions such as Sum(), Avg() etc on Non-additive facts.
For example of non-additive fact is any kind of ratio or percentage. Non numeric facts can also be a non-additive facts.
Types of Fact Tables
Below are detail types of fact tables:
Fact-less Fact Tables
A fact table that does not contain any measure is a fact-less fact table. This table will only contain keys from different dimension tables. This is often used to resolve a many-to-many cardinality issue.
For example, a fact table which has only productID and date key is a fact-less fact table.
Read more about fact-less fact here:
Centipede Fact Table
Centipede fact table is a normalized fact table. Modeller may decide to normalize the fact instead of snow flaking dimensions tables.
Conformed Fact Tables
They are measures re-used across multiple dimension models.
For example, KPI such as profit, revenue etc
Incident and Snapshot Facts
A fact table stores some kind of measurements and are captured against a specific time. Now it might so happen that the business might not able to capture all of its measures always for every point in time. Then those unavailable measurements can be kept empty (Null) or can be filled up with the last available measurements. The first case is the example of incident fact and the second one is the example of snapshot fact.
Cumulative Fact
This type of fact table describes what has happened over a period of time. For example, this fact table may describe the total sales by product by store by day.