Help me... how big is "big" in QlikView with respect to number of rows?

[#1]

I am relatively new to QlikView, coming from a pure relational database background and more recently from building OLAP cubes in SSAS.

Our implementation partner has warned us that our "Facts" table in our first QV application (which combines Sales, Demand, Receipts and Inventory at a SKU\Loc\Day level) is getting rather large, at 86 million records.

Is this really that large???  Speaking from a relational background, 86 million rows does not scare me...

Since QV is advertised as in-memory and having near instant response times, we have sold this to our business as such, and if we don't deliver this we're risking a perceived failure.  We we're even advised against holding inventory snapshots, as the volume of data would be too large?

I guess I am just trying to get a sense for what should be possible?  What is the accepted best practice for how much (or how wide of) data should be put into a single application.  We're building an application for our Inventory Management team, and to do their job they need the following types of data;

1. Sales (Retail Sales and Direct Invoices)
2. Demand (Direct Orders)
3. Purchase Orders and Receipts
4. Inventory
5. Plans
6. Store Traffic

Our plan was to build an application for them to do their job, so we planned to include all of these data points, but is this over ambitious?  Is the best practice in QlikView to build an application for a business process (i.e. Sales, Inventory, etc.) as opposed to such a wide application as we're attempting?

Any insight, thoughts would be appreciate.

Thanks,

Chris

[#2]

Hey Chris,

The short answer is - it depends.  The application size is more limited by the memory on the server and/or client.  Where you may see performance problems is in your charts depending on the complexity and number of expressions and dimensions.  I have worked with applications that have at least that many rows but the charts used were fairly simplistic. 

For long term planning, I think it is better to split into several applications.  At my previous employer we started with the one size fits all approach and ended with each group having their specific document to meet thier needs and goals.  You'll find that if you try to make it too generic then people are exporting to much to Excel for further modification or not realizing the full benefit ofd the tool because all they see is what they don't use. 

Just my two cents.

Cheers,

Heather

[kouroshkarimi]

As long as you have enough RAM you'll be ok. You absolutely do not want to start swapping to disk. With that amount of rows you might want to carefully consider what fields you actually need in the data model. Do some tests on your setup because it's difficult to tell for your exact circumstances, but I'm running 70-80 million rows with about 40-50 fields without issue.

Also, get as much calculation done in the script as you can. Minimise the number of loads and joins. Denormalise smaller tables... it all adds up.

[hic]

I agree with both the answers above. Just wanted to add that you should open the task manager and see how much memory QlikView is using and how much available physical memory you have left (Performance tab). This will give you a feeling for where you are. Because as kouroshkarimi points out: You do not want it to start swapping.

HIC

[IAMDV]

Hi Chris,

Please read this thread. Some good performance tips...

http://community.qlik.com/thread/18473

Cheers,

DV

www.QlikShare.com

[Anonymous]

Hello Kouroush,

Interesting. Would you give some technical information about the underlying machine that you are using?

Thanks,

Antoine

[kouroshkarimi]

Development environment is E5540 Xeon (dual), 52 GB RAM, shared between 3 users. Production is E7-4820 Xeon (quad), 1 TB RAM.

[Anonymous]

Hi Chris,

I agree with henric and in addition, i recommend you to do a proof of concept application. Enable the loggin in QV Server for this application, and analyzes the performance with QV Governance Dashboard, official application for this purpose. In statistics tab you can obtain the graphics like these:

Install the attached application for Governance:

Regards

Evandro

[Clever_Anjos]

The only true limitation is described into your manual, all the rest depends into how much memory you can install into your server.

There are some servers with 1Tb of RAM or more.

"The amount of data that can be loaded into a QlikView document is very large. It is primarily limited by the amount of primary memory of the computer. However, there is one inherent limitation in QlikView that you must be aware of when building very large documents: A QlikView document cannot have more than 2,147,483,648 distinct values in one field. The number of fields and tables as well as the number of table cells and table rows that can be loaded, is lim- ited only by RAM. When running the 32-bit version of QlikView, RAM is limited to 2, 3 or 4 GB, depending on operating sys- tem version and configuration, regardless of the size of physical memory in the computer.This limitation does not exist when running the 64-bit version of QlikView. "