Data Warehousing Object : Fact Tables

In the previous post on DW we saw different types of Data Warehousing Schemas. So the different types of schemas had one thing in common and that was, they had Fact and Dimension Tables. So these are nothing but Data Warehousing Objects. 

Apart from Fact Tables, Dimension Tables there are other things which come under Data Warehousing Objects, they are:

* Fact Tables

* Dimension Tables

* Hierarchies

* Unique Identifiers

* Relationships

In today's post we will be taking a look in Fact tables.

Fact Tables

A Fact table is a table which contains measurements or metrics or facts of business processes. Example:

* "monthly sales number" in the Sales business process

* "monthly profit amount" in the Profit business process

Most of them are additive (sales, profit), some are semi-additive (balance as of) and some are not additive (unit price).

The level of detail in Fact table is called the "grain" of the table i.e. the granularity can be fine or coarse. Fact table also contains Foreign Keys for the dimension tables.

Fact Types

Additive Fact

These are facts which can be summed up through all the dimensions in the fact table.

Example : 

Consider a Table for Sales with the following columns :

Date

Store

Product

Sales_Amount

The data in this table helps to find the Sales_Amount for a particular product in each store on a daily basis. Therefore, the column Sales_Amount is an additive fact as it can be summed up with all the three columns present in the table.

Semi-Additive Fact

These facts can only be summed with some dimensions in the fact table.

Example:

Consider a table for Balance of Account with the following columns:

Date

Account

Current_Balance

Current_Balance is a fact, which is semi-additive as it can be summed up with all the accounts number to get the total balance in the bank for all accounts, but when adding it through time it won't make any sense.

Non-Additive Fact

These facts cannot be summed with any dimensions in the fact table.

Example:

Consider a table for Balance of Account with the following columns:

Date
Account
Current_Balance
Profit_Margin

Current_Balance and Profit_Margin are facts. Here, Profit_Margin is a non-additive fact as it doesn't make any sense to add it with account level or date level.

The above were the fact types which we come across when dealing with tables. So the above information can be helpful whenever you see a data model and thus can ask questions on it, as why is additive and why non-additive.

These fact types help us to classify the Fact Tables into 2 categories :

* Cumulative
* Snapshot

Cumulative

If we go by the meaning of the word in English dictionary, it means "formed by or resulting from accumulation or the addition  parts or elements" and also the fact table also has data which is accumulated over days, i.e. it has data which describes what has happened over a time (day-to-day data). These are mostly additive.

Snapshot

Again if we go by the English meaning of the word, it means "snapshot is the state of a system at a particular point in time." Thus, this table also has information of a particular instance of time and usually includes more semi-additive and non-additive facts.

The example mentioned for additive, semi-additive and non-additive facts applies here also for the above mentioned Fact Table Categories.

Hope the post helped you to clear out your doubts on Fact Types and it categories. If you have any doubts, email me Click Here