Contents
What is a snapshot fact table?
Snapshot fact tables are similar to the transactional fact table in design but sample the data at predetermined points in time or as a result of a specific event. A transaction fact will capture each deposit or withdrawal when it occurs but the transaction fact does not care about the balance of the account.
Which of the following is an example of a periodic snapshot fact table?
An ideal example of a periodic snapshot fact table is the bank account balance. At the end of each day, the balances of every customer account in the bank is stored in this account balance table. Say there were 20,000 customers on Monday; 22,000 customers on Tuesday and 24,000 customers on Wednesday.
What are different types of facts?
There are three types of facts:
- Additive: Additive facts are facts that can be summed up through all of the dimensions in the fact table.
- Semi-Additive: Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.
How are snapshot fact tables used in data warehouse?
This week we will focus on periodic snapshot fact tables. (Periodic) Snapshot fact tables capture the state of the measures based on the occurrence of a status event or at a specified point-in-time or over specified time intervals (week, month, quarter, year, etc.).
What is a row in a periodic snapshot fact table?
A row in a periodic snapshot fact table captures some sort of periodic data — for instance, a daily snapshot of financial metrics, or perhaps a weekly summary of accounts receivable, or a monthly tally of inventory numbers. In other words, the ‘grain’ or ‘level of resolution’ is the period, not the individual transaction.
What’s the difference between a snapshot fact and a daily fact?
The snapshot fact, on the other hand, calculates the balance of the account at the end of each day. This snapshot fact can then be easily used to calculate the average daily balance for interest or fees.
Which is an example of an accumulating snapshot fact table?
The accumulating snapshot fact table is thus a method to measure velocity within the business process. Take, for instance, this business pipeline, which Kimball presented in the second edition of The Data Warehouse Toolkit: For that process, Kimball proposed the following accumulating snapshot table: