Type of Fact table

In this blog, I write about Type of Fact table and brief explanations.

Additive – that can be summed up through all of the dimensions in the fact table

Example for Additive

FactSales

DateKey

StoreKey

ProductKey

SalesAmount

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

Example for Semi-Additive, in this fact table current balance for each account at the end of the day, so that current balance cannot be summed for Datekey, but adding for accountkey is meaning full.

FactAccountBalance

DateKey

AccountKey

CurrentBalance

ProfitMargin

Non-Additive – that cannot be summed for any of the dimension in the fact table

For example, in the below fact table profit margin is non additive, it doesn’t make sense to add them up for accountkey or datekey

FactAccountBalance

DateKey

AccountKey

CurrentBalance

ProfitMargin

Another way of categorizing Data Warehouse Types/Fact table types

Cumulative Data Warehouse:  This type of fact table describes what has happened over a period of time. For example, tis fact table may describe the total sales by product by store by day. The fact for this type of fact tables are mostly additive facts.

FactSales

DateKey

StoreKey

ProductKey

SalesAmount

Snapshot Data Warehouse: This type of fact able describes the state of things in a particular instance of time, usually includes more semi-additive and non-additive facts. Mostly used by Banks and Insurance domain data warehouse designs.

FactAccountBalance

DateKey

AccountKey

CurrentBalance

ProfitMargin

Cheers!

Uma