Lookup Transformation - Lookup Cache

1. What is a lookup cache?

Cache is the temporary memory that is created when you execute the process\session.

2. When is lookup cache created?

Cache is created automatically when the process starts and is deleted automatically once the process is completed.

3. Where is the Cache created? Path?

$PMCacheDir   --> $PMRootDir/cache 
[ Note: $PMRootDir    -->  D:/Informatica/9.5.1/Server/InfaShared ]

4. What is the default cache?

By default, Non Persistent Cache is created. i.e Static Cache.

5. What are the types of cache?

1. Persistent Cache
2. Non Persistent Cache - Static Cache and Dynamic Cache

6. What is Persistent Cache?

By default, caches are created as non persistent, it will get deleted once the session run is completed.
You can configure to permanently store data.

Example 1: A mapping/process runs every day and the mapping has the lookup transformation to lookup references table that is not supposed to change for 6 months,

Solution 1: We can opt for Persistent cache,
Select the below properties:

1. Lookup Cache Persistent
2. Cache file name prefix (Cache file such as data and index files are created with the mentioned name)
3. Re-Cache from lookup source (If we need a rebuild of the cache in case there is a update of the lookup table)

Lookup Transformation - Persistent Cache - Property Setting

7. What are the differences between Static Cache and Dynamic Cache?

Static Cache : 

• A Cache is said to be Static if it does not change with the changes happening in the lookup table,
• Integration Service does not update the Cache when it is processing the date.
• The Static Cache is not synchronized with the target lookup
• It is the default cache.

Dynamic Cache:

• A Cache is said to be dynamic if it changes with the changes happening in the lookup table.
• Integration service updates the cache while it is processing the data.
• The Dynamic Cache is synchronized with the target table.
• Set the below properties:
• Dynamic Lookup Cache
• Insert Else Update

• A new output port is created NewLookupRow. 

NewLookupRow = 0 (Unchanged - IS performs no change)
NewLookupRow = 1 (Insertion - Integration Service inserts the row into the cache)
NewLookupRow = 2 (Update - Integration Service updates the row into the cache)

Reference Pics Below

Lookup Transformation - Dynamic Cache - Property Settings

Types of Lookup Tranformation

Types of Lookup Tranformation

There are two types of lookup transformation

Connected Lookup Transformtion


1. Input is obtained from mapping pipeline
2. It can return multiple values
3. It supports both static and dynamic cache
4. Multiple lookup instance is created in the mapping.
5. Returns a default value when no records.


Unconnected Lookup Transformation

1. Input is obtained from :LKP function
2. Returns only one value
3. It supports only static cache
4. One lookup instance is used in the mapping.
5. Return null value when no record.

Slowly changing dimention - Type III

Slowly changing dimention - Type III

The historical data is tracked/maintained by creating a new column and maintaining the old value in the new column.

Only the last 2 updates can be maintained,

1 . Import the source table in Source Analyzer and target table in the Target Designer.
2. In the Mapping Designer,

1. Drag and drop the source table and the Source Qualifier is automatically created.
2. Create a lookup Transformation based on the Target table and put a condition based on the ProductID
3. Now create a Router and group the condition for Insert/Update.

• Group A INSERT : IIF(ISNULL(PDT_ID_TRG),true,false)
• Group B UPDATE: IIF(NOT ISNULL(PDT_ID_TRG) AND PRODUCT_NAME != PDT_NAME_TRG,true,false)

GROUP A : It is used to insert the new records to the target table with the below values,

SURR_KEY  = SeqenceGenerator.NextVal();

Group B, It is used to update the values 

PRODUCT_NAME = SOURCE.PRODUCT_NAME
OLD_PRODUCT_NAME = TARGET.PRODUCT_NAME
UPDATED_DT = SYSDATE

4. Create Update Strategy Transformation for the respective groups from Router and finally map it to the target tables.

Mapping Designer

Output Data

Slowly Changing Dimension - Type II

Slowly Changing Dimension - SCD Type II

The historical data is tracked/maintained by creating multiple rows for the Natural Key in the Dimension Table by

1. Creating separate Surrogate Key
2. Creating different version number

Here instead of creating different version number we will create ,

EFF_START_DT
EFF_END_DT
CHANGED_FLAG

1 . Import the source table in Source Analyzer and target table in the Target Designer.
2. In the Mapping Designer,

1. Drag and drop the source table and the Source Qualifier is automatically created.
2. Create a lookup Transformation based on the Target table and put a condition based on the ProductID
3. Now create a Router and group the condition for Insert/Update.

• Group A INSERT : IIF(ISNULL(PDT_ID_TRG),true,false)
• Group B UPDATE: IIF(NOT ISNULL(PDT_ID_TRG) AND PRODUCT_NAME != PDT_NAME_TRG AND MOD_FLAG = 'Y',true,false)

GROUP A : It is used to insert the new records to the target table with the below values,

SURR_KEY  = SeqenceGenerator.NextVal();
EFF_START_DT = SYSDATE
EFF_END_DT = TO_DATE('30-12-2099','DD-MM-YYYY')
CHANGED_FLAG = 'Y'

Group B, There are 2 different pipelines, 

1. Add the modified row as a new row and set the values are below

SURR_KEY  = SeqenceGenerator.NextVal();
EFF_START_DT = SYSDATE
EFF_END_DT = TO_DATE('30-12-2099','DD-MM-YYYY')
CHANGED_FLAG = 'Y'

2. Update the modified row as below

EFF_END_DT = SYSDATE
CHANGED_FLAG = 'N'

4. Create Update Strategy Transformation for the respective groups from Router and finally map it to the target tables.

Mapping Designer Screen Shot

Output Data

Slowly Changing Dimension - Type I

Using Lookup Transformation - Dynamic Cache


With a dynamic cache, the Integration Service inserts or updates rows in the cache. When you cache the target table as the lookup source, you can look up values in the cache to determine if the values exist in the target. The Lookup transformation marks rows to insert or update the target.

Final Output -  The Second Table gets updated with the recent record

Table 1 - Application1 - No Primary Key

Table2  - Application2 - App_ID is the Primary Key

LookUp Tranformation - Condition Tab

Lookup Transformation - Properties Tab

Lookup Transformation - Ports Tab

Router - Groups Tab

Router - Ports Tab

Mapping Designing

Update Strategy - Property t/ab

Session Edit

Session Edit

Slowly Changing Dimension

Slowly Changing Dimension

Dimentions that change over time rather than changing on a regular schedule.

Types of Slowly Changing Dimensions

• SCD Type 1
• SCD Type 2
• SCD Type 3

SCD Type 1

Keeps only the current data and does not maintain the historical data.

i.e., Overwriting the old/existing value.

Example :

Suppose the employee changes his location. i,e from India to USA and then he gets shifted to Germany.

EMPLOYEE STAGING

EMPLOYEE DIMENSION

Employee Dimension

ADV:  Do not consume a lot of space to maintain data
DISADV:  It does not maintain historical data.

SCD Type 2 or Version Number Mapping

Keeps the current data and historical data as well.

It creates a new column PM_VERSION_NUMBER in the table to track the changes and PM_PRIMARY_KEY in the table to maintain the history.

EMPLOYEE DIMENSION- VERSION MAPPING

SCD Type 2 or Flag Mapping

Keeps the current data and historical data as well.

It creates a new column PM_CURRENT_FLAG in the table to track the changes and PM_PRIMARY_KEY in the table to maintain the history.

EMPLOYEE DIMENSION - FLAG MAPPING

SCD Type 2 or Effective date range Mapping

Keeps the current data and historical data as well.

It creates PM_BEGIN_DATE and PM_END_DATE to maintain the date range in the table to track the changes and

PM_PRIMARY_KEY in the table to maintain the history.

EMPLOYEE DIMENSION - EFFECTIVE DATE RANGE MAPPING

SCD Type 3 - Dimension Mapping

Keeps the current and partial historical data by adding new column PM_PREV_COL_NAME column to the table.

Informatica Interview Question - Node and Domain

Informatica Interview Question - Node and Domain

1. What is a Node? 

• When you install and run the informatica services, the installation is known as Node. 
• The logical representation of informatica installation is known as Node.

     2. What is Domain?

• Domain is a grouping of one or more nodes.
• A domain forms an environment upon which the informatica serivce processes run.
• A node from the domain connects to a relational database.
• This database have the tables of domain configuration repository.
• You can add nodes to the domain, when you install the node.
• (Basic description is : There is a node, there is a domain database. The stuff runs on Node and the domain has/stores the stuff.)
• Informatica supports Oracle, IBM DB2,Sybase,MQSQLserver for domain database

      3. What are the types of nodes?

•       Basically the node is of two types, Worker node and Gateway Node.

      4.  Describe Worker , Gateway and Master Gateway Node?

•  A node can either be a worker node or a gateway node.
•  Domain can have more worker node and gateway nodes but only one Master Gateway node.
• Each Domain has a minimum of one Gateway node.

Gateway Node:

• Gateway node runs the management services.
•  A gateway node connects to the domain database and changes the metadata stored in the domain tables.
• At a time, these tasks
• A gateway node acts as the master gateway node, which runs the management services of all the domains and it is the one node in the domain which changes the domain database.

Worker Node:

•     Worker node runs the Application Services.
• Cannot connect to the Domain database.

    5. What happens when the master gateway node fails?

•      One of the other gateway node elects the master gateway node.

Update Records Without using Update Strategy

Update Records Without using Update Strategy


This is very simple procedure.

Create a mapping. There is nothing to do with powercenter designer.

While creating session, we have to change few properties in the session property.

Mapping Designer

Edit Tasks - Properties - Treat Source Rows As - Update

Edit Tasks - Mapping - Update Else Insert  - Select

Joiner Transformation

Joiner Transformation:

You can join two tables using the Joiner Transformation. If you need to join 3 table Sources, then 2 Joiner Transformation is required. So if you need to join N number of source, you need N-1 Joiner Transformation.

The Joiner Transformation joins the source based on one or more condition between the 2 sources.

The 2 input source pipeline has master and detail pipeline. By default, the second source is considered as Master.

Mapping Designer

Joiner Transformation Edit- Give the Condition based on 2 source Table

Select Master/Detail for Source

Below query is executed for the above mapping.

select e.employee_id,first_name,d.department_id,d.department_name,e.email from employees e left outer join departments d on e.department_id=d.department_id;

This is otherwise called Normal Join.

Normal Join

Matching rows from master table and detail table

Master Outer Join

Returns the rows from detail table and the matching records of both the table.

Detail Outer Join

Returns the rows from master table and the matching records of both the table.

Start the Informatica Service using Task Manager

Start the informatica server

Using the task manager you can start the informatica server.

Task Manager - Start Informatica Server

Source Qualifier Transformation - Joins

Source Qualifier Transformation - Joins:

You can join two or more tables using the Source Qualifier Transformation. To achieve it, 

Set the Property of "User Defined Join" in the Properties Tab of the Edit Transformation.

Edit Transformations - Properties Tab

Mappng Designer

This works based on the below query:

select * from employees e,departments d where e.department_id=d.department_id;

Left Outer Join SQL Query - HR Schema

Source Qualifier Transformation - Filter

Source Qualifier Transformation - Filter:

Using Source Qualifier Transformation,

You can filter the rows of the source database by adding a where clause in the Source Filter in the Properties Tab of the Edit Transformation.

The Intergration Service adds a WHERE clause to the default query.

Edit Transformations - Properties Tab - Source Filter

Mappng Designer

This works based on the below query:

select * from employees e,departments d where e.department_id=d.department_id and d.department_name='IT';

Left Outer Join SQL Query - HR Schema

In the Workflow Manager, the session properties check the Source Filter Properties of Souce Qualifier Properties in the Mapping Tab.

Workflow Manager - Edit Task - Mapping Tab - SQ Properties - Source Filter

Source Qualifier Transformation - Sort

Source Qualifier Transformation - Sort:

Using Source Qualifier Transformation,

You can Sort the rows of the source by specifying the number of ports.

The Intergration Service adds a ORDER BY clause to the default SQL query.

Note: The Order of the column in the Source Qualifier Transformation should be order of number of ports which we specify for Sort Port.

Edit Source Qualifier Transformation - No of sorted ports

Mappng Designer

This works based on the below query:

select * from employees e,departments d where e.department_id=d.department_id and d.department_name='IT' ORDER BY first_name;

Left Outer Join SQL Query - HR Schema

In the Workflow Manager, the session properties check the Number of sorted ports Properties of Souce Qualifier Properties in the Mapping Tab.

Workflow Manager -Edit Task - Mapping Properties - SQ Properties