Cloud Data Integration : 2013 : March Skip navigation
2013

Cloud Data Integration

March 2013 Previous month Next month

It is easy to load data to an empty Salesforce Org. Often this can be achieved with minimum effort and using native Salesforce tools like Data Loader or the free Informatica Cloud Data Loader for Salesforce. But, what approach should be taken if the project requires continuous synchronization between the external data sources like other cloud applications, on-premise applications, databases or CSV files and Salesforce?

 

This can be done by using a special field in the Salesforce object called the External ID. Combining the UPDATE and INSERT tasks to create UPSERT, an Informatica Cloud UPSERT task can be used with the Salesforce External ID, populated with a unique value from the external data source system, to keep the data sources and Salesforce synchronized.

 

Salesforce Delete Problem

 

Unfortunately, the Salesforce application programming interface (API) does not permit you to delete records in Salesforce, unless you know their original Salesforce ID. That is, an External ID from an external system is not enough to be able to delete records in Salesforce. Only if you know the Salesforce ID of a record you can delete it in Salesforce.

 

This can be a major obstacle if you are looking to build data synchronization interfaces - which include deletes - between tyour data sources and Salesforce. Despair not, there is a solution!.

 

Let’s walk through how we can implement a delete operation between a CSV (comma-separated values) file and Salesforce using Informatica Cloud and a SQL staging database.


Solution

 

Imagine you are given a CSV file F every night and you are told to refresh your Salesforce data with it and the ‘refresh’ requires you to do an INSERT, UPDATE and a DELETE.

 

Next, you can quickly set up an Informatica Cloud task where you can read form F and UPSERT into Salesforce custom object C via your chosen External ID field as seen below (External ID in red).

1.jpg

Job done; but what about a DELETE?

 

We now understand that UPSERT handles only updates of existing records and inserts of new records and that it doesn’t delete records.

 

So, how would it be possible to delete a record from Salesforce if on Day 2 your CSV file F was delivered like below with the record ‘Mary’ - ‘002’ now deleted and you were expected to reflect this change to Salesforce?

 

How would you delete?

2.jpg

There is an easy way to overcome this with a staging database. By introducing a SQL staging database where you will load data from both sources, you can then perform an outer join and use the original Salesforce ID to delete the record. [1]

 

Two Informatica tasks can be used to extract data from the source and target into the staging database tables F,C like this:

3.jpg

Once the data is extracted in the database, then with a RIGHT OUTER JOIN view as in the statement above we can join the tables F,C and obtain the data set which will provide us the Salesforce ID (in red). This database view will also indicate by the presence of NULL values in the columns of the table F the fact that data has been deleted. The result of the above SQL in the staging database would look like below:

4.png

Now, with the above database view from the Staging DB as a source in an Informatica Cloud task, we can easily delete the Salesforce record. All we have to do is to create the Informatica Cloud task with a data filter on F.F_ID where we will select only records which have NULL F.F_ID column. By doing this, the returned set will only contain data which has been deleted in the file but now with the Salesforce ID of the deleted record.

5.png

The above database view with the NULL filter is now our delete task in Informatica Cloud. This view contains the Salesforce ID of the deleted record enabling you to delete a record in Salesforce.

6.png

 

The above solution can be easily automated with an Informatica Cloud schedule and task flow. An extract of the full Salesforce custom object C and the load of it to the staging database table C can be automated with an Informatica Cloud schedule, the same way we can automate the load of the CSV file F into the staging database table F. The database view V will always contain records which have been deleted. Propagating this view to Salesforce with an Informatica task will keep Salesforce synchronized with the CSV file at all times and it will also delete records which have been deleted in the CSV file from Salesforce automatically.

2bv2.PNG

 

 

On Tuesday, March 5, 2013, Informatica Cloud reached an unprecedented new height by processing more than TWO billion transactions in a single day.  It was only one year ago that Informatica Cloud processed one billion transactions in a day.   To double that number within a year is phenomenal.  The exponential trajectory of growth is a testament to the technology platform and the people who built it and support it.  A big "Thank You!" to our customers, the cloud community members who use our products, and everyone who helped us get here.  BIG DATA Congratulations to Everyone!! 

 

2bpic2.PNG

One of the most powerful Informatica Cloud features is Cloud Integration Templates. With Templates, you can develop your own data integration flows to perform functions that you cannot typically do using the standard Informatica Cloud applications.

 

For example, you can join objects/tables (even if there is no source-defined relationship or if they come from different connections), perform aggregations on data, add more targets, include additional data integration logic using PowerCenter mapplets (called “plug-ins”), and so on.

 

In addition you can also use Templates to standardize your data flow patterns. So Cloud Integration Templates offer data integration flow flexibility and at the same time provide a way to standardize your flows.

 

Let’s look at an example.

 

You want to synchronize your Sales data in Salesforce CRM with another system behind your organization’s firewall. Typically you would create Data Synchronization tasks using wizard user interface for each such object. Each task is created independently and the task list will look something like the screenshot below.

 

This works fine as long as your needs are all handled by the features available in the Data Synchronization wizard.

 

However, you may want to add more transformations to each task – for example, write the data to another target table/file, perform a standard lookup, or add some additional control fields to your targets. When you use independent Data Synchronization task you need to remember and configure each transformation identically in each.

 

This is where Cloud Integration Templates can be very useful – to define a “pattern” of data integration and apply it to several individual tasks (called Custom Integration tasks).

 

In this case you can create a generic Template that has a pattern which includes reading from a Salesforce object, performing a bunch of transformations and writing the data to a target CSV file.

 

Informatica Cloud includes a Microsoft Visio add-on tool called the “Cloud Integration Template Designer” that you can download from the Community site.  You can create a Template using this tool, as shown in the screenshot below.

 

Such a Template uses place holder objects and link rules to define a pattern. In this case we read from a source object, pass it through a filter (with a filter condition that could be either fixed in the template or parameterized and provided when we create the Custom Integration task), through a field mapping expression, and write to two targets – the main target for data and a control file where you can write some job related information for your reference.

 

None of these are actual database or application objects. When you create a Custom Integration task from this Template you provide the actual values for each parameter - specific objects for which you want to create tasks – Account, Contact, Lead, Opportunity and so on.

 

This way all of your tasks follow the same pattern. When you need to add a lookup or do some additional data transformations, you just edit the Template and add the new transformation logic. Then, in most cases, the tasks will be automatically updated with the new transformation logic in the Template. You can also have a set of Informatica Cloud REST API commands in a script that you can run each time a Template is modified to automatically generate all tasks again.

 

Cloud Integration Templates also allow you to define many types of transformations that are not available in a standard Data Synchronization or Data Replication task. Using Cloud Integration Templates you can create comprehensive data flows that have historically only been possible by using Informatica PowerCenter.

 

The example above illustrates a very simple use case. You can do much more using Cloud Integration Templates. And that’s not all! You can use the Informatica Cloud REST API to invoke all Templates from your external environment (Java, Apex etc.).

 

We’ll look at that in the next few posts!

Over the years we've seen incredible growth and participation on the Informatica Cloud Community. Thanks to all of our valuable contributors, especially Josh Vaughn, our User Experience guru who has recently become the most prolific contributor! Josh has become particularly active on our Ideas site, which is a forum we'd love to see more of our customers and partners join. It's a great way participate directly with our product management team and technical specialists. Post new ideas, vote ideas up and down and add your comments. One other point to note on the Ideas site - the Spring 2013 release included a number of features that came directly from the site. We love to hear from you all directly! (Check out some of the delivered features here.)

 

Speaking of growth, this week we reached a milestone with our multi-tenant cloud integration service - we processed 2 BILLION transactions in a day. This represents a doubling of the 1 BILLION milestone reached less than a year ago. You can check out the daily details on our Trust site.

 

So what can you expect from this blog? Well, maybe I'll turn the question to the community...what would you like to see?

 

Some ideas we have include:

 

  • Having customers and partners post their best practices, tips and tricks, etc.
  • Having the PM team providing regular updates on what's new and what's coming.
  • Having our technical specialist team post useful resources and link to new Marketplace blocks and Discussions/Documents.
  • Updates on upcoming events, webinars and other useful materials for customers and partners.
  • Success stories
  • And lots of other possibilities...


We hope you find this to be a valuable resource. We look forward to increased collaboration and communication about Informatica Cloud.