Defining a Dimension with Smart Keys

While conventional wisdom (and Kimball) says that using meaningful identifiers for surrogate keys is a bad idea, there are some exceptions where it makes sense.

Typical examples include:

• Calendar. An example of a Calendar smart key is – year, month, day e.g. 20110721. A DateTime value can be converted to this date format using this statement convert(char(8),date, 112).
• Time. (smart key example – hour ,minute, second e.g. 130159).

Dimensions which use ‘smart’ keys (i.e some kind of meaningful code as the surrogate key) are useful in your ETL because they do not require a dimension look up when loading a Fact table. They can also simplify SQL queries against your Data Warehouse.

Defining a smart key in Dimodelo

In Dimodelo Data Warehouse Studio you need to do 3 things to define the use of smart keys.

1. Formulate a smart key in your Staging table

The staging table is where the Smart key gets created. So for example, the source staging table of a calendar dimension would need a column that contains the smart key for each row (i.e. year, month, day e.g. 20110721).

2. Dimension Advanced Tab

On the advanced tab of the Dimension editor, set the ‘Custom Surrogate Key Name‘ to the name of the Smart Key in the Dimension. Un-tick the Surrogate Key is Auto Generated check box.

3. Dimension ETL Dialog>Advanced Tab

In the ETL dialog box for the Dimension, on the advanced tab, Set ‘Auto Generate Key’ to false, and select the column in the source Staging table that will be the source for your Smart Key. Only a single column can be used, so you may need to do a transform in your Staging table extract.

A column cannot be both a business key and a surrogate key, so don’t add the smart key column as an attribute of the Dimension. This will cause errors in code generation.