Quick Start: Supernatural Keys

Key Points: Supernatural Keys

  • Supernatural Keys, a type of surrogate key, persist unique values and are a designed to be unique when natural keys may change or not be guaranteed unique.
  • Key Stores are what tie supernatural keys together.
  • All supernatural keys that you want to relate should use the same key store. Different key stores will assign different key values.

Key Steps: Supernatural Keys

Adding a Supernatural Key Field

  1. Select Add a Supernatural Key Field on a table
  2. Name 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
  4. Choose to select the Only read from the store, if it fits your solution

Adding a Key Store

  1. Select Add next to the Key Store in the Add Supernatural Key Field dialogue box
  2. Provide a Name for the new Key Store
  3. Select a Database Schema, Hashing Algorithm, and Key Data Type

For more detailed instructions, please see Supernatural Key Fields, (in the Fields article).

Understanding Supernatural Keys

Supernatural Keys are independent of the natural keys, e.g. customer numbers, that are found in the data and is durable, i.e. it does not change. Since natural keys can 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.

In TimeXtender, supernatural keys work like this: When you create a key, you choose some of the other fields on the table to base the key on. These fields are then hashed together and the hashed value is compared to other values in a key store table. If the same hash already exists, the corresponding key value is returned. If the hash does not exist, it is inserted into the key store and a new key value is generated and returned.

All supernatural keys that you want to relate should use the same key store. The data type of the field depends on the key store you use.

Selecting Only read from the store if you do not want to create a new entry in the key store when the business keys do not match an existing entry. The value of the field will be null if no matching key is found. With managed execution enabled, tables with fields with this option enabled will be executed after tables where the option is not enabled. This ensures the greatest possibility of a matching key in the key store.

In Business key selection, the available fields are listed in the left-hand list while the selected fields goes in the right-hand list. Add the fields you want to base the supernatural key on to the right-hand column.You must select at least one, which will be hashed together to create the supernatural key.

Key stores are what tie supernatural keys together. You will typically need a key store for each concept (e.g. customer or employee) you want to have a supernatural key for. Key stores exist on the data warehouse, i.e. each data warehouse will have its own key stores.

Hashing Algorithms may affect performance, but there are considerations when it comes to supernatural keys and key stores. Generally, TimeXtender recommends to stop using deprecated and legacy hashing algorithms. Changes the hashing algorithm option from Legacy Binary, Legacy Plain Text or Plain Text settings to SHA-1 SQL Server 2005+ on the project level, which affects most hashed fields in the project. This improves performance since the new algorithm is faster in addition to being typesafe. The exceptions are supernatural keys and key stores, where changing the hashing algorithm would break existing data, and junk dimensions using the Legacy Integer setting, where the hashing algorithm can make sense.

For more detailed information, please see Performance Recommendations.

Key Data Type options include:

  • Unique identifier (GUID): A 16 byte string of characters, and values will be unique in all key stores and in all databases.
  • Database unique auto increment (bigint): A 8 byte int that has better performance than the unique identifier. Values will be unique in one database. If you have several key stores in the same database, their values will never overlap.
  • Auto increment: A customizable auto-incrementing value. You can customize the first value in the key store, the number to increment with when a new row is added and the data type. Values will be unique only within the same key store, and may overlap from other key stores.

For more detailed information, please see Supernatural Key Fields, (in the Fields article).