topic Re: Why avoid Synthetic Keys? in QlikView

Why avoid Synthetic Keys?

matthewjbryant — Wed, 27 May 2015 12:32:54 GMT

It seems to be common practice to create a key column to avoid Qlikview joining tables via a synthetic key. Why is this? I would've thought that the join Qlikview sets up would be very efficient.

Are there pros and cons to each approach?

Re: Why avoid Synthetic Keys?

john_s_w_wood — Wed, 27 May 2015 12:36:11 GMT

It may not be noticeable with smaller data sets but with very large ones (100,000- Millions of rows) you may see a degradation in performance in the refresh of charts when selections are made. It could also highlight that you have an 'ambiguous' join e.g. join on a field called Date where in fact one date may be order date and the other date is Delivery Date.

Re: Why avoid Synthetic Keys?

simospa — Wed, 27 May 2015 12:37:45 GMT

Synthetic Keys are entity that can create duplication of data, this can bring you to have a down of performances and ambigous joins.

Try to use Ctrl+T and in table viewer see how many rows your Synthetic Keys have and think that all that data are unnecessary. Then with Syn you create many connections between tables, and with large size of data you could have problem with front-end user (for examples when applying selections).

Avoid Synthetic Keys is always a best practice to follow when working with QlikView.

In my opinion (and in my job) I can't see pros in Syntethic Keys 🙂

Re: Why avoid Synthetic Keys?

Wed, 27 May 2015 12:39:05 GMT

Hi Matthew,

Refer to this article Synthetic Keys by Henric. This might clear your doubt.

Regards,

Sridhar

Re: Why avoid Synthetic Keys?

ramasaisaksoft — Wed, 27 May 2015 12:41:58 GMT

Hi Matthew,

when ever you are working with huge amount of data then you can find exact problem.

-->synthetic keys are reducing the speed of the application

-->Data ambiguity is a problem

if your data model is having 7 to 8 synthetic tables associated keys then for reloading of the application is also takes much time comparatively normal data model(Without synthetic keys)

Re: Why avoid Synthetic Keys?

matthewjbryant — Wed, 27 May 2015 12:42:24 GMT

Ah Henric. He'll save the day!