Tutorial corner

Informatica,ETL,oracle,sql/plsql

Unconnected Lookup Transformation in Informatica with example

Spread the love

Unconnected Lookup Transformation in Informatica with example: Earlier , we talked about Lookup transformation in informatica , steps to create it , configuring the port. We come to know about two type of Lookup Transformation based on the Connection type i.e. Connected Lookup Transformation and UnConnected Lookup Transformation. Here we will see one example of Unconnected Lookup Transformation in an Informatica mapping.

Example of Unconnected Lookup Transformation

Create a mapping to populate Dept related information for each employee EMP table with the help of Unconnected Lookup Transformation in Informatica

Source Data:

EMP Table Data:

EMPNO ENAME JOB DEPT

4

HOOVER SALES I

5

LINCOLN TECH

4

8

GRANT ENGINEER

5

9

JACKSON CEO

4

10

FILLMORE MANAGER

2

14

ROOSEVELT CPA

1

DEPT Table Data

DEPTNO DNAME LOC

1

ACCOUNTING ST LOUIS

2

RESEARCH NEW YORK

3

SALES ATLANTA

4

OPERATIONS SEATTLE

Target Table

EMP_DEP

EMPNO ENAME JOB DEPT DEPTNO DNAME

14

ROOSEVELT CPA

1

1

ACCOUNTING

10

FILLMORE MANAGER

2

2

RESEARCH

9

JACKSON CEO

4

4

OPERATIONS

5

LINCOLN TECH

4

4

OPERATIONS

4

HOOVER SALES I

8

GRANT ENGINEER

5

Solution:

Below are the steps to create full mapping.

  • Create a new mapping m_unconnected_Lookup_example by Go to toolbar -> click mapping-> Create
  • Drag Source (EMP, DEPT) and Target (EMP_DEPT) to the mapping.
  • Add an expression transformation as exp_EMP  and drag required port (EMPNO,ENAME,JOB,DEPT) from SQ_EMP source qualifier to exp_EMP .
  • Add Lookup Transformation by “Go to Toolbar->click Transformation -> Create. Select the Lookup transformation”
  • You can also select Transformation by clicking  function  button on Informatica Designer

    Lookup Transformation Shortcut

    Lookup Transformation Shortcut

  • Enter a name and click Done.

    Lookup Transformation Step 2

    Lookup Transformation Step 2

 

  • Select the Lookup source as DEPT from the Source TAB (We already created DEPT Source in previous examples , so use it as Lookup Source)

    Lookup Transformation Step3

    Lookup Transformation Step 3

  • You will notice Lookup Transformation will contains ports from Lookup source i.e DEPT.
  • Add input port in DEPT_NO (double)  by clicking on “New” option
Lookup Transformation Step4

Lookup Transformation Step4

  • Mark “R” return port as checked for DNAME port

Note : You can mark only one port as Return port in lookup Transformation

  • Add the lookup condition on the Condition tab. DEPTNO = in_DEPT

    Lookup Transformation Step 6

    Lookup Transformation Step 6

  • Click the Properties tab to configure the Lookup transformation properties. Configure lookup caching. Lookup caching is enabled by default for pipeline and flat file Lookup transformations

Check here for Properties of lookup transformation in informatica

Lookup Transformation Properties

Lookup Transformation Properties

  • Edit the exp_EMP Expression Transformation , and create a new output port DEPT_NAME and open the expression editor of it and expression as :LKP.LKP_DEPT(DEPT)
  • unconnected lookup transformation part 1

    unconnected lookup transformation part 1

  • Now join required ports (EMPNO,ENAME,JOB,DEPT,DEPTNAME) , join them from exp_EMP to EMP_DEP Target Definition.
  • 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 Mapping:

Full Mapping of UnConnected lookup Transformation example in Informatica

Full Mapping of UnConnected lookup Transformation example in Informatica

So, in this article we have gone through step by step example of unconnected Lookup Transformation in Informatica mapping. In next article , we will talk about  connected lookup Transformation in Informatica .

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