Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
gramqlik
Contributor III
Contributor III

Incremental load from Oracle table

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')

 

?

0 Replies