What is Fact Table in Data Warehouse

 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.

factimg

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.

 

One thought on “What is Fact Table in Data Warehouse

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s