Sunday 11 November 2012

ADVANTAGES OF SURROGATE KEYS

This is referred from The Data Warehouse Toolkit, Kimball

One of the primary benefits of Surrogate keys is that they buffer the data warehouse environment from operational changes. Surrogate keys allow the warehouse team to maintain control of the environment rather than being whipsawed by operational rules for generating, updating, deleting, recycling, and reusing production codes. In many organizations, historical operational codes (for example, inactive account numbers or obsolete product codes) get reassigned after a period of dormancy. If account numbers get recycled following 12 months of inactivity, the operational systems don’t miss a beat because their business rules prohibit data from hanging around for that long.

The data warehouse, on the other hand, will retain data for years. Surrogate keys provide the warehouse with a mechanism to differentiate these two separate instances of the same operational account number. If we rely solely on operational codes, we also are vulnerable to key overlap problems in the case of an acquisition or consolidation of data. Surrogate keys allow the data warehouse team to integrate data from multiple operational source systems, even if they lack consistent source keys.

Finally, surrogate keys are needed to support one of the primary techniques for handling changes to dimension table attributes.

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...

ShareThis