Transformation type:
- Informatica Manual Generation Of Surrogate Keyboard
- Informatica Manual Generation Of Surrogate Key Generator
- Informatica Manual Generation Of Surrogate Key Example
- Informatica Manual Generation Of Surrogate Keys
It is possible to generate sequential surrogate key in the target table without the use of an Informatica Sequence Generator transformation. Using this option, one can avoid any gap in the sequence numbers of the surrogate key. Sequencing Logic. Jun 12, 2009 Putting together a few steps to generate surrogate key: Most of you might've dealt with it already. But, sending it as it might be a quick reference incase of future use Generating it as such isn’t a big deal, it might get a little tricky when you are trying to insert new values in continuation of already existing surrogate key. Surrogate keys are widely used and accepted design standard in data warehouses. It is sequentially generated unique number attached with each and every record in a Dimension table in any Data Warehouse. It join between the fact and dimension tables and is necessary to handle changes in dimension table attributes. Informatica PowerCenter Training Course. Start learning informatica certification course & be a skilled professional in informatica powercenter, usaonlinetraining.com offers informatica online training in usa, canada, uk, australia, singapore, new zealand, mexico, uae, spain and brazil with experienced informatica trainers.
- Passive
- Connected Transformation.
- The Sequence Generator transformation generates numeric values.
- Use the Sequence Generator to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.
- We use it to generate Surrogate Key in DWH environment mostly. When we want to Maintain history, then we need a key other than Primary Key to uniquely identify the record. So we create a Sequence 1,2,3,4 and so on. We use this sequence as the key. Example: If EMPNO is the key, we can keep only one record in target and can’t maintain history. So we use Surrogate key as Primary key and not EMPNO.Sequence Generator Ports :The Sequence Generator transformation provides two output ports: NEXTVAL and CURRVAL.
- We cannot edit or delete these ports.
NEXTVAL:
- Use the NEXTVAL port to generate sequence numbers by connecting it to a Transformation or target.For example, we might connect NEXTVAL to two target tables in a mapping to generate unique primary key values.Sequence in Table 1 will be generated first. When table 1 has been loaded, only then Sequence for table 2 will be generated.CURRVAL:CURRVAL is NEXTVAL plus the Increment By value.
- We typically only connect the CURRVAL port when the NEXTVAL port is Already connected to a downstream transformation.
- If we connect the CURRVAL port without connecting the NEXTVAL port, the Integration Service passes a constant value for each row.
- when we connect the CURRVAL port in a Sequence Generator Transformation, the Integration Service processes one row in each block.
- We can optimize performance by connecting only the NEXTVAL port in a Mapping.
Example: To use Sequence Generator transformation- EMP will be source.
- Create a target EMP_SEQ_GEN_EXAMPLE in shared folder. Structure same as EMP. Add two more ports NEXT_VALUE and CURR_VALUE to the target table.
- Create shortcuts as needed.
Configuring Sequence Generator Transformation:
Configure the following properties of sequence generator transformation:
- Start Value: Specify the start value of the generated sequence that you want the integration service to use the cycle option. If you select cycle, the integration service cycles back to this value when it reaches the end value.
- Increment By: Difference between two consecutive values from the NEXTVAL port. Default value is 1. Maximum value you can specify is 2,147,483,647.
- End Value: Maximum sequence value the integration service generates. If the integration service reaches this value during the session and the sequence is not configured to cycle, the session fails. Maximum value is 9,223,372,036,854,775,807.
- Current Value: Current Value of the sequence. This value is used as the first value in the sequence. If cycle option is configured, then this value must be greater than or equal to start value and less than end value.
- Cycle: The integration service cycles through the sequence range.
- Number of Cached Values: Number of sequential values the integration service caches at a time. Use this option when multiple sessions use the same reusable generator. Default value for non-reusable sequence generator is 0 and reusable sequence generator is 1000. Maximum value is ,223,372,036,854,775,807.
- Reset: The integration service generate values based on the original current value for each session. Otherwise, the integration service updates the current value to reflect the last-generated value for the session plus one.
- Tracing level: The level of detail to be logged in the session log file.
Star Points about Sequence Generator:
- If Current value is 1 and end value 10, no cycle option. There are 17 records in source. In this case session will fail.
- If we connect just CURR_VAL only, the value will be same for all records.
- If Current value is 1 and end value 10, cycle option there. Start value is 0.
- There are 17 records in source. Sequence: 1 2 – 10. 0 1 2 3 –
- To make above sequence as 1-10 1-20, give Start Value as 1. Start value is used along with Cycle option only.
- If Current value is 1 and end value 10, cycle option there. Start value is 1.
- There are 17 records in source. Session runs. 1-10 1-7. 7 will be saved in repository. If we run session again, sequence will start from 8.
- Use reset option if you want to start sequence from CURR_VAL every time
Sequence Generator Transformation Ports:
The sequence generator transformation contains only two output ports. They are CURRVAL and NEXTVAL output ports.
NEXTVAL Port:
You can connect the NEXTVAL port to multiple transformations to generate the unique values for each row in the transformation. The NEXTVAL port generates the sequence numbers base on the Current Value and Increment By properties. If the sequence generator is not configure to Cycle, then the NEXTVAL port generates the sequence numbers up to the configured End Value.
The sequence generator transformation generates a block of numbers at a time. Once the block of numbers is used then it generates the next block of sequence numbers. As an example, let say you connected the nextval port to two targets in a mapping, the integration service generates a block of numbers (eg:1 to 10) for the first target and then another block of numbers (eg:11 to 20) for the second target.
If you want the same sequence values to be generated for more than one target, then connect the sequence generator to an expression transformation and connect the expression transformation port to the targets. Another option is create sequence generator transformation for each target.
CURRVAL Port:
The CURRVAL is the NEXTVAL plus the Increment By value. You rarely connect the CURRVAL port to other transformations. When a row enters a transformation connected to the CURRVAL port, the integration service passes the NEXTVAL value plus the Increment By value. For example, when you configure the Current Value=1 and Increment By=1, then the integration service generates the following values for NEXTVAL and CURRVAL ports.
If you connect only the CURRVAL port without connecting the NEXTVAL port, then the integration service passes a constant value for each row.
Informatica Manual Generation Of Surrogate Keyboard
Images for Sequence Generator Transformation:
Informatica Manual Generation Of Surrogate Key Generator
Primary Key | Surrogate 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. |
Informatica Manual Generation Of Surrogate Key Example
Note:why do we use surrogate keys in OLAP rather than using primary keys?
There are different reasons for this:
Informatica Manual Generation Of Surrogate Keys
- Surrogate keys are simple numeric values, as simple as normal counting.
So most of the time they save storage space. - As surrogate keys are simple and short, it speed-up the join performance.
- Best thing is that same pattern of surrogate keys can be used across all
the tables present in a star/schema. - 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.
- 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.
- 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.
- 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.