Enterprise Software

Step-by-step: Integrating SalesForce.com with on-premises databases and applications


If you're considering or currently administering a SalesForce.com account, this article may help you explore how to integrate your CRM data between SalesForce.com and in-house databases, flat files, and legacy applications. Here's a deep look at how to get the job done without having to study SalesForce.com APIs and writing, debugging, and maintaining custom code.

Five essential steps

1. Explore your integration goals

Before any data migration starts, you should clarify the goals of the on-coming SalesForce.com integration. You need to know exactly what data should be extracted and what data tables/fields should be considered as targets. Also, ask yourself, "Do I need to integrate SalesForce.com with one single database or multiple data sources? Is it enough to perform a one-time migration, or do I need an ongoing synchronization? Do I need to have SalesForce.com data backed up? Do I have enough experience to do manual coding, or would the use of visual data integration tools be the best decision?"

Create a list of the questions related to your integration project and then try to stick to the answers.

In case you are still considering manual coding, don't forget about the time and effort required to learn APIs, provide connectivity to both the source and the target, write transformation logic, and, most importantly, the tasks related to debugging, reporting, and future maintenance of the integration and the related metadata.

The more clearly you set the goals, the more accurate your SalesForce.com integration will be. Sometimes you may need to join data; sometimes it's all about eliminating duplications; and sometimes the data should be validated or filtered first. Try to set your goals properly.

2. Provide connection to data sources

To start reading and writing data, you need to establish connections to the sources. In other words, you need to gain access to data tables, data structures, data types, and data records. This is where data integration actually begins. With visual tools like Apatar, for instance, you can do it without having to write a single line of a code. Just open the "drag-and-drop" job designer, choose the necessary data connectors, enter SalesForce.com authentication details, and provide the paths to the rest of the data sources.

3. Map data sources together

According to The Gartner Group, corporate developers spend approximately 65 percent of their effort building bridges between applications. Luckily, today's data integration systems allow data to be linked even by non-technical users. Imagine you could visually design (drag and drop) a workflow to exchange data between files (Microsoft Excel spreadsheets, CSV/TXT files), databases (such as MySQL, Microsoft SQL, Oracle), applications (Salesforce.com, SugarCRM), and the top Web 2.0 destinations (Flickr, RSS feeds, Amazon S3), all without coding. As an example of such visual tools, Apatar embeds a visual job designer to enable users to create integration jobs, link data between the source(s) and the target(s), and schedule one-time or recurring data transformations.

Now that you have all data destinations configured, map appropriate source and target connectors to start joining the tables, as shown in Figure A. Figure A

4. Match the fields After that, you need to set the rules for data transformations. To do so, open the Transform module and then drag-and-drop into its work panel all the corresponding input and output fields. Map these fields together, as shown in Figure B. Figure B

Why is the mapping so critical?

  • You need to point where the source of data is and where the target is.
  • Sometimes you need to transform source table formats to SalesForce.com table formats. For instance, you have Time or Binary objects at the source and need to save them as text at the target.

With mapping, you tell your data integration tool what you want to do and where exactly you want this to be done. Otherwise, the source information will not correspond with the target information.

Before the era of visual data integration tools, all these operations required at least one skilled, highly-paid database developer.

5. Run the transformation job

Finally, run the transformation, and let your SalesForce.com accounts be populated. If all initial settings and mappings were accomplished accurately, you may now just sit back and relax. The data integration tool will do the integration.

But if you think that the job is over, don't be so naïve.

The Process Automation

Business data is never consistent; real-time updates, new data entries, and other changes require maintenance. If you need recurring integration jobs, you may also use the Scheduling function to automate the data integration process. Apatar enables you to set the moments of data migrations, enter the frequency of necessary data transformations, and specify the duration of the integration. For example, you can set data migrations to launch at midnight when there's no data activity in the enterprise's databases. This will prevent data collisions as well as data inconsistencies. In the morning, you will have the entire set of customer data synchronized. See Figure C. Figure C

SalesForce.com data backups

To have your customer data backed up, you can also use the Amazon S3 "simple storage service," which provides a low-cost Web interface that lets you easily store and retrieve any amount of data, at any time, from anywhere on the Web. Amazon S3 deploys the same highly scalable, reliable, fast, and inexpensive data storage infrastructure that Amazon.com uses to run its own global network of websites.

For instance, if a company's executive wants to have his or her company's most significant customer information backed up every day (e.g., extracted into flat files and saved to Amazon S3), the Apatar tool allows for this data to be backed up and then uploaded to Web storage at a specified time. All you need to do is configure the Amazon S3 connector and enter the frequency and the moments of SalesForce.com data backups in Apatar's Scheduling module. The ETL engine will do the rest automatically. See Figure D. Figure D

Mashups with SalesForce.com

You may also want to mashup data from multiple sources. For instance, take news from an RSS feed, extract  client's information from legacy or in-house CRM, add your custom notes, and then mix it all up and throw it across your SalesForce.com accounts. Using Apatar, developers can create integration jobs, called DataMaps, to link data between multiple source(s) and target(s), as shown in Figure E. These data "mashups" can be saved for future re-use, or sharing, or even re-distribution. In other words, now you have all the integration settings saved and will not have to waste your time again and again if you want to perform a similar task or repeat exactly the same transformations. Figure E

Integration with SalesForce.com may be easier than you think. While Apatar open source ETL toolkit enables data migration and transformation, visual mapping provides even a non-programmer with all of the means to do complex integration jobs. The Scheduling feature allows for synchronization with your local databases and makes SalesForce.com integration ongoing. With today's data management solutions, the business user has a powerful toolset not only to manage data streams within the enterprise, but to join data with the Web, keep it safely in SalesForce.com, and exchange information with partners globally.

5 comments
processautomation2
processautomation2

Hi Nice post, I think process automation is the heart for modern day improvement in science and technology. Thanks for the step by step making the article understandable to all novice also.

greg.altonji
greg.altonji

Nice coverage of important considerations when designing an integration. I've noticed that most integration vendors tools are ETL centric, requiring data field mapping anytime a new field is added or modified. Because our implementation is very dynamic (ie, changes to schema are very common, to support the changing business needs), we chose a non-ETL based technology to sync our instance to a local database (SQL Server in our case, but supports all the major players and many not so major). Any schema changes made to sf are automatically created in the local database copy. Columns deleted in the "cloud" do NOT delete the corresponding column in the database, so we don't worry about doing something stupid. This approach allows us to create a local copy of our sf data in the background, without any maintenance required. We then use ETL and hand-coding tools (I'd prefer we get some decent ETL tools here, but it's not my call) to integrate this local "data cache" to any number of internal databases we run locally. We use Sesame Software's "Relational Junction for Salesforce" technology. Took me (I'm no DBA) 15 minutes to set it up and it's been running ever since (2 years now).

fernando
fernando

Great post...I love visual integration tools. I've also written extensively about integration with Salesforce.com on by blog http://labastida.com, including an article where I note a trend of using Salesforce.com as a de-facto "integration hub" in the small and medium enterprise.

CompHelpNJ
CompHelpNJ

While researching a means of collecting donations on my website, I had dismissed SalesForce.com as being too difficult to integrate to. Now I'm having second thoughts - perhaps I need to give this another look using Apatar.

bt
bt

Hi there, first of all nice post - easy to understand. If you are looking for an integration service that is configuration and no coding, why not check out RapidiOnline. (http://www.rapidionline.com). We make it easy to integrate with salesforce.com and i.e. MS Dynamics NAV....and other systems/databases. We just don't use the "spaghetti model" drag and drop as it would get too confusing for the user to manage all the different mappings and connections.