Last week we defined the three types of fact tables and have established a solid foundation in the definition and use of transactional fact tables. This week we will focus on periodic snapshot fact tables.
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.).
The Snapshot Fact table and the Transactional fact table can be very similar in design. In the design above the sales are rolled up to the month in the Periodic Snapshot fact table. In this table, you are unable to drill down past the month, as the month is the lowest grain of this Periodic Snapshot fact table. Periodic Snapshot tables are also important when measuring those metrics which may not have a corresponding transaction, such as air temperature. We cannot capture transactions causing the air temperature to rise or fall but we can measure the temperature at predetermined points in time.
As you can see transaction fact tables can fall short in the needs of the users to measure key metrics and the use of snapshot facts are one of the ways to address these transactional fact shortcomings.
Snapshot Fact Design
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. The snapshot fact in dense as there will be a record for each time period or event regardless of the number of transactions or amount of change in the measure.
The traditional example used for a snapshot fact can be seen using bank account balances. 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.
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.
The measurements in snapshot fact tables are not additive, able to add them together into some meaningful metric, but these measures are usually semi-additive, able to use aggregate functions like AVG, MIN and MAX. For example, the average daily balance ($1,734.03) is quickly computed by adding all of the balance columns and dividing by the number of days or simply using the AVG aggregate function.
Capturing Active Record for Event or Time Period
Have you ever run up against a requirement which states that 1) you must be able to allow a record to be updated for an event or time period in the past and 2) you must be able to reproduce the detailed report as it was on any given day. This second requirement prevents one from simply updating the measures within a fact table, which is frowned upon in dimensional modeling as it impacts the reports upon which decisions were made. Therefore, we need to find a way to capture this information and associate it with the correct event or time period. In the example below, we have an equipment status report fact. Here we see that initially a record was entered on February 1 with a quantity of 5.
Notice how we have added an additional column called active monthly record flag. This will indicate if this is the record which should be used on all current and future calculations for this organization, equipment and month.
Later in the month on February 24 the equipment is re-inventoried and 2 more pieces of equipment are now available. Here we need to inactivate the original record as both the active record and the active monthly record and make the newly inserted fact record on February 24 become the active record and the active monthly record for reporting. Here we have captured when the record changed so if we need to show what a report printed on February 10 looked like we could regenerate this report.
On the first of each month, the current active record should be copied to the next month as the active record and the previous active record should be made inactive. Please Note: the active monthly record flag does not change this record remains active as this will be used when computing monthly summaries.
Now on March 4 they realized that there was one more piece of equipment on hand and should have been accounted for in February but sat on someone’s desk for several days without being input. To address this challenge, we need to go back and update February’s count without impacting the report as it was delivered on February 28 or March 1. To address this requirement, we need to do some work:
1) add a record on March 4 for the updated February record-setting the active monthly record to true and the active record flag to false
2) inactivate the active monthly record inserted on February 24
3) insert a new March active March record
4) inactivate the previously active March record.
Now the snapshot fact contains all detailed transactions and we can easily calculate the month over month values from this fact table.
You can see the power of this functionality for detailed transactional reporting as well as the ease in which data can be summarized for time period reporting.
Next week we will study the usage and benefits of accumulating fact table.