Update data in the same Salesforce Object with a 'Self-join' Informatica Cloud Task

When you use Informatica Cloud you usually use it to do Migration, Integration and Synchronisation. That's about what it is used for, right?

It is all about moving data from point A to point B or  syncing data between A and B. That is what  Informatica Cloud is built for, to move/sync data from A to B and while moving it, maybe transform it as well. A is the 'source' and  B is the 'target'. Correct! A can be an Oracle or ERP database and B can be a Salesforce Org. Classical use case of Informatica Cloud.

Well, not exactly. How about if you think a little bit differently.

How about if A is the source and A is the target, as well!

Yes, that's it. Read from A and Write to A via an Informatica Cloud synchronisation task. A kind of  Informatica Cloud Data Syncronisation Task which will 'self-join' an Object or Table. To be used to 'self cleanse' or  'self update' the Salesforce custom object data or Oracle database table. This is possible, yes we can use  Informatica Cloud as 'self-join' SQL command as well, to edit our data 'in-place' in one single object/table.

I will try to explain this concept with an example. Suppose you have data in your Salesforce standard object called 'Account' which you want to change. A standard approach would be to 'export' data in a CSV file via a report, change the data manually in Excel or something and then use Apex Data Loader to put the changed data back to the Accounts object in your Salesforce Org right?

The screen shots below show you how to do this type of operations without actually exporting the data, but doing it in place with an Informatica Cloud synchronisation task.

What this synchronisation task does is, it uses the same Salesforce Org connection and Account object as 'Source' and as 'Target'. That is it, the Account Standard object for this task is the Source connection and the Target connection at the same time.

This way the Informatica Cloud task, reads the data from the Account object and writes it back, after it transforms it, into the same Account object.


Create a Source on the Account object

Create a Target for the same Account object in the same org. Just choose as target the same object.

Field Mapping a kind  of  'Self-Join', see Id=id

Use DECODE function in the same Field Mapping to manipulate data

The Informatica Cloud string function 'DECODE' does the trick. Observe how it changes the occurrences of the string 'Direct Employer' to 'Employer' in the Type field of the standard Salesforce Account object, which is actually the same object it reads it from.

decode(Type,'Direct Employer','Employer')

When you save and run this task it will read from the same Account object and write back to the same Account object the changed data. All within an Informatica Cloud Data Synchronisation 'Self-join' task! No file downloads, Excel use or whatever. Just one command. Given the plethora of  Informatica Cloud String, Arithmetic, Date etc... functions available, just imagine what you can do to your data!

This is a wonderful and simple example of how Informatica Cloud can be used to cleanse data, I think.