Comparisons between Fact Table Types

Fact table contains measures of an event in a business process that is located at the center of the star schema/snow flake schema surrounded by dimensional tables. Fact tables are often defined by their grain. There are three basic fact tables. They are :- 1) Transaction 2) Periodic Snapshot and 3) Accumulating Snapshot Fact tables.

1) Transaction Fact Table:-  This fact table is a basic one that represents an event that occurs at a point in time. The grain for this fact table is defined as "one row per line in a transaction". Hence, this fact table helps us to analyze the data in most extreme detailed atomic level. But all business questions cannot be answered with this type. Example :- One order line of an order receipt per a customer transaction.

2) Periodic Snapshot Fact Table:- As the name implies, this fact table is used to see the performance of business over certain time period intervals. Unlike transaction fact table, periodic snapshot can help us take a picture of the activity at the end of a week, month and so on. There are many fact table rows in this fact table than transaction fact table due to the possibility of occurrence of various metrics during the time period. Example:- Sales performance of a product over a period of a month.

3) Accumulating Snapshot Fact Table:- This fact table is used to represent a process that has a definite beginning and definite end with milestones or standard intermediary steps in between. This type of fact table is most suitable for performing pipeline analysis. These snapshot tables have multiple date foreign keys representing milestones in the process. These date dimensions are handled by role-playing date dimensions. Fact row in this fact table also contains duration between milestones. Example:- Processing of an order.

See below table for important differences between these fact table types:-

Complementary Fact Table Types:- The above three fact table types are basic ones. However, to provide a complete view of business, transaction and Snapshot fact tables are frequently combined and modeled. 

References:-

• Kimball, R., & Ross, M. (2013). The data warehouse toolkit. (3rd ed.). Indianapolis, Indiana: John Wiley & Sons, Inc.,.