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