Surrogate Key



A surrogate key may also be called as:
  • synthetic key
  • entity identifier
  • system-generated key
  • database sequence number
  • fact less key
  • technical key
  • arbitrary unique identifier
-It is the system generated key which cannot be edited by the user.
-It is the primary key of the dimension table in warehouse.
-It is nothing but the sequence which generates the numbers for primary key column.

Surrogate keys are system generated keys. They are integers.

Surrogate keys are extremely useful when having type 2 data (i.e. storing historical information)
For ex: Consider one has a table in which a person and his location are stored. Now when his location is changed and we want to keep a historical record of the same it is stored with a surrogate key that will help us to uniquely identify the record. This is also a reason that OLTP keys are not used in the warehouse and a separate dimension or surrogate key is maintained.

What is the data type of the surrogate key?
Surrogate keys are the system generated keys and used to eliminate the redundancy in the primary keys. So they are always integer numbers.
Generate surrogate key
Goal: Fill in a data warehouse dimension table with data which comes from different source systems and assign a unique record identifier (surrogate key) to each record.
Scenario overview and details   : To illustrate this example, we will use two made up sources of information to provide data about customer’s dimension. Each extract contains customer records with a business key (natural key) assigned to it.

In order to isolate the data warehouse from source systems, we will introduce a technical surrogate key instead of re-using the source system's natural (business) key.
A unique and common surrogate key is a one-field numeric key which is shorter, easier to maintain and understand and independent from changes in source system than using a business key. Also, if a surrogate key generation process is implemented correctly, adding a new source system to the data warehouse processing will not require major efforts.

Surrogate key generation mechanism may vary depending on the requirements; however the inputs and outputs usually fit into the design shown below:

Inputs:
- an input represented by an extract from the source system
- data warehouse table reference for identifying the existing records
- maximum key lookup

Outputs:
- output table or file with newly assigned surrogate keys
- new maximum key
- updated reference table with new records 

How to test the surrogate key?

usually test with the following way

1. total count = max(sgk) - min(sgk) +1
2. select sgk from table group by sgk having count(*)>1; 

Comments

  1. AnonymousFebruary 28, 2017 at 7:56 AM

    Hi mahesh , can you explain what is the above two syntax do -

    1. total count = max(sgk) - min(sgk) +1
    2. select sgk from table group by sgk having count(*)>1;

    ReplyDelete

Post a Comment

Popular posts from this blog

Data Migration Testing Startegy

Image
1.   Purpose of the Article The purpose of this Aricle is to describe the different test stages that will be performed; how the testing will be prepared and executed for each stage, and the criteria that need to be met before transition from one stage to the next can occu r. 2.   Phases involved in Data Migration Testing Below are the phases involved in testing data migration. ·         Fusion ·         Release 1.       Design 2.       Implementation a)       Ear b)       Iterations 3.       SIT (System Integration Testing) 4.       UAT (User Acceptance Testing) 2.1.         Fusion (Discover and Define) In this Phase the requirements for the...
Read more

Informatica DVO

What is Data Validation Option ? What can be tested ? To answer above two questions, Data Validation Option is a ETL Testing tool which comes along with power center and enables to test and validate data Data Validation Option reads table definitions from Power Center metadata repositories, and checks the data for inconsistencies Verify that moved or transformed data is complete and accurate and has not been changed because of errors in the movement or transformation process Will not check the correctness of transformations or mappings designed in Informatica Data Validation stages A typical workflow or stages for data validation consists of multiple tasks- 1. Data Validation Option reads one or more Power Center metadata repositories 2. We define the validation rules(creation of tests) in Data Validation Option 3. We run the rules(tests) to ensure the data conforms to the validation rules. When we do this, Data  Validation Option performs the following tasks: ...
Read more

ETL Testing

Image
Data ware house is relational database which is subject oriented, integrated, time-variant and nonvolatile collection of data used to support strategic decision making process Subject Oriented Data warehouses are designed to help you analyze data. For example, to learn more about your company's sales data, you can build a warehouse that concentrates on sales. Using this warehouse, you can answer questions like "Who was our best customer for this item last year?" This ability to define a data warehouse by subject matter, sales in this case, makes the data warehouse subject oriented. Integrated Integration is closely related to subject orientation. Data warehouses must put data from disparate sources into a consistent format. They must resolve such problems as naming conflicts and inconsistencies among units of measure. When they achieve this, they are said to be integrated. Nonvolatile Nonvolatile means that, once entered into the warehouse, data should not cha...
Read more