Updating Periodic Snapshot Fact Tables
2 posters
Page 1 of 1
Updating Periodic Snapshot Fact Tables
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
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
Re: Updating Periodic Snapshot Fact Tables
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.
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.
![-](https://2img.net/i/empty.gif)
» Periodic snapshot fact tables with sparse data
» Designing Periodic Snapshot Tables
» Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.
» Transaction Fact or periodic snapshot fact
» Large Fact Table and Maintaining Periodic Snapshot: Practice
» Designing Periodic Snapshot Tables
» Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.
» Transaction Fact or periodic snapshot fact
» Large Fact Table and Maintaining Periodic Snapshot: Practice
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum