Informatica Power Center Practice Exercises

Below are some exercises to improve ETL skills using Informatica Power Center.

1. Load a target table with truncate and load strategy.
2. Load a target table by Inserting/Updating using transformations Lookup and Update strategy.
3. Load a target table with Insert Else Update without using update strategy transformation
• This strategy is helpful if target table is huge and number changes going to target are less
4. Handle duplicates in Lookup transformation
• You could leave default properties to pickup the last value in duplicates
• Or you could change the properties to pick a value that is appropriate to your scenario
• Or you could also choose to get all matches including duplicates from lookup
5. Join data coming from multiple sources using Joiner transformation and load into a single target
6. Observe performance difference of using sorted input vs no sorted input in Joiner transformation
• Data can be sorted in source query if it is a database source
• Data can be sorted in sorter transformation if you cannot sort in source
• You may need to use sorter transformation if you are getting data from different source systems because they may return different output for same sort criteria (Ex: DB2 vs Oracle - Sort order for nulls/symbols is different between these two databases)

Lookup Salesforce table in Informatica PC

Overview
To lookup Salesforce table from Informatica Power Center, you need to use Salesforce lookup transformation. Regular Lookup transformation cannot be used.

Details

• Add Salesforce lookup transformation in mapping. In dialog box that appears, enter the user name, password and service url.
• After connecting, choose the object you would like to lookup and click Ok to import.
• Lookup ports
• You can keep the lookup ports you need and delete others.
• All connected lookup ports will be included in SOQL Select statement which would be generated by Integration service
• Default ports
• LKP_FILTER - You can pass filter condition to this port which will be added to the Where clause of Lookup SOQL.
• LKP_MATCHIDX - unique sequence id of match
• 0 => when no matches found
• 1 - n => 1 for first match found, and 2 if second match is found etc..
• Lookup input ports
• Input ports can be configured so those would be added in SOQL where clause 

Notes

• Salesforce lookup transformation returns multiple matches by default
• There is no cache - Integration services generates SOQL statements based on each input row
• Use Source Qualifier and Joiner instead if you would like to cache Salesforce lookup data
• There is no query overriding

 -

Informatica Salesforce Connection

Licensing
Salesforce connector for Informatica Power Center needs to be purchased separately, and install the given plugins.

Connections
After Salesforce plugins are installed, you could create Salesforce connections in Informatica Workflow Manager under Application connections.

Informatica PC Repository Query - Get Session Run statistics

Below query returns session run statistics with rows processed by sources/targets of a session. It supports multiple sources/targets.
(tested on Informatica 9.5 repository setup on Oracle)

SELECT distinct
a.workflow_run_id
,d.workflow_name
,e.instance_name as session_name
,case when widget_type = 2 then 'Target'
  when widget_type = 3 then 'Source'
end as Instance_Type
,a.instance_name
,a.applied_rows
,a.rejected_rows
,a.start_time
,a.end_time
FROM
opb_swidginst_log a,
opb_mapping b,
opb_session c,
opb_wflow_run d,
opb_task_inst e
WHERE
b.mapping_id = c.mapping_id
and c.session_id = e.task_id
and a.task_instance_id = e.instance_id
and a.workflow_run_id = d.workflow_run_id
and a.widget_type in (2,3)
and d.workflow_name = 'wf_XXXXX'
and e.instance_name = 's_XXXXX'
--and a.workflow_run_id = 'wf_Run_ID_XXXXX';

Informatica PC Repository Query - Search Expressions

In below query, you can modify filters as needed to search the expressions in a mapping/project.
(tested on Informatica 9.5 repository setup on Oracle)

SELECT
a.subject_area
,a.mapping_name
,d.instance_name as Expression_Name
,c.field_name
,c.expression as Expression_Value
FROM
REP_ALL_MAPPINGS a
INNER JOIN (select distinct mapping_id, widget_id, instance_id, instance_name from OPB_SWIDGET_INST) b
ON
a.mapping_id = b.mapping_id
INNER JOIN REP_WIDGET_FIELD c
ON
b.widget_id = c.widget_id
INNER JOIN REP_WIDGET_INST d
ON
  b.widget_id = d.widget_id
WHERE
a.subject_area = 'f_XXXXX'
AND a.mapping_name = 'm_XXXXX'
AND d.widget_type = 5
AND c.expression like '%TRIM%'
--AND b.instance_name in ('exp_XXXXX')
--AND c.field_name in ('exp_field_XXXXX')
order by a.subject_area, a.mapping_name, d.instance_name

Informatica PC Repository Query - Get Joiner Type

Below query returns joiner type of all joiner transformations used in a project folder.
(tested on Informatica 9.5 repository setup on Oracle)

select distinct
a.mapping_name
,b.instance_name as Joiner_Name
,case c.ATTR_VALUE
 when '1' then 'Normal Join'
 when '2' then 'Master Outer Join'
 when '3' then 'Detail Outer Join'
 when '4' then 'Full Outer Join'
 else c.attr_value
end as Join_Type
FROM
REP_ALL_MAPPINGS a
INNER JOIN (select distinct mapping_id, widget_id, INSTANCE_ID, instance_name from OPB_SWIDGET_INST) b
ON
a.mapping_id = b.mapping_id
INNER JOIN OPB_WIDGET_ATTR c
ON
  b.widget_id = c.WIDGET_ID
INNER JOIN OPB_WIDGET d
ON
  b.widget_id = d.widget_id
WHERE
 a.SUBJECT_AREA = 'f_XXXXX'
 AND c.WIDGET_TYPE = 12
 AND c.ATTR_ID = 4
 AND d.IS_VISIBLE = 1
order by a.mapping_name, b.instance_name;