Thursday, March 21, 2013

What is Fact Table? Types of Fact Table?

A fact table is a table that contains the measures of interest. For example, sales amount would be such a measure. This measure is stored in the fact table with the appropriate granularity. For example, it can be sales amount by store by day. In this case, the fact table would contain three columns: A date column, a store column, and a sales amount column. 

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.


The first step in designing a fact table is to determine the granularity of the fact table.
By granularity, we mean the lowest level of information that will be stored in the fact table. This constitutes two steps:
  • Determine which dimensions will be included.
  • Determine where along the hierarchy of each dimension the information will be kept.

The determining factors usually goes back to the requirements. 

No comments:

Post a Comment