Union Transformation in Informatica

Data Merging:

Data Merging is a process of combining 2 or more sources data into a single output. Generally, we will use only 2 Transformations in Informatica to perform the process of data merging.

1. Union Transformation
2. Joiner Transformation

Union Informatica Transformation:

• It is active and Connected Transformation
• We say this is an active because while joining the data from various sources there will be change in the total number of records and also changes the order of the rows.
• It is used to combine multiple sources which are having same structure.
• It can receive 'n' number of inputs and provides single output.

Union Transformation in Informatica

Prerequisites for Union Transformation:

All the inputs which is coming from different sources should have the same structure.

• Number of columns in both sources should be same
• Data types of the corresponding columns should be same.
• Order of the columns must be same

Union Transformation is created with the following 2 types of groups.
Input Group: It receives the data from the sources or from other T/Rs
Output Group: It sends the data either to the target or to the other T/Rs

Note:

• By default, Union Transformation is created with a single input group and single output group.
• Number of sources in this is equal to the number of input groups in 'Union Transformation'.
• For every source or input there should be a corresponding input group in "Union Transformation".
• Union Transformation works like 'union all' in Oracle as this will allow duplicate records because we can not eliminate duplicate with this. Don't worry as we have another option to achieve this as mentioned below.
• After this Transformation use Sorter Transformation to eliminate the duplicate records.
• Union Transformation supports both homogeneous sources as well as heterogeneous sources.

Union Transformation in Informatica Scenarios:

1. Design a mapping to combine Cust_Hyd and Cust_Chennai which are having the same structure.

Procedure to combine homogeneous sources:

• Connect to the Scott Schema and create 2 tables with the same structure
• Import the meta data of both the sources using source analyzer and save the meta data by using control+s. This is just a keyboard short cut to save the meta data in informatica
• Create a target with any name by following the naming convention
• Create a mapping with the name related to that mapping.
• From repository drag both the sources and target to design the mapping.
• Create a Transformation of type union with the name union_customer
• Go to the header of union and select groups tab.
• Add the input ports and select the groups tab and add the name of the ports.
• Connect the ports from both the sources to the corresponding groups in the union.
• Create a Transformation of type sorter with the name sort distinct
• Connect the ports from the output group of union to sorter
• Select Distinct property in Sorter Transformation.

• Connect the ports from sorter to the corresponding ports in the target and save the mapping and the remaining process is common. I did not mention remaining process because it is very easy as you are familiar to run the workflows.

If you follow all these steps exactly, you will get the expected result without any failure in the session or workflow and it also takes very less time as it have very less number of records in the sources and targets.

Limitations of union transformation in Informatica:

There are very less limitations while using the union. We can perform union on Relational databases and on flat files also. So it is very useful while performing union in heterogeneous sources also. The one and only limitation is we can use this if we have same structure. It will not work if the source structure is different. If they are different we have to use another Transformation to achieve the result.

Read More

Sorter Transformation in Informatica

Sorter Transformation:

This is one of the most frequently used transformation in Informatica during the mapping design.

• It is active and connected transformation.
• It is used to sort the input data either in ascending order or in descending order based on the key port.
• Key port is also called as sorted port.
• 'Sorter Transformation' allows multiple key ports.
• Sorter Transformation in Informatica sorts the data based on the order of the ports when multiple keyports are selected.
• It is also used to eliminate the duplicate records from different type of sources and other transformations also.

Sorter Transformation is active or passive:

Without any doubt, we can say that the Sorter Transformation is Active because it can remove the duplicate records. When the duplicates records were removed from the sources or from any other transformations then definitely there will be changes in the total number of rows as there will be reduction in the total number of records. Hence, we can say that sorter transformation as active. Select 'Distinct' Property in the sorter transformation to eliminate the duplicates.

Sorter Cache in Informatica:

In case of Sorter Transformation, Informatica Server builds a cache which is called as sorter cache(cache memory) at the time of execution. It loads all the input data into sorter cache and performs sorting based on key column and sends the output. It releases the cache once the sorting is done. The following transformations in Informatica require cache memory at the time of execution.

• Sorter Transformation
• Aggregator Transformation
• Rank Transformation
• Joiner Transformation
• Lookup Transformation

For the other transformations like Router Transformation or any other cache will not be created.
Sorter Transformation is created with the following 3 types of ports.

1. Input Port
2. Output Port
3. Key Port

Informatica Scenarios using Sorter Transformation:

 

Import the emp table as our source first using the source analyzer and then follow the below steps.

1. Create a target with the name emp_sort with the same structure as source(emp)
2. Create a mapping with the name m_sorter
3. Drag the source (emp)
4. Drag the target (emp_sort)
5. Create a transformation of type sorter with the name sort_sal and then Connect the ports from source qualifier to the Sorter Transformation
6. Go to the header of sorter and then select ports tab.
7. Select the key for the required ports on salary and then apply and ok
8. Connect the ports from the sorter to target.
9. Save the mapping, session and run the workflow.

How to display null values at bottom in Sorter Transformation:

• By default, Informatica Server displays null values first while sorting the data.
• Go to the header of the sorter and select the ports tab.
• Select keyports for the comm and set direction as descending.
• Select properties tab and set the property as Null Treated Low  and then save the mapping.

Performance Tuning in Sorter Transformation:

There are very  less chances to discuss about this topic in the Informatica Interviews. As we know that sorting can also be done at the database level itself. If we use sorter immediately after the relational source, it is better to do the sorting in the database. Because sorting in Informatica takes long time when compared to the sorting in the Relational databases like Oracle, Mysql, SQL Server. If the source is a flat file then there is no other option. At that situation, we have to this transformation only. One more Performance tuning method is that to use this transformation as close to the source. So that you can remove the records which are having the duplicates at the initial stages itself.

Read More

Router Transformation in Informatica

Router Transformation:

• It is an Active and Connected Transformation.
• It is used to apply multiple conditions and to load the data into multiple targets. We can define any number of conditions here. There is no limit for this.
• We know that Filter Transformations acts as filter in SQL but Router Transformation acts as IIF in Informatica case .. when statement in the databases.
• 'Router Transformation' receives single input data set(records) and provides multiple outputs data sets.
• Router Transformation is created with the following 2 types of groups:

Input Group: It receives the data record by record.
Output Group: It sends the data record by record either to the target or to any other downstream transformation for the further processing.

Again Output Groups are categorized into 2 types:

1. User Defined Output Group and
2. Default Group

User Defined Output Group:

It allows the user to define the filter condition to restrict or to limit the data. It is almost as similar as filter transformation and the Default Group captures all the rejected records where it does not satisfies any of the given conditions.

Scenarios where we can use Router Transformation:

1. To design a mapping to load all department number 10 employees into one target and the department number 20 employees into another target and the rejected records into another target.

Procedure to work with Router Transformation:

1. Create 3 targets with the names as Emp_Dept10, Emp_Dept20 and Emp_rejects with the same structure as the emp using Target Designer and Save the meta data in the repository.
2. Create a mapping with the name m_router
3. From the repository drag the source(emp) from the repository database and in the same way also drag all the three targets into the workspace.
4. Go to transformations menu and then create a transformation of type router with the name Rout_deptno
5. Connect the ports from the source qualifier to Router Transformation
6. Open the header of Router Transformation and select 'Groups Tab'
7. Add the following output groups on the option as shown below. Group name:Group Filter Condition Emp_dept10 as Deptno=10 and Emp_dept20 as Deptno=20
8. Apply and then ok.
9. Connect the ports from the corresponding output groups of router to the corresponding target and save the mapping.
10. Follow the naming conventions and create session, workflow and execute it.

Note:

1. Router Transformation checks all the given conditions defined inside the filter conditions for each and every input record and then gives the output.
2. To make the performance more better during the design of thee mappings, use Router Transformation in place of many filter transformations. It is one of the performance tuning method in Informatica. More transformations will make the mapping to run very slow. So try to minimize the use of transformations as much as you can and see for the best performance tuning methods.

Read More