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.
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
- multiple-occurring columns and
- 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
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 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.
Normalizer Transformation Generated Keys
Changing Normalizer Transformation Generated Keys
Normalizer Transformation Example
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|
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
- Rank Transformation in Informatica
- Aggregator Transformation in Informatica
- Expression Transformation in Informatica
- Lookup Transformation in Informatica
- ETL Project Architecture
- Interview Questions