I have 50 million records with 35 columns my QVD size is 6.5GB ..HelpNeeded

[Anonymous]

I have 50 million records in my fact table and has 35 columns in the table so when i create the QVD the size of the QVD reached to 6.5 GB .. Is there any way i can reduce the size of the QVD. or is that expected.

[chris_johnson]

Hi,

Well QlikView should reduce the size of the file as much as it can, removing duplicate values and only storing them once.

One other thing you could try which would make use of this principle is that if you are storing fields that can be split up into fields that QlikView can then reduce down that may help.

For example, if you are extracting Post Codes then instead of having thousands of individual post code you can split it up and reduce the number you need to store. Post Code values 'B1 1AA', 'B1 2AA', B1 3AA' can be split up into Post Code Start and Post Code End. 'B1' can then be stored ONCE in Post Code Start and then you just need to store the different values for Post Code End.

What I'm saying is all about creating fields which have duplicate values that QlikView can store once and reduce the file size. Phone Numbers is another good one, where you can split out the area code (which will be duplicated many times and therefore only stored once).

Hopefully someone else can chip in too as other than looking at your columns and field values it's hard to say anything more.

Regards,

Chris

[Miguel_Angel_Baeyens]

Hi Vinay,

What is the size of the fields you are storing i. e.: string literals, numeric values, double float numeric values...? If you have a date field in the source table, you can store month by month or year by year to make smaller and more manageable QVD files.

Hope that helps.

Miguel

[Anonymous]

Thank you Chris Abwat- Jhonson for the quick response. I will see if there are any columns like that and get back to you on that.

[Anonymous]

Hi Miguel I have so many date fields in the table but only one date field which is more important for me. can you suggest me in detail how can i slice the QVDs based on the year.

[Miguel_Angel_Baeyens]

Hi Vinay,

You would need to do a loop, at least for the first load, let's say you know there are 12 months to load. The code then would look like

FOR i = 1 TO 12
     Data:
     LOAD *;
     SELECT *
     FROM TABLE WHERE Month(DateField) = $(i);
     STORE Data INTO [Data_2012$(i).qvd] (qvd);
     DROP TABLE Data;
NEXT
// load data later in the script to create the data model
Data:
LOAD *
FROM Data_2012*.qvd (qvd);

It's not the faster way of loading data, though, but in some cases there is no other way and it's better to have more smaller files than only one huge file.

Hope that helps.

Miguel

[Anonymous]

Thank you Miguel this information helps a lot.

[#8]

Hi, Chris & Miguel

Thanks for very usefull information.

Regards

IndianQvLover

[Anonymous]

Something else that might be useful:  If you are using keys to connect your tables together in QlikView, you might consider using AUTONUMBER() to take those down to an integer.  I know I am all always working with keys like this that unnecessarily take up lots of data space: { 21EC2020-3AEA-1069-A2DD-08002B30309D}.  Reducing them to a number greatly reduces size AND speeds up the performance of your application.

Another tip would be to get rid of any unused fields.  I use Rob Wunderlich's Document Analyzer pretty rugularly.

[Anonymous]

hi Aaron thank you for your response i have such kind of keys in my data model .can you please suggest me how i can i use the autonumber() in order to bring them to integer. If you have any example sorry if i am asking more.