Handling history / periodic data

View previous topic View next topic Go down

Handling history / periodic data

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

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 ?

ruf81

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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