Which approach is better

[#1]

1) One huge table with 20 millions rows and all columns in one table.

2) Few smaller tables + one huge without some columns

For example: Production which generate 30.000 rows every day has about 20 correcsponding columns att the end (5 columns from production, 5 for cost side, 5 for sales side, 5 for currency rates)

The question is: is it better to keep currency rates for every row, or to recalculate all costs and sales later when user pick one currency. First approach would take a lot of memory but user selection could be solved with simple set analysis. Second solution is better for memory but calculation will be impossible (I think).

Imagine, that final result will need to have some pivot tables, charts etc and that user will "click" a lot. Every click is new calculation. I will have production for last 6 months and it means that there will be about 15-20 milions rows in game. So,

what is better: 20 milions rows with 20 columns loaded and sorted in different charts and tables, or 20 milion rows with 10 columns + 5 to 10 connected tables. From database perspective, it is better to split and than to connect with queries, but I am not sure that I know if I can run queries during clicking. Is there some better approach that I can think about.

[fosuzuki]

Hi,

I wont really answer your question, but conceptually speaking, the overall performance is usually better in a star schema data model rather than in a single table. Of course, there are a lot of other things to consider which may be contrary to this recommendation.

I'm attaching the document QlikView Best Practices, from 'Enterprise Framework', and in page 15 you will find a brief overview of data models.

Hope this points you in the right direction.

Regards,

Fernando

[hic]

If you want to use the currency rate for calculations, then this might be a reason to keep all numbers in one table: I assume you want to make a calculation like Sum(Cost*CurrencyRate).

If the two fields Cost and CurrencyRate resides in two different tables, then QlikView will need to generate all possible combinations between two tables before it can start calculations. But if both numbers are in one table, the calculation can be made directly. So I suspect it is better to keep all numbers in one table.

However, to be really sure, there is only one way: You need to test...

[Anonymous]

In my opinion, fewer joins is always better. QV's great data compression makes one big table a good choice.