What is surrogate key?
- 5 Answers
- 20222 Views
- IBM, Informatica, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / msb.dwh
SURROGATE KEY IS SYSTEM GENERATED PRIMARY KEY
1)THIS IS USED WHEN DATA COMMING FROM MULTIPLE SOURCES THEN
THERE IS CHANCE OF DUPLICATION SO WE USED THIS
2) THE P.KEY ON TABLE MAY HAVE ANY DATATYPE BUT NO(S.KEY)
ACCUPYES VERRY LESS SPACE
3)MENTAING THE QUARY IS SIMPLE FOR END USER
| Is This Answer Correct ? | 18 Yes | 2 No |
Answer / venkat
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.
------------------------------------------------------------
--------------------
A surrogate key is a system generated sequential number
which acts as a primary key.
| Is This Answer Correct ? | 11 Yes | 0 No |
Answer / ram
Surrogate keys are keys that are maintained within the data
warehouse instead of keys taken from source data systems.
| Is This Answer Correct ? | 10 Yes | 1 No |
Answer / maruthi
SURROGATE KEY IS SYSTEM GENERATED PRIMARY KEY
1)THIS IS USED WHEN DATA COMMING FROM MULTIPLE SOURCES THEN
THERE IS CHANCE OF DUPLICATION SO WE USED THIS
2) THE P.KEY ON TABLE MAY HAVE ANY DATATYPE BUT NO(S.KEY)
ACCUPYES VERRY LESS SPACE
3)MENTAING THE QUARY IS SIMPLE FOR END USER
| Is This Answer Correct ? | 7 Yes | 2 No |
Answer / meena
A surrogate key in a database is a unique identifier for
either an entity in the modeled world or an object in the
database. The surrogate key is not derived from application
data.
There appear to be two definitions of a surrogate in the
literature. We shall call these surrogate (1) and surrogate
(2):
Surrogate (1) This definition is based on that given by
Hall, Owlett and Todd (1976). Here a surrogate represents
an entity in the outside world. The surrogate is internally
generated by the system but is nevertheless visible by the
user or application.
Surrogate (2) This definition is based on that given by
Wieringa and de Jung (1991). Here a surrogate represents an
object in the database itself. The surrogate is internally
generated by the system and is invisible to the user or
application.
| Is This Answer Correct ? | 6 Yes | 6 No |
What are necessary tasks to install reportnet software?
Explain difference between view and materialized view?
Explain dimension table ?
How to add a number to a macro variable?
What is the "junk dimension"? Give with examples?
What are fundamental stages of data warehousing?
What is Discover and Execute in XMLA?
Where we use surrogate key explain with example?
What is the working process for application.cfm?
Explain what is fact less fact table? Where you have used it in your project?
What is mapplet?
Why should we use data ware housing and how can you extract data for analysis with example?
Teradata 
