Published May 05, 2017 by

Different types of fact in data warehousing

A fact table can have multiple facts and can have a reference with multiple dimensions. Ultimately the objective of fact is for doing aggregations to view the data in different dimensions.
There are three fact types categorized based on the level of sum up/roll up by each dimension of a fact table.

Additive Fact:

An additive fact is a fact which can be summed up by all dimensions in a fact table.
In below example the fact table contains Quantity, Price and Total are facts. Quantity and Total can be rolled up by all 3 dimensions (location, month and product).
additive fact example

Semi-Additive Fact:

A fact which can be summed up by only a few dimensions in a fact table is called Semi-additive fact.
In below example illustrates that the purchase fact table has three facts  (Purchase_qantity, Total_amount, and Stock_quantity).
semi-additive example
The measure Stock_quantity shows how much stock still available including the purchased quantity.
It does not require to see Stock_quantity for a specific month, instead, we can see the Stock_quantity for a location for a specific product.

NonAdditive Fact:

A fact which cannot be summed up by any of the dimension in the fact table is called nonadditive fact.
In below star schema shows that  three facts (Quantity, Price and Total) exist in Sales fact table.
non-additive fact example
There is no use in rolling up the price measure by all 3 dimensions (location, month and product).
Whereas it can be useful to view a report of variation in price value across location for a period for a product.
    email this