Transaction fact table, Cycle snapshot fact table, and cumulative snapshot fact table

Source: Internet
Author: User

In the field of data warehousing there is a concept called transaction fact table, the Chinese translation is generally "transaction fact table". The Transaction fact table is one of three basic types of fact tables in the Data Warehouse for dimension modeling, and the other two are the periodic snapshot fact table and the cumulative snapshot fact table.

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

Transaction fact table records the facts of the transaction level, which holds the most atomic data, also known as the Atomic fact table. The data in the Transaction fact table is generated after the transaction event occurs, and the granularity of the data is usually one record per transaction. Once a 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 that the transaction occurred, and the fact that it records is the content of the transaction 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 establish a clustered fact table to provide users with high-performance analysis.

2 in the field of data warehousing has a concept called periodic snapshot fact table, the Chinese general translation of "Periodic snapshot fact table."
The periodic snapshot fact table records facts at regular, predictable intervals, such as daily, monthly, yearly, and so on. Typical examples are the Sales day snapshot table, Inventory day snapshot table, and so on.

Periodic snapshots The granularity of the fact table is one record per time period, typically thicker than the granularity of the Transaction fact table, and a clustered table established on top of the transaction fact table. The periodic Snapshot fact table has fewer dimensions than the transaction fact table, but it records more facts than the transaction fact table.

The date dimension of the periodic snapshot fact table is usually the day of the expiration of the record period, and the fact is that some of the aggregated fact values are in this time period. The fact table's data cannot be changed once it is inserted and is updated incrementally.

3 in the field of data warehousing has a concept called accumulating snapshot fact table, the Chinese translation is generally "cumulative snapshot fact table."
The cumulative Snapshot fact table and the periodic snapshot fact table are similar in that they store snapshot information for transactional data. But there is also a big difference between the periodic snapshot fact table records the determined cycle data, and the cumulative snapshot fact table records the indeterminate period of data.

The cumulative Snapshot fact table represents a time span that completely overwrites the life cycle of a transaction or product, typically with multiple date fields that record key points in time throughout the lifecycle. Additionally, it will have an additional date field to indicate the date of the last update. Because many of the dates in the fact table are not known at first load, you must use the Proxy keyword to handle undefined dates, and such fact tables can be updated after the data is loaded to supplement the information that is subsequently known.
For example,
Order Date
Scheduled delivery date
Actual Ship Date
Actual delivery date
Number
Amount
Shipping


Features

Transaction facts

Cycle Snapshot fact

Cumulative Snapshot Fact

Time/Time

Time

Period

Time Multiple points with shorter spans

Granularity

Each line represents a transaction event

Each line represents a time period

Each line represents a business cycle

Fact table loading

Add

New Add

Add and modify

Fact table update

Not update

update

When new events are generated

Time dimension

the business date

End of time

Multiple business process completion date

The fact

Trading activity

Performance in a time period

Qualify performance within multiple business phases

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.