Home  
 
  Ask Questions  
 
  Feed Back  
 
  My Questions  
 
  Contact Us  
 
  About Us  
 


 Interview Questions  
 Our Services  
NEW INTERVIEW QUESTIONS.COM DATA WAREHOUSING INTERVIEW QUESTIONS DATA WAREHOUSING BASICS INTERVIEW QUESTIONS DETAILS


NewInterviewQuestions.com - Home for World's Largest Interview Questions Website.

A D V E R T I S E M E N T




Add to My Questions Add to My Questions | Forward Forward | Print Print


Question Question What is surrogate key ? where we use it expalin with examples

Answer Answer surrogate key is a substitution for the natural primary key.

It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a surrogate primary key is that it is unique for each row in the table.

Data warehouses typically use a surrogate, (also known as artificial or identity key), key for the dimension tables primary keys. They can use Infa sequence generator, or Oracle sequence, or SQL Server Identity values for the surrogate key.

It is useful because the natural primary key (i.e. Customer Number in Customer table) can change and this makes updates more difficult.

Some tables have columns such as AIRPORT_NAME or CITY_NAME which are stated as the primary keys (according to the business users) but ,not only can these change, indexing on a numerical value is probably better and you could consider creating a surrogate key called, say, AIRPORT_ID. This would be internal to the system and as far as the client is concerned you may display only the AIRPORT_NAME.

2. Adapted from response by Vincent on Thursday, March 13, 2003

Another benefit you can get from surrogate keys (SID) is :

Tracking the SCD - Slowly Changing Dimension.

Let me give you a simple, classical example:

On the 1st of January 2002, Employee 'E1' belongs to Business Unit 'BU1' (that's what would be in your Employee Dimension). This employee has a turnover allocated to him on the Business Unit 'BU1' But on the 2nd of June the Employee 'E1' is muted from Business Unit 'BU1' to Business Unit 'BU2.' All the new turnover have to belong to the new Business Unit 'BU2' but the old one should Belong to the Business Unit 'BU1.'

If you used the natural business key 'E1' for your employee within your datawarehouse everything would be allocated to Business Unit 'BU2' even what actualy belongs to 'BU1.'

If you use surrogate keys, you could create on the 2nd of June a new record for the Employee 'E1' in your Employee Dimension with a new surrogate key.

This way, in your fact table, you have your old data (before 2nd of June) with the SID of the Employee 'E1' + 'BU1.' All new data (after 2nd of June) would take the SID of the employee 'E1' + 'BU2.'

You could consider Slowly Changing Dimension as an enlargement of your natural key: natural key of the Employee was Employee Code 'E1' but for you it becomes
Employee Code + Business Unit - 'E1' + 'BU1' or 'E1' + 'BU2.' But the difference with the natural key enlargement process, is that you might not have all part of your new key within your fact table, so you might not be able to do the join on the new enlarge key -> so you need another id.


© NewInterviewQuestions.com


If you have the better answer, than send it to us. We will display your answer after the approval.
Answer Answer:*
Name Name:*
Email Address Email Address:*
     Verification Code Code Image - Please contact webmaster if you have problems seeing this image code Not readable? Load New Code
Process Verification  Enter the above shown code:*
 


Rate the above answer. Help us to know about the answer.
Category Category Data Warehousing Basics Interview Questions
Rating Rating (2.8) By 17 users
Added Added 7/26/2006
Views Views 153857
Rate it Rate it!
Free Offers Free Offers: 300 Free Magazines for you. No credit card required. No Shipping Charges. Absolutely Free for 1 Year.

Please Note: We keep on updating better answers to this site. Subscribe to our newsletter to get notified when better answer is posted.

Notify me when better answer is posted!
Email:





 Sponsors  

Latest 10 Answers
When there is any situation that u should attach a new patch to ur kernel , then download the patch and install it,to make changes to kernel , go to ok prompt and type boot -r, it will update or reconfigure the kernel
Honesty, helpful and reliable
procedure is a set of sql statement ,are combined to gether to perform a specific task or to solve a specifi problem.it does not return value.
leadership is power to influence a group so that the members willingly exert efforts as aspired by the leader.
I have the unique combination of technical skill and the ability to build strong customer relationship. I have the ability to stay focussed in stressfull situation. I would be great addition to your team.
first execute the bc search spec to fetch the data then executes the applet search spec if applet search spec is not match with bc it skips
TCP-Transmission Control Protocol. TCP is Reliable. TCP is Connection oriented. UDP-User Datagram Protoco UDP is Unreliable. UDP is Connectionless.
By default in structure all data member and member function are public,i,e if you not mentation any access specifier. But in case of class By default all data member and member function are private.
Structure is a user defined data type. By default all structure variables are public type and we cann't use access specifier in structure type variable, where as in class we can define objects(variables) under required access specifier type.
variable cost is the cost which is not fixed in nature

Newinterviewquestions.com is a part of Vyom Network. Top



Copyright © 2017, newinterviewquestions.com. All rights reserved Privacy Policy | Terms and Conditions

Page URL: http://www.newinterviewquestions.com/interview/8137/Default.asp?cachecommand=bypass


Download Yahoo Messenger | Web Hosting | Dedicated Server | Free SMS Domain Registration | Windows Hosting | Linux Hosting | Java Hosting | Virtual Dedicated Server | Dedicated Server | Reseller Web Hosting | Unlimited Windows reseller hosting | Unlimited Linux reseller hosting | Cheap Windows reseller hosting | Cheap Linux reseller hosting | Cheap Windows hosting | Best Windows hosting | Windows hosting Multiple Domains | Unlimited Windows hosting | Compare hosting plans | Cheap Linux hosting | Best Linux hosting | Linux hosting Multiple Domains | Unlimited Linux hosting | Cheap asp.net hosting | Best asp.net hosting | unlimited asp.net hosting | Cheap asp.net reseller hosting | Unlimited asp.net reseller hosting | Java Features | Cheap Java hosting | Best Java hosting | unlimited Java Hosting | Unlimited Linux reseller hosting | Java Hosting Multiple Domains Quick2host Reviews Buy Online Traffic