Connected Lookup Transformation in Informatica

• Passive Transformation 

• Used to search 

• To get the related values

• To compare the values

• Used in slowly changing dimensions (SCDs)

Lookup Transformation in Informatica

The source EMP does not have deptname. Target DB required deptname.

Lookup DB in this case is our Source DB which has DEPT table (SCOTT schema)

Note: To increase the performance all other ports are projected directly from SQ to the TGT.

e.g. EMPNAME,SAL etc.

Example:

Source Target

1 X 3000 10 1 X 2000 10

3000 NE 2000 (NE means not equal to)

Here, the lookup is TGT. The sal changed from 2000 to 3000. This change needs to be projected to the TGT.

Relate the Source and Target tables first.

The values are compared.

The changes are then projected to the TGT. Change of information affects dimensions.

Lookup uses Data Cache and Index Cache.

It also uses its own cache.

Default LOOKUP CACHE is STATIC CACHE (Does not change any value – brings the value and relates)

Just relates the data and pass the data.

No change to the data is possible.

In Dynamic Lookup, if we want to project the changed data, we need Associated Port (A.P.).

When Dynamic option is selected, AP,IN,IC ports are highlighted.

Steps:

Define Source (EMP).

Define Target (DIM_JOIN).

Define Mapping M_LKUP_C_S (Lookup-connected-static)

Drag Source,Target to the Mapping Designer workspace.Create a Lookup Transformation T_LKP_C_S.

Click on Create.

Lookup Transformation in Informatica

Select Lookup table for Lookup Transformation.

Lookup Transformation in Informatica

Lookup table is DEPT which in this case in the Source Database itself. Select the Source tab and select DEPT and then click on OK.

Note: From version 7.0 onwards, Lookup flat file concept has been introduced.

Lookup Transformation in Informatica

The structure of lookup table is brought up with the lookup ports.

Deptno,dname and loc are the lookup ports. (YES).

Project the related port from the SQ to the Lookup Transformation.

Lookup Transformation in Informatica

Edit the lookup transformation and in the ports tab see that the input port for deptno1 is checked.

Lookup Transformation in Informatica

The deptno and deptno1 ports should have same data type and size.

Select the condition tab.

Add a condition.

Lookup table column deptno = transformation port deptno1.

Click on Apply and then OK>

Properties Tab

Lookup Transformation in Informatica

Lookup SQL Override.

Lookup Table name.

Lookup Caching enabled. If we cache the values, we can save the values for Persistent Cache and we can use Dynamic. By default, it is checked.

Lookup policy on multiple match. By default, First value is matched because we are just passing the data.

1 x 1000 (First time projected)

1 x 2000 (Second time projected)

1 x 1000 -> same as the first time projection, here first value match does not work (as it is same as the first time projected data), so we use last value match.

In static definition, USE FIRST VALUE.

Lookup Condition is disabled.

Connection Information $source or $target.

Source Type (database or flat file)

Tracing level.

Lookup Cache Directory name

Re- cache from lookup source.

Flat file Properties:

Thousand Separator.

Decimal Separator.

Project the required ports from Lookup to the target and SQ to TGT.

Lookup Transformation in Informatica

Repository-> Save

Create a Session.

At the session level specify three connections STC,TGT and LKP.

TARGET CONNECTION

Lookup Transformation in Informatica

Lookup Transformation in Informatica

$Source contains the Source database.

Lookup table name is DEPT.

Create a Workflow.

Save the Repository.

Start the Workflow.

Lookup Transformation in Informatica

Preview the output data.