How do you handle large reloads of QVWs (1GB and more)?

[#1]

Hi gurus,

I would like to know how do you handle and manage large QVWs (>1GB) if they should be updated more than once a day.

We do have 3 tiered layer

1) QVD 1:1 (3 GB) - join historic and new data into one table - 25 Minutes

2) QVD with certain joins, logic ... (4 GB) - not an optimized qvd load caused by calculations - 55 Minutes

3) QVW loads everything from 2nd QVD layer in Quality Layer (1 GB) - join several tables, many of them as qvd optimized - 17 Min

4) QVW has to be published via publisher into access point (1 GB) - 5 Min

If just one new record comes from the data source, then first and second layer, both with 10 Million records have to be refreshed.

Finally QVW has to be re-processed from scratch. Afterwards QVW has to be saved on HDD and then copied into another folder as productive.

Is there any other method to do this faster and more efficient?? - without dropping any fields in the qvd table ...

Thank you very much in advance for any inputs.

Regards

Stan

[#2]

Hi Stani,

  As per your drawing, You are expecting the how to insert the only new data into old QVW/QVD's right?

If that is you are expecting need to implement the Incremental Load Concept.

   Example: You have the one QVD name called sales.qvd with the Yesterday data. now you don't want to pull and load again all the data into qvd. for that create a script next to that script.

Step1 : Load the QVD

Step2 : Write the Script for new Data to pull. I mean Query Eg. Select * From sales Where Datamodified=Today()

Step 3: Concatenate both of the tables using Concatenate.

Step4 : replace the sales.qvd with the new data.

Sales:

Load * From sales.qvd;

concatenate

Select  * from sales where Datemodified=Today()

Store Sales into sales.qvd;

Santhosh G

[rajeshvaswani77]

Hi Stan,

You must ensure that the best practices as have been defined by QlikTech are followed while teh QVW has been created.

You need to check that there are no loops, synthetic keys and the final QlikView schema is a Star or a Snow Flake schema.

Also QlikView is clever enough that it stores the distinct value of a field only once, to save the memory/space/size.

For example : if number 999 occurs million times, it will store only 999 once and then have a pointer the next time thus occupying less bytes. You might need to look at options to break some fields at the QVD layer. Could be have date month and year split, so the space saves.

thanks,

Rajesh Vaswani

[Clever_Anjos]

A combination of Incremental Load (as described into your manual) and Partial Reload will speed up your reload

[#5]

Anjos,

exactly, I would love to use a partial reload as a standard solution. Will this work? Reload just tables which have changed every time?

Stan

[Clever_Anjos]

Check your manual for ADD keyword.

Pasting here part of my manual

[#7]

Anojs,

have you successfully implemented this feature? Could you please share some experience?

Thank you

Regards

Stan

[Clever_Anjos]

We have some customers that implemented it with sucess.

The great issue is taking aditional care while coding your script

1. Assure both routines (full and partial)
2. Being very carefull about not loading same data twice (using where exists for example)