Types
of Facts
There are different kinds 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.
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 represents the total sales amount for that week.
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:
|
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 represents 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.
4) Derived facts
- Derived fact is shown as a kind of fact, along with base fact.
- A derived fact is created by an inference or a mathematical calculation from terms, facts, other derivations, or even action assertions.
- A base fact is a fact that is a given in the world and is remembered (stored) in the system.
- Derived facts are created by performing a mathematical calculation on a number of other facts, and are sometimes referred to as calculated facts.
- Derived facts may or may not be stored inside the fact table.
- Base fact is generally defined, when a fact can be picked-up from the source system without any derivation. The derived fact is a data field which is derived from the base fact
5) Textual facts
- Textual fact consists of one or more characters (codes). They should be strictly avoided in the fact table. Textual codes such as flags and indicators should be stored in dimension tables so they can be included in queries.
- Textual facts are non-additive, but could be used for counting.
6) Pseudo fact
- Pseudo fact when summed, a pseudo fact gives no valid result. They typically result when you design event-based fact tables.
- In SQL Server 2005, you cannot perform a non-logged load directly into a partition. However, you can load into a separate table that we will call the pseudo-partition. Under certain conditions, you can switch the pseudo-partition into the partitioned table as a metadata operation that occurs extremely quickly.
7) Factless fact
- A fact table with only foreign keys and no facts is called a factless fact table.
- Is a table which is similar to Fact Table except for having any measure; I mean that this table just has the links to the dimensions.
- Use of Factless fact table is these tables enable you to track events; indeed they are for recording events.
