Updating Periodic Snapshot Fact Tables

View previous topic View next topic Go down

Updating Periodic Snapshot Fact Tables

Post  jimbo1580 on Wed Jan 13, 2010 11:19 am

What are the best practices for maintaining periodic snapshot fact tables if the atomic data that created them is modified? We have a couple periodic snapshot tables that capture new inventory and new revenue at the end of every month. Sometimes the business users may correct data at a later time or back date inventory or revenue weeks later.

Should I be recalculating the periodic snapshot for the affected periods?
How would I know what periods to recalculate for?
What if the revenue calculation is based on a formula that cannot be executed correctly after the end of the month?

All help is appreciated!

Thanks

jimbo1580

Posts : 23
Join date : 2009-04-30

View user profile

Back to top Go down

Re: Updating Periodic Snapshot Fact Tables

Post  ngalemmo on Wed Jan 13, 2010 1:08 pm

It depends on what the snapshot is supposed to represent. If it represents the inventory as reported at a point in time, then no, you would not update it. This would be an 'accounting' point of view... when you close a financial period, its closed... restating a closed period is not a trivial excersise.

But, if you need to be able to handle retroactive changes, the best approach is to add delta rows with an additional 'as-of date' dimension, representing the date the change was made. This will handle both situations... you can report inventory as it was originally counted as well as any adjustments made at any point in time.
avatar
ngalemmo

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

View user profile http://aginity.com

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