Types of Facts Table

The fact table is a central table in the data schemas. It is found in the centre of a star schema or snowflake schema and surrounded by a dimension table. It contains the facts of a particular business process, such as sales revenue by month. Facts are known as measurements or matrices. It captures a measurement or a metric. It is an essential concept for data warehousing and BI Certification.

The fact table stores quantitative information of analysis that is not arranged. The fact table is a primary table in the dimensional model. It also contains measurement, metric and quantitative information.

Types of Facts

There are three types of facts:

1. Summative facts: Summative facts are used with aggregation functions such as sum (), average (), etc.
2. Semi summative facts: There are small numbers of quasi-summative fact aggregation functions that will apply.
   For example, consider bank account details. We also cannot also apply () for a bank balance which will not have useful results, but the minimum() and maximum() functions return useful information.
3. Non-additive facts: We cannot use numerical aggregation functions such as sum (), average (), on non-additive facts. For non-additive facts, ratio or percentage is used.

Types of Fact Table

There are three types of fact tables:

1. Transaction Fact Table

The transaction fact table is a basic approach to operate the businesses. These fact tables represent an event that occurs at the primary point. A line exists in the fact table for the customer or product when the transaction occurs.

Many rows in a fact table connect to a customer or product because they are involved in multiple transactions. Transaction data is often structured quickly in a one-dimensional framework. The lowest-level data is the rawest dimensional data that cannot be done by summarized data.

2. 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.

Example: The daily equilibrium fact is expressed by the customer dimension but not by the time dimension.

Periodic snapshots require the performance of the business at regular and estimated time intervals. Unlike a transaction fact table where we load a row for each event, with periodic snapshots, we take a picture of the activity at the end of the day, week, or month, and then another picture at the end of the next period.

Example: Performance summary of a salesman during the previous month.

3. Accumulated Fact Sheet

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

For example, we are processing an order. An order remains in the process until it will be processed. As the step towards completing the order is completed, the corresponding row in the fact table is updated.

Fact less Facts

We have also a transaction fact tables which contain no measures. We call it as fact less fact tables. These tables are used to capture the action of the business process. For example, a criminal case is a simple fact with no measures but can have a lot of dimensional attributes associated with the fact.