Surrogate Key (English Version)

Surrogate Key

The Surrogate word can be interpreted as an artificial synthetic product that is used to replace a natural product; Surrogate = Substitute; Applying this definition to dimensional modeling can say that a synthetic key is an artificial key that is used to replace a natural key the transactional environment.

The Surrogate Key (numeric, incremental and meaningless) arises from a concern not to have an informational database, linked and dependent on a transactional database. That is, has the benefit of immunize the DW  of the environmental changes and operational changes. Having the key independent of all other columns insulates possible changes in relationships of values ​​from the database or database design (making the database faster) ensuring the singularities.

Usually generate the surrogate key column based on a numeric data type which varies storing bytes according to the value stored. That is, if a field NUMERIC (10) is used in the definition of a Surrogate Key value will not be stored ten positions indicated in the definition of the field, but the actual value stored with the limit of ten positions. In Oracle this characteristic can be observed using the function VSIZE.

ETL tools have several tasks for the generation of Surrogates Key's as SAS Data Integration Studio, SAP Data Services, IBM Data Stage and etc..

DBMS solutions can also provide the generation of these keys as the data type  IDENTITY of SQL Server and  Oracle SEQUENCE function.

Why use Surrogate Key:

•        Keys transactional can be reused;

•        Treatment of historic dimensions;

•        Saving space;

•        Enable the integration of databases that have not integrated codes matching in transactional databases;

•        Enable the creation of control values ​​without including in transactional environment. Example: Client not registered, open date, etc..;

•        Improved performance in joins between tables.

How should be a Surrogate Key:

•        Must be an integer;

•        Sequential numbering increasing;

•        Must start at 1 until the required number;

•        There must have implicit meaning;

•        Shouldn't be a composite of transactional key.