Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Handling history / periodic data

2 posters

Go down

Handling history / periodic data Empty Handling history / periodic data

Post  ruf81 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

Back to top Go down

Handling history / periodic data Empty Re: Handling history / periodic data

Post  ngalemmo 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

Back to top Go down

Back to top

- Similar topics

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