Types of Facts
There are three types of facts:
1. Additive:
Additive facts are facts that can be summed up through all of the
dimensions in the fact table.
2. 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.
3. 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 |
-------------------------
| Account |
-------------------------
| Current_Balance |
-------------------------
| Profit_Margin |
-------------------------
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 |
-------------------------
| Store |
-------------------------
| Product |
-------------------------
| Sales_Amount |
-------------------------
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 non-additive 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:
1. 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.
2. 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.
Sumber :
http://www.1keydata.com/datawarehousing/fact-table-types.html