• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

Normalizer Transformation in Informatica

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.

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.
Interested in mastering Informatica Training? Enroll now for FREE demo on Informatica Training Online.

Normalizer Transformation Example

Account.txt

YearAccountMonth1Month2Month3
Salaries
Benefits

Ex:

YearAccountMonth1Month2Month3
1999Salary200030004000

 

yearAccountMonthAmount
1999Salary12000
1999Salary23000
1999Salary34000

 

 

 

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 NameAccurseData typePrecisionScale
YearNumber
AccountString
AmountNumber

 

 

 

 

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

Summary
Review Date
Reviewed Item
Normalizer Transformation in Informatica
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Normalizer Transformation in Informatica"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Site Disclaimer, Copyright © 2016 - All Rights Reserved.

    Support


    Please leave a message and we'll get back to you soon.