Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Updating Periodic Snapshot Fact Tables

Go down

Updating Periodic Snapshot Fact Tables Empty Updating Periodic Snapshot Fact Tables

Post  jimbo1580 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

Back to top Go down

Updating Periodic Snapshot Fact Tables Empty Re: Updating Periodic Snapshot Fact Tables

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Back to top


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