DWH Tutorial – Part 4


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.
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.
Non-Additive: Non-additive facts are facts that cannot
be summed up for any of the dimensions present in the
fact table.
Let us use examples to illustrate each of the three types of facts.
The first example assumes that we are a retailer, and we have a
fact table with the following columns:
Date
Store
Product
Sales_Amount
The purpose of this table is to record the sales amount for each
product in each store on a daily basis. Sales_Amount is the fact.
In this case, Sales_Amount is an additive fact, because you can
sum up this fact along any of the three dimensions present in the
fact table — date, store, and product. For example, the sum of
Sales_Amount for all 7 days in a week represent the total sales
amount for that week.
Say we are a bank with the following fact table:
Date
Account
Current_Balance
Profit_Margin
The purpose of this table is to record the current balance for
each account at the end of each day, as well as the profit margin
for each account for each day. Current_Balance and
Profit_Margin are the facts. Current_Balance is a semi-additive
fact, as it makes sense to add them up for all accounts (what’s
the total current balance for all accounts in the bank?), but it
does not make sense to add them up through time (adding up all
current balances for a given account for each day of the month
does not give us any useful information). Profit_Margin is a nonadditive
fact, for it does not make sense to add them up for the
account level or the day level.
Types of Fact Tables
Based on the above classifications, there are two types of fact
tables:
Cumulative: This type of fact table describes what has
happened over a period of time. For example, this fact
table may describe the total sales by product by store by
day. The facts for this type of fact tables are mostly
additive facts. The first example presented here is a
cumulative fact table.
Snapshot: This type of fact table describes the state of
things in a particular instance of time, and usually
includes more semi-additive and non-additive facts. The
second example presented here is a snapshot fact table.

Advertisements

Comment please...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s