Freeze official report data

Hi,

I am a consultant working for a governemental agency. The agency is responsible for producing the officials public statistics based on Yearly ETL from a few transactionnal systems. In my project I need to help implement a new BI solution in a real RDBM.

The main problem I want to resolve with my client is the following: Daily and monthly reports can change but when he releases his official annual public reports, he wants to freeze the most granular data for good (dimensions and facts). This data is sensitive and shouldn't change after the extraction but it does. Let say he had produce a report showing 75 units produced for 2008, when he makes the 2009 reports, even if the data has changes to 76 for 2008, he doesn't want to show 76 but 75 because his reports are Officials and his subscribers would always ask why the difference compare to the last year report.

It's been 5 years since my last DW project, but my previous clients were able to deal with changes made afterward so in some instances we either recreated the whole fact table on every ETL (lights tables) or on others we only updated/added the new changed facts since the last ETL.

I have some sort of ideas of how to achive what my new client wants but I believe it would be very time and resource consuming.

- Create a regular DW that always updates itself with the most recent change for the daily and monthly reports.
- Save the output of every yearly report in a table
- Make a backup of every year and restore them in separate DB in case we want to make verifications or to add a new indicator in the next annual reports.
- Make views to join those yearly tables.
- ...

Please convince me that this is something that it is done elsewhere and give me your advice on how to do it.

Thank you.

It's done all the time, but usually not the way you describe.

Basically, don't update the measures on a particular row. Just insert deltas or restated measures with appropriate effective date ranges so you can recreate what was known at any point in time.

Then it's like a ... slowly changing fact ?

Yes, that's the basic idea.