Identity in SQL, Sequence in Oracle, Sequence Generator in Informatica.
For Example : You are extracting Customer Information from OLTP Source and after ETL process, loading customer information in a dimension table (DW). If you take SCD Type 1, Yes you can use Primary Key of Source CustomerID as Primary Key in Dimension Table. But if you would like to preserve history of customer in Dimension table i.e. Type 2. Then you need another unique no apart from CustomerID. There you have to use Surrogate Key.
Another reason : If you have AlphaNumeric as a CustomerID. Then you have to use surrogate key in Dimension Table. It is advisable to have system generated small integer number as a surrogate key in the dimension table. so that indexing and retrieval is much faster.
Above answer was rated as good by the following members: phdsong
RE: what is the need of surrogate key;why primary key ...
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 repeatition of data.
Need for surrogate key not Primary Key
If a column is made a primary key and later there needs a change in the datatype or the length for that column then all the foreign keys that are dependent on that primary key should be changed making the database Unstable
Surrogate Keys make the database more stable because it insulates the Primary and foreign key relationships from changes in the data types and length.
RE: what is the need of surrogate key;why primary key ...
Surrogate key is system generated unique number.
Identity in SQL, Sequence in Oracle, Sequence Generator in Informatica.
For Example : You are extracting Customer Information from OLTP Source and after ETL process, loading customer information in a dimension table (DW). If you take SCD Type 1, Yes you can use Primary Key of Source CustomerID as Primary Key in Dimension Table. But if you would like to preserve history of customer in Dimension table i.e. Type 2. Then you need another unique no apart from CustomerID. There you have to use Surrogate Key.
Another reason : If you have AlphaNumeric as a CustomerID. Then you have to use surrogate key in Dimension Table. It is advisable to have system generated small integer number as a surrogate key in the dimension table. so that indexing and retrieval is much faster.
RE: what is the need of surrogate key;why primary key ...
One more reason for using surrogate keys in a Datawarehouse.
1. As data is extracted from disparate sources , where in each source might have primary keys with data types or formats inherent to the underlying database, if the same primary keys are are used in the DW , there would be inconsistencies in representation of data which would make quering of the database a difficult job , so the surrogate keys are implemented to circumvent these kind of situations .
RE: what is the need of surrogate key;why primary key ...
Datawarehousing depends on the surrogate key not primary key, for suppose if u r taking the product price it will change over the time, but product no. will not change but price will change over the time to maintain the full hystorical data about the product it is necessary in datawarehouse.
1 
0