Tutorial corner

Informatica,ETL,oracle,sql/plsql

Normalizer Transformation in Informatica with Example

Spread the love

Informatica Normalizer Transformation, is a connected and active transformation which let you to normalize your data  by receiving a row with information scatter in multiple columns to multiple row a for each instance of column data. For example, a student have score for each subject scattered in 5 columns, with the help of normalizer transformation you can create multiple rows for each subject (Database Normalization). We will go in more details about informatica normalizer transformation with example.

Informatica Normalizer Transformation

Informatica Normalizer Transformation

Business Scenarios of Informatica Normalizer Transformation:

Normalizer Transformation help in normalizing your data, making it for flexible by eliminating redundancy and inconsistent  dependency. It help you representing data in a better manner.

Is Normalizer Transformation Active or Passive?

Normalizer Transformation is a Active transformation as it create multiple row for each input row.

Is Normalizer Transformation Connected or Unconnected ?

Normalizer Transformation is a Connected Transformation.

Properties of Normalizer Transformation:

  • Active/Passive: Normalizer Transformation is a Active  transformation as  it create multiple row 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

Note: Apart for user defined ports , Normalizer transformation also has below default port created .

  • GCID:  The normalizer transformation generate this ID column for each set of multiple occurring  column. It act as the index for the  instance of the multiple-occurring data. For example, if a column occurs 3 times in a source record, the normalizer transformation 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 increase the 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 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.

Configuring Ports of Normalizer Transformation in Informatica

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..

Configuring properties of Informatica Normalizer Transformation:

Modify the Normalizer Transformation properties by clicking on the Properties tab.

Property Description
Reset At the end of a session, resets the value sequence for each generated key value to the value it was before the session.
Restart Starts the generated key sequence at 1. Each time you run a session, the key sequence value starts at 1 and overrides the sequence value on the Ports tab.
Tracing Level Amount of detail displayed in the session log for this transformation. The options are Terse, Normal, Verbose Data, and Verbose Initialization.

Check here for filter transformation example in informatica.

How to use Normalizer transformation in Informatica?

Use the following procedure to create a Normalizer transformation in Informatica mapping.

Steps to create an Normalizer transformation:

  • In the Mapping Designer, open a Mapping.
  • Click Transformation > Create. Select Normalizer transformation.
  • You can also select Normalizer Transformation by clicking  function button on Informatica Designer

    Informatica Normalizer Transformation

    Informatica Normalizer Transformation shortcut

  • Enter a name and click Done.
  • You can create ports in Normalizer transformation  by selecting and dragging it from input transformation to Normalizer transformation. You can manually create input ports on the Normalizer Ports tab by double clicking on it.
    Informatica Normalizer Transformation step 4

    Informatica Normalizer Transformation step 4

    Informatica Normalizer Transformation step 5

    Informatica Normalizer Transformation step 5

  • Click the Transformation tab and configure transformation properties. ( change Tracing Level as per your need)

    Informatica Normalizer Transformation step 6

    Informatica Normalizer Transformation step 6

  • Click OK.
  • Connect output ports of Normalizer Transformation to other transformation or target.

Example of Informatica Normalizer Transformation:

Check here for Normalizer transformation example in informatica.

Please check below link for Informatica normalizer transformation youtube video.

More about Informatica Normalizer Transformation:

Informatica Normalizer Transformation rules and Guidelines:

  • For Handling Cobol Data ( which contain multiple type of data occurring  multiple time). VSAM normalizer transformation is used. It act as a source qualifier for Cobol Source Definition.
  • Occurs clause is specified when the source row has a multiple-occurring columns where as.
  • Redefines clause is specified when the source has rows of multiple columns.

Hope you enjoyed this informatica tutorial on Informatica normalizer transformation. Please add your feedback/Suggestion in comment.

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme