How does Qlik create synthetic key link tables?

[steverosebrook]

We've had a synthetic key in our primary data model for several years.  It's an expected issue, since we have several tables that have composite keys.  Initially, I wasn't sure how to create link tables, so I just let Qlik create a synthetic key, and all has been fine with our apps. 

However, we've recently learned via Qlik training how to use Autonumber and AutonumberHash128 - as well as manual concatenated key fields.  We thought we'd try what we learned in training to get rid of our synthetic key.  Unfortunately, in each case, what we tried made our app load longer (much longer in the case of AutonumberHash128).

So rather than hearing about the merits of Autonumber, AutonumberHash128, and/or manual concatenated keys and which one is better, my question is: How does Qlik internally build the synthetic key link table?  It seems obvious after our testing that what Qlik is doing internally is basically building a link table for us - but how is it actually accomplishing it?  I'm assuming it's doing some kind of autonumber process, but given that the synthetic key process is the fastest method we've tested, it must do it much more efficiently than we're able to do.

Could someone help explain the internal process Qlik uses to build synthetic key link tables?

Thanks,
Steve

[petter]

There is no official documented description from Qlik about how they are created. However if you think about it it is not so hard to figure out.

1) Qlik Sense first will discover that it needs a synthetic key as soon as two or more tables share two or more fields with the same name.

2) Next it is a simple match table that is created and it has to use some autonumber mechanism probably always starting at 0 and incrementing with 1. This key is used between all associated tables and the matching fields are kept in the extra synthetic table.

3) Synthetic keys are not always bad. But it is important that you really try to understand each particular synthetic key that is created and verify that it is valid and working correctly.

4) Synthetic keys can make a data model much harder to understand - so removing them and replacing them with your own link tables might be a smart thing to do most of the time. However there is a trade-off and you have pointed it out. It might cost a lot more processing time in you load script.

This thread goes into a longer discussion about the pros and cons of synthetic keys:

Re: Should We Stop Worrying and Love the Synthetic Key?