Data Integration into Salesforce.Com Using INFORMATICA 8.1.1

Data Integration into Salesforce.Com Using INFORMATICA 8.1.1

Author’s name

CHANDAN PAUL

SIMRAN DHAWAN

Table of Contents

1.Introduction

1.1.Purpose

1.2.Intended Audience

2.Overview on Salesforce.com & Connector

3.Mapping design with Salesforce.Com

4.Sessions & workflows Salesforce.Com

5.Limitations with Salesforce.Com

6.Advantages with Salesforce.Com

1.Introduction

1.1.Purpose

The purpose of this document is to illustrate how to use Informatica 8.1.1 Connector for SalesForce.com sources and targets. Informatica now provides data integration from disparate legacy applications to SFDC. Informatica On-Demand, an innovative Software-as-a-Service offering to enable cross-enterprise data integration. Here SFDC data source & Informatica is hosted in an integration platform. Then application is outsourced and can be accessible with credentials given.

1.2.Intended Audience

This document is intended for architects, designers, programmers, testers and technical managers in the Business Intelligence and Data Warehouse domain. It is also useful to business analysts, solution designers in CRM domain the Wipro Technologies BIDW ETL team works on CRM applications and intends to learn and use in data integration to Salesforce.Com projects. This document contains the technical details on how to source and store data in Salesforce.com using Informatica.

This document has details on mapping design, importing sources & targets, SOQL filter condition, session & workflow configuration. This document gives overview on mapping, sessions and workflow creation and associated configuration and limitations. This will help the developers to understand code implementation point of view.

2.Overview on Salesforce.com & Connector

Over the past few years, on-demand customer relationship management (CRM) applications went from being the choice of the most forward-thinking organizations to the corporate standard. Now that businesses have embraced the Internet model for CRM, they are ready to bring the benefits of on-demand to the rest of the enterprise. Note that SalesForce connector will be only available with the licensed version of Informatica 8.1.1. Connector will enable data exchange through objects that will leverage SalesForce.com application server capabilities.

Salesforce Platform Edition makes it easy for companies to extend on-demand applications to new departments that don’t need Salesforce.com’s CRM applications. This new platform license makes your custom applications and third-party applications from the AppExchange available to everyone in your organization. Following are some of the tools available, provided by SalesForce dot com to view data required by developers, senior management for generating strategic reports.

2.1 AppExchange: This will be used to view the data model, access data helps running query in the database.

2.2 Custom Apps: Your own unique apps—built on the business user and developer-friendly Apex platform—supporting the precise requirements of your unique business.

2.3 Beyond CRM: Dozens of on-demand business apps available in the AppExchange for everything from recruiting to project management to compliance and much more.

2.4 Analytics: The same powerful reporting, dashboard, and analytic capabilities of the full Salesforce CRM application.

2.5 Complete Platform: Backed by the full power of the world’s first on-demand platform, Apex.

3.Mapping design with Salesforce.Com

Informatica provides a very stable and an easy-to-use data integration solutions with the help of which, Informatica developers will be able to build simple and complex mappings in short time frame.

PowerCenter allows you to migrate data from various data sources to Salesforce.com, integrate data from diverse data sources with Salesforce.com, and use the built-in data quality and data cleansing functionalities of PowerCenter to analyze, evaluate, and cleanse Salesforce data or vice-versa.

Given below are the steps to follow while developing mappings for which Salesforce.com is the source or target. Before starting the development it’s a good procedure to follow a checklist so as to know whether the dependencies (if any) are all addressed.

**Follow the instructions below

  • Copy this file to "Informatica Client Installation directory / bin".
  • Modify the file to reflect your proxy server settings.
  • Restart the Designer.

STEPS TO BE FOLLOWED: -

The steps to be followed while creating a mapping in Informatica are as follows:

  1. Importing the source and target definitions from SFDC:-
  2. Login into SFDC folder in Informatica Designer.
  3. For sources go to Source Analyzer Click on Sources and then on Import from Salesforce.com. Similarly for targets go to Target designer Click on Targets and then on Import from Salesforce.com
  4. Type in the login credentials in the dialogue box which include User Name, Password and Service URL and click on Connect.
  5. Select the objects that you want import as sources or targets.

[All the entities/tables are referred to as Objects in Salesforce.com]

  1. Using Application Source Qualifier transformation in the mapping [For Reading Data from SFDC]
  • Application Source Qualifier transformation is used in the mapping to communicate to the integration service about how to read the data to be extracted from Salesforce source. For multiple usages of Salesforce sources you must have that many no. of application source qualifier transformations.
  • On reading data from the SFDC source the integration service converts the data from its native data type to the transformation datatype and the sane happens when the data is written to the targets.

Limitations with application source qualifier:

  • Unlike in the source qualifier transformation we cannot specify a query override at the mapping level whereas it can be done at the workflow level by using a SOQL query override.

  1. Writing data into Salesforce.com
  • There are four update methods in which data can be written into the target, which are INSERT, UPDATE, DELETE and UPSERT.
  • Insert, Update, Delete being all straight forward, the new functionality called Upsert (Update else Insert) has been introduced. It works on the External ID field which is defined at the salesforce.com end i.e. if the incoming row has an existing value for the External ID field then salesforce.com will treat the row as an update else as an insert. External ID can be defined on any of the columns[Depends on the requirement].
  • However all the fields in the target object cannot be inserted or updated as there are read only system level fields also, for e.g. ‘Id’ which is a unique ID that SFDC generates each time a record in inserted into the SalesForce database. Similarly there are other fields also.
  • The target fields in SFDC can be classified into three types which are Creatable, Updatable and External ID for each of them a binary number value is specified as shown in the diagram below. Only one customized field can be defined as an External ID field at the Salesforce.com end.

[‘0’, if the functionality for the field is disabled and ‘1’, if the functionality for field is enabled at the Salesforce.com end].

[The fields post fixed by ‘__C’ are customized fields in Salesforce.com]

4.Sessions & workflows Salesforce.Com

There are not many changes while creating the workflows and sessions for mappings which include Salesforce.com sources and targets.

1. Source as Salesforce.com objects:

  • There is an option to limit the data that is being read at the session level by using a SOQL filter condition which basically just overrides the WHERE part of the query and the rest is automatically generated by the integration service with the help of the source definitions.
  • In the example below we have created a workflow level variable $$LAST_RUN_DATE and the condition specified extracts all the records that were modified after the last session run.

2. Target as Salesforce.com objects:

  • The unique Upsert option defined at the SFDC target level can be configured in two ways, which are
  • Insert as Upsert
  • Update as Upsert
  • When you select the first option, the Integration Service Upsert any records flagged as insert. By default, all records will be treated as insert.
  • When you select the second option, the Integration Service Upsert any records flagged as update. Select this option only if you have flagged records as update using the Update Strategy transformation in the mapping or using the Treat Source Rows As session property.

5.Limitations with Salesforce.Com

These limitations are from a developer’s point of view.

  • The environment setup has to be done by an SFDC administrator.
  • No query override can be given at the application source qualifier level and has to be done at the session level with the help if a SOQL filter condition.
  • Filter condition should be according to the SOQL syntax and invalid entry will fail the session.
  • Only workflow level variables can be used as part of the SOQL filter condition.
  • There are limitations with the date functions that are specified as the SOQL filter condition.
  • External ID on a customized field has to be defined if Upsert functionality has to be achieved.
  • We have to make sure that we do not connect any of the read only SFDC system fields in the mapping.

6.Advantages with Salesforce.Com

These are advantages from an Informatica designer point of view.

  • The SFDC CRM system is very user friendly system as a result of which special hands on training for getting used to the UI would hardly take any time.
  • A 30 day trial period also exists that can be made use of to understand the system better.
  • The above saves a lot of time in training a person with SFDC knowledge
  • There’s nothing to be installed so the developers can just get started. It’s a no frills software to work with.
  • Quick in terms of performing complex integration tasks without any manual programming involved.
  • Availability of salesforce.com is not an issue as test results show that the platform is stable to feed data into it and to also extract data for processing.

Wipro TechnologiesPage 1 of 1311/13/2018