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.
