how to load only 6 months of data to my datamodel?

[jim_chan]

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

[prieper]

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

[jim_chan]

Yes I mean -

I wish to limit the data on loading into your datamodel?

I dont want to change anything on the qvds

[prieper]

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

[sergioskv]

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.

[andrey_krylov]

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

[prieper]

JOIN will be more performant than my option on larger datasets

[jim_chan]

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

[frank_dehner]

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 🙂