This kind of fact table represents the snapshot status (i.e. the measures of the state at some point-in-time) periodically (e.g. daily, weekly, monthly). Typically, it is useful for recording account/stock balance. It is the preferred way to capture balance figures because the balance can't be aggregated from the transactions from an individual single period but from the very beginning.
Technically, transactional events can also be summarized to store the snapshot directly in this way. (e.g. Monthly Sales Fact Table). However, we always recommend keeping an atomic grain fact table. So, using sales as an example, we keep both atomic Transaction Fact Table and its aggregated fact table.
I believe that Monthly Sales Fact table can be defined as a periodic snapshot fact table or a aggregated fact table which depends on how it is propagated. If it is created directly from the snapshot of the source system, it is a Periodic Snapshot Fact table. Otherwise, if an atomic transactional fact table is built first in the Data Warehouse, it is an aggregated fact table built on-top from that base fact table.
No comments:
Post a Comment