XML Transformation

XML Source Qualifier Transformation: You can add an XML Source Qualifier transformation to a mapping by dragging an XML source definition to the Mapping Designer workspace or by manually creating one.
 We can link one XML source definition to one XML Source Qualifier transformation.
 We cannot link ports from more than one group in an XML Source Qualifier transformation to ports in the same target transformation.

XML Parser Transformation: The XML Parser transformation lets you extract XML data from messaging systems, such as TIBCO or MQ Series, and from other sources, such as files or databases.
 Used when we need to extract XML data from a TIBCO source and pass the data to relational targets.
 The XML Parser transformation reads XML data from a single input port and writes data to one or more output ports.

XML Generator Transformation: The XML Generator transformation lets you read data from messaging systems, such as TIBCO and MQ Series, or from other sources, such as files or databases.
 Used when we need to extract data from relational sources and passes XML data to targets.
 The XML Generator transformation accepts data from multiple ports and writes XML through a single output port.

Stage 1 (Oracle to XML)
 We are gonna generate an xml file as output with the oracle emp table as source.

Step 1: Generate the XML target file.
• Import the same emp table as source table
• Go the targets and click on import the XML definition.
• Later choose the Non XML source from the left hand pane.
• Move the emp table (source table) from all sources to the Selected Sources.
• After which, we got to click on open to have the target table in the target designer.
• Sequential Steps to generate the xml target table is shown in below snap shots.

Step 2: Design the mapping, connect the SQ straight away to the target table.
• Create the name of the mapping as per the naming convention.
• Save the changes.

Step 3: Create task and the work flow.
 • Double click on the work flow and go to the mapping tab and here we got to specify the output file directory. (C :/) ….
• Run the work flow ,check in the C drive and look for an file by name emp.xml …

Stage 2 (XML to Oracle)
 Here source is gonna be the xml file and the target file is the oracle file.

Step 1: Importing the source xml file and import the target transformation.
• Go the sources and click on the import XML definition.
• Browse for the emp.xml file and open the same.
• The first three windows are gonna be same as in previous case.
• Target table is gonna be the same EMP table.

Step 2: Design the mapping.
• Connections for this mapping is gonna be the following way.
• Save the mapping.

Step 3: Create the task and work flow.
• Create the task and the work flow using the naming conventions.
• Go to the mappings tab and click on the Source on the left hand pane to specify the path for the input file.

 Step 4: Preview the output on the target table.

Sorter & Router Transformation

Sorter Transformation

• Connected and Active Transformation

• The Sorter transformation allows us to sort data.

• We can sort data in ascending or descending order according to a specified sort key.

• We can also configure the Sorter transformation for case-sensitive sorting, and specify whether the output rows should be distinct.

When we create a Sorter transformation in a mapping, we specify one or more ports as a sort key and configure each sort key port to sort in ascending or descending order. We also configure sort criteria the Power Center Server applies to all sort key ports and the system resources it allocates to perform the sort operation.

The Sorter transformation contains only input/output ports. All data passing through the Sorter transformation is sorted according to a sort key. The sort key is one or more ports that we want to use as the sort criteria.

Sorter Transformation Properties

1. Sorter Cache Size:

The Power Center Server uses the Sorter Cache Size property to determine the maximum amount of memory it can allocate to perform the sort operation. The Power Center Server passes all incoming data into the Sorter transformation Before it performs the sort operation.

• We can specify any amount between 1 MB and 4 GB for the Sorter cache size.

• If it cannot allocate enough memory, the Power Center Server fails the Session.

• For best performance, configure Sorter cache size with a value less than or equal to the amount of available physical RAM on the Power Center Server machine.

• Informatica recommends allocating at least 8 MB of physical memory to sort data using the Sorter transformation.

2. Case Sensitive:

The Case Sensitive property determines whether the Power Center Server considers case when sorting data. When we enable the Case Sensitive property, the Power Center Server sorts uppercase characters higher than lowercase characters.

3. Work Directory

Directory Power Center Server uses to create temporary files while it sorts data.

4. Distinct:

Check this option if we want to remove duplicates. Sorter will sort data according to all the ports when it is selected.

Performance Tuning:

Sorter transformation is used to sort the input data.

1. While using the sorter transformation, configure sorter cache size to be larger than the input data size.

2. Configure the sorter cache size setting to be larger than the input data size while Using sorter transformation.

3. At the sorter transformation, use hash auto keys partitioning or hash user keys Partitioning.

Router Transformation

• Active and connected transformation.

A Router transformation is similar to a Filter transformation because both transformations allow you to use a condition to test data. A Filter transformation tests data for one condition and drops the rows of data that do not meet the Condition. However, a Router transformation tests data for one or more conditions And gives you the option to route rows of data that do not meet any of the conditions to a default output group.

Mapping A uses three Filter transformations while Mapping B produces the same result with one Router transformation.

A Router transformation consists of input and output groups, input and output ports, group filter conditions, and properties that we configure in the Designer.

Working with Groups

A Router transformation has the following types of groups:

• Input: The Group that gets the input ports.

• Output: User Defined Groups and Default Group. We cannot modify or delete Output ports or their properties.

User-Defined Groups: We create a user-defined group to test a condition based on incoming data. A user-defined group consists of output ports and a group filter Condition. We can create and edit user-defined groups on the Groups tab with the Designer. Create one user-defined group for each condition that we want to specify.

The Default Group: The Designer creates the default group after we create one new user-defined group. The Designer does not allow us to edit or delete the default group. This group does not have a group filter condition associated with it. If all of the conditions evaluate to FALSE, the IS passes the row to the default group