Handling history / periodic data

Go down

Handling history / periodic data

Post  ruf81 on Sat Dec 06, 2014 2:14 pm


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 ?


Posts : 2
Join date : 2014-06-12

View user profile

Back to top Go down

Re: Handling history / periodic data

Post  ngalemmo on Sat Dec 06, 2014 6:37 pm

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.

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

View user profile http://aginity.com

Back to top Go down

Back to top

- Similar topics

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