IBM InfoSphere Data integration with Salesforce

Salesforce is a leading on-demand customer relationship management (CRM) system. It offers a complete hosted solution for companies to manage their customer contacts, track sales orders, and streamline their sales processes. It also allows customers to tailor their software as a service, therefore gaining a competitive advantage.

IBM Information Server is an ideal solution to integrate and synchronize Salesforce CRM data with the business data stored within back end enterprise resource planning (ERP) systems and other data analytical applications.

Software products needed for designing ETL jobs for Salesforce.com

•IBM Information Server DataStage, which includes:
◦DataStage Client
◦DataStage Server
◦DataStage Metadata Repository
◦DataStage Domain Server
Information Server DataStage products can be installed separately on different hosts or installed on the same host.
•Information Server Pack for Salesforce.com (IS Salesforce Pack)
The IS Salesforce Pack is a companion product of the IBM Information Server. The pack has been developed to connect to Salesforce.com through Web services. The GUIs of the IS Salesforce Pack are installed on the DataStage Client. The runtime part of the pack is installed on the DataStage Server.
•Apache Axis
Apache Axis is an external component that is used to manage the transport layer of the IS Salesforce Pack. It contains libraries for the HTTP/HTTPS protocols

Apache Axis as external component

Architecture overview

Each software components in Figure 1 plays a different role in designing and executing the ETL jobs for Salesforce.com:

•The Information Server DataStage Client and IS Salesforce Pack GUI components provide a smart UI to allow users to design ETL jobs, import and export metadata, and set up data operations to be performed on Salesforce.com
•The Information Server DataStage Server and IS Salesforce Pack server components enable users to schedule and run the ETL jobs.
•The Information Server Domain Server manages Information Server user accounts and authorization.
•The Information Server Metadata Repository is a single repository database for storing and sharing metadata definitions.
The Information Server Salesforce Pack includes three major components:

•Salesforce metadata browser for browsing and importing the definitions of the Salesforce objects from Salesforce.com to DataStage.
•Salesforce Load Stage for loading data from non-Salesforce data sources to Salesforce.com. It is a Salesforce certified loading solution.
•Salesforce Extract Stage for extracting data from Salesforce.com to non-Salesforce data sources. It is a Salesforce certified extraction solution. Both Salesforce load and extract stages depend on the Salesforce.com Web service APIs.

Salesforce metadata browser

The pack has a smart metadata browser. It supports browsing both Salesforce objects and custom objects created by Salesforce users. It is capable of importing the selected object definitions from the Salesforce back to IBM Information Server for future usages. The metadata browser displays only the objects and fields that are applicable to the user-selected data operations. Below Figure shows the metadata browser for the query operation. The metadata browser shows only the Salesforce objects on which users can perform query operations.

Metadata browser and Upsertable objects in end-user's Salesforce account:

As shown in two Figure, different sets of objects, including custom objects, are displayed based on user-selected operations. This feature eliminates the guessing game for the end user regarding what operation is associated with which object and the reverse.

The metadata browser shows not only the Salesforce objects but also the field attributes related to those objects. It further simplifies the end user’s task at the DataStage job design phase. In the below Figure, the Opportunity object is selected with UPSERT operation. Only the fields in the object that are qualified for the UPSERT operation are displayed.

Opportunity fields with Upsert operation

In the below Figure, the same Opportunity object is selected with the Query operation. Compared to above Figure , a different set of fields is shown for the Opportunity object. Only the fields in the object that are qualified for the Query operation are visible.

Opportunity fields with Query Operation:

The metadata browser imports the Salesforce object and field definitions into DataStage. You first select the interested Salesforce object and its data fields, as shown in below Figure . Then you click Import to convert the selected Salesforce object definitions to the DataStage table definitions. The created table definition is shown in Figure 7. You can also save the table definitions to the DataStage repository for future usages.

Importing Salesforce metadata:

Saving the metadata definitions in Datastage: