none
What is a Business Key in Data Warehousing?

    Question

  • Hi,

    Could someone please explain me what exactly is a Business Key in the DW world? A few references I've gone through online are quite confusing. Please explain me the importance of Business Key, when is it used and how is it different from a Surrogate Key?

    Your help is highly appreciated.

    Thanks.


    Known is a DROP, Unknown is an OCEAN.

    Thursday, October 03, 2013 2:25 PM

Answers

  • Hi,

    I try to explain it by using simple term:

    Business Key usually be the natural key, by setting the business key you could be able to apply different type of SCD to maintain your DW, more information can be found through the link below:

    http://blogs.msdn.com/b/karang/archive/2010/09/29/slowly-changing-dimension-using-ssis.aspx

    Surrogate key will usually be the numeric ID that being generated automatically by the system.

    For example, in Production dimension table, Product barcode could be business key as it's unique for that specific product. However barcode can't be Surrogate key as Surrogate key need to be numeric and generated by the system. In this case the Surrogate key could be the ID which automatic incremented whenever new product comes in.

    Regards

    Stephen

     
    Thursday, October 03, 2013 4:10 PM
  • The link I provided above is an example of how to use Business Key to apply the SCD.

    You are right about the Surrogate Key, however for Business Key's definition, it doesn't need to be exactly alpha or alphanumeric, as long as it's link to the record and unique, you could use that attribute as Business Key.

    As the example I mentioned ealier on, bar code of the product could be all numeric, but it's link to the product and unique for that specific product, therefore it could be set as business key.

    Regards

    Friday, October 04, 2013 9:27 AM

All replies

  • Hi,

    I try to explain it by using simple term:

    Business Key usually be the natural key, by setting the business key you could be able to apply different type of SCD to maintain your DW, more information can be found through the link below:

    http://blogs.msdn.com/b/karang/archive/2010/09/29/slowly-changing-dimension-using-ssis.aspx

    Surrogate key will usually be the numeric ID that being generated automatically by the system.

    For example, in Production dimension table, Product barcode could be business key as it's unique for that specific product. However barcode can't be Surrogate key as Surrogate key need to be numeric and generated by the system. In this case the Surrogate key could be the ID which automatic incremented whenever new product comes in.

    Regards

    Stephen

     
    Thursday, October 03, 2013 4:10 PM
  • Thanks for the reply Stephen. I didn't understand the relation between Business Key and the link you've provided.

    However, from what you described below and what I understood was, whenever you have an alpha or or alphanumeric column as primary key, you consider it as Business Key and whenever it is an integer with an autoincrement value, it is considered as Surrogate Key. Is that what you mean?


    Known is a DROP, Unknown is an OCEAN.


    • Edited by Bangaaram Thursday, October 03, 2013 7:18 PM
    Thursday, October 03, 2013 7:17 PM
  • The link I provided above is an example of how to use Business Key to apply the SCD.

    You are right about the Surrogate Key, however for Business Key's definition, it doesn't need to be exactly alpha or alphanumeric, as long as it's link to the record and unique, you could use that attribute as Business Key.

    As the example I mentioned ealier on, bar code of the product could be all numeric, but it's link to the product and unique for that specific product, therefore it could be set as business key.

    Regards

    Friday, October 04, 2013 9:27 AM