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