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

[Anonymous]

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

[#2]

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

[Anonymous · Accepted solution]

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