Incremental Load in QlikView Presented by QuontraSolutions

Information about Incremental Load in QlikView Presented by QuontraSolutions

Published on August 13, 2014

Author: quontra123

Source: authorstream.com

Content

Incremental Load: Incremental Load Presented By Quontra Solutions Online Training & Placement Support Call Us:+ 404-900-9988 Email:[email protected] Web:www.quontrasolutions.com Incremental Load: Incremental Load Is sometimes called… Incremental Load Differential Load Delta Load Incremental Load: Incremental Load Goal: Load only the new or the changed records from the database. The rest should already be available, one way or another. Comments on Buffer Load: Comments on Buffer Load Buffer (Incremental) Load … is a solution only for Log files (text files), but not for DBs. Buffer (Stale after 7 days) Select … is not a good solution. It makes a full Load after 7 days. And nothing in between… Incremental Load: Incremental Load Load new data from Database table (slow, but few records) Load old data from QVD file (many records, but fast) Create new QVD file Procedure must be repeated for each table Different DB-changes: Different DB-changes If source allows … Append only. (Logfiles) Insert only. (No Update or Delete) Insert and Update. (No Delete) Insert, Update and Delete. 1) Append only: 1) Append only Must be Log file Loads records added in the end of the file 1) Append only: 1) Append only Buffer (Incremental) Load * From LogFile.txt (ansi, txt, delimiter is '\t', embedded labels); Done! But it should be renamed to „Buffer (Append) Load…“ 2) Insert only: 2) Insert only Can be any DB Loads INSERTed records Needs the field ModificationDate 2) Insert only: 2) Insert only QV_Table: SQL SELECT PrimaryKey, X, Y FROM DB_TABLE WHERE ModificationTime >= #$(LastExecTime)# ; 2) Insert only: 2) Insert only QV_Table: SQL SELECT PrimaryKey, X, Y FROM DB_TABLE WHERE ModificationTime >= #$(LastExecTime)# ; Concatenate LOAD PrimaryKey, X, Y FROM File.QVD; 2) Insert only: 2) Insert only QV_Table: SQL SELECT PrimaryKey, X, Y FROM DB_TABLE WHERE ModificationTime >= #$(LastExecTime)# ; Concatenate LOAD PrimaryKey, X, Y FROM File.QVD; STORE QV_Table INTO File.QVD; Almost done But there is a small chance that a record gets loaded twice 2) Insert only: 2) Insert only QV_Table: SQL SELECT PrimaryKey, X, Y FROM DB_TABLE WHERE ModificationTime >= #$(LastExecTime)# AND ModificationTime < #$(BeginningThisExecTime)# ; Concatenate LOAD PrimaryKey, X, Y FROM File.QVD; STORE QV_Table INTO File.QVD; 3) Insert and Update: 3) Insert and Update Can be any DB Loads INSERTed and UPDATEd records Needs the fields ModificationDate and PrimaryKey 3) Insert and Update: 3) Insert and Update QV_Table: SQL SELECT PrimaryKey, X, Y FROM DB_TABLE WHERE ModificationTime >= #$(LastExecTime)# ; Concatenate LOAD PrimaryKey, X, Y FROM File.QVD WHERE NOT Exists(PrimaryKey); STORE QV_Table INTO File.QVD; 4) Insert, Update and Delete: 4) Insert, Update and Delete Can be any DB Loads INSERTed and UPDATEd records Removes DELETEd records Needs the fields ModificationDate and PrimaryKey Tricky to implement 4) Insert, Update and Delete: 4) Insert, Update and Delete QV_Table: SQL SELECT PrimaryKey, X, Y FROM DB_TABLE WHERE ModificationTime >= #$(LastExecTime)# ; Concatenate LOAD PrimaryKey, X, Y FROM File.QVD WHERE NOT EXISTS(PrimaryKey); Inner Join SQL SELECT PrimaryKey FROM DB_TABLE; STORE QV_Table INTO File.QVD; 4) Insert, Update and Delete: 4) Insert, Update and Delete ListOfDeletedEntries : SQL SELECT PrimaryKey AS Deleted FROM DB_TABLE WHERE DeletionFlag = 1 and ModificationTime >= # $(LastExecTime) #; QV_Table: SQL SELECT PrimaryKey, X, Y FROM DB_TABLE WHERE ModificationTime >= #$(LastExecTime)# ; Concatenate LOAD PrimaryKey, X, Y FROM File.QVD WHERE NOT Exists(PrimaryKey) AND NOT Exists(Deleted,PrimaryKey); Drop Table ListOfDeletedEntries ; STORE QV_Table INTO File.QVD; OK, but needs a DeletionFlag LastExecutionTime & Error handling: LastExecutionTime & Error handling Let ThisExecTime = Now(); { Load sequence } If ScriptErrorCount = 0 then Let LastExecTime = ThisExecTime; End If Final Script: Final Script Let ThisExecTime = Now(); QV_Table: SQL SELECT PrimaryKey, X, Y FROM DB_TABLE WHERE ModificationTime >= #$(LastExecTime)# AND ModificationTime < #$(ThisExecTime)# ; Concatenate LOAD PrimaryKey, X, Y FROM File.QVD WHERE NOT EXISTS(PrimaryKey); Inner Join SQL SELECT PrimaryKey FROM DB_TABLE; If ScriptErrorCount = 0 then STORE QV_Table INTO File.QVD; Let LastExecTime = ThisExecTime; End If Summary 1: Summary 1 Incremental Load possible for… Append only. (Logfiles) Yes! Insert only. (No Update or Delete) Yes! Insert and Update. (No Delete) Yes! Insert, Update and Delete. Slow, or demands DeletionFlag Summary 2: Summary 2 „Incremental Load“ normally not equivalent to Buffer (Incremental) Load … Comment: Comment The solutions above are (alone) probably not robust enough. In addition, the complete table should probably be reloaded regularly, perhaps once a month.

Related presentations


Other presentations created by quontra123