Surrogate Keys

Surrogate keys have become quite common in large data warehouse solutions. They often offer better performance and reduced storage compared to using natural keys.

To find the best candidates for surrogate keys in an existing solution you can use the Tuning Advisor.

PS6_DimMember_Part2_SalesEx.PNG

After transferring a table with a surrogate key you will be able to browse the view to see the resulting surrogate keys your natural keys are replaced with.

 

PS6_SurrogateKey_Part4_SalesEx.PNG

 

If you browse the surrogate key table you will see a collection of all surrogate to natural key mappings.

 

PS6_SurrogateKey_Part3_SalesEx.PNG

 

You can edit surrogate key settings to i.e. enable tracking of attribute history (Slowly changing dimension type 2).

 

PS6_SurrogateKey_Part2_SalesEx.PNG

 

 

See also

Slowly Changing Dimension Type 2