Informatica Interview question with answer for lookup Transformation

In below article , we will go through the some the Informatica Interview question based on the lookup transformation in Informatica. In last tutorial , we have gone through the detailed explanation of lookup Transformation.
You can find a good details of Lookup transformation here.

We also checked some of the example of connected and unconnected lookup transformation also.

Lets go through some of the Informatica Interview question on Lookup transformation.

Check here : Difference connected and unconnected Lookup Transformation here .

Que: What is a pipeline lookup transformation??

Ans :A pipeline lookup transformation is used to perform lookup on application sources such as JMS, MSMQ or SAP. A pipeline lookup transformation has a source qualifier as the lookups source.

Que: What are the different type of Sources for Lookup ?

Ans :

• Relational source or target definition in the repository
• Flat file source or target definition in the repository
• Table or file that the Integration Service and PowerCenter Client machine can connect to
• Source qualifier definition in a mapping

Ques. What is a lookup transformation?
Ans : A lookup transformation is used to look up data in a flat file, relational table, view, and synonym.

Ques: What are the tasks of a lookup transformation?

Ans : The lookup transformation is used to perform the following tasks?

• Get a related value: Retrieve a value from the lookup table based on a value in the source.
• Perform a calculation: Retrieve a value from a lookup table and use it in a calculation.
• Update slowly changing dimension tables: Determine whether rows exist in a target.

Ques: . What is "Output Old Value on Update"?
Ans : This option is used when dynamic cache is enabled. When this option is enabled, the integration service outputs old values out of the lookup/output ports. When the Integration Service updates a row in the cache, it outputs the value that existed in the lookup cache before it updated the row based on the input data. When the Integration Service inserts a new row in the cache, it outputs null values. When you disable this property, the Integration Service outputs the same values out of the lookup/output and input/output ports.

Ques: . How do you configure a lookup transformation?

Ans : Configure the lookup transformation to perform the following types of lookups:

• Relational or flat file lookup
• Pipeline lookup
• Connected or unconnected lookup
• Cached or uncached lookup

Ques: What are the options available to configure a lookup cache?
Ans : The following options can be used to configure a lookup cache:

• Persistent cache
• Recache from lookup source
• Static cache
• Dynamic cache
• Shared Cache
• Pre-build lookup cache

Check here : Difference Static Cache and dynamic Cache  in Informatica here .

I also found a good article on Informatica Interview question on Lookup Transformation  here and here

Slowly Changing Dimension Type 2 Examples (SCD 2)

  SCD Type-2 In Informatica[/caption] Slowly Changing  Dimension Type-2 ,(also known as SCD -2)  tracks historical changes  by  keeping multiple records for a given natural key in the dimensional tables .For example , we may need to track the current location of a supplier along with its previous location just to track his sales in different region .
 Example of SCD Type -2

For Example : If we want to keep track of DEPT of an employee , we can add two extra fields , Say Start-date , End_date to keep track of those records: EMP_SCD2

SRG_KEY	EMPNO	NAME	DEPT	START_DATE	END_DATE
101	1	JOHNSON	OPERATIONS	12/17/1990	4/13/1992
102	2	HARDING	ACCOUNTING	2/2/1998	9/17/1998
103	3	TAFT	RESEARCH	1/2/1996
104	4	HOOVER	SALES	4/2/1990	7/11/1993
105	1	JOHNSON	ACCOUNTING	4/13/1992
106	2	HARDING	SALES	9/17/1998
107	4	HOOVER	OPERATIONS	7/11/1993
108	5	LINCOLN	OPERATIONS	6/23/1994

As you see in above example , we can keep track of records by adding effective _date fields . Other way to accomplish this process is to

• Effective Date
• Flagging
• Versioning

I found a good article on "Slowly Changing Dimension Type 2 Examples (SCD 2)"  here.  and full implementation of SCD Type -2 in Informatica can be found here Other good article on SCD are as below :-

• Slowly Changing Dimension Type 2 Examples (SCD 2) 
• SCD Type 2 implementation in Informatica with example
• Slowly Changing Dimension Type 3 Examples (SCD 3)
• SCD Type 3 implementation in Informatica with example
• Slowly Changing Dimension Type 6 Examples (SCD6)
• SCD Type 6 implementation in Informatica with example

  Hope you enjoyed this small and useful article on SCD Type -2 (Slowly changing dimension type -2 ) and example of SCD Type-2 In Informatica  :)