Tutorial corner

Informatica,ETL,oracle,sql/plsql

Union Transformation in Informatica with Example

Spread the love

Union Transformation in Informatica, is a connected and active transformation which let you merge data from multiple pipelines or pipeline branches into one pipeline branch. Union Transformation merges data of similar source  based on UNION ALL SQL statement. It does’t remove the duplicate records while merging (same as Union ALL). In below article we will go through the details of Union Transformation. We will also discuss the steps of adding /configuring Union transformation in Informatica Mapping. We will also check some performance tuning related guidelines for Union Transformation.

 

Union Transformation in informatica

Union Transformation in informatica

Business scenarios of Union Transformation in Informatica:

As name predict, Union transformation is used to merge data from multiple pipelines into single one.

Is Union Transformation Active or Passive?

Union Transformation is a Active  transformation as  it combines two or more data streams into one. Although no of rows from multiple input and Output is same, but order in which these row are coming to output can change.

Is Union Transformation Connected or Unconnected?

Union Transformation is a Connected Transformation.

How Union Transformation work?

Integration Services processes all input groups in parallel. It  reads sources connected to the Union transformation and pushes blocks of data into the input groups of the transformation. Union transformation processes the blocks of data based on the order it receives the blocks from the Integration Service irrespective.

Properties of Union Transformation:

  • Active /Passive: Union Transformation is a Active transformation as  it combines two or more data streams into one. Although no of rows from multiple input and Output is same, but order in which these row are coming to output can change.
  • Connected/Unconnected Transformation: Union Transformation is a connected Transformation.
  • Types of ports in Union Transformation:
    • Input: For reading input Data.
    • Output: For providing output Data.
  • Types of Group in Union Transformation:
    • Input: For reading input Data from Multiple Source.
    • Output : For providing output Data to Single Target.

Union Transformation Components:

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

  • Transformation tab: You can rename the transformation and add a description.
  • Properties tab: You can specify the tracing level.
  • Groups tab: You can create and delete input groups. Designer displays groups you create on the Ports tab.
  • Group Ports tab: You can create and delete ports for the input groups. Informatica Designer displays ports you create on the Ports tab.

You cannot modify the Ports, Initialization Properties, Metadata Extensions, or Port Attribute Definitions tabs in a Union transformation.

Groups in Union Transformation:

There basically two type of groups in Union Transformation as shown in below figure.

Union Transformation in informatica groups

Union Transformation groups in informatica

Input Group

Input group is to take input data from multiple source. Designer copies port of the input group to create a set of output ports for output group.

Output Group

Output group is there to transfer data to multiple target from each output group. Output port of these groups can’t be altered. Port information is taken from Input group itself. Output group can be further classified as:

NOTE: The Designer automatically create output groups once a single input group is created

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

NOTE: You will notice that you can’t change the port properties from Port Tab. It can be changed from Group Port tab only.

Configuring Union Transformation Properties:

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

Property Description
Language Programming language used to develop this Custom Transformation.
Module Identifier The name of the DLL or shared library containing the procedure.
Function Identifier The name of the procedure in the module.
Runtime Location The location that contains the DLL or shared library.
Tracing Level Amount of detail displayed in the session log for this transformation. The options are Terse, Normal, Verbose Data, and Verbose Initialization.
Is Partitionable If this transformation cannot run in multiple partitions, then select ‘No’. If this transformation can run in multiple partitions but those partitions must run on the same node, then select ‘Locally’. If each partition can run on a different node on a grid, then select ‘Across Grid‘.
Inputs Must Block Specifies whether or not the procedure associated with the transformation blocks incoming data. You can clear this option when the procedure code does not block incoming data, or when it can use either a blocking algorithm or a non-blocking algorithm.
Is Active Specifies whether this transformation is an active or a passive transformation. When you enable this option, the transformation can generate 0, 1 or more output rows for each input row. Otherwise, it can generate only 0 or 1 output row for each input row.
Update Strategy Transformation Specifies whether or not this transformation defines the update strategy for output rows.
Transformation Scope Specifies how the Informatica Integration Service applies the transformation logic to incoming data: Row, Transaction, or All Input. This property is Row for passive transformations.
Generate Transaction Specifies whether or not this transformation can generate transactions.
Output Is Repeatable Specifies whether the output data is ordered: Never, Based On Input Order, or Always. This property is Based On Input Order for passive transformations.
Requires Single Thread Per Partition Specifies whether this Custom Transformation requires single thread per partition. If checked, each partition of this Custom Transformations will be executed by one and only one thread.
Output Is Deterministic The same set of output data is generated during the session run.

Check here for filter transformation example in informatica

How to use Union transformation in Informatica?

Use the following procedure to create an Union transformation in Informatica mapping.

Steps to create an Union transformation:

  • In the Mapping Designer, open a Mapping.
  • Click Transformation > Create. Select Union transformation.

    Union Transformation in informatica step 2.1

    Union Transformation in informatica step 2.1

  • You can also select Transformation by clicking  function  button on Informatica Designer.
Union Transformation Shortcut in informatica

Union Transformation Shortcut in informatica

Union Transformation in informatica 2.2

Union Transformation in informatica step 2.2

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

    Union Transformation in informatica step 3

  • Double-click the title bar of the Union transformation to open the transformation.
  • Click the Groups tab, and then click the Add button to create a user-defined group. The Designer assigns a default name for each group but they can be renamed.
    Union Transformation in informatica step 4

    Union Transformation in informatica step 4

  • Click the Transformation tab and configure transformation properties. ( change Tracing Level as per your need).
    Union Transformation in informatica step 5

    Union Transformation in informatica step 5

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

Example of Union Transformation:

Check here for Union transformation example in informatica.

More about Union Transformation:

Union Transformation rules and Guidelines:

  • We can create multiple input groups in Union Transformation, but only one output group.
  • All input groups and the output group must have matching ports. The precision, datatype, and scale must be identical across all groups.
  • Union transformation does not remove duplicate rows. To remove duplicate rows, we may need to use Sorter Transformation with distinct option clicked
  • You cannot use a Sequence Generator or Update Strategy transformation upstream from a Union transformation.
  • The Union transformation does not generate transactions.

So, in this article we have gone through properties of Union Transformation in informatica, component/Properties of Union Transformation, scenarios where to use it , steps to create it and  performance related guidelines. Hope you enjoyed the article. Please feel free to give your feedback.

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