topic Re: How many tables in ideal QV apllication in QlikView

How many tables in ideal QV apllication

deepakqlikview_123 — Wed, 04 Jun 2014 10:48:48 GMT

Hi All,

Can you please tell me how many tables will be in ideal QV application and what will be records count ideally.

Thanks

Re: How many tables in ideal QV apllication

Wed, 04 Jun 2014 10:55:19 GMT

Hi,

difficult question ....:)

regards

Darek

Re: How many tables in ideal QV apllication

deepakqlikview_123 — Wed, 04 Jun 2014 10:58:44 GMT

Hi Dariusz,

I know table numbers and record size can vary as per requirement, but I just wanted to know how it should be ideally.

Thanks

Re: How many tables in ideal QV apllication

Wed, 04 Jun 2014 11:18:21 GMT

I had 500 mln fact rows in my last app.

I found, that it is better to join some dimensions together.

So, at the end i had:

2 fact tables (~400 mln and ~ 100 mln) (there was some relation between 2 fact types), 2 dimension tables (after joining they had about 100 th. and 250 th.) + some technical tables (with expressions, some technical dimensions etc).

Less tables means shorter way during selection propagation.

regards

Darek

Re: How many tables in ideal QV apllication

deepakqlikview_123 — Wed, 04 Jun 2014 11:27:15 GMT

Hi all,

Can u plz suggest what would we count for number of tables and records ideally.

Thanks

Re: How many tables in ideal QV apllication

Peter_Cammaert — Wed, 04 Jun 2014 12:18:19 GMT

IMHO it's not the count of tables that is most important (nor the count of records, just increase the amount of RAM). For example if you have 8 big tables chained together in a star or snowflake diagram (e.g. 1 facts table and 7 dimension tables connected straight to the facts), performance will be way better than if you make a linear model like T1 <-> T2 <-> T3 <-> F1 <-> T5 <-> T6 <-> T7 <-> T8. The associative distance will be more important (the time it takes QV to walk from a seleciton in T1 to the associated records in T8).

Ideally, you get the best response times from a data model composed of exactly 1 (one) table (using "reverse or de-normaization"). QlikView compression takes care of the additional space requirements caused by repeating all dimension values.

Re: How many tables in ideal QV apllication

Wed, 04 Jun 2014 12:21:56 GMT

However many you need to display your information accurately and concisely

Re: How many tables in ideal QV apllication

Wed, 04 Jun 2014 12:22:52 GMT

I think we can all agree that there is no one "correct" answer to this. It all depends on the amount of data you have, and the way you have it organized. For some applications two tables might be ideal, but in others 20-30 may serve you better. There's no one size fit all answer here

Re: How many tables in ideal QV apllication

Wed, 04 Jun 2014 12:25:40 GMT

QV compression of course is great, but there is 2 important things in general for each field stored:

- distinct values

- binary index.

binary index grows up when number of distinct field values grows up (more bits needed to know which value we have in some table row) and when number of rows in your table grows up (we need to know value for each row).

So, with one table there will be in fact for each field big index stored.... this is why it is worth anyway to have star schema.

Sometimes there are also other factors which causes, that some other model than star schema is needed, because it is better for your calculations.

It is really many factors to consider when you plan your data model for bigger application

Re: How many tables in ideal QV apllication

fkeuroglian — Wed, 04 Jun 2014 12:56:50 GMT

Hi, see this

Re: How many tables in ideal QV apllication

Anonymous — Wed, 04 Jun 2014 13:11:16 GMT

One table is the ideal, if it logically makes sense

Number of rows - the fewer the better. Whatever makes sense of course.