Updating Periodic Snapshot Fact Tables

[jimbo1580]

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

[ngalemmo]

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.