SORTER TRANFORMATION

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

Example: Sorting data of EMP by ENAME

• Source is EMP table.
• Create a target table EMP_SORTER_EXAMPLE in target designer. Structure same as EMP table.
• Create the shortcuts in your folder.

Creating Mapping:

1. Open folder where we want to create the mapping.

2. Click Tools -> Mapping Designer.

3. Click Mapping-> Create-> Give mapping name. Ex: m_sorter_example

4. Drag EMP from source in mapping.

5. Click Transformation -> Create -> Select Sorter from list. Give name and click Create. Now click done.

6. Pass ports from SQ_EMP to Sorter Transformation.

7. Edit Sorter Transformation. Go to Ports Tab

8. Select ENAME as sort key. CHECK mark on KEY in front of ENAME.

9. Click Properties Tab and Select Properties as needed.

10. Click Apply -> Ok.

11. Drag target table now.

12. Connect the output ports from Sorter to target table.

13. Click Mapping -> Validate

14. Repository -> Save

• Create Session and Workflow as described earlier. Run the Workflow and see the data in target table.
• Make sure to give connection information for all tables.

Sample Sorter Mapping :

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.

EXPRESSION Tranformation

• Passive and connected transformation.

Use the Expression transformation to calculate values in a single row before we write to the target. For example, we might need to adjust employee salaries, concatenate first and last names, or convert strings to numbers.

Use the Expression transformation to perform any non-aggregate calculations.

Example: Addition, Subtraction, Multiplication, Division, Concat, Uppercase conversion, lowercase conversion etc.

We can also use the Expression transformation to test conditional statements before we output the results to target tables or other transformations. Example: IF, Then, Decode

There are 3 types of ports in Expression Transformation:

• Input
• Output
• Variable: Used to store any temporary calculation.

Calculating Values :

To use the Expression transformation to calculate values for a single row, we must include the following ports:

• Input or input/output ports for each value used in the calculation: For example: To calculate Total Salary, we need salary and commission.
•  Output port for the expression: We enter one expression for each output port. The return value for the output port needs to match the return value of the expression.

We can enter multiple expressions in a single Expression transformation. We can create any number of output ports in the transformation.

Example: Calculating Total Salary of an Employee

• Import the source table EMP in Shared folder. If it is already there, then don’t  import.
• In shared folder, create the target table Emp_Total_SAL. Keep all ports as in EMP table except Sal and Comm in target table. Add Total_SAL port to store the calculation.
• Create the necessary shortcuts in the folder.

Creating Mapping:

1. Open folder where we want to create the mapping.
2. Click Tools -> Mapping Designer.
3. Click Mapping -> Create -> Give mapping name. Ex: m_totalsal
4. Drag EMP from source in mapping.
5. Click Transformation -> Create -> Select Expression from list. Give name and click Create. Now click done.
6. Link ports from SQ_EMP to Expression Transformation.
7. Edit Expression Transformation. As we do not want Sal and Comm in target, remove check from output port for both columns.
8. Now create a new port out_Total_SAL. Make it as output port only.
9. Click the small button that appears in the Expression section of the dialog box and enter the expression in the Expression Editor.
10. Enter expression SAL + COMM. You can select SAL and COMM from Ports tab in expression editor.
11. Check the expression syntax by clicking Validate.
12. Click OK -> Click Apply -> Click Ok.
13. Now connect the ports from Expression to target table.
14. Click Mapping -> Validate
15. Repository -> Save

Create Session and Workflow as described earlier. Run the workflow and see the data in target table.

As COMM is null, Total_SAL will be null in most cases. Now open your mapping and expression transformation. Select COMM port, In Default Value give 0. Now apply changes. Validate Mapping and Save.

Refresh the session and validate workflow again. Run the workflow and see the result again.

Now use ERROR in Default value of COMM to skip rows where COMM is null.

Syntax: ERROR(‘Any message here’)

Similarly, we can use ABORT function to abort the session if COMM is null.

Syntax: ABORT(‘Any message here’)

Make sure to double click the session after doing any changes in mapping. It will prompt that mapping has changed. Click OK to refresh the mapping. Run workflow after validating and saving the workflow.

Performance tuning :

Expression transformation is used to perform simple calculations and also to do Source lookups.

1. Use operators instead of functions.
2. Minimize the usage of string functions.
3. If we use a complex expression multiple times in the expression transformer, then Make that expression as a variable. Then we need to use only this variable for all computations.

Read more: http://informaticatutorials-naveen.blogspot.com/2011/04/expression-transformation_20.html#ixzz3BbLiAEwX
Under Creative Commons License: Attribution Non-Commercial

FILTER Transformation

                                                   

• Active and connected transformation.

We can filter rows in a mapping with the Filter transformation. We pass all the rows from a source transformation through the Filter transformation, and then enter a Filter condition for the transformation. All ports in a Filter transformation are input/output and only rows that meet the condition pass through the Filter Transformation.

Example: to filter records where SAL>2000

• Import the source table EMP in Shared folder. If it is already there, then don’t Import.
• In shared folder, create the target table Filter_Example. Keep all fields as in EMP table.
• Create the necessary shortcuts in the folder.

Creating Mapping:

1. Open folder where we want to create the mapping.
2. Click Tools -> Mapping Designer.
3. Click Mapping -> Create -> Give mapping name. Ex: m_filter_example
4. Drag EMP from source in mapping.
5. Click Transformation -> Create -> Select Filter from list. Give name and click  Create. Now click done.
6. Pass ports from SQ_EMP to Filter Transformation.
7. Edit Filter Transformation. Go to Properties Tab
8. Click the Value section of the Filter condition, and then click the Open button.
9. The Expression Editor appears.
10. Enter the filter condition you want to apply.
11. Click Validate to check the syntax of the conditions you entered.
12. Click OK -> Click Apply -> Click Ok.
13. Now connect the ports from Filter to target table.
14. Click Mapping -> Validate
15. Repository -> Save

Create Session and Workflow as described earlier. Run the workflow and see the data in target table.

How to filter out rows with null values?

To filter out rows containing null values or spaces, use the ISNULL and IS_SPACES Functions to test the value of the port. For example, if we want to filter out rows that  Contain NULLs in the FIRST_NAME port, use the following condition:

IIF (ISNULL (FIRST_NAME), FALSE, TRUE)

This condition states that if the FIRST_NAME port is NULL, the return value is FALSE and the row should be discarded. Otherwise, the row passes through to the next Transformation.

Performance tuning:

Filter transformation is used to filter off unwanted fields based on conditions we Specify.

1. Use filter transformation as close to source as possible so that unwanted data gets Eliminated sooner.
2. If elimination of unwanted data can be done by source qualifier instead of filter,Then eliminate them at Source Qualifier itself.
3. Use conditional filters and keep the filter condition simple, involving TRUE/FALSE or 1/0

Read more: http://informaticatutorials-naveen.blogspot.com/2011/04/filter-transformation_20.html#ixzz3BbL45F4p 

Under Creative Commons License: Attribution Non-Commercial          

Router transformation

Router transformation

Chapter 18 . Router Transformation

• Active and connected.(number of rows can increase in the output if same row satisfies multiple condition)
• 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.
• The Router transformation is more efficient. For example, to test data based on three conditions, you only need one Router transformation instead of three filter transformations to perform this task.
• Another advantage is that when you use a Router transformation in a mapping, the Integration Service processes the incoming data only once. When you use multiple Filter transformations in a mapping, the Integration Service processes the incoming data for each transformation.
• A Router transformation has the following types of groups:
  ♦ Input: The Designer copies property information from the input ports of the input group to create a set of output ports for each output group.
  ♦ Output: There are two types of output groups:
  1) User-defined groups
  2) Default group

Note that we cannot modify or delete output ports or their properties.

• 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. Create one user-defined group for each condition that you want to specify.

• The Integration Service tests the conditions of each user-defined group before processing the default group.
• The Integration Service determines the order of evaluation for each condition based on the order of the connected output groups.
• If a row meets more than one group filter condition, the Integration Service passes this row multiple times.

• The Integration Service processes user-defined groups that are connected to a transformation or a target in a mapping. The Integration Service only processes user-defined groups that are not connected in a mapping if the default group is connected to a transformation or a target.
• Defualt Group:

1. The Designer creates the default group after you create one new user-defined group.
2. The Designer does not allow you to edit or delete the default group. This group does not have a group filter condition associated with it.
3. If all of the conditions evaluate to FALSE, the Integration Service passes the row to the default group.
4. If you want the Integration Service to drop all rows in the default group, do not connect it to a transformation or a target in a mapping.
5. The Designer deletes the default group when you delete the last user-defined group from the list

• Using Group Filter Conditions:

1. You can test data based on one or more group filter conditions. You create group filter conditions on the Groups tab using the Expression Editor.
2. A group filter condition returns TRUE or FALSE for each row that passes through the transformation, depending on whether a row satisfies the specified condition. Zero (0) is the equivalent of FALSE, and any non-zero value is the equivalent of TRUE. The Integration Service passes the rows of data that evaluate to TRUE to each transformation or target that is associated with each user-defined group.
3. There is no group filter condition associated with the default group.

• Adding Groups:

Adding a group is similar to adding a port in other transformations. For every new group that we add the Designer copies property information

from the input ports to the output ports.

1. Click the Groups tab.

2. Click the Add button.

3. Enter a name for the new group in the Group Name section.

4. Click the Group Filter Condition field and open the Expression Editor.

5. Enter the group filter condition.

6. Click Validate to check the syntax of the condition.

7. Click OK.

• Ports:

Input ports are in the input group, and output ports are in the output groups.

-Input ports: Input ports can be created by copying them from another transformation or by manually creating them on the Ports tab.

-output ports: The Designer creates output ports by copying the following properties from the input ports:

♦ Port name

♦ Datatype

♦ Precision

♦ Scale

♦ Default value

When you make changes to the input ports, the Designer updates the output ports to reflect these changes. You cannot edit or delete output ports.

Names of the output port are dependent on the input port names.

Example:

• Rules for connecting Router transformation:

♦ You can connect one group to one transformation or target.

♦ You can connect one output port in a group to multiple transformations or targets.

♦ You can connect multiple output ports in one group to multiple transformations or targets.

♦ You cannot connect more than one group to one target or a single input group transformation.

♦ You can connect more than one group to a multiple input group transformation, except for Joiner transformations, when you connect each output group to a different input group.

• Steps to create Router transformation.

1. In the Mapping Designer, open a mapping.

2. Click Transformation > Create.

Select Router transformation, and enter the name of the new transformation. The naming convention for the Router transformation is RTR_ TransformationName. Click Create, and then click Done.

3. Select and drag all the ports from a transformation to add them to the Router transformation, or you can manually create input ports on the Ports tab.

4. Double-click the title bar of the Router transformation to edit transformation properties.

5. Click the Transformation tab and configure transformation properties.

6. Click the Properties tab and configure tracing levels.

7. Click the Groups tab, and then click the Add button to create a user-defined group.The Designer creates the default group when you create the first user-defined group.

8. Click the Group Filter Condition field to open the Expression Editor.

9. Enter a group filter condition.

10. Click Validate to check the syntax of the conditions you entered.

11. Click OK.

12. Connect group output ports to transformations or targets.