Synthetic keys: to have or not to have ?

[#1]

Dear Community,

I am working to pass the certification.  One question I have concerns the synthetic keys (one item of the exam).

I read often: should be removed  (best practices, many answers from here ans there, ans so on)

I sometimes read: you can keep these synthetic keys (HIC blogs on IntervalMatch and Slowly Changing Dimensions for example)

Question: in what case, do we need to remove them (by building our own key) ? in what case, can we keep them as they are ?

Fabrice

[Gysbert_Wassenaar · Accepted solution]

See this blog post: Synthetic Keys

And this discussion: Should We Stop Worrying and Love the Synthetic Key?

Long story short: synthetic keys are very often, but not always, the symptom of a problem in the data model, but never the cause. If your data model has a problem then fixing it will often also remove synthetic keys. Getting the data model right should be the focus of your attention, not the removal of synthetic keys.

[#3]

Thanks Gysbert,

I remember I had read these posts a some time ago, and I have forgotten them. I will never be certified if I forget everything !!

I understand better the topic now : as Henric says, I believe the consensus is wrong (but I have no experience to prove it). In the book QlikView 11 for Developers, the author suppresses the synthetic key to create his own. We see that way of doing many times : and the two blogs seem to say that it is useless. Once again: the consensus.

You say a bad data model is often the cause of these keys. I agree except that most of the time I see that the data model is wrong (just) because of this key : it would become good without it ? We even change the model to avoid it. It will be another debate ...

Thanks again

Fabrice

[barryharmsen]

Hi Fabrice,

As one of the authors of QlikView 11 for Developers let me add my 2 cents. There is nothing wrong with synthetic keys per se. However, our book mainly targets novice and intermediate developers and roughly 90% of synthetic keys they (seem to) create are caused by faults in the data model. That is why we simply recommend they be removed. When just learning QlikView you're not looking for the entire debate on the pro's and cons of synthetic keys, the book is big enough as it is

Of course, once you get more experienced in your use of QlikView you will recognize the times when a synthetic key and a composite key or link table achieve exactly the same. At that time you can decide for yourself which route you want to take.

Kind regards,

Barry

[#5]

Barry,

Thanks for the answer.

Your book is my bible and I recommend it each time someone asks for a good book. Because it is really a good book (by the way, congratulations to you and Miguel Garcia for the job and the synthetic way to learn QV).

And because it is my bible, everything in it is true . For the synthetic keys, by reading again the book and some posts, I was clearly lost if I could use them or not.

It seem's that the right answer is "it depends". It depends on your experience (that I do not have yet) and on the data model.

Cheers

Fabrice

[hic]

Somewhat late, but I'll add my two cents.:

I totally agree with both Barry and Gysbert. If you want a simple message, it must be: "Remove the synthetic keys"

If you want a slightly more correct message - but not as simple - it would be: "Is the data model correct?"

1. If the answer is "Yes", then keep the synthetic keys.
2. If the answer is "No", then fix the data model. And repeat this test. (You still might keep the Synthetic keys...)
3. If the answer is "I don't know", then remove the synthetic keys. And pray that the data model will become correct...

A couple of rules of thumb:

• If you have a synthetic key made from another synthetic key, then you absolutely have a bad data model.
• If you didn't expect a synthetic key when running the script, but still get one, then you probably have a bad data model.

HIC

[#7]

Thanks Henric,

It is a very good guideline to keep or to remove these keys.

Fabrice