Normalizer Transformation in Informatica

Ratings:
(4.4)
Views:1875
Banner-Img
  • Share this blog:

Normalizer Transformation

This post is a continuation of Informatica Tutorials. Normalizer transformation type is Active & Connected. The Normalizer transformation is used in place of Source Qualifier transformations when you wish to read the data from the COBOL copy book source. Also, a Normalizer transformation is used to convert column-wise data to row-wise data. This is similar to the transpose feature of MS Excel. You can use this feature if your source is a COBOL copybook file or relational database table. The Normalizer transformation converts columns to rows and also generates an index for each converted row.

Normalizer Transformation in Informatica-normalizer-transformation

This is type an active from the transaction which allows you to read the data from cobal files. Every Cobol source definition default associates with normalizing transformation.

Use the normalize transformation to convert a single input record into multiple output records, this is known as horizontal port

Normalizer Transformation Uses

Normalizer can be used to deal with
  1. multiple-occurring columns and
  2. multiple record types created using redefines.

Normalizer Transformation Properties

Active /Passive : Normalizer Transformation is an Active transformation as it creates multiple rows for each input row.

Connected/Unconnected Transformation : Normalizer Transformation is a connected Transformation.

Types of ports in Normalizer Transformation

Input:  For reading input Data

Output: For providing output Data

GCID:  The normalizer transformation generates thisID column for each set of multiple occurring column. It act as the undex for the instance of the multiple-occurring data. For example, if a column occurs 3 times in a source record, the normalizer returns a value of 1,2 or 3 in the generated column ID. We can use this GCID to give some name to those value. We will see its more use in our example.

GK_VALUE:  This port increasethe sequence value for each input row. Unlike GCID,(which was repeating itself for each set of group GK_VALUE keep on increasing with each source row processed.

Informatica Tutorials & Interview Questions

Normalizer Transformation Components

When you configure a Normalizer transformation, define the following components:

Transformation tab: You can rename the transformation and add a description. Properties tab: You can specify the tracing level. Normalizer tab: Here you define the input/output port along with occurrence. For port which receive values from multiple column we gives occurs greater than 1 . Ports tab: Once port under normalizer is created , you will see input and output port for normalizer. For nomalizer ports with occurs greater than 1 , will see multiple input port but only one output port. ( see example for more details)

NOTE: The Designer automatically create input /output groups once normalizer port are created under normalizer port.

Configuring Normalizer Transformation Ports

[sociallocker id=14725]

You can create input ports by copying them from another transformation or by manually creating them on the Group Ports tab.The Designer creates output ports by copying the following properties from the input ports.

Port Details

  • Port name:  Add the name of the port.
  • Datatype, precision, and scale:  Configure the datatype and set the precision and scale for each port.
  • Default Value: In case port data is null , you can define its default value.

[/sociallocker]

Normalizer Transformation Generated Keys

The Normalizer transformation returns at least one generated key column(note this is different than GCID column) in the output row. The Integration Service increments the generated key sequence number each time it processes a source row. When you create a Normalizer transformation, the generated key value is 1 by default. The naming convention for the Normalizer generated key is GK_< redefined_field_name >.
You can view the current generated key values on the Normalizer transformation Ports tab. At the end of each session, the Integration Service updates the generated key value in the Normalizer transformation to the last
value generated for the session plus one. The maximum generated key value is 9,223,372,036,854,775,807.

Changing Normalizer Transformation Generated Keys

Reset the generated key sequence: When you reset the generated key sequence, the Integration Service resets the generated key start value back to the value it was before the session. Can be set on the properties tab.
 
Restart the generated key sequence: When you restart the generated key sequence, the Integration Service starts the generated key sequence at 1 the next time it runs a session.
When you restart the generated key sequence, the generated key start value does not change in the Normalizer transformation until you run a session. When you run the session, the Integration Service overrides the sequence number value on the Ports tab.
When you reset or restart the generated key sequence, the reset or restart affects the generated key sequence values the next time you run a session. You do not change the current generated key sequence values in the
Normalizer transformation. When you reset or restart the generated key sequence, the option is enabled for every session until you disable the option.
If you want to enrich your career and become a professional in Informatica, then visit Tekslate - a global online training platform: "Informatica Training" This course will help you to achieve excellence in this domain.

Normalizer Transformation Example

Account.txt

Year Account Month1 Month2 Month3
  Salaries      
  Benefits      

Ex:

Year Account Month1 Month2 Month3
1999 Salary 2000 3000 4000

 

year Account Month Amount
1999 Salary 1 2000
1999 Salary 2 3000
1999 Salary 3 4000

 

 

     

Procedure

Create a source definition for the file account. Txt create a target definition with the name S _ Account

Year, account, month, amount

Create a mapping with the name M _data_pivote

Drop the source and target definition

Create the transformation type normalize

Double click on normalize transformation select the normalize tab

Column Name Accurse Data type Precision Scale
Year   Number    
Account   String    
Amount   Number    

       

 

 

Click apply, click ok From source qualifier connect the ports to normalize transformation From normalize transformation connect the ports to the target (GCID – amount -> month) GCID – generated column ID From repository menu click on save

Normalizer Transformation Types

VSAM Normalizer transformation

A non-reusable transformation that is a Source Qualifier transformation for a COBOL source. The column attributes are read-only. The VSAM Normalizer receives a multiple-occurring source column through one input port. The normalizer columns are created from the COBOL source.

Pipeline Normalizer transformation

A transformation that processes multiple-occurring data from relational tables or flat files. The pipeline Normalizer transformation represents multiple-occurring columns with one input port for each source column occurrence. The normalizer columns are to be created manually.

For indepth understanding of Informatica, click on

You liked the article?

Like : 0

Vote for difficulty

Current difficulty (Avg): Medium

Recommended Courses

1/15

About Author
Authorlogo
Name
TekSlate
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.