topic Re: Best Data Model - Thoughts? in QlikView

Best Data Model - Thoughts?

Mon, 21 Sep 2015 09:33:07 GMT

So like a lot of businesses, we have millions of rows, (circa 20). At present, all of the data gets put into one Fact Table, not keyed, has 170 columns and is slow. My question is this - is it better to have one fact table with 170 columns or split it out into a star schema?

Re: Best Data Model - Thoughts?

tresB — Mon, 21 Sep 2015 09:41:26 GMT

That depends. If the table is mostly populated (information density is high, i.e - less number of nulls) the single table would be a better choice. If the table is actually derived from multiple tables and has got lots of nulls (because of joins or concatenations), might be you have to look back and think of breaking it down.

Re: Best Data Model - Thoughts?

Mon, 21 Sep 2015 09:43:09 GMT

Any chance you could expand on why this is please?

Re: Best Data Model - Thoughts?

Gysbert_Wassenaar — Mon, 21 Sep 2015 09:47:35 GMT

Perhaps this blog post helps: A Myth about the Number of Hops.

Where do you experience slow performance? Possible the data model isn't the problem but a chart is. Or one or more expressions that are not optimal for their purpose.

Re: Best Data Model - Thoughts?

tresB — Mon, 21 Sep 2015 09:56:41 GMT

If you go with many-table way, it gives you normalized model which would have advantages of:

Memory Efficient - (single table would consume much memory)
More meaningful when look at the model - you can understand and manage easily
Risk of wrong calculation reduces - that could appear in the process of joins(improper)

But this is not a such straight-forward case of winning over. There would always be a trade-off between CPU efficiency and Memory efficiency, and de-normalized approach(single table) wins by a small margin there.

Re: Best Data Model - Thoughts?

Mon, 21 Sep 2015 10:01:50 GMT

but more specifically where NULL values are concerned? a normalised model would win over a one table fact model because ...

Re: Best Data Model - Thoughts?

tresB — Mon, 21 Sep 2015 10:19:22 GMT

Though the NULL itself would not cause a big problem, but the other factors caused it's creation would. More nulls means information density is less and more number of refrences to a single value in terms of memory pointer. Memory pointers would require to point and store the same value numerous times and that becomes a performance killer.