building financial data mode - lab#21conditional lookup

7/30/2019 Building Financial Data Mode - Lab#21Conditional Lookup

1/32

Informatica Power Center 9.0.1

Building Financial Data Mode - Lab#21Conditional Lookup

Description:BISP is committed to provide BEST learning material to the beginners

and advance learners. In the same series, we have prepared a complete

end-to end Hands-on Guide for building financial data model in

Informatica. The document focuses on how the real world requirementshould be interpreted. The mapping document template with very

simplified steps and screen shots makes the complete learning so easy.

The document focuses This document contains step by step process for

conditional lookup transformation (Unconnected lookup) in Informatica Power

Center 9.0.1. Join our professional training program and learn from

experts.

History:Version Description Change Author Publish Date0.1 Initial Draft Upendra Upadhyay 21th Aug 2011

www.bispsolutions.com www.bisptrainigs.com www.hyperionguru.comPage 1
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/


2/32

0.1 Review#1 Amit Sharma 29th Aug 2011

ContentsContents .......................................................................................................................... 2

Introduction ..................................................................................................................... 3

Connected and Unconnected Lookup .......................................................................... 3

Purpose .................................................................................................................... 3

Objective .................................................................................................................. 3

Summary .................................................................................................................. 3

Performing Process.................................................................................................... 4

Importing Source and Target Table .............................................................................. 5

Creating Mapping ......................................................................................................... 9

Step by step process for creating Dynamic lookup transformation...........................9

Creating Workflow ................................................................................................... 22

Execute Workflow, Review Data and Check Log Files ................................................. 28



3/32

Introduction

Lookup transformation is Passive and it can be both Connected and Unconnected as well. It is usedto look up data in a relational table, view, or synonym. Lookup definition can be imported either

from source or from target tables. But conditional lookup is unconnected lookup transformation.

Connected and Unconnected Lookup

Connected lookup: A connected lookup transformation receives source data,performs a lookup and returns data to the pipeline. We can use a dynamic orstatic cache and cache includes all lookup columns used in the mapping. And ifthere is no match for the lookup condition, the Informatica Power Center Serverreturns the default value for all output ports.

Unconnected Lookup: An Unconnected lookup receives values from: LKPexpression from another transformation.. We can use a static cache and cacheincludes all lookup/output ports in the lookup condition and the lookup/return

port. If there is no match for the lookup condition, then Informatica PowerCenter server returns NULL value.

PurposeRead from a large source that has a few rows of invalid data. To validate specific data, look up the

value in another table. This is only necessary for about one row out of ten.

ObjectiveBuild a mapping to load an SRC_IRCS table data that contains theses column INTEREST_RATE_CD,

IRC_NAME, IRC_FORMAT_CD, ISO_CURRENCY_CD, ACCRUAL_BASIS_CD etc. and used two

Lookup table SRC_CURRENCY and SRC_IRCS _FORMAT_CD. Give condition for UN_LKP_VALUEcolumn in Target Table, if ACCRUAL_BASIS_CD=3 then load Currency_Name from SRC_CURRENCYLookup table otherwise load IRCS_FORMAT_DESC from SRC_IRCS_FORMAT_CD Lookup table andload these data into TRG_UNL_IRCS.

SummarySOURCE TABLE NAME LOOKUP TABLE NAME TARGET TABLE NAME

SRC_IRCS SRC_CURRENCY TRG_UNL_IRCS

SRC_IRCS_FORMAT_CD



4/32

Performing Process.

Open Informatica Power Center Designer.

Import Source Table in Source Analyzer window and Target table in Target Designer

windows.

Create mapping in Mapping Designer window and then drag and drop source and targetcolumn into mapping designer window.

Create two lookup transformation and select lookup table and edit lookup table and select

port as a return value and also create one port for input in ports tab then give condition incondition tab.

Create Expression Transformation and create one port in ports table and give condition forcall both lookup, and then map these column and save it.

Open Informatica Power Center Workflow Manager.

Create workflow in workflow designer window, create task and then select mapping for this

task, assign session and specify connection information and then save it.

Execute workflow.

Open Informatica Power Center Workflow Monitor.

Check execution of this workflow in workflow monitor and then preview data in target

designer or target RDBMS and then check execution log file.


Process Flow Chart :


5/32

Importing Source and Target Table

Import data from source and create target table (There are two types to create target table, manually or import

from database). There are following step to importing source and target database (Table).

Step-1 Click on Start > All Programs > Informatica 9.0.1 > Client > Power

Center Client> Power Center Designer.

Step-2 Then Connect to Repository in Informatica Power Center Designer Right click on repository name and

click on Connect.

Step-3 Go to Tools Menu and click on Source Analyzer to import source table.

Step-4 And then go to Sources Menu in Informatica Power Center Designer.


Right Click on

Select Source Analyzer


6/32

Step-5 And then select Source menu and click on Import from database. In Source menu, some another

option available such as Import from File(Flatfile), Import from Cobol File and Import XML Definition.

Step-6 Specify Username, Owner name, password and click on connect. Then select source table and click

OK.


Click on Import

from


7/32

Step-7 View data in source analyzer, Right click on SRC_CREDITCARD and select Preview Data.

Step-8 Specify Username and Password and connect it. Then Close it.



8/32

Step-9Now click on Target Designer.

Step-10 And then select Target menu and click on Import from database to import target table.

Step-11 Specify Username, Owner name, password and then click on connect and then select table and then

OK.

Step-12 Target table in Target Designer window.



9/32

Creating Mapping

Mappings represent the data flow b/w sources to targets. When the Informatica Power

Center Server executes a session, it uses the instructions configured in the mapping to

read, transform, and write data.

Every mapping must contain the following components:

Source definition: It describes the characteristics of a source.

Transformation: A transformation is a repository object which reads the data, modifies the data

and passes the data. Transformations in a mapping represent the operations that the integrationservice performs on the data.

Target definition: It describes the target table.

Step by step process for creating Dynamic lookup transformation.

Step-1 Go to Mapping Designer and Create New Mapping and then name of mapping and click OK.

Step-2 Then drag and drop source table in Mapping Designer Window.



10/32



11/32

Step-3 Then Create Unconnected lookup transformation, Click on Transformation menu and click

on Create, then select Lookup in drop down and give name of lookup transformation and the click

on Create button, then select lookup table for Lookup Transformation and click OK. then click

Done.

Unconnected Lookup is called upon conditionally. It is ideal to use when the majority or all of

the rows do not need a return value (ports) from it. This conditional Lookup is called unconnected

because no physical links are present it stands alone, unconnected.

Step-4 Select Lookup table from Source Analyzer.


Select Lookup

from Source

Select lookup from drop

Import lookup table

from RDBMS or Flatfile

Select Lookup table

from Target

Select Lookup from

Source Analyzer


12/32

Step-5 Again create another lookup transformation,, Click on Transformation menu and click on

Create, then select Lookup in drop down and give name of lookup transformation and the click on

Create button, then select lookup table for Lookup Transformation and click OK. then click Done.

Step-6 Import lookup table from RDBMS. Click on Import button and click on From Relational

Table to import lookup table.


Select Lookup

from Source

Select lookup from drop

Import lookup table

from RDBMS or Flatfile

Select Lookup table

from Target

Select Lookup from

Source Analyzer


13/32

Step-7 Select ODBC Connection, then give Username and Password and click on Connect button,

select table and click on OK button.

Step-8 Now, Right click on Lookup table and select Edit to edit the lookup table.


Right click on

Lookup Table.


14/32

Step-9 Now, Go to Ports table and add one port as a input, name of port and then check "R" port as

a return value.

Ports

Mixed.

"L" denotes lookup port.

"R" denotes port used as a return value (Unconnected Lookup only) - The

R port is only required in when the Lookup is unconnected lookup. If checkedwhile connected it will be ignored. Only one port can be selected hence it acts like a

radio button.

Step-10 Now, Go to Condition tab and give lookup condition.



15/32



16/32

Step-11 Now, Right click on another Lookup table and select Edit to edit the lookup table.

Step-12 Now, Go to Ports table and add one port as a input, name of port and then check "R" port as

a return value.

Ports

Mixed.

"L" denotes lookup port.

"R" denotes port used as a return value (Unconnected Lookup only) - TheR port is only required in when the Lookup is unconnected lookup. If checkedwhile connected it will be ignored. Only one port can be selected hence it acts like a

radio button.


Right click on

Lookup Table.


17/32

Step-13 Now, Go to Condition tab and give lookup condition.



18/32

Step-14 Then Create Expression transformation, Click on Transformation menu and click on Create,

then select Expression in drop down and give name of expression transformation and the click on

Create button.


Select Expression

in drop down.


19/32

Step-15Now, passes all source qualifier transformation column into expression table.

Step-16 Then, right click on Expression table and select Edit.



20/32

Step-17Now, Go to Ports tab and create a column and give name of column and specify Data type,

check only Output port for this column and then write expression in expression editior.

Step-18 Write expression as below. Select IIF function in function tab then select column and then

select lookup table and select port from ports tab and then validate it.



21/32

Step-19 map expression transformation table to target table column.

Step-20 . Save this mapping.



22/32

Creating Workflow

Workflow Manager: Workflow load the data between source to target b/w sequential manner. And

also Define run-time properties for a mapping, known as sessions.

Step-1 Open Informatica PowerCenter Workflow Manager and then go to go to workflow designer

and click on workflow menu to create workflow. Specify name of workflow and click OK.

Step-2 Workflow Designer.



23/32

Step-3 Create Task and assign session, Click on Task menu and click on create and then name of

session, click Create, select mapping and click OK.



24/32

Step-4 Session in workflow designer windows.

Step-5Now create flow B/W Workflow to Task. Select Line Task and link to Start to Filter.

Step-6 Work Flow Designer Windows.



25/32

Step-7Now Configure Connection to Target, Then Right Click on Expression and Edit, Then Click Mapping

tab and configure connection for your Target table schema in oracle 11g RDBMS. And then click OK.

Step-8 In Mapping. Specify Connection and property.



26/32

Step-9 Set These property for target table.

Insert: Check this option to insert a row in the target table.

Delete: Check this option to delete a row in the target table.

Truncate Table: check this option to truncate the target table before loading the data.

Update as Update: Update the row in the target table.

Update as Insert: Insert the row which is flagged as update.

Update else Insert: If the row exists in the target table, then update the row. Otherwise,insert the row.



27/32

Step-10Now save (ctrl+s) this workflow and check it.



28/32

Execute Workflow, Review Data and Check Log Files

Workflow monitor: Workflow monitor is helpful in monitoring and tracking the workflow created in

Informatica power center.

Step-1Now Start Workflow, Right click on Workflow Designer Window and Click on Start Workflow.



29/32

Step-2 Check session in Informatica PowerCenter Workflow Monitor.


Target table load. Session successful


30/32

Step-3 Target Table. Right click on Target Table in Target designer and give Username, Owner

name and password and then click on Connect.



31/32

Step-4 Session Log. Right click on session and select session log.


Lookup table connection

Lookup table SQL Query.


32/32

SQL Query.

Target Load

Session Load

building financial data mode - lab#21conditional lookup

Documents