Why QV Recalculate Graphics Objects ?

[suzel404]

Hi all,

I need information about the performance of QV.

I have a datamodel built by left join between qvd (several steps)

LOAD ... LEFT JOIN LOAD .... STORE LOAD ... LEFT JOIN LOAD STORE ...etc.

Final, I have one QVD : 7 000 000 of records. Size : 1,91 Go. Fields in table : 320.

When I create graphics with expressions in Graphic . dimension : None. Expression : Set Analysis (Count(<{FLAG1={1}, FLAG2={1}, FLAG3={1}, FLAG4={1} > } FIELD).

How can I avoid recalculation ? When I change the tab this issue is the same.

What could be the best optimisation script and Design ?

I know I can use only the field necasssary. Is there other thing ?

(I use a function od translate, What's why is there a table in the data model). IS possible that QV does a cartesien product with all fields in the script ? I really don't understand what's happening).

Many thanks for your help.

QV version QV 11.20 SR5

HOST RAM : 16 Go.

[Oleg_Troyansky]

It's hard to guess without seeing the full picture, but your data structure makes me think that you might be experiencing a Cartesian multiplication because you have no native associations in your data model. All the tables are islands. If your chart requires fields from multiple tables, then you are likely to have a Cartesian multiplication.

Try to link as many tables as you can. There is no reason for your language and the labels not to be linked. If you have a single date, link your Calendar to the main data table.

The more natural associations can be defined in the data model, the better it is for performance.

Plus, 320 fields is A LOT !!! QlikView doesn't do well with such as wide table. Think creatively how you can reduce the number of fields.

Cheers,

Oleg Troyansky

www.masterssummit.com

[suzel404]

Thanks Oleg.

The devlopment method choosen in my team is to use One table. Prohibit the link between tables (no associations).

All fields in one table. (There is no DWH).

With natural associations on qvd of 2 Go, the sélections in qv are not quickly but slow. Chart are recalculate.

With one table on qvd of 2 Go, the sélections in qv are not slowly. I am not sure about that.

Thanks you for your reply.

[ashfaq_haseeb · Accepted solution]

Hi,

For this you need to go through these blog posts from henric.

http://community.qlik.com/blogs/qlikviewdesignblog/2014/04/14/the-qlikview-cache

http://community.qlik.com/blogs/qlikviewdesignblog/2013/08/20/the-calculation-engine

Regards

ASHFAQ

[IAMDV]

Hi,

I agree with Oleg. Your Joins might be doing Cartesian product. You need to double check that and I'm not sure about the reason behing sticking to one table. I think data modelling is more of an ART than SCIENCE. It all depends on your data & requirements.

Anyway, my design suggestions are to use the calculation condition on each chart object to make sure that QlikView is not calculating while the user is making the selections. And if possible move your filters to seperate tab compared to the chart objects. Because Qlikview calculates everything present in current sheet and I had this confirmed with Qlik. Also, as you have one table I'd recommend simplify your expressions as much as possible to just calculate instead of complex Set Analysis.

Are you using 320 fields with Straight table/ Pivot table?

My 2 pence and I wish you good luck!

Cheers,

DV

www.QlikShare.com