Compare and store changes

[#1]

I need some help with setting up a way to store historical data.

The datasource changes alot and unfortunatly source system doesnt save some changes.

Unfortunatly things are even hard-deleted from the source which means I want to also find/mark things in QV that they were deleted.

Today what I store looks basicaly like this:

Key	amount	price	value1	value2
1	5	20	Big	Something
2	20	30	Huge	Goodbye

This is reloaded every night, which means I can always only see the current state of things.

And I want to be able to store compare it each day and store:

Key	amount	price	value1	value2	ActiveFrom	ActiveTo	Deleted	ActiveVersion
1	5	20	Big	Something	2010-08-10	2012-10-05
1	5	20	Small	SomethingElse	2012-10-06			1
2	20	30	Huge	Goodbye	2010-08-10		1

Anyone got a clue where I should start?

Do I have to manually compare every column of every table? or is there some kind of quicker compare?

basically I dont care what has changed, as soon as ANYTHING changes I want a new record for it.

[Anonymous · Accepted solution]

Hi,

Could try using a composite key of all the fields you think will change:

    Key&'-'&Amount&'-'&Price&'-'&Value1&'-'&Value2 AS CompKey

You can then do the following series of steps:

1. load in your fresh data to TodaysTable

2. load in yesterday's data (stored off using a qvd from end of this process the day before) to YesterdaysTable

3. take from todaystable rows which have not changed using where clause:

    WHERE EXISTS (CompKey,YesterdaysCompKey);

4. add on rows that have changed, again using a where clause:

    WHERE EXISTS (CompKey,YesterdaysCompKey);

That would only account for those rows that were there before, or have changed slightly. As for new rows, you should be able to bring in those one from TodaysTable that didn't exist on yesterdays:

    WHERE NOT EXISTS (Key,YesterdaysKey);

[#3]

Had completely forgotten about this post

But Jonathan did get it right, to do something like this you need to load, reload reload reload the data building up one thing at a time. Creating a "checksum" for each ID helps alot.

I've now had this running without much trouble since around the time of the original post.

It is however a terrible time and resource-waster, and if you HAVE to do this then you should be asking source system to create a audit of some sort instead, but it is doable