4 Replies Latest reply: Mar 31, 2015 10:32 AM by Brian Garland RSS

    Reload Advice

    Brian Garland

      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 Pearce

          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
              Brian Garland

              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 Pearce

              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