Informatica Manual Generation Of Surrogate Key

Primary KeySurrogate Key
Primary key is a special constraint on a column or set of columns in a table.A surrogate key is any column or set of columns that can be declared as the primary key instead of a “real” or natural key.
A table can have only one primary key. Primary keys are used in OLTP.A surrogate key has multiple names. surrogate keys are used in OLAP.
Primary key ensures that the column(s) are unique and does not allow any NULL values in the column(s). By default, the primary key creates a unique index on the column(s).You can call a surrogate key as artificial key, integer key, non-natural key, and synthetic key and so on. Surrogate keys are integers that are assigned sequentially as needed to populate a dimension table.
Primary key is defined on an existing column in the table. Primary key may contain numeric as well as non-numeric values. Primary keys hold some business meaning.surrogate does not hold any business meaning. Sometimes there can be several natural keys that could be declared as the primary key, and these are all called candidate keys. So a surrogate is a candidate key.
Physically, a primary key is implemented by the database system using a unique index, and all the columns in the primary key must have been declared NOT NULL.A table could actually have more than one surrogate key, although this would be unusual.
A table may have only one primary key, but it may be composite (consist of more than one column).The most common type of surrogate key is an incrementing integer, such as an auto increment column in MySQL, or a sequence in Oracle, or an identity column in SQL Server.

Note:why do we use surrogate keys in OLAP rather than using primary keys?

Informatica manual generation of surrogate keysGeneration

Generating Surrogate key with help of Unconnected lookup and expression transformation 'Hi All, My requirement is to generate surrogate key.For this I am using unconnected lookup and expression.In unconnected lookup, i am using only 2 ports: 1 is input and 2 is primary key column pkey which is return port and also writing sql overvide. Aug 22, 2015 This Video explains how to load the data with surrogate key with out using Sequence Generator. Teradata dialect does not support identity key generation. Its very good work around. Informatica Real Time Scenario 5-Generating the Surrogate Key with out.

There are different reasons for this:

Informatica Manual Generation Of Surrogate Keys

  1. Surrogate keys are simple numeric values, as simple as normal counting.
    So most of the time they save storage space.
  2. As surrogate keys are simple and short, it speed-up the join performance.
  3. Best thing is that same pattern of surrogate keys can be used across all
    the tables present in a star/schema.
  4. The primary key is a unique key in your table that you choose that best uniquely identifies a record in the table. All tables should have a primary key, because if you ever need to update or delete a record you need to know how to uniquely identify it.
  5. A surrogate key is an artificially generated key. They’re useful when your records essentially have no natural key (such as a Person table, since it’s possible for two people born on the same date to have the same name, or records in a log, since it’s possible for two events to happen such they they carry the same timestamp). Most often you’ll see these implemented as integers in an automatically incrementing field, or as GUIDs that are generated automatically for each record. ID numbers are almost always surrogate keys.
  6. Unlike primary keys, not all tables need surrogate keys, however. If you have a table that lists the states in America, you don’t really need an ID number for them. You could use the state abbreviation as a primary key code.
  7. The main advantage of the surrogate key is that they’re easy to guarantee as unique. The main disadvantage is that they don’t have any meaning. There’s no meaning that “28” is Wisconsin, for example, but when you see ‘WI’ in the State column of your Address table, you know what state you’re talking about without needing to look up which state is which in your State table.