2 Replies Latest reply: Jan 12, 2015 7:26 PM by Rob Wunderlich RSS

    Weekly incremental load by pulling in 12 weeks and replacing in QVD

      Hi all -

       

      I have been struggling with syntax here. I have a requirement for a 12 week requirement. Huge amounts of data, nearly a billion rows from each of the fact tables involved. We do not have unique keys as that would take too much memory (about 4-7 times in several tests, using hash128 and just contcatenating dimensions to make unique keys).

       

      Anyway, the requirements are fairly simple: extract the last 20 weeks of data from the data source. In the stored QVD of full history data, bring in all history except for the last 20 weeks, and concatenate that with the 20 weeks loaded.

      -----------------------

      Question: would you pull in the last 20 weeks in the load statement, and pull in everything older than 20 weeks in the QVD in the concatenation load?

      ------------------------

      Sample code I'm applying this to:

      ------------------------

      Sales_Fact_Table:

      LOAD

      Sales_Type,
      Salesperson_ID,
      Report_Date,
      Customer_Age,
      Product_ID,
      Number_Sold,
      Number_Forecast;

       

      SQL SELECT

      Sales.Sales_Type as Sales_Type,
      Sales.Salesperson_ID as Salesperson_ID,
      Sales.Report_Date as Report_Date,
      Sales.Customer_Age as Customer_Age,
      Sales.Product_ID as Product_ID,
      Sales.Number_Sold as Number_Sold,
      Sales.Number_Forecast as Number_Forecast

       

      FROM

      Data.Sales as Sales

       

      WHERE

      Report_Date >= '1/1/2015'

       

      GROUP BY

      Sales.Sales_Type as Sales_Type,
      Sales.Salesperson_ID as Salesperson_ID,
      Sales.Report_Date as Report_Date,
      Sales.Customer_Age as Customer_Age,
      Sales.Product_ID as Product_ID,

       


      Concatenate (Sales_Fact_Table)

      LOAD

      Sales_Type,
      Salesperson_ID,
      Report_Date,
      Customer_Age,
      Product_ID,
      Number_Sold,
      Number_Forecast;
         
      FROM

      ..\Sales_Facts.qvd(qvd);


      STORE

      Sales_Fact_Table into

      ..\Sales_Facts.qvd (qvd);


        • Re: Weekly incremental load by pulling in 12 weeks and replacing in QVD
          Ajay Krishnan Prabhakaran

          Hi there,

          Here is a sample on doing incremental load -

           

          Let vExists = If(Filesize('Yourfile.qvd')>0,-1,0)  ;

          Let VNo_Of_Days_To_Load = 140 ;

           

          If $(vExists ) then

            Max_Day:

            Load Distinct

            DAY_CODE

            From Yourfile.qvd(qvd);

           

            RENAME Table Max_Day to Temp;

           

            Max_Day:

            NoConcatenate

            Load Max(DAY_CODE) as Max_Day

            Resident Temp;

           

            Drop Table Temp;

           

          Let VMin_Date_To_Load = Date(Peek('Max_Day'));

          Set vFilter = DAY_CODE >= '$(VMin_Date_To_Load )' ;

          Else

          LET VMin_Date_To_Load =  Date(Today()- $(VNo_Of_Days_To_Load),'MM/DD/YYYY');

          Set vFilter = DAY_CODE >= '$(VMin_Date_To_Load )' ;

           

          EndIf

          Table:

          Load * From DB

          Where vFilter ;

           

          If $(vExists ) then

          Concatenate(Table)

          Load * From Yourfile.qvd(qvd)

          Where DAY_CODE >= Date(Today()- $(VNo_Of_Days_To_Load),'MM/DD/YYYY') and

          DAY_CODE < '$(VMin_Date_To_Load )' ;

           

          EndIf

           

          Store Table into Yourfile.qvd(qvd)  ;

          Drop Table Table;

           

          This is just for an idea. You have to tweak it for your needs.

           

          Thanks

          • Re: Weekly incremental load by pulling in 12 weeks and replacing in QVD
            Rob Wunderlich

            If an optimized QVD load is important (I'm guessing is is based on the sizes), I would load the QVD first and then Concatenate the SQL results. Use a where not exists() to maintain the optimized load.

             

            ExcludeDates:

            LOAD

              date(today() - RecNo())+1 as Report_Date

            AutoGenerate 7*20

            ;

             

            Sales_Fact_Table

            LOAD * FROM ..\Sales_Facts.qvd(qvd)

            WHERE NOT exists(Report_Date)

            ;

             

            // Load the SQL and concat to already loaded table

             

            -Rob

            http://masterssummit.com

            http://robwunderlich.com