Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I am praticising 3 tier model - ETL -> datamodel -> binary load to UI.qvw.
Now, the QVDs are heavy, too huge that hae caused the datamodel to become 2GB.
so can I add a function or script in my datamodel - just to fetch the latest 6 months of data and reload? i mean overall, if possible , i dont want to go to each table to write a srcipt to set a limit.
Rgds
Jim
not quite clear to me: Do you wish to limit the data on loading resp. storing into QVD? Do you still need historic data, i.e. data being older than 6 months?
Or do you wish to limit the data on loading into your datamodel?
Is it workable to limit the data by year or by month and store into separate QVD's?
Peter
Yes I mean -
I wish to limit the data on loading into your datamodel?
I dont want to change anything on the qvds
simpliest then would be
LOAD
*
FROM myQVD (QVD)
WHERE
MyDateField >= ADDMONTHS(TODAY(), -6);
Depending your datastructure you may have to remodel the Where-Clause to match formats used.
Also you may calculate with MONTHSTART or the like.
Peter
Hi!
Add variable like this:
vLimit = AddMonths(Today(1),-6) ; // this is date for 6 month ago
After use this variable in Where clause like this:
QVD:
LOAD
Date
...
WHERE DateFromQVD >= $(vLimit);
Skvortsov S.
I think you can load a temp calendar
[TempTable]: LOAD Date(MonthStart(Today(), -6) + IterNo() - 1) as [Date] AutoGenerate 1 While Date(MonthStart(Today(), -6) + IterNo() - 1) <= Today();
and then for each big table with the Date field
FOR Each vTableName in $(vListOfTables)
inner join([$(vTableName )]) LOAD * Resident [TempTable];
NEXT
DROP Table [TempTable];
or as suggested by Peter but I would replace
WHERE MyDateField >= ADDMONTHS(TODAY(), -6); with Where Exists(Date);
for optimised load
JOIN will be more performant than my option on larger datasets
Thanks! But in my data model, there are more than 20 tables - the dim and fact. Do you mean I have to add in the where clause on each of the fact tables? I have about 10, I guess.
Rgds
Jim
Try to use Where Exists(<fieldname>) instead of a direct field comparison. With a simple Where Exists() you keep the "optimized load" of QVD data which is much more faster than load with standard comparison statements. To use the Exists() you have to build a white-list (...a temporary table) of allowed values first, e.g. a list of valid months. Of course this will only work, if your QVD files already contain such a month value.
And yes, you have to add this Where Exists() statement to all your load statements 🙂