Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, i have an application that is too big for our current server. I want to try and only load in the last 8 weeks worth of data but am unsure how to do this in the load script.
Can anyone help please?
I have attached my load script
Hi Chris,
You need to add a WHERE clause to each table you want to reduce data, for example
LOAD StoreNumber,
TransId,
TransStartDate,
TransStartTime,
EmployeeId,
TillNo,
TransStatus,
OverallDiscount,
Nolines,
TradingDate,
ZreadRef,
TrainingMode,
TrainerId,
%LkStoreTransID,
%lkStoreEmployee
FROM $(cFileName) (qvd)
WHERE Date(TransStartDate) >= Date(Today() -56);
You can try a debug loading limiting the load to the first X number of rows.
Hope that helps.
BI Consultant
Hi Miguel,
Thanks for your help again, is there a way of obtaining this but by still loading in optimized?
Thanks
Chris
Hi Chris,
You could create select from source so instead of having thw WHERE in the LOAD part, you have it in the SQL part, using the DBM dates functions. The result is two QVD files, one with 8 weeks, the other with the whole data. Or you do one load unoptimized with these 8 weeks, and store this reduced files into another different QVD files, so the next time you reload you reload from the reduced ones. Even using a variable or something in the file
SET vAllData = 1; // 1 will load from the complete QVD, 0 will load from the reduced QVD you have created with this where.
Table:
LOAD *
FROM File_$(vAllData).qvd (qvd);
Hope that helps.
BI Consultant
Thanks Miguel.
Instead i have loaded in my calender first using your where clause
WHERE Date(TransStartDate) >= Date(Today() -56);
as the calender is smallest table, so an unoptimised load isn't a problem.
Then i used where exists clause on the other tables.
It seemed to work locally, i am now just about to try it on the server.
Thanks for your help...