Dump your legacy CRM system for Salesforce.com using open source tools

Do you have a business application you’d drop like a hot rock if you could just get your data out? Fire up this open source drag-and-drop tool and get happy.

The truth is that most data-integration projects in today’s enterprises never get built. The return on investment (ROI) on these small projects is too low to justify bringing in expensive middleware. So, if you want to perform the migration at zero license costs, you will probably need an open source data integration or Extract, Transform and Load (ETL) solution, coupled with an open source database. This article will explore a common task: how to get your customer data into Salesforce.com. We’ll use Apatar Open Source ETL for visual data integration mapping and metadata management, and a MySQL database for staging the data for cleansing or enrichment.

Six Easy Steps to S-a-a-S CRM Migration

You’ve decided to move your CRM data from, say, GoldMine to the Salesforce.com on-demand CRM system. What steps should be taken to have your customer and enterprise data migrated to your new software-as-a-service package?

1. Ask Yourself Preintegration Questions

Before any data migration, ask yourself some questions to clarify the goals of the oncoming integration process. For data-integration specialists, it is critical to know:

1. What data tables, fields, and rows should be extracted and taken as a source?
2. Where do I want to put the extracted data entries?
3. Do I need to provide data migration from one single database or multiple data sources, perhaps using a staging database?
4. Do I need a one-time migration to SalesForce.com or recurring synchronization?
5. Do I have all of the required skills to do manual coding, or it is better to use visual data-integration tools?

If you still consider manual coding, take into an account the time and effort to study APIs, write connectivity to both the source and the target, write transformation logic and, most important, the tasks related to debugging, reporting, and future maintenance of the integration and the related metadata,

The more clear you make your goals, the more accurate your data integration will be, so set your goals properly.

2. Connect to Data Sources

To read the source data, you need to establish connections to source databases. You need to gain access to data tables, data structures and data entries. This is where data integration actually starts. With visual tools like Apatar, you can do it without having to write a single line of code. Just open the “drag-and-drop” job designer, choose the necessary data connectors, enter SalesForce.com and GoldMine (or other legacy CRM/ERP) authorization details, and provide the paths to the servers or storage files. The application is ready to operate with data.

Figure 1: Open the job designer to see the available data sources.

3. Set up a Staging MySQL Database

You may also want to mash-up data from multiple sources. Say, take news from an RSS feed, extract a client’s information from GoldMine CRM, add your custom notes, and then mix it all up and throw it across to your SalesForce.com account. Consider this step if you have multiple data targets from which to aggregate information, or if you have to apply complex data-cleansing or -enrichment rules to the data on its way between the source and the target. This step is optional, but sometimes it’s worth considering.

To connect to a MySQL database, use MySQL connector. The process of establishing the connection is very close to the instructions of the previous step. Just drag-and-drop the connector to Apatar’s work panel, enter database authorization details and provide the paths to MySQL server.

Figure 2: You can mash up data from multiple sources using joins and filters.

Figure 3: The MySQL connector prompts you for database information.

4. Match Data Sources

According to the Gartner Group, corporate developers spend about 65% of their effort building bridges between applications. Luckily, today data-integration systems allow data to be linked even by nontechnical users. Imagine if 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.

In Apatar, the visual job designer for integration is called DataMaps. It links data between the source(s) and the target(s), and can schedule one-time or recurring data transformations.

To use DataMaps, connect to the data destinations, and then match appropriate source fields from GoldMine and target fields from Salesforce.com to start populating the tables.

Why is the mapping so critical?

First, you need to point where the source of data is and where the target is. Second, sometimes you need to transform GoldMine table formats to SalesForce.com table formats. For instance, if you have Time or Binary objects at the source and need to save them as text at the target. Using mapping, you tell your data-integration tool what transformations you want to do and where exactly you want them to be done.

Figure 4: In the DataMaps screen, match the source field names to the appropriate destinations.

5. Generate Necessary IDs

Some tables in SalesForce.com database are related by means of key IDs. For example, an Account table, which contains information about a customer’s company, is related to a Contact table, which contains an individual’s name and phone number. SalesForce.com’s engine generates these IDs automatically. The problem occurs when a data-integration specialist wants to populate both tables together. To link the tables, he or she needs to know the key ID generated by SalesForce.com. Otherwise, the customer’s personal information will not correspond with the customer’s company information.

Let’s now explore how Apatar can solve this problem. To gain control over SalesForce.com IDs, you need to use two SalesForce.com connectors. Configure one connector for the SalesForce.com Account table, and another one for the SalesForce.com Contact table. Map all the nodes as shown in the picture below:

Figure 5: The first step in linking Account and Contact tables: create a transform and a join.

SalesForce.com IDs will be generated in the connector configured for the Account table, where the name of a client’s company is stored. To set the rules for this action, you need to open Transform module, drag-and-drop the input and output fields containing the client’s company names, and map these fields together.

Figure 6: The second step in linking Account and Contact tables: open the Transform module, and map the “company” and “name” fields.

This would allow you to have SalesForce.com generated IDs for the Account table, all based on the name of the client’s company. Now these IDs should be transferred into another connector, configured for the Contact table. For this purpose, Apatar uses the Join module with the join condition inside.

When a user enters the Join window, he drag-and-drops “ID” fields from SalesForce.com Account and Contact tables, maps these fields together, and sets the condition to link the tables.

Figure 7: Now open a Join window and drag to connect the “ID” fields in the two tables.

Here you need to specify the fields, which should be equal in both the SalesForce.com Account and GoldMine CONTACT1 tables. To provide a relation based on a customer’s company name, choose the “Name” field from  the “Account” table and “COMPANY” from the “CONTACT1” table.

Figure 8: Match the appropriate fields in the SalesForce.com Account table and the GoldMine CONTACT1 table.

After that, drag-and-drop all the input and output fields you want to transfer from GoldMine to SalesForce.com into Join’s work panel. Map the fields together.

That’s it. The migration is about to be done.

6. Let the Migration Flow

Now it’s time to 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 perform the migrations.

Reusable DataMaps

In many cases, you need to repeat the migration later, or redo it from the start. With Apatar, developers can create reusable DataMaps to store the data links between source(s) and target(s). These “mash-ups” can be saved for future reuse, or sharing, or even redistribution. In other words, you will have all the integration settings saved, and so you will not have to waste your time again and again if you want to perform a similar task or repeat the same transformations.

Schedule the Migrations

Business data is never consistent. Real-time updates, new data entries, all these changes require maintenance. If you need recurring integration jobs, you may also use the scheduling function to automate the data-integration process. Tools such as Apatar enable you to configure the scheduling feature and set the moments of data migrations. You enter the frequency of necessary data transformations and specify the duration. 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 entire customer data synchronized or backed up.

As you see, CRM migration to SalesForce.com doesn’t require too much effort. While the Apatar open source ETL toolkit enables data transformations, visual mapping provides even a nonprogrammer with all of the means to do complex integration jobs. The scheduling feature allows synchronizing with your local databases and allows SalesForce.com integration to be ongoing. Today, almost any developer or business user can control the process of CRM/ERP data migration from the beginning to the end.

Alex Khizhnyak is Apatar evangelist and co-founder of Belarus Java User Group. Since 1998, he has gained experience as an author, editor, Web writer, media specialist and blogger. So far, his education background combines IT, programming, economics and journalism. Alex works at Apatar Inc. and runs a blog on Open Source and Data Integration.