Data table design

[#1]

Hi

Can anyone recommend a good learning source on the basics of data table design. Like having all data in one single table vs. lots of small tables?

[Josh_Good]

This is a big 'it depends' type of thing.  Generally speaking QlikView is fast enough that you don't need to worry about extensive optimizing of the data model to get noticeable performance gains.  Therefore, typically I organize my tables in a logical fashion that will enable developers and users to easily understand the model and find fields.  If the tables are already in a star schema then great but if not I typically don't bother to change the structure unless there are performance issues.

Strictly speaking you will get better performance when fewer joins need to be traversed to do the aggregation.  So this would drive you to a single table.  However as a table gets wider (i.e. more fields) it will tend to impact performance.  So that drive the data model to be more like a star schema.

Once again typically none of this matters in the real world since the app design (i.e. the objects and equations) tend to be bigger drivers on performance.  Best practice is to create a logical, easy to understand data model that requires the least amount of work.  Then build the app and optimize only if it is needed.

[#3]

Thx, but I would like to know more about the "depends"

When you say "performance" I guess you mean time - time it takes to show and calculate things? I thought most of it was "in-memory"? When does a table has two many fields? Does is depend on hardware like mainly memory?

When does a table has too many fields? Does is depend on hardware like mainly memory?

Apart from the time issue, I will never have an issue with one table - is there no cases, where you need to split the table into two tables, before you can do this or that kind of report?

Star schema vs. snowflakes, how to design it right concerning fact table and dimensions, often it will be obvious? 

[Josh_Good · Accepted solution]

Yes when I say 'performance' I mean rendering time and load on the server.  The limits on how big a table or data model can be is dependent on the hardware.  RAM will limit how much data can be stored in memory and the CPU will limit how fast calculations can be performed.  A table can't really have too many fields (assuming you need all the fields) you just have insufficient hardware.

I feel you are over thinking this a bit.  In almost every QlikView environment general good data modeling practices will give excellent performance.

If your have a particularly unique environment with massive amounts of data (Billions of records and thousands of fields) then you will need to worry about the specific trade offs between joins vs larger tables.  This could require a specific data model for your data (the actual data content will effect what you do) so I would recommend you engage with QlikView Consulting Services.

[#5]

Thx, well I want to become really good with QlikView, Business Intelligence and Data modelling in general. So I want to have both the practical and theoretical skills.

Over thinking, perhaps, but to become the best or at least in the league, you must start somewhere right (hmm is this a 10 year project ...).