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

[#1]

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

[#2]

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

[rwunderlich · Accepted solution]

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