transactional fact vs periodic snapshot fact

View previous topic View next topic Go down

transactional fact vs periodic snapshot fact

Post  rammnch on Tue Oct 15, 2013 10:31 am

What is the difference between transactional fact and periodic snapshot fact table? As per my knowledge transactional fact table store each record per transaction and its short lived where as periodic fact table also does the same thing. I could not understand the diff between these two fact tables. Can some one please explain me? Thanks

rammnch

Posts : 5
Join date : 2013-10-10

View user profile

Back to top Go down

Re: transactional fact vs periodic snapshot fact

Post  ngalemmo on Tue Oct 15, 2013 11:06 am

A transactional fact is one where the rows represent a net change. For example, a bank transaction where you deposit $10. The measure in the fact is $10, which represents a net change to your balance. Transactional facts are typically insert only.

Snapshot facts represent a state, a periodic snapshot is one that represents states over time. For example, my bank balance right now is $500. A fact table that contains my current balance is a snapshot fact. (Note, a transactional fact that contains a running balance measure is still considered transactional). A periodic snapshot may contain my balances at the end of each month over some period of time. Snapshots may be updated in place.

A fact table containing sales order lines may be one or the other depending on how it is designed. If you just store the order line, and when it is changed, the line is updated, its a snapshot. If you store a new complete image of the lines when it is changed, it is an accumulating snapshot. If you store the difference between the original line and the new line (i.e. net change), it is transactional.

Neither type has anything to do with the retention of the data. A transactional fact is by no means 'short lived'.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: transactional fact vs periodic snapshot fact

Post  Jeff Smith on Wed Oct 16, 2013 12:28 pm

What would you call a fact table built from Loan Application system?

What if you wanted to see Load Applications that were fully adjudicated (denied, approved) as well as a count of records in the various stages of being processed? A person managing Loan Products may want to see the Denial Rate and a person managing the load processing group might want to see how many applications are being processed per day, the number of applications that are in middle of being processed, number of applications that have been in the system for more than 1 day, 2 days, 15 days, etc.

Would you have 1 transaction table and update the status of the application or would you have 2 facts, one with completed applications and the other with multiple versions of the application (a version for each change in status)?

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: transactional fact vs periodic snapshot fact

Post  ngalemmo on Wed Oct 16, 2013 2:00 pm

If the fact represents an image of what the complete state of the transaction was at a point in time, it is a snapshot. Wither you update in place or maintain versions of it over time, it is still a snapshot. If there is an original fact and subsequent fact rows for the same document reflect the net change from the previous state, it is transactional.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: transactional fact vs periodic snapshot fact

Post  BoxesAndLines on Thu Oct 17, 2013 8:51 am

You may also build an accumulating snapshot if you want to investigate loan application performance metrics, i.e. how long app has been sitting in underwriting.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: transactional fact vs periodic snapshot fact

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum