Performance Issue - looking for your take

[avastani]

Here's my situation, the attached image shows what I am looking to do. However, in order to achieve this, every cell is an expression and the main expression is Pick(rowno(), exp1, exp2, ..., expN)

I tried using Set Analysis for the expressions and also tried with removing the set analysis for the product from the expression and putting it in the script which essentially gave me multiple fields.

In addition, the user has the ability to pick, Currency Type, Volume Unit of Measure and Scaling on the entire table. Not to mention the ability to view MTD, QTD and YTD.

With set analysis my performance is 45-60 seconds PER CLICK. without set analysis my performance is 4+ minutes per click. I would have hoped that removing the set analysis and precomputing the results would have made it better, however, it is the opposite.

I have 5 such tables that the user can pick to view, but only 1 can be seen at any one time.

The data is SUM and GROUPed by to ensure maximum compression. The date selections use flags as do the product expressions use flags instead of strings comparisons.

Is there anything else someone has tried with performance issues, short of breaking the tables into its groups and then displaying 1 at a time? It improves performance a little bit but not as drastically as I would hope for it to. May be I am pushing QV too hard with 300+ expressions in 1 table. This is with only 1million records. Thanks for your input folks.

[#2]

hi, i would be interested to see a copy of your dashboard as im designing a similar type. Would you be able to attach a copy of it?

[avastani]

Cannot attach the app due to security concerns, not about the data but the logic etc. However, I am happy to help you with your issues.

[#4]

Hey Amir,

This is just my armchair opinion, but a lot of performance issues I see like yours are due to rigid requirements that aren't reflective of QlikView's capabilities.

I can't really see what's going on in your spreadsheet, but I get the impression based on the performance problems that you're seeing, and the description of your elements that you're summarizing a lot of data that has little to do with each other all in a single table, and that your design process was extremely specific. (I want these measures, in a table that looks like this, with this here, and that there, etc)

The obvious solution to the problem is to simply break it up into multiple objects so you don't have to use the pick(), and various expressions workaround to accomplish your requirement.

I think if you can be somewhat more flexible about how the data is being displayed, you can achieve the performance that you're looking for.

I guess, ultimately what I'm getting at is: if your approach is giving you performance problems, try another approach.

[avastani]

Hello Ryan,

Interesting observation.. The performance issues are due to rigid reqs, however, splitting them into multiple objects does not rid of the core problem and it seems that this is where QV falls apart. It is great at doing single expressions across dimension or multiple single expressions over multiple dimensions.

But when you scale this to a scorecard like view (something many a clients are beginning to adopt) in QV, there's no easy way to do it when you are dealing with datasources like SAP, Oracle, etc where you have multiple hierarchies, and other complex metric definitions with set analysis i.e. factors that dynamically affect the expression based on the users click, for eg. scaling, currency, volume, energy conversions etc.

It is something that I have posted in the ideas area and hopefully catches the light of day with RnD to come up with an ingenious solution to tackle scorecards. QV is good at what it does but that does part is too simple and unfortunately enterprise decision-makers don't like it that simple.. they need to be able to see multiple metrics at the same time and make decisions. Execs rarely have the time or patience to click through the application to get their answers. They are too busy playing monopoly buying and selling so we have to cater to them...

Hope this helps.