0 Replies Latest reply: Jan 27, 2015 9:37 PM by Shivendoo Kumar RSS

    Incremental load of new as well as old data into a QVD file based on a configuration table and automating the same?

    Shivendoo Kumar

      Hi All,

       

       

      Seek your help in incremental load of FACT QVD File. We are using publisher.

       

       

      My Fact table in DB is getting loaded based on TXN_DATE using SSIS. We have a user configuration table

      where we have columns ETL Name and FROM_DATE and TO_DATE. FACT ELT is designed to pick

      FROM_DATE and TO_DATE from user configuration table based on ETL Name and then laod the data into destination.

      FACT ETL is designed to Delete and load.

       

       

      For Example: In My Fact we have data till 2015-Jan-27 but we got to know that we have some issue

      with 2015-Jan-26 data in source and got corrected in source today on 2015-Jan-28 and need to reload 2015-Jan-26 data.

       

       

      So We will update FROM_DATE (2015-01-26) and TO_DATE (2015-01-27) in our user configuration table for ETL 'FACT_ABC_ETL'

      and then RUN the ETL FACT_ABC_ETL. What It will do is pick the data from user configuration table and in source query it will be passed

      like SELECT * FROM SOURCE.SOURCE WHERE TXN_DATE >=FROM_DATE AND TXN_DATE<TO_DATE.

       

       

      But we can see that we have already loaded data in table FACT_ABC which are having some issue so we have a delete strategy to

      delete existing Target data and then relaod like DELETE FROM TARGET.FACT_ABC WHERE TXN_DATE >=FROM_DATE AND TXN_DATE<TO_DATE.

       

       

      ETL FLOW:

      Step-1: Get FROM_DATE (2015-01-26) and TO_DATE (2015-01-27) from user configuration table for ETL 'FACT_ABC_ETL' name

      Step-2: Delete existing data from TARGET.FACT_ABC like DELETE FROM TARGET.FACT_ABC WHERE TXN_DATE >=FROM_DATE AND TXN_DATE<TO_DATE

      Step-3: Reload the Target table based on FROM_DATE (2015-01-26) and TO_DATE (2015-01-27) like SELECT * FROM SOURCE.SOURCE WHERE TXN_DATE >=FROM_DATE AND TXN_DATE<TO_DATE

       

       

      Now Same step-1,2,3 want to implement for FACT QVD file which can load data from my FACT Table from DB and then This QVD file can be used to load data in QVW reports.

      How to achieve this and automate using publisher? Please guide me as I am new to Qlikview.