Let us consider an scenarios where you have designed a very good Data Warehouse , its catering all your reporting need and its in production. After two years organization decides to reuse there business keys of products
e.g. there was product called Baby Powder after two years organization decides to stop selling at its cost is high and sell is low and instead Baby Powder they launched a Talcum Powder for Men and want to give same key to the product say 336.
Now while designing a Data Warehouse you have used Business Keys as Primary Key in dimension table with this new change you will have to update the dimension table to replace Baby Powder with talcum Powder and organization does want to remove all data of Baby Powder, what will you do now?
So to avoid such situation its always better to use Surrogate Keys as Primary Key in dimension table along with Business Key.
What is Surrogate Key?
Surrogate Keys are integers that are assigned sequentially in the dimension table which can be used as PK.
e.g.
| SK_Prouduct | Product_ID | Product Name | Cost | Acive |
| 1 | 336 | Baby Powder | 444 | N |
| 2 | 345 | Cream | 34 | Y |
| 3 | 336 | talkem Powder | 44 | Y |
Now with above table you can use same code for another Product, mark the product as active and inactive and maintain all the product in Data Warehouse
Advantages of Surrogate Key:
- Surrogate Key allow to cater all your data need that arises from the Business or operational changes and recycling.
- Surrogate Keys allow the data warehouse to integrate data from all sources if they lack natural Business keys.
- Surrogate keys are very helpful for ETL transformations.
Did you enjoy this article? Please subscribe to Email or RSS to receive all the FREE updates!
Related posts: