Defining a Dimension with Smart Keys

While conventional wisdom and Kimball recommend against using meaningful identifiers for surrogate keys, there are some exceptions that make sense.

Typical examples include:

• Calendar. An example of a Calendar smart key is – year, month, day – 20110721. A datetime value can be converted to this date format in SQL Server using this expression CONVERT(char(8), date, 112).
• Time (Smart Key example – hour, minute, second 130159).

Dimensions that use ‘smart’ keys (i.e., some kind of meaningful code as the key) are useful in your ETL because they do not require a dimension lookup when loading a Fact table. You can simply just calculate the foreign key based on the data that is the source of your Fact. They can also simplify SQL queries against your Data Warehouse.