Several types of fact tables for the Data Warehouse

Transaction fact tables, periodic snapshot fact tables, and cumulative snapshot fact tables, fact snapshots

In the field of data warehousing there is a concept called transaction fact table, in which Chinese is generally translated into "Transaction fact tables". The Transaction fact table is one of the three basic types of fact tables in the Data warehouse modeled by dimension, and the other two are periodic snapshot fact tables and cumulative snapshot fact tables.

The Transaction fact table uses the same consistency dimension as the periodic snapshot fact table, the cumulative snapshot fact table, but they are very different in describing the business facts.

The Transaction fact table records the fact of the transaction level, preserving the most atomic data, also known as the "Atomic fact table". The data in the Transaction fact table occurs after the transaction event, and the granularity of the data is usually one record per transaction. Once the transaction is committed and the fact table data is inserted, the data is no longer changed and is updated incrementally.

The date dimension of the Transaction fact table records the date on which the transaction occurred and the fact that it records the transactionThe content of the activity.The Transaction fact table allows the user to perform a special detailed analysis of the transaction behavior.

Through the Transaction fact table, you can also create a clustered fact table to provide users with highAnalysis of performance.

2) There is a concept in the field of data warehousing called periodic snapshot fact table, which is generally translated as "periodic snapshot facts Tables".
A periodic snapshot fact table with a regular, predictableTime intervals to record facts, time intervals such as daily, monthly, yearly, and so on. Typical examples are the Sales day snapshot table, the Inventory day snapshot table, and so on.

The granularity of a periodic snapshot fact table is one record per time period, usually coarser than the granularity of the Transaction fact table, and is a clustered table built on top of the transaction fact table. The number of dimensions for a periodic snapshot fact table is less than the transaction fact table, but the fact of the record is more than the transaction fact table.

The date dimension of a periodic snapshot fact table is usually the end date of the record time period, and the fact that it is recorded is a number of aggregated fact values during this time period. The fact table data cannot be changed once it is inserted, and is updated incrementally.

3) There is a concept in the field of data warehousing called accumulating snapshot fact table, which is generally translated as "cumulative snapshot fact tables".
The cumulative Snapshot fact table and the periodic snapshot fact table are similar in that they store snapshot information for transactional data. But they are also very different, and the periodic snapshot fact table records the data that determines the period of the data, while the cumulative snapshot fact table records the indeterminate period of time.

The cumulative Snapshot fact table represents a time span that completely covers the life cycle of a transaction or product, and it typically has multiple date fields that record key point-in-time points throughout the life cycle. In addition, it will have an additional date field that indicates the date of the last update. Because many dates in the fact table are not known for the first time they are loaded, you must use the Proxy keyword to process the undefined date, and such a fact table can be updated after the data is loaded to supplement the date information that is subsequently known.
For example,
Order Date
Scheduled delivery date
Actual Ship Date
Actual delivery date
Number
Amount
Shipping

Characteristics	Trading Facts	Periodic snapshot Facts	Cumulative Snapshot Facts
Time/Period	Time	Period	Multiple points of time with short spans
Particle size	Each row represents a trading event	Each row represents a time period	Each row represents a business cycle
Fact table loading	New	New	New and modified
Fact Table Update	Do not update	Do not update	Update when new events are generated
Time dimension	Business Date	The end of Time	Completion date for multiple business processes
Fact	Trading activities	Performance over the time period	Limit performance across multiple business phases

Several types of fact tables for the Data Warehouse