topic Re: QVD File Size Limit in QlikView

QVD File Size Limit

Wed, 14 Jan 2015 04:10:25 GMT

Hello All,

I have a scenario in which I would need to aggregate large amount of data (about 11 million rows for 2 days). I am not able to load this data into QVD and it kind of takes forever.

However, the bigger problem is I would need to aggregate it for a month. Thats roughly 100+ million rows !!

Any suggestions about how to handle this scenario would be greatly helpful!!

Thanks.

Re: QVD File Size Limit

suniljain — Wed, 14 Jan 2015 05:54:38 GMT

100+millions rows is not huge volume . Try to handle aggregation in script itself and push maximum business logic in back end .

Re: QVD File Size Limit

Wed, 14 Jan 2015 06:08:39 GMT

Hi Sunil,

Thanks for the answer. The 100 mil really is the result set after aggregation(far more rows in the actual tables). Also all aggregation is done in the DB. I am just loading the aggregated result from DB into QlikView and then into QVD. And still I am not able to accomplish this in any reasonable amount of time.

How long does it generally take to read say 10 million rows of 20 columns into QV?

Re: QVD File Size Limit

tresB — Wed, 14 Jan 2015 06:27:59 GMT

It could depend on many factors, like your RAM size, CPU, db source(location), network trafiic, calculation complexity in the script to mention few. It could be as fast as 2 minutes; it could be never-ending(like in your case). Therefore, you should probably look into those factors and try to improve there.

Re: QVD File Size Limit

Anonymous — Wed, 14 Jan 2015 06:35:58 GMT

Are you aggregating data at the time of load into Qlikview (in select query) ? or aggregated data is already loaded to DB table and you are just fetching that data with simple select query ?

Re: QVD File Size Limit

Wed, 14 Jan 2015 07:04:45 GMT

Yes, Select query is aggregating. The script is something like the following:

Load *;

Select x,y,sum(z)

FROM A,B,C

WHERE ...

Group by x,y

But it is my understanding, that the Select is handled at the database and and Qlikview just loads the results. Please correct if I am mistaken.

Also, in some other BI tools, there is an option of dynamic querying. The reports have a prompt page and depending on the values selected we will be able to filter the tables in the query. In other words, we never query the entire table only a subsection of it. Can we do something similar in QV?

Thanks.

Re: QVD File Size Limit

tresB — Wed, 14 Jan 2015 07:17:52 GMT

Gokul krishnaa CB wrote:

But it is my understanding, that the Select is handled at the database and and Qlikview just loads the results. Please correct if I am mistaken.

Yes you are right here. However, the time being taken by your db still would be counted in qv load for the script:

Load *;

Select x,y,sum(z)

FROM A,B,C

WHERE ...

Group by x,y

Because, qv would only work on returned set of data. Try to run the same query in the db directly and see how long it takes.

Re: QVD File Size Limit

Anonymous — Wed, 14 Jan 2015 08:07:17 GMT

ok .. so you can just try to load all the data in qlikview with simple select query and then do aggregation in qlikview with resident load and then store resulted table to qvd.

Lets see if your performance is increased . Because your DB query is taking time because of aggregation function .

Also try to see the time taken by your simple select query in database.

Re: QVD File Size Limit

Wed, 14 Jan 2015 09:00:34 GMT

Thanks Tresesco. But my doubt is, does accessing the db via QV(OLE DB) somehow increasing the load time?

The following post is interesting in this regard.

Incremental Loads are dead, long live the CSV

I am not sure if this works as well.

Balkumar, I think joining in QV is bound to take more time than if we do it in the native DB.

Re: QVD File Size Limit

tresB — Wed, 14 Jan 2015 09:11:21 GMT

That is very much a possibility. Thanks for the link an interesting one to play with.