incremental reload of .QVD on date/time fields.

[#1]

When we do incremental update on .QVD it checks with its underlying table for timestamp. Correct me if I am wrong. Is there any other way around if there is no timestamp in some of the table? I have combination of various dimension table and couple of fact tables our usual star schema. Fact table have timestamp but not every dimension table. How can I delta load/ incremental update in this case.

What we have been doing until now is, dropping and recreating QVW's all together. But its not a viable option once the data tables size has increased exponentially.

Any help will be greatful.

[Oleg_Troyansky · Accepted solution]

The lack of timestamps is quite a problem... On the flip side, there might be a plausible compromise. Usually Fact tables are huge, while Dimension tables can be big, but not way too big.

You could use incremental load for Fact tables and still reload your Dimensions in full - it should still be manageable if you used to reload the whole thing before...

[tabletuner]

Hi Nims,

If your fact table contains a date field then this is the only table that needs to be loaded incrementally. The dimension tables are usually small compared to the fact table, so you can reload them completely.

Regards,

Tjeerd

[johnw]

The basic question is this:

"How can I recognize rows that have been inserted/updated/deleted since my last reload?"

If you can answer that question, your answer will tell you how to do an incremental reload. If you CANNOT answer that question, then you cannot do an incremental reload.

As an example, let's say your database uses an automatically assigned ascending integer as a key, and rows are only inserted, not updated or deleted. Even without a timestamp, you can recognize what the highest key in the QVD is, and load only keys higher than that. So yes, you can do it without a timestamp. But you must have SOME way to identify the rows that need to be loaded.