topic Re: Reload Advice in QlikView

Reload Advice

Anonymous — Thu, 19 Mar 2015 16:59:18 GMT

So I have never used partial reload or dynamic update, but I now have an application do so and I'd like some advice on the best way to set it up.

My script loads about 30 million records of inventory transactions that cover 24 months, up through midnight last night. I now have access to a database view my DBA created that is identical except that it provides near real-time data SINCE midnight last night. This will give users up to the minute inventory transactions, as well as history, so that they always know what is going on with a particular product.

So I load the large history QVD each morning but how do I set up my script and Publisher task to pick up the "real time" updates throughout the day, but without reloading the 30 million row history each time? Below is what my current script looks like where I'm loading it all, but just once per day.

Transaction_History:

LOAD

Branch_Plant,

SKU,

Fiscal_Period_Date,

Order_Number,

%SKULotCodeKey,

Order_Type,

Order_Type_Description,

Document_Type,

Document_Type_Description,

Reason_Code,

Reason_Code_Description,

Lot_Code,

Transaction_Date,

Transaction_Reference,

Transaction_Explanation,

Transaction_Quantity,

Lot_Status_Code,

Lot_Status_Code_Description

FROM

Detail_Inventory_Transactions_MultiYear.qvd

(qvd);

Current_Transactions:

Concatenate ("Transaction_History")

LOAD

"Branch_Plant",

SKU,

"Fiscal_Period_Date",

"Order_Number",

Text(SKU)&Text(Lot_Code) as %SKULotCodeKey,

"Order_Type",

"Order_Type_Description",

"Document_Type",

"Document_Type_Description",

"Reason_Code",

"Reason_Code_Description",

"Lot_Code",

"Transaction_Date",

"Transaction_Reference",

"Transaction_Explanation",

"Transaction_Quantity",

"Lot_Status_Code",

"Lot_Status_Code_Description"

;

SQL SELECT *

FROM "Fg_DataMart".dbo."Detail_Inventory_Transactions_Changes";

Re: Reload Advice

richard_pearce6 — Thu, 19 Mar 2015 17:13:31 GMT

Hi Brian,

If your load is optimised its no problem for QlikView.

Best way to do this is to set up an extract qvw layer which handles incremental loading. I have an example here http://community.qlik.com/docs/DOC-7563 detailed videos on how this works is on my blog. QlikCentral.com

Best regards

Richard

Re: Reload Advice

Anonymous — Thu, 19 Mar 2015 20:15:00 GMT

While this data is loading optimized, the app goes on to load other data (e.g. production, sales) and perform a number of transformations and calculations. In total, it takes about 7 minutes for the app to do a full reload. However, the users are wanting a 10 minute refresh, at most, which is why I'm trying to see if I can do a partial reload of just that second table or maybe utilize a dynamic update since that second table is essentially "live" data. Obviously, doing a seven minute reload every ten minutes is not the way I want to go. And I don't really understand your incremental approach or how it could be applied to my situation.

Re: Reload Advice

richard_pearce6 — Mon, 30 Mar 2015 09:22:53 GMT

Hi Brian,

Sorry for the delay in responding.

This incremental load technique is the best way (in my opinion) of handling frequent reloads. It works on the principle of taking small bit size chunks and saving it to a QVD file. Most of the time taken is loading and saving the QVD files although this can be optimised by using monthly (or even daily) qvd files.

Once the data is stored in a QVD file then the dashboard can reload super fast and there's no need for a partial re-load.

Richard

Re: Reload Advice

Anonymous — Tue, 31 Mar 2015 14:32:06 GMT

Ok, thanks for the reply.