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

GeekInterview.com > Interview Questions > Data Warehousing > Basics

Go To First | Previous Question | Next Question

Basics | Question 85 of 113

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

Total Answers and Comments: 8

Last Update: March 10, 2009 Asked by: venkat

Sponsored Links

Best Rated Answer

—

Submitted by: Jeff Seger

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.

Above answer was rated as good by the following members:
mri_sonam

January 18, 2007 06:13:36

srinivas

RE: What is the role of surrogate keys in data warehou...

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

Is this answer useful? Yes | No

January 18, 2007 06:21:19

srinivas

RE: What is the role of surrogate keys in data warehou...

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

Is this answer useful? Yes | No

February 19, 2007 02:43:58

joesph

RE: What is the role of surrogate keys in data warehou...

can you give more information about surrogate key with examples

Is this answer useful? Yes | No

February 22, 2007 19:57:57

Jeff Seger

RE: What is the role of surrogate keys in data warehou...

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.

Is this answer useful? Yes | No

1 0

Overall Rating: +1

April 24, 2007 03:02:17

Kadhirvelu

RE: What is the role of surrogate keys in data warehou...

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.

Is this answer useful? Yes | No

April 30, 2007 23:03:16

ravi76

Member Since: April 2007 Contribution: 1

RE: What is the role of surrogate keys in data warehou...

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.

Is this answer useful? Yes | No

September 26, 2008 02:02:07

lakshmib

Member Since: September 2008 Contribution: 3

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

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.

Is this answer useful? Yes | No

March 10, 2009 16:12:09

rajithav

Member Since: March 2009 Contribution: 1

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

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.

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank