Currently we do a SHA512 hash and cast the resulting varbinary as a bigint. This approach still has a very tiny chance there might be a hash collision because of the cast. Therefore we evaluated the Supernatural key functionality.
The Supernatural Key approach has two limits for us:
1. You need a key store per different number of fields you select
2. It's limited to a db / project
1. Makes key management overly complicated, 2. makes consolidation with External BUs quite difficult
I would therefore like to see a global key store where you would simply store the hash result (or UUID) and the autoincrement bigint, for all your projects.
This would allow easy consolidation of different BUs and provide a way of avoiding hash collisions by a) making sure different selections do not generate hash collisions, b) eliminating the need for casting, c) making one supernatural key you looked up part of a second supernatural key (e.g. hash Company and when a table has Company in its PK include the supernatural key of company in your new supernatural key).
This observation and idea stems from the fact that many of our customers are buying each other and ask us to combine their BI. And also from issues encountered which resulted in these requests: