RE: What is the role of surrogate keys in data warehou...
A surrogate key is a simple Primary key which maps one to one with a Natural compound Primary key. The reason for using them is to alleviate the need for the query writer to know the full compound key and also to speed query processing by removing the need for the RDBMS to process the full compound key when considering a join.
For example, an shipment could have a natural key of ORDER + ITEM + SHIPMENT_SEQ. By giving it a unique SHIPMENT_ID, subordinate tables can access it with a single attribute, rather than 3. However, it's important to create a unique index on the natural key as well.
RE: What is the role of surrogate keys in data warehou...
Surrogate key is nothing but a primary key to identify records uniquely in dimension and fact tables. It is basically a running system-generated sequence number.
RE: What is the role of surrogate keys in data warehou...
A surrogate key is a substitution for the natural primary key. We tend to use our own Primary keys (surrogate keys) rather than depend on the primary key that is available in the source system. When integrating the data, trying to work with the source system primary keys will be a little hard to handle. Thats the reason why a surrogate key will be useful even though it serves the same purpose as that of a primary key. Another important need for it is because the natural primary key (i.e. Customer Number in Customer table) can change and this makes updates more difficult.
RE: What is the role of surrogate keys in data warehouse and how will u generate them?
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 repetition of data.
