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