Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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
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
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