topic How much data can QlikView handle? in QlikView

How much data can QlikView handle?

Tue, 27 Apr 2010 14:15:02 GMT

Guys,

We are conducting a vendor review and comparing Tableau, QlikView, Spotfire and Xcelsius.
How much data can Qlikview handle? From QlikView's manual, there is no limit on the amount of data except that it is limited by the database QlikView is connecting to. But, from your experience, how much in GB, can QlikView handle with a given Server size.

Also, Please point me to any resources that have some data on the comparison of these 4 tools.

Thanks,
Atlas.

How much data can QlikView handle?

Tue, 27 Apr 2010 22:31:07 GMT

Hey Atlas,

You need to evaluate Qlikview in slightly different terms.

Since Qlikview is an in momry associative technology, you're limited only by the number of records you can fit in memory.

That said, your concept of 'quantity' of data also needs to change. Your database, which is (as an example) 4 GB in size on your database server won't be 4GB in memory. QV is highly optimized, and generally speaking sees a compression rate of approximately 80%. (yup, I said 80) - I won't go into detail about HOW it does it, but it's not a big secret.

From there, the complexity and number of things you do to your data also impacts the size of that data in memory. Also, the number of users that are hitting each QV document will also inflate the size slightly.

Basically, there is no direct answer to your question. There is no 'limit' to the 'number' of records. It's not like Excel that can only handle 65k rows on a sheet. The limitations of the application are restricted by the physical capacity of the server running the document, the complexity of that document, and the density of the data.

Given an adequately beefy server, QV could handle anything you could ever throw at it. (and damned well I might add).

We went through the same process, and attempting to compare QV with basically any other solution occasionally looks like nonsense, because QV is a completely different paradigm.

I would recomend you download the free 'personal edition' of the software, it's quite easy to use, and get a sense of. A bit more complex to master, or develop with great efficiency.

How much data can QlikView handle?

johnw — Tue, 27 Apr 2010 23:58:07 GMT

This won't mean much, but just as a random data point, I can run or reload MOST of our applications on a old 32-bit dual-core machine with 2 GB of RAM. Only the largest applications require me to swap over to a 64-bit single-core machine with 4 GB of RAM. Our servers are a little beefier, but honestly, not by much. I think the main server is a quad core 64-bit with 8 GB. We're serving about 100 users with about 100 document accesses per day between them. Our biggest tables in our biggest documents are probably in the lower tens of millions of rows. Performance is good most of the time.

There are links to several reports comparing QlikView to other tools in the resource library, but the two I've read don't really discuss required server size. They're at a much higher level than that.

http://www.qlik.com/us/explore/resources.aspx

How much data can QlikView handle?

Anonymous — Tue, 21 Sep 2010 14:55:59 GMT

Jonh, a friend asked me about the data size once.

They already have a DW with 8TB.

Can Qlikview deal with it?

I don't know about numbers of users or any other information.

Regards,

Leandra Scordamaglia

How much data can QlikView handle?

johnw — Tue, 21 Sep 2010 17:37:06 GMT


Leandra Scordamaglia wrote: 8TB. Can Qlikview deal with it?

I'll give it a "maybe". My understanding is that QlikView is designed for gigabyte databases, not terabyte databases. But I don't think there's a fundamental limitation preventing its use on terabyte databases.

If we needed the ENTIRE data warehouse in memory at the same time, even with 10x compression, that's 800 GB of data. But it takes more for each user. Let's say we could run about 20 users at the same time for about 2 TB. That's how much RAM we'd need, because we can't afford to swap to disk. Looks like in theory, we can run up to 2 TB of physical memory on the beefy versions of Windows Server. So on paper, we CAN probably have our entire warehouse in memory with the right hardware and software.

In practice, it should be simpler than that, and we should be able to support a lot more concurrent users than that. We probably won't need the entire data warehouse in memory. We don't typically make one monolithic QlikView application that covers every single aspect of the business. We make QlikView applications that are targetted for specific areas of the business, particular groups of users. Each of these will only be taking on a small portion of the total data in the data warehouse. Now in the long run, between them all, if we have an active user base, we MIGHT still need as much memory as I mentioned, or even significantly more, but it could probably be spread across multiple servers rather than one giant box. So I suspect it's just a matter of economics rather than any fundamental limitations.

Unfortunately, our data volumes are MUCH lower than this, so I'm not sure how applicable my direct experience will be. I'm going to guess that we're currently managing about 50-100 GB of raw data in QlikView. I think we have several hundred users, but probably only a handful are active at any one time. I believe we're handling that on one physical server broken up into several virtual servers. I think the physical server has only 8 GB. I don't manage the servers, so I'm not entirely certain.

Maybe someone from a bigger shop can comment better on how well QlikView is handling larger data volumes like this. There must be someone with this much data or more. 8 TB isn't really all that much data in the grand scheme of things.

How much data can QlikView handle?

Wed, 13 Oct 2010 13:28:05 GMT

Hi,

Does the operating system on which I'm working matter, in terms of how much data I can re-load?

I'm working on Windows Vista and using Qlikview 9 and have 2 GB RAM on my machine.

I was trying to load a CSV of around 5.2 GB containing around 18 Million records and it gave that "Out of Logical and/or Virtual Memory 2 Mb" error. (the data has around 80 columns and contain text as well as numbers in them)

Since then I tried increasing the amount of Virtual Memory to 4096 Mb and re-loaded the data and still it showed the same error.

Then I tried breaking the CSV into 3 smaller CSVs of around 1.5 Gb to 1.7 Gb each but still can't load the data.

I don't think there is a problem with the data, as it works when I do a limited load.

Could you suggest anything else which would help in loading the data?

How much data can QlikView handle?

Wed, 13 Oct 2010 14:44:41 GMT

Hi anant_iyer1

yes, loading depends on your OS. If you are using a 32-bit Windwos (it doesn't depend on the version) the hard limit is 4GB RAM. Because 2**32 is 4GB. Even if you have 32GB in your computer, this is the hard limit using any 32bit-OS.

This limit even decreases at least to about 3.4 GB because the rest is used for internal purposes.

So if you have to load as much as you have to, try any 64-bit Windows and of course enough physical RAM. How much it will take depends an your data.

Anyway, I would Try to split the data(-files) and store them into several qvds and build them together afterwards.

Hope this helps

Roland

How much data can QlikView handle?

Wed, 13 Oct 2010 15:04:27 GMT

Hi kurokarl,

I had tried the approach suggested by you, of storing the data in separate QVDs and then stacking them using concatenate. It loaded the initial QVDs but still was not able to load all the QVDs.

In that regard I wanted to ask that does loading a large file (be it CSV or QVD) take the same amount of memory (RAM) as is does loading smaller files one after the other?

I think it takes the same amount of memory as I was monitoring the task manager for the amount of RAM taken up during the load.

And if so, then is there any way to overcome that, apart from increasing more RAM?

How much data can QlikView handle?

Wed, 13 Oct 2010 16:19:51 GMT

Hi again,

the truth is, you will need the memory (even for a short moment of loading the last few rows) into your model. The splitting of the data was suggested to be a workaround. Loading i.e. data of one year into one qvd, or one month per qvd, or one region, whatever.

But, as I said before, if you want to load all the data in one application, you will need the memory.

regards

Roland

How much data can QlikView handle?

johnw — Sat, 16 Oct 2010 01:13:14 GMT

I believe that with 32-bit, the limit is 2 GB per application even if you can address 4 GB total. The 2 GB limit may be adjustable to 3 GB with I think some bios setting. It doesn't matter how much physical or virtual memory you add if 2 GB is all operating system will address for your app. So your limit is probably 2 GB. While I'd expect compression to get your 5.2 GB CSV below 2 GB, perhaps the compression isn't occurring as the table is being loaded, but only after. I'd have thought the multiple-QVD approach would get around that, as we used a similar approach for our worst offender before we finally bit the bullet and bought 64-bit everything. But maybe it's just too much data, period.

If you're 32-bit, you might try to figure out the setting that gives you 3 GB of addressable space. When I tried it on my own PC, though, it caused it to become unbootable, and it took our techies a couple hours to get it back up and running, so use at your own risk.

If you're 64-bit (operating system and QlikView), and you feel that you're reading in only the data you need from the file, then RAM might be the only answer.

As always, it's hard to debug something like this remotely, so I wouldn't go and buy 64-bit everything and 32 GB of RAM just because some guy on a forum said you might need to.

How much data can QlikView handle?

suniljain — Sat, 16 Oct 2010 09:24:53 GMT

we are already using qlikview in a environment where we have more than 20 crores of record in a single table and we have more than 20 table which have that much data.

How much data can QlikView handle?

Sun, 17 Oct 2010 22:52:42 GMT

Hi Anant_Iyer1. If you are having trouble with smaller (1.5Gb) csv files, then I suspect it someting to do with the way you are loading the data rather than the data itself. A straight load should have no problem loading 1.5Gb, due to the way Qlikview compresses the data. But if you are doing any joins, that can drastically increase the memory requirement. We would need to see the script to better understand the issue.

Steve

How much data can QlikView handle?

Mon, 18 Oct 2010 05:12:42 GMT

For one of my applications I convert 36GB of text files into 11GB of QVD files and load that into a QVW file, add some more fields, do some joins and end up with a 2.6GB QVW file.

So you can get pretty hefty. But I do this on a 64bit machine with a lot of memory. I makes it to almost 15GB of memory at one point 😕

How much data can QlikView handle?

Mon, 18 Oct 2010 08:39:03 GMT

"Out of Logical and/or Virtual Memory" can happen if there are too many distinct values. It happens sometimes even in 256 GB of memory. Try not to load everything, but data that is really needed. And install the latest available hotfixes.

To anwer the original question, 200 million rows is business as usual, with decent performance. Above that, performance degrades quickly if you do "load *; select *".

You can go above 500 million rows, if you optimize the design and can afford hundreds of GB of memory and tens of CPU cores.

-Alex

How much data can QlikView handle?

Mon, 18 Oct 2010 11:11:35 GMT

Hi,

Thanks a lot to all of you, esp. to Alex for pointing out the distinct values thing. I think that might just be the problem, because most of the columns in the data that I'm trying to load have distinct values, i.e., each column has a large number of distinct values.

I had already tried aggregating the data, and had no problems in loading the aggregated data. Was trying to get the non-aggregated data into qlikview to see what type of analysis I can do with that.

Thanks again.

Anant

How much data can QlikView handle?

Mon, 14 Feb 2011 20:42:21 GMT

Hi Jhon,

Our company is planning to buy qlik view but I am stuck with the problem of having huge amount of data could you please tell me if we have to buy servers are we base on QVW file or QVD file.Because every year we have have data of 2tb .Doesnt QVD file would sit on SAN .How we can decide how much RAM is needed and I need to know exactly based on which file size we should decide RAM as we have more than 800 users.

Thanks

Radhika

How much data can QlikView handle?

johnw — Mon, 14 Feb 2011 21:20:21 GMT

A very basic rule of thumb is that you get 90% compression and then need another 5-10% per concurrent user. So you could vastly oversimplify and say you need (10% * 2 TB) * (100% + 7.5% * 800) = 12 TB of RAM per year. Since Windows only supports 2 TB of RAM, what you want thus looks impossible.

It's not, though. Chances are excellent that your 2 TB per year will be split across 50 or more different applications, each with only some small fraction of the total data. Maybe your biggest application is 200 GB per year with 5 years of data, so 1 TB. You won't have 800 concurrent users of this application if you only have 800 users. Maybe at the worst time of the worst day you have 50 concurrent users. Now you're looking at (10% * 1 TB) * (100% + 7.5% * 50) = about 500 GB. So you might be OK with 500 GB - 1TB on the server (allowing for other applications in the higher number).

Taking another approach, as a very rough estimate, I think our shop has about 100 GB of uncompressed data in QlikView, we have about 300 users, and we're just starting to bump into memory limits at 12 GB of memory on the server. Bump us up to 800 users, and maybe we'd need 20 GB. So with the way our own data is split across applications, and based on the way our own users use the data, we need about 20% as much memory on the server as we have raw, uncompressed data. So if you keep 5 years of 2 TB per year, you have 10 TB of raw data, and you'd need 2 TB of memory on the server.

So it's hard to say exactly how much memory you'll need. But I think it's pretty clear that it will be a lot. Of course, if you're used to 2 TB of data per year, maybe 500 GB to 2 TB of RAM on a server isn't a lot to you.

How much data can QlikView handle?

Mon, 14 Feb 2011 21:43:27 GMT

Hi Jhon,

2TB data is compressed with QVD files(of total 35 applications) and each application 30 users are there and we have total all applications of 300gb of QVW file.Please let me know how I should look into taking server size.Say each application will have like 30GB QVW file size for 12 months so could you please let me know how I can handle this data.Does QVD file also sits on RAM .

Thanks

Radhika

Re: How much data can QlikView handle?

Fri, 17 May 2013 08:33:29 GMT

Hi Atlas

Ii joind this community couple of months before....and saw you query on the data compression data volumes handiing on the application level....one thing i wanted to throw my thoughts on the same..you are comparing Qlikview Vs tableau Vs Spotfile Vs Xcelsius..lets take xcelsius first which was my favoorite ....

Xcelsius:- it is a just dashbaord tool which uses excel as source for processing the data in it..whenver you run the dashbaord, the data has to pass thru excel..i spent so many years and i've developed 1000 + dashbards by using xcelsius and different with connectors...interms of data processing it depends what exacty you are showing .

xcelsius is meant for highly aggreagted number you cannot take beyond that...if you have highly aggregated numbers then go for xcelsus,

but with xcelsius you cannot fill your requirement it needs BO or any Other OLAP connectors to connect it.......LOOK AND FEEL (user interface ) is amazing afcourse it uses flash lah.....

Qlikview:-Qlikview is a different tool it uses its RAM to process the data and compress 90 % of your original table having said that it all depends on data quality(Duplicates/Joins..etc)...the good thing about the tool is much faster if your comparing with xcelsius....one more good thing about is, you dont need data wareshouse at all it uses QVD as data source and stores all the data in it,

whenver you generate QVW the QVW triggers the QVD to process the same data.....

Cost wise and processing wsie go with qlikview.....if you have anything BIG in you mind like different reporting flavours like CANNED,ADHOC, DASHBAORDS, Drag and drop concepts for user then go for SAP BO, or Tableau

i dont know much about tabluau much but look and feel wise is damm good

good thing about tableau is, it will suggest the graphs based on you dimension, metrics selection...because most of the BI users want look and Feel and and some really Inteligence(Suggestions) in the Tool....

The above comments are purly based on my experince .......I'am implemntating complete End to End implementation on qlikview..will update on the same if you have any queries.....and please avoid any typo/grammatical mistakes..

Regards

Imran Khan

mahindrasatyanm

BI Arch

Re: How much data can QlikView handle?

Fri, 17 May 2013 08:51:13 GMT

Hi Atlas

xcelsius is meant for highly aggreagted number you cannot take beyond that...if you have highly aggregated numbers then go for xcelsus,

but with xcelsius you cannot fill your requirement it needs BO or any Other OLAP connectors to connect it.......LOOK AND FEEL (user interface ) is amazing afcourse it uses flash lah.....

whenver you generate QVW the QVW triggers the QVD to process the same data.....

i dont know much about tabluau much but look and feel wise is damm good

Regards

Imran Khan

mahindrasatyanm

BI Arch