Tutorial corner

Informatica,ETL,oracle,sql/plsql

How to use filter transformation in informatica

Spread the love

Filter transformation in InformaticaFilter Transformation in informatica is active and connected transformation. Here we will discuss How to use filter transformation in informatica mapping with example.

How to use filter transformation in informatica

How to use filter transformation in Informatica 

Create an Informatica mapping to populate employee for Dept=1 with the help of filter transformation in informatica?

Check here for more about filter transformation in informatica

Below are the steps to create

  • Create a new mapping (ex m_filter_transformation_exp)
  • Drag the source ( EMP) and Target Definition ( EMP_TAR) into the mapping
  • Go to Toolbar->click Transformation -> Create. Select the Filter transformation

Step to add Filter Transformation Part1

Step to add Filter Transformation Part1

Step to add Filter Transformation Part2

Step to add Filter Transformation Part2
  • Enter a name like FLT_DEPT_1, click on Create and then click on Done.
  • Add all additional port from source input which is going to be use in following transformation.
  • Edit the properties of FLT_DEPT_1 Transformation  by clicking on “Edit” option

Properties of Filter Transformation:

Click on properties tab of filter Transformation and give the input as described as below  :

Step to add Filter Transformation Part4

Filter Condition : Condition which to filter out desired records

Tracing Level: Logging level to be tracked in the session log file.

Filter Transformation Examples:

Lets take an example of using Filter Transformation.

Example Statement:

Create a mapping to load the target table with Dept1 records only.

Source Data:

EMPNO ENAME JOB MGR HIRE DATE SAL COMM DEPT

14

ROOSEVELT

CPA

9

34984

35000

1

10

FILLMORE

MANAGER

9

34555

56000

2

11

ADAMS

ENGINEER

10

35139

34000

2

3

TAFT

SALES I

2

35066

25000

500

3

9

JACKSON

CEO

32874

75000

4

6

GARFIELD

MANAGER

9

34090

54000

4

 

Target Data:

EMPNO ENAME JOB MGR HIRE DATE SAL COMM DEPT

14

ROOSEVELT

CPA

9

34984

35000

1

 Solution:

    • Create a new mapping m_filter_example by Go to toolbar -> click mapping-> Create
    • Drag Source (EMP) and Target  (EMP_TAR) to the mapping.
    • Step to add Filter Transformation Part1
      Step to add Filter Transformation Part1

      Step to add Filter Transformation Part2

      Step to add Filter Transformation Part2
      • Enter a name like FLT_DEPT_1, click on Create and then click on Done.
      • Add all additional port from source input which is going to be use in following transformation.
      • Edit the properties of FLT_DEPT_1 Transformation  by clicking on “Edit” option

      Properties of Filter Transformation:

      Click on properties tab of filter Transformation and give the input as described as below  :

      Step to add Filter Transformation Part4

      Filter Condition : Condition which to filter out desired records

  • Add DEPT=1  and click  on validate  to validate the condition (our condition is simple  :) so no issue)
  • Set Tracing Level: Logging level to be tracked in the session log file.
  • Click ok  to save and close the filter transformation
  • Now join the output port of Filter Transformation  with the EMP_TAR
  • Click on Mapping ( from tool bar) -> then Validate ( to validate the mapping)
  • Now save the mapping  ( by clicking on Repository-> Save or you can also press Ctrl+S)
  • Generate the workflow and run it

Over all Informatica Mapping:

Filter Transformation Example Part 5

Filter Transformation Example

So in this article we have gone through details about filter Transformation in Informatica ,component/Properties of filter Transformation ,informatica scenarios where to use it , steps to create it and performance related guidelines

The Author

admin

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