topic Re: X records with n fields versus 1 record with (n + X) fields in QlikView

X records with n fields versus 1 record with (n + X) fields

Anonymous — Sun, 08 Sep 2013 18:58:18 GMT

Hi,

For a model that I am building, I am trying to evaluate which is the optimal way to go forward.

I need to create for each record to be analysed a set of parameters.

So basically, table size + performance as always are of key interest.

Intuitively, I lean for 1 record with (n + X) fields, with a key that links it back to my Fact table.

Before I start simulating in order to assess the impact, does anyone

- have a thought on the matter?

- identify additional key interest areas

These parameters will be :

0 - generated in the load script

1 - calculated based on the 10 fields (or a combination) that are given

2 - used to generate additional parameters in the record

Kind regards,

Antoine

Re: X records with n fields versus 1 record with (n + X) fields

Sun, 08 Sep 2013 23:16:07 GMT

Performance depends on both the number of records and the number of fields. I would not recommend a fact table with 10 million rows, but 300 fields. I've even seen a case where having 600 fields caused a weird error in QlikView.

The worst performance results have occurred when the model contains 2 tables with a large number of records and fields. For this reason I stopped using link tables long ago, but your question is a good one. I don't know what has more effect on performance: a large number of fields or a large number of columns.

Look forward to the results.

Karl

Re: X records with n fields versus 1 record with (n + X) fields

Anonymous — Mon, 16 Sep 2013 16:06:44 GMT

I managed to split the problem in half.
I generated the minimum required records and the minimum required fields.

When I went to full blast (i.e. all records) I generated out of 30k 16 mio with a size of 150 MB.

Execution time 11 min.

When I divided the problem, I kept a 500K records with additional 23 fields. Size 6MB

Execution time less than a minute.

Now for the next part of the riddle, which is how to automate the creation of the 23 fields.

http://community.qlik.com/thread/92181