Surrogate Keys


Surrogate keys are system-generated, redundant, unique keys, redundant because many data source systems include their own keys. Surrogate keys address technical issues encountered with implementing data warehouses from multiple sources and changes over time.

Best practice in designing a data warehouse model is the use of surrogate keys to establish and preserve the relationships among the data. There are several reasons why you want to do this. Some include:

  • Source data keys are subject to change in the OLTP applications.

  • Some data sources might require that long compound keys and surrogate keys usually use integer format to conserve storage space.

  • Combining data from multisource systems might result in duplicate source keys. Traditional identifiers such as social security numbers, universal product codes (UPCs), international standard book numbers (ISBNs), and other assigned code numbers result in duplication (not to mention privacy issues).

  • Source data changes can be managed with a new surrogate key preserving original, historical associations.

Surrogate keys are used in a data warehouse to accommodate the technical issues that result from consolidating data from multiple data sources and changes over time. Surrogate keys prevent duplicate key problems from arising if you combine data from multiple sources. For changing dimensions, you can keep historical information by using a surrogate key, such as when you want to track the same customer at two different addresses at different points in time. Finally, if a key changes in the source system, the use of a surrogate key in the data warehouse saves you the effort of rebuilding your database to accommodate the source system changes. You can easily implement surrogate keys by creating an identity column in each of your SQL Server 2005 dimension tables.




Microsoft SQL Server 2005 Integration Services Step by Step
MicrosoftВ® SQL Server(TM) 2005 Integration Services Step by Step
ISBN: 0735624054
EAN: 2147483647
Year: 2007
Pages: 152

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net