Handling history / periodic data

[ruf81]

Hi.

I have made simple reporting soultion with no history handling using SSIS / PowerPivot.
Currently there are two databases: a staging- and a data mart (star schema). I then pull data to into PowerPivot.

Due to a very tight time constraint there was no time for evaluating need for history handling.
Of course, it has now come up as requirement.

Currently - I do a full load every time. The report is monthly updated.
Trouble is, there will be some changes back in time in the source systems, so when I do a full load the next month, there might be changes to the last month.
E.g: When updating the report now in December - the october numbers will differ slightly from the last version (which was updated in november).

The business requirement is to freeze the monthly data once they are loaded.
Any recommendations how to approach this ? Periodic snapshot fact table ?

[ngalemmo]

I would calculate net change and load that. Carry both the business timestamp (the period the data represents) and the system timestamp representing when the row was added. This allows you to report both original and current values very easily.

You could also store a 'replacement' row (instead of net change), but this requires you maintain effective and expiration timestamps to control which row is used. This adds complexity to the load process as you need to update the previous version of the row.