What is the role of surrogate keys in data warehouse and how will u generate them?

  • Interview Candidate
  • 31
  • 7237

Showing Answers 1 - 31 of 31 Answers

srinivas

  • Jan 18th, 2007
 

A key which links the Dimension and Fact table. It is a sequence no.

  Was this answer useful?  Yes

srinivas

  • Jan 18th, 2007
 

The Surrogate key role is it links the Dimension and Fact table. It avoids smart keys and Production keys.

  Was this answer useful?  Yes

joesph

  • Feb 19th, 2007
 

can you give more information about surrogate key with examples

  Was this answer useful?  Yes

Jeff Seger

  • Feb 22nd, 2007
 

A surrogate key is a simple Primary key which maps one to one with a Natural compound Primary key.  The reason for using them is to alleviate the need for the query writer to know the full compound key and also to speed query processing by removing the need for the RDBMS to process the full compound key when considering a join.

For example, an shipment could have a natural key of ORDER + ITEM + SHIPMENT_SEQ.  By giving it a unique SHIPMENT_ID, subordinate tables can access it with a single attribute, rather than 3.  However, it's important to create a unique index on the natural key as well.

Kadhirvelu

  • Apr 24th, 2007
 

Surrogate key is nothing but a primary key to identify records uniquely in dimension and fact tables. It is basically a running system-generated sequence number.

ravi76

  • Apr 30th, 2007
 


A surrogate key is a substitution for the natural primary key.  We tend to use our own Primary keys (surrogate keys) rather than depend on the primary key that is available in the source system.  When integrating the data, trying to work with the source system primary keys will be a little hard to handle.  Thats the reason why a surrogate key will be useful even though it serves the same purpose as that of a primary key.  Another important need for it is because the natural primary key (i.e. Customer Number in Customer table) can change and this makes updates more difficult.

  Was this answer useful?  Yes

lakshmib

  • Sep 26th, 2008
 

Surrogate Key is an artificial identifier for an entity. In surrogate key values are generated by the system sequentially(Like Identity property in SQL Server and Sequence in Oracle). They do not describe anything. Primary Key is a natural identifier for an entity. In Primary keys all the values are entered manually by the user which are uniquely identified. There will be no repetition of data.

  Was this answer useful?  Yes

rajithav

  • Mar 10th, 2009
 

Surrogate key is a primary key for a dimension table. Most important of using it is independent of underlying database i.e Surrogate key is not affected by the changes done in the database.

  Was this answer useful?  Yes

Phani22585

  • Jul 26th, 2010
 

Surrogate Keys are one which are similar to Primary keys.
Their significance will comes into picture during  the maintainiance of Dimenion table with Historic valus also.

For example consider the below eaxmple. Product_Dim (Update/Insert)

Prod.Id  Prod.name
-------------------------
 101          Pen
 102          Pencil

For example in future If the Product name of 'Pen' was changed to 'Ink Pen'. Then the current record will be replaced. But if if u want to maintain a history of Dimenion records then this type of Dimension structure is not usefull.
At this point of time Surrogate keys will come in to picture.

Surr.Key Prod.Id  Prod.name Flag
-------------------------------------------
 1001       101         Pen          NEW
 1002       102         Pencil       NEW

Now if the name of Pen is changed to Ink Pen then we can insert data as below with out over writiing the Earlier record.

Surr.Key Prod.Id  Prod.name Flag
-------------------------------------------
 1001       101         Pen          OLD
 1002       102         Pencil       NEW
 1003       101         Ink Pen    NEW

Conclusion:
Surrogate keys are no were related to Business information. It is just populated by using any sequence. But were as Primary Key will have some business information.

No need of having the Surrogate keys if we are not maintaining any history of Dimension records. We can simply use the Primary keys there.

bcsandip

  • Oct 8th, 2010
 

Along with Jeff's answer, the surragate key also distinguish 2 records with the same combined primary key in the denormalized DWH tables where history is maintained.

  Was this answer useful?  Yes

Anindya1281

  • Jun 4th, 2011
 

surrogate key is a substituted key for the business key in data warehouse

This is generated while the loading process (ETL)

attributes of surrogate key

1) It has the same granularity of the Business Key (BK) it replaces
2) usually numeric

Advantages

1) It replaces alpha numeric composite BK hence saves space
2) Helps in the slowing changing dims
3) Join queries work faster
4) Data masking 
    For example if the SSN number acts as the primary key for a customer table but we don't want to show the ssn number to the users so we can use a surrogate key which would replace the ssn number and can still use it for the purpose of data mappings

thanks

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions