Periodic Snapshot Fact Tables

In our last post we looked at one of three ways to design a fact table, called a transaction fact table.  Today, let's look at a second design, called the periodic snapshot.  Remember from Ralph Kimball's teaching (and last week's post) that a transaction fact table gains a row each time that something happens.  Using our banking example, from last week, each deposit or withdrawal will result in a record being inserted.  Looking at only one record will allow us to see that one event.  Adding these records will allow us to see the balance.

A periodic snapshot contains snapshots of the data as it existed at various points in time.  Unlike a transaction fact table, selecting one row (or perhaps a subset of rows if it is semi-additive) will display the current value at that point.  Our example from last week involved opening a checking account at Acme Bank on 2/1/14 and making an initial deposit of $3,000.  Three days later you withdrew $200.  Five days after that, you deposited $1,000.  If a periodic snapshot were written to show the balance at a daily level, a plain-english version may look something like this:

Date	Amount
2/1/2014	$3,000
2/4/2014	$2,800
2/9/2014	$3,800

Now, let's use this table to answer the same questions that we examined last week:

1.) What was the account balance on 2/4?

Unlike the transaction fact table, in order to find the balance on 2/4, we only need to look at the 2/4 row.  The 2/4 row contains a snapshot of the current balance on 2/4, as opposed to only the event that occured on 2/4.  By looking at the 2/4 row, we will see that the balance was $2,800.

2.) What was the account balance on 2/9?

Using the same logic that was explained in #1 above, look only at the 2/9 rows.  This will give you a value of $3,800.

3.) How much was deposited on 2/9?

This type of question cannot be answered using a periodic snapshot.  A periodic snapshot will store the current state of the business process as of the applicable period, but will not store the events leading to the current state.

In the future we will look at writing some ETL to populate a periodic snapshot fact table.  We will also take a look at an additional fact table design.

Image courtesy of cooldesign / FreeDigitalPhotos.net