Reload Advice

[Anonymous]

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";

[richard_pearce6]

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

[Anonymous]

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.

[richard_pearce6]

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

[Anonymous]

Ok, thanks for the reply.