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?

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.

Connected Lookup Transformation

1. Let us assume that EMP will be source table. DEPT will be LOOKUP table.
2. Create a target table T_CONN_LKP_EMP in target designer. Table should contain all ports of EMP table plus DNAME and LOC.
3. Creating Mapping by click Tools -> Mapping Designer.
4. Click Mapping-> Create-> Give name. Ex: m_CONN_LKP_EMP
5. Drag EMP and Target table.
6. Connect all fields from SQ_EMP (Source Qualifier transformation) to target except DNAME and LOC.
7. Then go to Transformation-> Create -> Select LOOKUP from list. Give name and click Create.

8. As DEPT is the Source definition, click Source and then Select DEPT.
9. Click Ok.
10. Now Pass DEPTNO from SQ_EMP to this Lookup. DEPTNO from SQ_EMP will be named as DEPTNO1. Edit Lookup and rename it to IN_DEPTNO in ports tab.
11. Now go to CONDITION tab and add CONDITION.
12. DEPTNO = IN_DEPTNO and Click Apply and then OK.
13. We are not passing IN_DEPTNO and DEPTNO to any other transformation from LOOKUP; we can edit the lookup transformation and remove the OUTPUT check from them.
14. The final mapping look like as shown below..

15. Then Mapping -> Validate and save the mapping.
16. Create Session and Workflow. Run the workflow and see the data in target table.
17. Make sure to give connection information for all tables.
18. Make sure to give connection for LOOKUP Table also.
19. We use Connected Lookup when we need to return more than one column from Lookup table.

ODBC Connection in Informatica

ODBC stands for Open Data Base Connectivity. An ODBC is a middleware or an interface which allows you any database to Informatica.

How to create an ODBC in Informatica

For creating an ODBC, we can follow two methods:

1. Go to Control Panel => Administrative Tools =>Data Souces (ODBC)
2. Select System DSN tab and click on Add
3. Select the driver Oracle in OraDB10g_home
4. Then click Finish.
5. Enter the following details:
• Data Source Name: Source_Scott_ODBC (your choice)
• TNS Service name: ORCL
• User Id: Scott
6. Then click on Test Connection
7. There enter password and finally click on Ok.

Or you can also creating an ODBC from Informatica level as follows:

1. While Creating the target, first we need impliment the metadata of the target by drag the source in
2. Target Designer and then Generate/Execute SQL.
3. Click on Connect then click on Add.

4. Select System DSN tab and click on Add

5. Select the driver Oracle in OraDB10g_home
6. Then click Finish.

7. Enter the following details:
• Data Source Name: Source_Scott_ODBC (your choice)
• TNS Service name: ORCL
• User Id: Scott

8. Then click on Test Connection
9. There enter password and finally click on Ok.