Questions - Incremental Load

[orital81]

Hi, I used the Incremental load examples in this forum that was very useful,

Although I have a couple of questions which would help me understand:

First, ill present the basic logic as understood by me:

1. Define your Timestamp date field

2. Select the table from the Database where Timestamp > Max(Timestamp)

3.  Concatenate the Delta from step 2 with the existing table Where NOT exists(Key)

My questions are :

1. Why do we need to filter by Timestemp if we already filter all irrelevant lines in the Concatenate step (Where not exists (Key)) ?

2. When using the condition "Where not exists (Key)" how will we get only new lines, how do we get lines that were updated (having the same key)?

[swuehl]

Have you also looked into the excellent example in the current QV cookbook, available on Rob's download site?

The actual approach is a little dependent on your needs (add / update / delete records?). In your above post, I am a bit unsure if Timestamp is giving us a modification Timestamp of records, or is it the unique key (edit: uniqueness only given in combination with Timestamp if you consider updates) you state in step 3 (PK vs ModDate in Robs example)?

Basically, you need a modification timestamp, to only load new / updated records from the source. You then need the PK to concatenate only the old / not updated records from your qvd. If you don't need to handle updates, I believe the WHERE clause is not necessary in this step (if the modification timestamp is unique itself).

(I don't get into deleting keys, look into the cookbook sample for this.)

So, trying to answer your questions:

1) If we don't use a Timestamp filter, I believe we are doing a full reload from source. The subsequent concat from qvd would only re-add records deleted from the source since the last reload, right? For sure we could also do a full reload, but that's not what we want (i.e. we want to stress the source least, speeding up reload by use of a qvd).

2) Not sure if I understand, but I think that's why you use two different where clauses (for Timestamp and PK, see above)?

Hope this helps,

Stefan

[#3]

answer to your second question:

first you fetch the max(timestamp) from your data then you apply where timestamp > max(timestamp), so if any modification is done then its timestamp must have changed which is greater than max(timestamp), then it automatically update your record.

regards

vijit

[orital81]

Thanks Vijit,

But when concatenating both tables with the condition "Where not exists(key)" (that's the next step) we remove all records with the same Key, then how will this enable us to update a record?

[swuehl · Accepted solution]

We are not removing ALL records with the same key, we keep the one first loaded, i.e. in the step loading from source in the case of an updated record.

[orital81]

Thanks for the clarification swuehl!