DataStage - Importance of Surrogate Key in Data warehousing?

GeekInterview.com > Interview Questions > Data Warehousing > DataStage

Go To First | Previous Question | Next Question

DataStage | Question 3 of 390

Importance of Surrogate Key in Data warehousing?

Surrogate Key is a Primary Key for a Dimension table. Most importance of using it is it is independent of underlying database. i.e Surrogate Key is not affected by the changes going on with a database.

Total Answers and Comments: 7

Last Update: April 29, 2009

Sponsored Links

Best Rated Answer

—

Submitted by: abhishek

The concept of surrogate comes into play when there is slowely changing dimension in a table.
In such condition there is a need of a key by which we can identify the changes made in the dimensions.
These slowely changing dimensions can be of three type namely SCD1,SCD2,SCD3.

These are sustem genereated key.Mainly they are just the sequence of numbers or can be alfanumeric values also.

Above answer was rated as good by the following members:
antonyraj.deva

September 05, 2005 05:56:03

abhishek

RE: Importance of Surrogate Key in Data warehousing?

The concept of surrogate comes into play when there is slowely changing dimension in a table.
In such condition there is a need of a key by which we can identify the changes made in the dimensions.
These slowely changing dimensions can be of three type namely SCD1 SCD2 SCD3.

These are sustem genereated key.Mainly they are just the sequence of numbers or can be alfanumeric values also.

Is this answer useful? Yes | No

1 0

Overall Rating: +1

September 15, 2005 09:18:40

sekhar

RE: Importance of Surrogate Key in Data warehousing?&n...

this will be used in the concept of slowly changing dimension. inorder to keep track of changes in primary key

Is this answer useful? Yes | No

September 21, 2006 09:41:55

opbang

Member Since: March 2006 Contribution: 46

RE: Importance of Surrogate Key in Data warehousing?&n...

I slightly differ from the reply posted by Mr. Abhishek

Surrogate Key should be system generated number and it should be small integer. For each dimension table depending on the SCD and no of total records expected over a 4 years time you may limit the max number. This will improve the indexing performance query processing.

Is this answer useful? Yes | No

1 0

Overall Rating: +1

November 14, 2006 23:55:00

vskanth

RE: Importance of Surrogate Key in Data warehousing?&n...

surrogate is the systemgenerated key it is a numaric key it is primary key in the dimension table and it is forgien key in the fact table it is used to hadle the missing data and complex situation in the datastage

Is this answer useful? Yes | No

January 25, 2007 03:45:41

vara

RE: Importance of Surrogate Key in Data warehousing?&n...

can uplz elobrate how performance improves queryprocessing

Is this answer useful? Yes | No

May 05, 2008 06:39:11

madhava1238

Member Since: May 2008 Contribution: 8

RE: Importance of Surrogate Key in Data warehousing?

surrogate key is nothing but a primary key it gives a serial no. to all records.
surrogate key is act's as a major role in type 2 scenario.

if u have any updates to be applied on a database it will be directely effected on datawarehouse part(using surrogate key)

Is this answer useful? Yes | No

April 29, 2009 05:55:31

ASHOK1324

Member Since: April 2009 Contribution: 36

RE: Importance of Surrogate Key in Data warehousing?

Primary Key does not allow data duplications for actual source data. We cannot maintain historical data of each record using PK.

SID acts as a primary key in target WH systems that allows data duplications and maintains complete historical data along with current data.

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank