Business Key
The business key is the column or columns that uniquely identify a record within the dimension. Where the dimension maps back to a single or a main table in the source system, it is usually possible to ascertain the business key by looking at the unique keys for that source table. Some people refer to the business key as the 'natural' key. Examples of business keys are:
The product SKU in a product dimension
The customer code in a customer dimension
The calendar date in a date dimension
The 24 hour time in a time dimension (i.e. HHMM) (e.g.1710)
The airport short code in an airport dimension.
It is assumed that business keys will never be NULL. If a null value is possible in a business key then the generated code will need to be modified to handle the null value by assigning some default value. For example the Where clause in a dimension update may become:
SQL Server: Where isnull(business_key,'N/A') = isnull(v_LoadRec.business_key,'N/A')
Oracle: Where nvl(business_key,'N/A') = nvl(v_LoadRec.business_key,'N/A')
DB2: Where coalesce(business_key,'N/A') = coalesce(v_LoadRec.business_key,'N/A')
Note: Business keys are assumed to never be Null. If they could be null it is best to transform them to some value prior to dimension or stage table update. If this is not done an unmodified update will probably fail with a duplicate key error on the business key index.