Pages

Thursday, March 21, 2013

What is Fact? Types of Fact?


Data, usually numeric and additive, that can be examined and analyzed.
Examples include sales, cost, and profit.
Fact and measure are synonymous,  Fact is more commonly used with relational environments, measure is more commonly used with multidimensional environments.

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.

Derived fact (or measure)
A fact (or measure) that is generated from existing data using a mathematical operation or a data transformation.
Examples include averages, totals, percentages, and differences.
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 non-additive fact, for it does not make sense to add them up for the account level or the day level.

No comments:

Post a Comment