Fact tables in DWH

A Fact Table is a central table in a star schema of a data warehouse. A fact table stores quantitative information for analysis and is often de normalized. A fact table works with dimension tables and it holds the data to be analyzed and a dimension table stores data about the ways in which the data can be analyzed. A fact table consists of two types of columns. The foreign keys column allows joining with dimension tables and the measure columns contain the data that is being analyzed.

For example I am using “AdventureWorksDW2012”. This Database design for Data warehouse.

FactInternetSales table is a Fact table.   

If we will see in cube Fact table is surrounded by dimensions. See below  

Fact: FactInternetSales

Dimension: prefix start with Dim 

Type of facts

Ø  Non-additive: we cannot use aggregation function such as SUM (), AVG () etc. on non-additive facts. For example of this type of fact is any king of ratio or percentage.

Ø  Semi-additive: Semi-additive facts are those where only a few of aggregation function can be applied.

Ø  Additive: in this type of facts we can be used with aggregation function like sum, avg etc.

Type of Fact Tables

Ø  Transaction fact table: A Transactional table is the most basic and fundamental. The grain associated with a transactional fact table is usually specified as one row per line in a transaction. It holds data of the most detailed level causing it to have great number of dimensions associated with it.

Ø  Snapshot fact table: The snapshot fact table describes the state of things at a particular time and contains many semi- additive and non- additive facts.

Ø  Accumulated fact table: The accumulated fact table is used to show the activity of a process that has beginning and end.

Ø  Fact less fact table: Fact less fact table which contain no measures. We call it as fact less fact table. These tables are used to capture the action of the business process.