Now, let's use this table to answer these very simple questions:
1.) What was the account balance on 2/4?
Notice how a row was added each time that a deposit or withdrawal was made. In order to find the balance on 2/4, we must look at everything that happened through 2/4. If we only look at the 2/4 row, we will only see the $200 withdrawal. However, it is important to know that $3,000 existed in the account before that withdrawal. So, if we sum the 2/1 row and the 2/4 row, we will see that the balance on 2/4 (after that transaction posted) was $2,800.
2.) What was the account balance on 2/9?
Using the same logic that was explained in #1 above, sum the 2/1, the 2/4, and the 2/9 rows. This will give you a value of $3,800.
3.) How much was deposited on 2/9?
When looking at only one of the events that occured as opposed to the sum of everything that occurred, only that one row should be taken into consideration. The 2/9 row by itself will tell us that $1,000 was deposited into the account. Unlike #2 above, we do not need to consider the $2,800 that was in the account prior to 2/9.
In the future we will look at writing some ETL to populate a transaction fact table. We will also take a look at some additional fact table designs.
Image courtesy of twobee / FreeDigitalPhotos.net