topic Re: data model optimisation in QlikView

data model optimisation

Anonymous — Fri, 28 Aug 2015 14:02:55 GMT

Hi,

I'm searching for the ways to optimize the data model of my application in terms of dashboard performance.

Actually, my source data is very simple. It has the logic of an OLAP cube.

There's a dimension 'Advertisers' which is split by the dimension 'Brands', then 'Brands' are split by 'Subbrands' and so on.

Advertisers > Brands > Subbrands > Year > Month > Media type > Media vehicle > Region > Product Category and so on ... and finally

> Budget.

It comes to me in no other format but a very big Excel table with thousands and thousands of rows in it and up to 20 columns where each column corresponds to each dimension.

In QV on the backfront I normally do the following:

1) load this huge Excel table

2) store it as qvd-file and drop it

3) load the fields from the qvd-file to the one final table

Then my data model is ready.

Is it possible to optimize the data and to improve dashboard performance?

Thank you in advance,

Larisa

Re: data model optimisation

marcus_sommer — Sat, 29 Aug 2015 16:40:27 GMT

From a gui performance point of view performed such single table which included facts and dimensions mostly very well (except for the needed amount of RAM - if you haven't enough RAM it will be massive slow down the performance). So the performance will depend on the kind of calculations you are doing - I assume that you have not many records maybe less then 1 M records, so the performance is really (too) slow?

- Marcus

Re: data model optimisation

Anonymous — Mon, 31 Aug 2015 07:31:20 GMT

Hi Marcus,

The number of records is less than 1 mln now. The performance is not too slow now. But as soon as a new dimension is added, I notice a bit of a slow down.

I thought that maybe QlikView has a special data format (olap cube) which is more effective then a huge straight table (in qvd format) with multiple dimensions.

Larisa

Re: data model optimisation

gautik92 — Mon, 31 Aug 2015 07:47:20 GMT

drop unused fields

Re: data model optimisation

marcus_sommer — Mon, 31 Aug 2015 07:58:47 GMT

The way how qlikview the data stored (store only byte-stuffed unique values per column and use bit-stuffed pointers to associated them to the other fields) is a lot more efficient as the method of a classical olap-cube, see: Symbol Tables and Bit-Stuffed Pointers.

What do you mean with huge straight-table? A table with a 1 M rows and/or with a lot of (dozens ?) columns? To display such an object will need a lot of RAM and therefore time especially to transfer them from server to the client.

By an object which showed a more consolidate view of about a few dozens / hundreds rows which are calculated over millions of rows is normally very fast.

- Marcus

Re: data model optimisation

qlikviewwizard — Mon, 31 Aug 2015 08:01:51 GMT

Hi Larisa,

Please go through these documents.

Hope this will help you.

Thank you.

https://community.qlik.com/docs/DOC-7343

https://community.qlik.com/docs/DOC-4556

https://community.qlik.com/docs/DOC-5830

Re: data model optimisation

Anonymous — Mon, 31 Aug 2015 08:34:18 GMT

Thanks everyone for your comments!