Incremental load from Oracle table

gramqlik — Thu, 15 Mar 2018 11:05:57 GMT

I recently embarked on introducing incremental loading. The plan was to start with a fairly simple QVW and once I get to grips with it, apply incremental loads to a number of other existing dashboards, to hopefully improve reload times company-wide.

My first attempt was a simple "new records only" load on a table containing about 1.5 million records. After battling with date formats, I finally got this working, and it has halved the amount of time the extract takes to pull data from the database, which is great.

The second stage is to perform a more complex "new, updates and deletes" load. I'm looking to apply this to a couple of tables, each containing around 100,000 records. This is a fairly static number (we'll never get to millions of records in these tables) as old records are periodically archived off.

1) First thing to ask - is it worthwhile performing an incremental load on tables of this size? I've seen advice saying "incremental loading is for tables containing millions of records". There's obviously a cost to the processing time incurred when applying incremental loading, particularly for the more complex type with updates and deletes. So is there a rough point (in terms of record count) where it becomes pointless to apply it?

If the answer to that is "it's still worth applying incremental loads for smaller tables", then:

2) I have an Orders table, which doesn't contain a unique primary key. In the Oracle db, it looks like there is a composite key PK_Order, which is maxde up of CUSTOMER_ID, ORDER_ID and LINE_ID. From what I can gather, I cannot refer to PK_Order in the QVW because it doesn't actually exist as a field. So for the new records, I don't think I can use:

OrderLines:

LET LastExecTime = DATE(FLOOR(ReloadTime()), 'DD/MM/YYYY');

LET ThisExecTime = DATE(FLOOR(Now()), 'DD/MM/YYYY');

SQL SELECT

PK_Order,

CLIENT_ID,

ORDER_ID,

LINE_ID,

FIELD4,

FIELD5,

FIELD6

FROM Orders

;

CONCATENATE

LOAD * FROM Orders.qvd

WHERE NOT EXISTS(PK_Order);

INNER JOIN

SQL SELECT PK_Order FROM Orders;

Instead.... it looks like I need to create a composite key of CLIENT_ID, ORDER_ID and LINE_ID within the SQL SELECT in the QVW, and use that. How do I go about this?!

3) Each Order record has 2 date fields - CREATED_DATE and UPDATED_DATE. Every record has a value in the former, but only those that have been modified have a value in the latter. Once I have the Primary Key sorted, presumably I can then use these dates to identify updated records. Would I use something like:

OrderLines:

LET LastExecTime = DATE(FLOOR(ReloadTime()), 'DD/MM/YYYY');

LET ThisExecTime = DATE(FLOOR(Now()), 'DD/MM/YYYY');

SQL SELECT

<Composite field>,

CLIENT_ID,

ORDER_ID,

LINE_ID,

CREATED_DATE,

UPDATED_DATE,

FIELD4,

FIELD5,

FIELD6

FROM Orders

WHERE GREATEST(NVL(CREATED_DATE), NVL(UPDATED_DATE))

BETWEEN TO_DATE('$(LastExecTime)', 'DD/MM/YYYY')

AND TO_DATE('$(ThisExecTime)', 'DD/MM/YYYY')

- or -

WHERE COALESCE(UPDATED_DATE, CREATED_DATE)

BETWEEN TO_DATE('$(LastExecTime)', 'DD/MM/YYYY')

AND TO_DATE('$(ThisExecTime)', 'DD/MM/YYYY')

topic Incremental load from Oracle table in QlikView

Incremental load from Oracle table