Friday, March 21, 2014

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

3 comments:

  1. Wow. Million thanks for explaining the Periodic Snapshot Fact table in detail.

    ReplyDelete
    Replies
    1. So glad you enjoyed it. Thanks for the comment!

      Delete
  2. Hello Brian,

    Currently i have a dataset which contains subscriber info with his avialable balances as below:

    SUBSCRIBER_ID | BALANCE1_ID | AVAILABLE_BALANCE_1 | BALANCE2_ID | AVAILABLE_BALANCE_2 | BALANCE3_ID | AVAILABLE_BALANCE_3

    #################################################################
    1a | 1 | 100 | 2 | 30 | 3 | 25
    2b | 3 | 30 | 5 | 18 | 2 | 45
    3c | 2 | 55 | 1 | 65 | 5 | 20

    we are getting this info on a daily basis at the end of the day as a periodic snapshot.

    My question is how can i model this data in my data warehousing system.

    Here the BALANCE_ID can vary for each subscriber as you can see.

    IF i want to query for a subscriber on a particular day what was his available balance with balance_id as 1 , then i need to store each column as a row for each balance.

    Can you please help on this how can i design a periodic snapshot for this ?

    Thanks,
    Suhrid Ghosh

    ReplyDelete