A Supernatural Key can be used as a persistent and durable key when this type of key is not otherwise available in the data.

 A supernatural key is a durable key that is independent of the natural keys found in the data, e.g. customer numbers. Durability here means that the keys will not change. Since natural keys can sometimes change in the source system and are unlikely to be the same in different source systems, it can be very useful to have a unique and persistent key for each customer, employee, etc.

TimeXtender Data Integration implements supernatural keys as follows:

1. Create the supernatural key by first selecting some other fields available on the table to base the key on. The selected fields will be combined and then hashed in order to create the key value.
2. Once the hash value is calculated as the key value, this hashed value is compared to other values that are already in the key store table. If the same hash exists, then the corresponding key value will be returned. If the hash does not exist, then it is inserted into the key store and a new key value is generated and returned.

Content

Adding a Supernatural Key Field

To add a supernatural key to a table, follow the steps below.

1. Right-click on a table in a data area and click on Add Supernatural Key Field.

   

2. Enter a name for the Supernatural Key Field and add a Key Store (see below for directions) or select a key store that is already created.
3. Add at least one field to the supernatural key in the Business key selection, from the available fields that are listed out on the left. Select fields and click the Add button to move these fields over to the right side to include them in the list of fields that the supernatural key will be based on.

   Note: The order of fields matters since two lists of identical fields will only give the same result if they are ordered in the same way. Use the Move Up and Move Down buttons to reorder the fields.

4. Select Only read from the store to disable the creation of new entries when a key is not found in the key store. The field's value will be null when no matching key is found. With managed execution enabled, tables with fields that have this option enabled will be executed subsequent to tables where this option is not enabled, which ensures the greatest possibility of a matching key being found in the key store.
5. Click OK to add the supernatural key field.

Adding a Key Store

Key stores tie supernatural keys together, and are needed for each concept that requires a supernatural key to implement, e.g. customer, employee, etc. Key stores can be created on either the data area level, which means that each area will have its own key stores.

Add a Key Store for a supernatural key using the following steps:

1. If the Prepare Instance data area does not already contain a supernatural key field, then first add a supernatural key. See Adding a Supernatural Key Field above for more information. In the Add Supernatural Key Field dialog, select an existing key store or click the Add button to open the Add Key Store dialog.

   

    

2. In the Name box, type a name for the key store.
3. (Optional) In the Database schema list, select the database schema from those available. 
4. (Optional) In the Hashing algorithm list, click on the hashing algorithm to be used. Note that the selected Hashing Algorithm may affect performance. In general, we recommend using the SHA-1 SQL Server 2005+ or one of the other suggested options as they are type safe and can improve performance. There are other deprecated and legacy hashing algorithms available such as Legacy Binary, Legacy Plain Text, or Plain Text settings, but these should only be used for debugging. 
5. Click on the data type for the key from one of the following options:
   • Unique identifier (GUID): A 16-byte string of characters that is, for all practical purposes, universally unique.
   • Database unique auto-increment (bigint): A 8-byte int that is only unique within the database, but has better performance than the unique identifier.
   • Auto increment: A customizable auto-incrementing value where the user can specify the following:
     1. The first value in the key store.
     2. The number to increment when a new row is added.
     3. The data type.
6. Click OK to add the key store.

Video showing the setup

The video shows how to add a key in the customer dimension table and add a key in the fact table to make relations easy in the Deliver Instance.

https://use.vg/Je5dR6