More tables vs wider tables

[pjaredchurch]

Hi All,

Can I get your thoughts on the pro's & con's on data model design of using less but wider tables vs more narrow tables.

I'm interested primarily in the App, I'm not thinking about the storage requirements for QVD's.

It seems to me like Qlik does normalisation for every column in memory anyway, so putting everything into a single table shouldn't take up any more memory, in fact potentially it becomes smaller because no key's a necessary to join between tables in the data model.

What benefits/disadvantages do you see?

Thanks,

Jared

[asinha1991]

Qlik uses concept of symbol table wherein a symbol table is created for each field. This symbol table has  bit code for each unique value in the field. In your actual data table these bit code are used instead of actual values. This results in lot of compression (as you will have a bit code even for blob) but having large tables still shall increase size( once I denormalized many to many relationship and ended up doubling model size).

Advantages of single table I have experienced are

1. If you have two big tables, then onscreen calculation is better in front end with denormalization. It avoids hoping to a great extent.

2. if you have common dimensions then you can make more comprehensible star schema data model.

I would avoid doing it if.

1. one of the table is very small (have few rows)

2. there are scenarios of many to many relationship causing lot of redundancy.

3. Since Qlik is inmemory, RAM of the server should always be considered. If size of app is very high in proportion to RAM, it will be slow anyways.

[marcus_sommer]

There is no general rule that one approach is better as another - it will always depend on the data, data-quality, development time, refresh time-frames, knowledge/experience, response-times, the biggest bottleneck in the environment, ...

Personally I start always with the approach to create a star-scheme - if it's sufficient worked (functional and perfomance) it's fine and if not I need to look for mistakes and optimizations and sometimes I decide to change the datamodel - but it's not often.

- Marcus