Interview questions on Lookup transformatioin?

Q. What is a lookup transformation?

Ans: The Lookup transformation is passive transformation. Lookup is a transformation to look up data in a flat file or a relational table, view, or synonym.

There are two types of Lookups in Powercenter Designer, namely:

1. Connected Lookup
2. Unconnected Lookup .

Q. What are the tasks of a lookup transformation?

How to return muliple values using Unconnected lookup?

Today we discuss how to return muliple values using Unconnected lookup. First of all, the UnConnected lookup has one return port and returns one column from each row. If the interviewer asked the above scenario, follow the below solution.

• Let us assume EMP will be source table and DEPT will be LOOKUP table.
• Create a target table T_UNCONN_LKP in target designer. Table should contain all ports of EMP table plus DNAME and LOC.
• Go to the Mapping Designer and create the mapping with source, target, lookup and expression transformations as shown below:

Difference between Connected and UnConnected Lookup Transformation:

1. Connected lookup receives input values directly from mapping pipeline whereas UnConnected lookup receives values from :LKP expression in another transformation.
2. Connected lookup trasformation will process each and every row whereas UnConnected lookup will not process each and evry row. It will return the values based expression condition.
3. If the LKP condition is not matched the lookup transformation will return the default value in case of Connected lookup. If no match found for the LKP condition, the lookup transformation will return null values in case of Unconnected lookup.
4. Connected lookup transformation can use static or dynamic cache, whereas Unconnected lookup can use only static cache.
5. Connected lookup returns multiple columns from the same row whereas the UnConnected lookup has one return port and returns one column from each row.
6. Connected lookup supports user-defined default values and the UnConnected lookup does not support user defined values.

Unconnected Lookup transformation

An unconnected Lookup transformation is separate from the pipeline in the mapping. We write an expression using the :LKP reference qualifier to call the lookup within another transformation.

Steps to configure Unconnected Lookup:

1. Add input ports.
2. Add the lookup condition.
3. Designate a return value.
4. Call the lookup from another transformation.

Example for create a unconnected Lookup Transformation

1. Let us assume EMP will be source table and DEPT will be LOOKUP table.
2. Create a target table T_UNCONN_LKP in target designer. Table should contain all ports of EMP table plus LOC.
3. Go to the Mapping Designer.
4. Click Mapping-> Create-> Give name. Ex: m_UNCONN_LKP
5. Drag Source and Target table.
6. Creat an EXPRESSION transformation and pass all ports from Source Qualifier to EXPRESSION transformation.
7. Then connect all fields from EXPRESSION to target except DNAME.
8. Create a LOOKUP transformation.

9. As DEPT is the Source definition, click Source and then Select DEPT.
10. Click Ok.
11. Now Edit the Lookup Transformation. Go to Ports tab.
12. As DEPTNO is common in source and Lookup, create a port IN_DEPTNO ports tab. Make it Input port only and Give Datatype same as DEPTNO.

13. Designate LOC as Return Port. Check on R to make it.
14. Now add a condition in Condition Tab.
15. DEPTNO = IN_DEPTNO and Click Apply and then OK.
16. Now we need to call this Lookup from Expression Transformation.
17. Edit Expression transformation and create a new output port out_LOC of datatype as LOC. 

18. Open the Expression editor and call Lookup as given below:
19. We double click Unconn in bottom of Functions tab and as we need only DEPTNO, we pass only DEPTNO as input.

20. Write the expression as :LKP.LKPTRANS(DeptNo)
21. The final mapping looks like as below..

22. Validate the call in Expression editor and Click OK.
23. Then save the mapping.
24. Create Session and Workflow. Run the workflow and see the data in target table.
25. Note:
26. Make sure to give connection information for all tables.
27. Make sure to give connection for LOOKUP Table also.