Types of Facts in Data warehouse

A fact table is a table that contains the measures of business.

Types of Facts

There are three types of facts:

• Additive:

 Additive facts are facts that can be summed up through all of the dimensions in the fact table.

Example: Take three types of facts. assumes that we are in shop, and we have a fact table with following  columns

Additive Fact

If you see this table it is used to record the sales amount of shop by date,store and product.

The column Sales amount can be get summed up on each level of its dimensional (date,store,product).

The sales amount is called additive fact because it can be summed up through all of the dimensions in the fact table

• Semi-Additive:

Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.

Example: Consider you are in bank and you have the following table

Semi-Additive

The purpose of this table is to have current balance by account by end of each day. 

We can sum up the current balance of each account, and it will not make sense if we sum the current balance by date. So this table is called semi-additive fact.

• Non-Additive:

Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.


Example : I can use same table of semi-additive fact as example for non-additive.

Non-Additive

In this table profit margin is non-additive as we can't sum up profit margin either by account or date, so profit margin we can consider as non-additive fact.