[et_pb_section][et_pb_row][et_pb_column type=”1_4″][et_pb_sidebar admin_label=”Sidebar” orientation=”left” area=”et_pb_widget_area_1″ background_layout=”light” /][/et_pb_column][et_pb_column type=”3_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left”]

Defining a Dimension with Smart Keys

[/et_pb_text][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left”]

While conventional wisdom, and Kimball say 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 – 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- 130159).

Dimensions which 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 look up when loading a Fact table. They can also simplify SQL queries against your Data Warehouse.

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

  • On the advanced tab of the Dimension document, 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.
  • 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 if there are multiple columns that make up the Smart Key.

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.

[/et_pb_text][/et_pb_column][/et_pb_row][/et_pb_section]