Madhura

• Sep 23rd, 2011

 

In slowly changing dimensions to have unique key in a record.

Sreedhar Lokaray

• Nov 2nd, 2011

 

By definition, surrogate key is a system generated key. This key is used as Primary key in the Dimensions. Why?

There are generally two principles to be applied when choosing Primary keys for the Dimensions table.

First principle, avoid built-in meaning in the primary key of the dimension table. In other words, the Product_ID in the operational system has built in meaning. The value in the key carry a meaning, like first 3 characters indicate product name, next 5 characters indicate category etc. These are built in meanings in the key.

Second principle, do not use production system keys as primary keys for the dimension table. Some companies reassign the customer_ID of the old customers who have left many years ago to new customers. In this given case if we use customer_ID as the primary key in the Customer Dimension then we will have problem because the same customer_ID could relate to the data for the new customer and also to the data of the old customer.

That is why surrogate keys are used as primary keys for dimension table.

Hope this clarifies your question.

sivaranjith

• Mar 25th, 2019

 

Surrogate key is used in Datawarehousing concept for SCD-2 implementation and there are history records stored for a particular record we cant use primary key as integrity violation will occur for the same record so in that case Surrogate key is used for historical and new records.