Load only last 8 weeks data

[hopkinsc]

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

[Miguel_Angel_Baeyens]

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.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[hopkinsc]

Hi Miguel,

Thanks for your help again, is there a way of obtaining this but by still loading in optimized?

Thanks

Chris

[Miguel_Angel_Baeyens]

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.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[hopkinsc]

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...