What is Fact(Fact IN datawarehoue)

Fact in Dimention Modeling

A fact is something that is quantifiable (Or measurable). Facts are typically (but not always) numerical values that can be aggregated.

Types of Fact  

• Additive Fact
• Semi-additive
• Non-additive

Additive Measures

Additive facts are facts that can be summed up through all of the dimensions in the fact table.It can be used with any aggregation function like Sum(), Avg() etc. Example is Sales Quantity etc. This article helps you to understand the differences between dimensional data/ factual data etc. from a fundamental perspective

Non-additive Measures

Non-additive measures are those which can not be used inside any numeric aggregation function (e.g. SUM(), AVG() etc.). One example of non-additive fact is any kind of ratio or percentage. Example, 5% profit margin, revenue to asset ratio etc. A non-numerical data can also be a non-additive measure when that data is stored in fact tables, e.g. some kind of varchar flags in the fact table.

Semi Additive Measures

Semi-additive measures are those where only a subset of aggregation function can be applied. Let’s say account balance. A sum() function on balance does not give a useful result but max() or min() balance might be useful. Consider price rate or currency rate. Sum is meaningless on rate; however, average function might be useful.

What is a fact-less-fact?

A fact table that does not contain any measure is called a fact-less fact. This table will only contain keys from different dimension tables. This is often used to resolve a many-to-many cardinality issue. 

Example:

Consider a school, where a single student may be taught by many teachers and a single teacher may have many students. To model this situation in dimensional model, one might introduce a fact-less-fact table joining teacher and student keys. Such a fact table will then be able to answer queries like,

1. Who are the students taught by a specific teacher.

2. Which teacher teaches maximum students.

3. Which student has highest number of teachers.etc. etc. 

Facebook Commentbox

Datawearhouse concepts     Data Warehouse Architectures   Datawearhouse Architecture Overview
Data Mart   Datawearhousing Schemas  Dimention Data Modeling  Dimention  Fact  OLAP vs OLTP