topic Are synthetic keys bad? in App Development

Are synthetic keys bad?

Markbhai — Mon, 29 Apr 2024 23:18:27 GMT

I am fairly bew to Qlik

I am building an app and the data model contains 4 tables with fields which all refer to each other and I cant seem to get the resulting dashboard to work unless a synthetic key is created.

Is this generally acceptable or am I better to try to fix this?

Thanks

Re: Are synthetic keys bad?

rwunderlich — Tue, 30 Apr 2024 02:47:57 GMT

It's acceptable if the synthetic keys are "valid", that is, multiple fields are required to make a proper link. However some people prefer to always remove synthetic keys so there is no doubt of a data model issue.

See here for a step by step approach to evaluating and resolving synthetic keys. https://qlikviewcookbook.com/resolving-synthetic-keys/

-Rob

Re: Are synthetic keys bad?

igoralcantara — Tue, 30 Apr 2024 03:03:55 GMT

I always prefer to remove them since they impact performance for having an extra table in between other tables but the main reason I dislike it is because it makes the data model harder to read and interpret.

Re: Are synthetic keys bad?

Vegar — Tue, 30 Apr 2024 05:33:01 GMT

Synthetic keys are not necessarily bad, in some cases you can experience improved performance keeping a synthetic key.

If you have a lot of synthetic keys between your four tables, then I would rework my script in order to remove them. Normally that is done by creating new dimensions by concating the different field values into one common field name.

However, when you say four tables, then I also would suggest you to consider if a concatenated table could be an alternative way to remodel your application. That is adding data from all four tables into a single table. That will also eliminate any synthetic key you have between them.

Re: Are synthetic keys bad?

maxgro — Tue, 30 Apr 2024 06:27:23 GMT

When it comes to synthetic keys I also like to suggest these 2 posts

https://community.qlik.com/t5/Design/Synthetic-Keys/ba-p/1472634

https://community.qlik.com/t5/QlikView-App-Dev/Should-We-Stop-Worrying-and-Love-the-Synthetic-Key/td-p/213649

Re: Are synthetic keys bad?

marcus_sommer — Tue, 30 Apr 2024 07:17:41 GMT

Just an addition to "valid" synthetic keys - in my experience it's restricted to scenarios in which you have a full control to the data and you could ensure the data-quality and no NULL within any key-parts, mostly exceptions like an intervalmatch may create such one.

If not you may get wrong results within some calculations especially in partial sums. If just a few key-values aren't properly it's hard to detect and may occur quite late in the development process by loading the final data-set or even more worse nobody noticed it ...

In regard to save efforts and performance I suggest to avoid synthetic keys at the beginning.

Re: Are synthetic keys bad?

Markbhai — Tue, 30 Apr 2024 08:56:17 GMT

Thanks everyone. It is really good to hear what others think. Specifically the comment from Vegar, which made me bash my head in a realisation of my dumbness, in that I could always join the tables in a different way.

I have now created a left join between all of the tables, which makes life much easier!

Thanks all.