Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
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