What is Fact Table?
A Fact table stores quantified data to measure the business performance. It is a measure that can be summed, averaged or manipulated. Fact table is a table surrounded by the dimension tables in the Star Schema of a Data Warehouse.
The Fact table consists of two types of column:
A Dimension key (foreign key) – A foreign key that joins with dimension tables
A Measure – where data is analysed
Types of Fact Tables
There are three types of Fact Table grains
- Transaction Fact Table
- Periodic Snapshot Fact Table
- Accumulating Snapshot Fact Table
Transaction Fact Table
A Transactional Fact Table is the most common and fundamental view in business process. It is a measurement taken at a single instant. For instance, Sales order, Purchase order etc.
Periodic Snapshot Fact Table
Periodic Snapshot Fact tables measures the performance of the business at a predefined span of time or at a regular interval such as end of day, week, month, quarterly and so on. For instance, Quarterly financial report, monthly performance of an employee etc.
Accumulating Snapshot Fact Table
Accumulating Snapshot Fact Table demonstrates the predictable progress through a well-defined business process that has the beginning and end. These fact tables are updated as the specific steps of the order are achieved. It will often have multiple dates as the order passes through different course of lifetime. For instance, Order processing, school admissions etc.