Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
chriys1337
Creator III
Creator III

Datamodelling Best Practice

Hello,

I am having a question about the best practice for Datamodelling in Qlik Sense.

I understood that all keys and numerical figures should centralized in the fact table and around are the dimensions.

I have implemented it like this. Now there are some dimensional table which ONLY include the key and one non numerical field.

A experienced developed gave me the advice to join these tables, which consists of 2 fields only, back to the fact table but this would break the inital recommendation of splitting between numerical facts and key vs. dimensional fields.

Do you have any experience about this matter and can share how you are doing it?

 

 

Labels (1)
4 Replies
marcus_malinow
Partner - Specialist III
Partner - Specialist III

The advice you've been given is perfectly valid. I'd only caution against it if at some point your dimension is likely to gain additional columns.

Another approach would be to create a junk dimension, to hold the contents of all your single field dimensions, however the key to this can grow large depending on the cardinality in these fields.

With this sort of issue there's no one-size-fits-all solution. Your approach will be governed by issues around performance (reload and end user), data volume, data cardinality, etc. I would not discount the approach suggested because it doesn't conform with the 'ideal' approach.

Vegar
MVP
MVP

Your recommendation "that all keys and numerical figures should centralized in the fact table and around are the dimensions" is a good rule of thumbs. In this case you will most likely don't see any big difference in performance either or the other way. 

Personally I would probably have broken the rule of thumb in this situation, as I get more clearity in my data model. One less box in my data model overview.

gmenoutis
Partner - Creator II
Partner - Creator II

It is refreshing to listen to someone support the normalized way @Vegar

I, too, would recommend keeping it as it is. There seems to be a strong tension for joining tables in order to avoid runtime joins. I do not agree with this part. A Normalized data model never returns duplicates. The other can. Sooner or later, it will probably happen.

Also, while "Advice" come and go, there is never substantiation behind it. I suspect that the qlik engine is too deep for instructors to get to details. The most detailed reference I've found is bit-stuffed pointers. We are still blind to the workings of the underlying (postgres IIRC?) database, so a large part of advice is speculation.

My humble opinion? The only things that should be joined in a central table are the section access fields. Denormalizing will rarely give a performance boost that can be felt, while the duplication errors created will most certainly be so.

mark_costa
Partner - Creator II
Partner - Creator II

Old topic but it is a nice discussion.

For your case, at the end, I think it will not make any difference, especially if we are not talking about too many records. This is more about code style.

Personally I think the whole thing about data for reporting in Qlik is to have denormalized data, this is faster than the normalized way. If the join is not affecting your records in a bad way, go for it, it is one step removed in your charts calculation.

Now, if we are talking about designing a Data Warehouse, I will definitely keep the tables separated.

 

Regards,

Mark Costa

Get hooked on Qlik at qlikbait.net