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?

Lookup Caches

We can configure a Lookup transformation to cache the lookup table. The Integration Service (IS) builds a cache in memory when it processes the first row of data in a cached Lookup transformation. The Integration Service also creates cache files by default in the $PMCacheDir. If the data does not fit in the memory cache, the IS stores the overflow values in the cache files. When session completes, IS releases cache memory and deletes the cache files.

Note:

• If we use a flat file lookup, the IS always caches the lookup source.
• We set the Cache type in Lookup Properties.

Lookup Cache Files:

1. Lookup Index Cache: Stores data for the columns used in the lookup condition.
2. Lookup Data Cache: For a connected Lookup transformation, stores data for the connected output ports, not including ports used in the lookup condition. For an unconnected Lookup transformation, stores data from the return port.

Types of Lookup Caches:

1. Static Cache:
By default, the IS creates a static cache. It caches the lookup file or table and looks up values in the cache for each row that comes into the transformation. The IS does not update the cache while it processes the Lookup transformation.
2. Dynamic Cache:
To cache a target table or flat file source and insert new rows or update existing rows in the cache, use a Lookup transformation with a dynamic cache. The IS dynamically inserts or updates data in the lookup cache and passes data to the target. Target table is also our lookup table. No good for performance if table is huge.
3. Persistent Cache:
If the lookup table does not change between sessions, we can configure the Lookup transformation to use a persistent lookup cache. The IS saves and reuses cache files from session to session, eliminating the time required to read the lookup table.
4. Recache from Source:
If the persistent cache is not synchronized with the lookup table, we can configure the Lookup transformation to rebuild the lookup cache. If Lookup table has changed, we can use this to rebuild the lookup cache.
5. Shared Cache:
There are two types of Shared Cache. They are...
1. Unnamed cache: When Lookup transformations in a mapping have compatible caching structures, the IS shares the cache by default. You can only share static unnamed caches.
2. Named cache: Use a persistent named cache when we want to share a cache file across mappings or share a dynamic and a static cache. The caching structures must match or be compatible with a named cache. You can share static and dynamic named caches.

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.

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.

Lookup transformation

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 .

We can import a lookup definition from any flat file or relational database to which both the PowerCenter Client and Server can connect. We can use multiple Lookup transformations in a mapping.

Lookup transformation act as left outer joiner. If a particular null record matches found, then it returns null value.

The PowerCenter Server queries the lookup source based on the lookup ports in the transformation. It compares Lookup transformation port values to lookup source column values based on the lookup condition. Pass the result of the lookup to other transformations and a target.

For example, if we want to retrieve all the sales of a product with an ID 10 and assume that the sales data resides in another table called 'Sales'. Here instead of using the sales table as one more source, use Lookup transformation to lookup the data for the product, with ID 10 in sales table.

Different caches can also be used with lookup like static, dynamic, persistent, and shared. The dynamic cache cannot be used while creating an un-connected lookup.

We can use the Lookup transformation to perform following:

• Get a related value: EMP has DEPTNO but DNAME is not there. We use Lookup to get DNAME from DEPT table based on Lookup Condition.
• Perform a calculation: We want only those Employees who’s SAL > Average (SAL). We will write Lookup Override query.
• Update slowly changing dimension tables: Most important use. We can use a Lookup transformation to determine whether rows already exist in the target.

TIP: We use Connected Lookup when we need to return more than one column from Lookup table.