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. 

  • Interview Candidate
  • 21
  • 13232

Showing Answers 1 - 21 of 21 Answers

abhishek

  • Sep 5th, 2005
 

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. 

sekhar

  • Sep 15th, 2005
 

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

  Was this answer useful?  Yes

opbang

  • Sep 21st, 2006
 

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.

vskanth

  • Nov 14th, 2006
 

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

  Was this answer useful?  Yes

vara

  • Jan 25th, 2007
 

can uplz elobrate, how performance improves, queryprocessing

  Was this answer useful?  Yes

madhava1238

  • May 5th, 2008
 

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)

  Was this answer useful?  Yes

ASHOK1324

  • Apr 29th, 2009
 

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.

  Was this answer useful?  Yes

Datastage Etl

  • May 13th, 2011
 

Surrogate Key is used to  produce the Sequence numbers. So that, based on the Surrogate Key generated, we can  identify the  Unique Id in  any column. 

Surrogate Key is mainly implemented  in the SCD Type-2  
The main features of Surrogate  Key are developed in the Datastage 8.0 Version .
Some people may have doubt, If we have  Primary key why we go with Surrogate Key 
Because , Primary key will allow  the  numerical or alphabetical  in the records. But  Surrogate Key will allow only Numerics in the column. That means  performance of the Job will be   high. 
And In  Datastage 7.5 x2, we have a problem with  the  re-start of the  Sequence  with the Surrogate Key. 
In  Datastage 8.0.1, Surrogate key automatically identifies the last generated sequence number and it will continue with the next number ( For  Ex:  n+1 )  

  Was this answer useful?  Yes

Ganesh

  • Oct 8th, 2017
 

Surrogate key and business key are both are same or not,
if different business key act as a primary key but Surrogate key acts as ?

  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