Follow Us:
Data Integration is a must have in today’s business environment. Competition has never been higher and it is imperative to gain new customers as well as hold on to existing customers. Costs continue to rise, shrinking margins of profit, making management of COGS more important than ever. Data integration between your silos of systems, whether they are best of breed business systems or home grown data collection systems, is an ever increasing requirement in order to squeeze every dollar out of your margins. But what is data integration?
Data integration is a widely used term that broadly defines the act of sharing data between systems that results in an increase value of that data, and in most cases the hope is that the whole is greater than the sum of its parts. Let’s break down the different aspects of data integration to get a better understanding of how it can be implemented and how it adds value to your data.
In very simple terms this can be seen as moving data from one place to another. This could mean an unsophisticated migration of customer leads that have been collected at a trade show and importing them into a CRM system. This typically requires little transformation of data during the migration process. However, for a company that is interested in maintaining a very clean data environment, it may include street address and email address validation as part of the migration process, as well as lead assignment logic to categorize the leads during the process. However, a much more robust solution could be engineered to automate the process so it can be used regardless where the leads originate. It could also incorporate a system for collection of the leads via a web page that can make the data available in real-time, resulting in reaching potential customers very shortly after they’ve made their contact information available.
Another view of data migration could be moving larger amounts of data, from a cloud-based application, to an on premise database in order to make that data available for analysis that could not be accomplished with the tools of the cloud application. Additionally an aspect is perhaps moving on premise data from an application database to a data mart/data warehouse where analysis cubes are built to report marketing customer life cycle information. Operations such as these are not one-time migrations, but on-going migrations with well thought out transformation logic built in to the process.
Having shown a few examples of data migration, I’m sure to you can see the multiple ways and scenarios that data migration can and should be done to add value to your existing data silos.
It’s a bits semantical to describe integration as being different than migration, but for the purpose of this discuss, I believe it helps to make a distinction of sorts. While migration is taking data that is unconnected to a main system and moving it into that main system(s), integration is more like having multiple main systems and sharing data between them. Depending on the problem being solved for, an integration can be one-way or two-way, time-based or ‘real-time’. For example, a typical integration might be comprised of a CRM system integrating its data with a back office ERP system. This might be automatically moving accounts from the CRM system to the ERP system when those accounts become customers. It may also include moving sales orders taken in the CRM system and moving those into the ERP system for processing. Changes to account records and sales order records are synchronized between both systems, eventually having invoice records being integrated to the CRM system, giving the sales rep a 360o view of the customer without relying on the finance department to constantly supply them with up to date customer information.
On-going integration of transactional data moving to a data warehouse can also be considered as data being integrated between established business systems required for efficient and effective business operations.
Now that we’ve set the table by defining a few data integration scenarios, let’s review some of the many tools available for building effective integration solutions. We will be focusing our discussion, at this point, on tools used for integration with Microsoft Dynamics CRM and Salesforce, although the principles, if not the specifics, can be applied to many systems and many scenarios.
ETL (Extract, Transform, Load)
The widely used acronym, ETL is synonymous with data integration and is defined (currently) on Wikipedia as:
Extracts data from homogeneous (similar, i.e. all SQL Server databases) or heterogeneous (not similar, i.e. databases, text, XML, APIs) data sources.
Transforms the data for storing it in the proper format or structure for the purposes of querying and analysis.
Loads it into the final target.
As a very loose definition, the tools we will look at could be defined as ETL tools, however, purists may not consider some of these tools in the same category. Often when speaking of ETL tools, the conversation centers around tools that cost in the hundreds of thousands of dollars and are typically considered ‘enterprise’ level. However, there are several tools available, some even free, that can accomplish ETL operations that can meet modest to very complex requirements.
Salesforce offers two data migration tools that are a part of the Salesforce eco system:
Data Import Wizard – located under Setup/Data Management within the Salesforce application, this tool requires that the user format their data in a very specific way in order to migrate the data into the salesforce application. It is also limited to migrating Accounts and Contacts, Leads, Solutions and Campaign Member Status (as well as some custom objects). It is also limited to 50,000 records per operation.
Data Loader – is a downloadable tool that runs on the desktop. It is a free download from Salesforce and can perform Inserts, Updates, Upserts, Deletes and Exports of data. The Data Loader utilizes an API connection to Salesforce which requires a Salesforce login, password and security token. It also can be configured for use of the Bulk API which can tremendously improve performance for large record sets.
Dynamics CRM offers two migration tools that are part of the Dynamics CRM eco system:
Data Import Wizard – located under Settings/Data Management within the Dynamics CRM application, the data must be formatted in a very specific way to migrate the data into the Dynamics CRM application. A variety of file types can be used as the source of data.
Life Cycle Services Data Loader – is a web based tool that requires a separate login from the Dynamics CRM application. It provides additional flexibility outside of what is offered with the Data Import Wizard and allows for a variety of file types as the source of data. You must be a CRM administrator in order to login to the Data Loader. For large data sets, this tool shows much better performance than the Data Import Wizard.
Microsoft SQL Server Integration Services (SSIS) is a component of SQL Server and is accessed through the SQL Server Management Studio. SSIS provides the ability to create connections to data sources using most, if not all, industry standard technologies (text, ODBC, Native, etc.). It also provides for accessing API’s but the process may not be as straight forward as hoped. If creating a connection to Dynamics CRM online, a few extra steps need to be accomplished to gain access. It is not plug and play.
Performance of SSIS integration packages can be very good and the ‘packages’ (or the collection of all the created processes, including connections) can be saved off-line and are portable to other environments hosting SQL Server. The user interface is somewhat intuitive but is not necessarily intuitive enough to navigate without some training. Training could be self-taught through on-line tutorials, or paid engagements. Some coding may be required for certain requirement solutions.
Microsoft offers a free integration solution for Dynamics CRM and the ERP back-offices in the Dynamics line of products, including Dynamics GP, Dynamics AX and Dynamics NAV. The connector solutions are downloadable from the Dynamics ERP applications associated web sites. These are prebuilt configuration entities and mappings between the typical entities of a front-office and back-office integration (Account, Contacts, Opportunities, etc.)
It is designed as a single purpose integration solution that does not included functionality for migration or integration with other data stores or applications. Also, not every version of the Dynamics line is supported, so it would be imperative to know if you current application stack meets the minimum requirements, and, if there would be no need for additional migration and integration requirements from other data stores, before selecting this as your chosen solution.
The greater goal of this offering was to give small business a more affordable integration option, and a free download does meet that minimum requirement.
Scribe Software has been around since the mid 1990’s and has found great success growing the company around migration and integration with Microsoft products. Although, the tool is essentially agnostic toward both the source of data and the target of the data translations. Scribe provides data connections using most (if not all) industry standard technologies, but has the added component of application specific ‘adapters’. These adapters provide access to the supported application through that application’s API and accompanying metadata. End users then see all the objects and entities of the supported application in a no coding environment. Powerful migrations and integration can be built for one-time or on-going data integration, without having to write a single line of code.
Scribe offers two separate tools for building data integrations, Scribe Insight, which is a client side server application, and Scribe Online, which is a cloud based service. Scribe also offers ‘templates’, which are fully developed integration solutions for integration with Dynamics CRM or Salesforce and a number of the Microsoft Dynamics line of back-office ERP application. They also offer a number of other templates for specific migration scenarios. The templates can be used out-of-the-box or easily customized and extended for specific use cases.
Jitterbit has been around for about 10 years and has seen substantial growth in the integration market. Initially, Jitterbit tied its wagon to Salesforce and made market headway doing so. Jitterbit still has a very strong relationship with Salesforce and offers a free ‘Data Loader’ for migration to the Salesforce application. However, they are not limited to integration with just Salesforce. Jitterbit boasts of the ability to connect to any data source or target using most (if not all) industry standard technologies, as well as the ability to make generic connections to both SOAP protocol and REST protocol web services.
Jitterbit is also a mostly no coding environment and offers a drag-and-drop design environment for building migration and integration scenarios. Jitterbit also offers ‘connectors’ for many business applications. These connectors provide access to the supported application through that application’s API and accompanying metadata. This makes the supported application’s objects and entities available without having to code a connection. Powerful migrations and integration can be built for one-time or on-going data integration, without having to write a single line of code.