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.