CRM, CRM features, Data integration

Part 3: Which Data Integration Tool should I use?

Recap

In my previous blog I introduced the following topics:

  • Supported and unsupported data import methods
  • Identified two categories for data integration tools:
    • Free
    • Cost-associated tools
  • Examples of the data integration tools available to you

Data Integration Tools

I have previously stated that not each data integration tool can be used for each data integration type. Why is this you ask? To differentiate between the tools I will give a short summary of each tool I have listed previously and highlight which Integration Types the tool can be used for.

Free Tools

Data Import Wizard

The data import can only be used for standard Data Migrations from a single comma-separated values (.csv), XML Spreadsheet 2003 (.xmlss), or text (.txt) source file into CRM. So any data migrations where a legacy system is involved – if you cannot export the data from the source system into one of the above files formats,then this tool will not meet your requirements.

This tool can:

  • Create Data maps which can be reused
  • Can map to parent records using one field only (unique value needed)
  • Can utilise the out of the box duplicate detection rules

The tool has the following additional limitations:

  • Cannot import at a scheduled time
  • Manual executed import
  • Has a row/file size limit in one import operation

Configuration Migration Utility

The data import is generally used to move records from a single source CRM to a target CRM organisation; this is mainly used for moving definition/configuration records where the GUIDS are required to be intact/identical across the source and target CRM organisations. In practice – it is not used as a full on data migration tool (although it could but very tricky and not designed/advised to be used as such) but is used in solution deployments where these records are used in the functionality/solution(s) being deployed.

This tool can:

  • Import Dynamics CRM record data with relationships (assuming parental records are the identical across both systems)
  • Export Dynamics CRM record data with relationships (assuming parental records are the identical across both systems) to keep as a backup or use if restoring an Organisation

The tool has the following additional limitations:

  • Cannot import at a scheduled time
  • No filtering criteria to exclude records – it takes all records for that particular entity and either all or selected attributes
  • Manual executed import
  • Microsoft Dynamics CRM to Microsoft Dynamics CRM only

XrmToolbox

Manage N:N Relationships

The default out of the box data import wizard can only import records directly and populate N:1 relationships; it cannot handle N:N relationships. The ‘Manage N:N relationships’ plugin which can be installed into the XrmToolbox; this tool can only be used to create associations between two existing records and requires 2 unique values to match to the records (this does not have to be the ID’s). Only .txt or .csv source files may be used and the records must exist in two columns (the tool allows you to choose a separator). Normally, the general use of this plugin is in conjunction with the data import wizard when uploading data from source spreadsheets – and can only be used for Data Migrations.

This tool can:

  • Import Dynamics CRM record associations over N:N relationships (assuming unique values are provided for both associating records)
  • Can do deletions (disassociations’) in bulk – useful for fixing data on an ad hoc basis.
  • Tells you which data rows fail

The tool has the following additional limitations:

  • Cannot import at a scheduled time
  • Can only perform associations
  • Manual executed import
  • Manually create the source file

MSCRM Toolkit

I actually made an error and got slightly confused when referencing this tool – I incorrectly referenced the Data Transporter which is apart of the XrmToolbox – when in actual fact the tool is called (which is apart of the MSCRM Toolkit):

Reference Data Transporter

The Configuration Migration Utility allows users to migrate configuration records between CRM organisations, the Reference Data Transporter goes one step further and allows your to filter the configuration records from CRM and transfer them to your target organisation.

It also allows the records to be synchronised at a set time (i.e. to keep the records in sync); this tool can be used in both Data Migration and Data Integration scenarios. This Tool could additionally be used in basic Data Replication across two Dynamics CRM systems due to its scheduling and filtering (i.e. by last modified on) to update recently changed data.

This tool can:

  • Import Dynamics CRM record data with relationships (assuming parental records are the identical across both systems)
  • Export Dynamics CRM record data with relationships (assuming parental records are the identical across both systems) to keep as a backup or use if restoring an Organisation
  • Synchronise the Dynamics CRM record data at set times across Dynamics CRM Organisations

The tool has the following additional limitations:

  • Microsoft Dynamics CRM to Microsoft Dynamics CRM only

Connector for Microsoft Dynamics

Microsoft provided a free (with valid software assurance) integration tool that would allow a custom integration between Dynamics CRM and one of the Dynamics ERP software systems (such as AX/GP/NAV/SL). The Connector provides an effective integration of Sales Order process originating in Dynamics CRM all the way through to Sales Invoicing in the ERP system.

The Connector for Microsoft Dynamics can be used in both Data Migration and Data integration Scenarios due to its two-way Connectors. Unfortunately – Dynamics CRM 2016 will be the last major Dynamics CRM version the ‘Connector for Microsoft Dynamics’ will Microsoft will support (announcement here) – this is most likely due to the recent announcement of Dynamics 365 and the interoperability framework  of Dynamics NAV 2016+ (i.e. integration with Dynamics CRM will be out of the box).

There was a Dynamics CRM to Dynamics CRM Connector that allows for Integrations between CRM organisations to take place i.e. to keep data in sync. This Tool could be used in basic Data Replication across two Dynamics CRM systems due to its scheduling and filtering (i.e. by last modified on) to update recently changed data.

This tool can:

  • Transfer data to both source and target systems in a unidirectional or bidirectional data integration
  • Can be scheduled to run at set times or on an ad hoc basis
  • Basic Error Level reporting and warning notifications
  • Easy to use interface

The tool has the following additional limitations:

  • Can only transfer records flagged for integration (requires record flagging initially)
  • Can only be used to transfer data between Dynamics CRM and the Dynamics ERP suite (out of the box).

Cost Associated Tools

Third Party Tools

Now I can go on and on about the next two tools – but I will keep it brief; the functionality will be roughly similar across each product and the same could also be said for the other third-party products I have already mentioned as functionality will be similar (except the location of the software).

Scribe Insight

Scribe Insight is the most recognised (and most used from what I can tell) data integration platform out there in the Dynamics community (including AX, NAV, GP etc.); so much so that Microsoft used it to perform their internal migration from CRM 2011 to CRM Online.

This tool can perform Data Migrations and Integrations to integrate/migrate multiple source and multiple target systems at the same time.

Scribe has the following (example) list of data connectors:

  • txt/csv/excel spreadsheets
  • ODBC Conncetions
  • SQL
  • MySQL
  • SalesForce
  • XML

Full list of connections for Scribe Insight here

Scribe Insight can perform Data Replication – but there are not really any default templates for this out of the box with Insight; it would not be hard to create a Data Replication process for the data you wish to replicate.

This tool can:

  • Transfer data to both source and target systems can perform omni-directional data migrations/integration/replications.
  • Can be scheduled to run at set times or on an ad hoc basis
  • Can be triggered by Events (i.e. manual or MSQ driven, file upload etc.)
  • Advanced Error Level reporting and warning notification administration and avanced error handling with Microsoft Message Queues.
  • Easy to use interface
  • Advanced Training material and courses/blogs
  • Full Support with valid Software maintenance
  • Great community to assist with issues

The tool has the following additional limitations:

  • On premise installation and (ideally) requires SQL Database
  • Time to train puts potential users off; people view Scribe as needing to have a “developer” mind (which is not true)
  • Cost to purchase the licenses (you can buy 1 off migration licenses or full software licenses)
  • Connectivity is not as advanced as Scribe Online

Scribe Online

The younger sibling of Scribe Insight – Scribe Online; originally used to perform basic data integrations with which the functionality and features have grown so much over the last few years that it really is a viable alternatice data integration platform to Scribe Insight.

Firstly – it is a cloud based (subscription licensed) iPaas which is accessed via a web browser; it currently is built on a Silverlight UI but is (without breaking any NDA i might have signed up with Scribe over the last few months) being rebuilt to use an HTML 5 interface (known as Scribe v2 – and can only be a good thing).

Scribe Online has a multitude of connectors available to it – full list can be seen here which give (in my opinion) Scribe Online the edge over Scribe Insight, but it also has a CDK which allows ISV’s/Partners to build Connectors for their own products or services which immediately makes Scribe Online more attractive – especially to cloud services like Marketo or SalesForce (and of course Dynamics CRM Online).

Scribe Online advertises itself to being a data integration platform based in the cloud – but it also offers a server called “Replication Services” which allows you to configure source entities for replication to a SQL DB (either on premise or Azure hosted); this is a very powerful and popular product and many customers use this for local SQL reporting and SQL backups. Scribe also offer a PowerBI Solution which is built on top of RS.

This tool can:

  • Transfer data to both source and target systems can perform omni-directional data migrations/integration/replications.
  • Can be scheduled to run at set times or on an ad hoc basis
  • Advanced Error Level reporting and warning notification administration
  • Easy to use graphical interface and very easy to pick
  • Advanced FREE Training material and courses/blogs
  • Full Support with valid Subscripton
  • Great community to assist with issues
  • On Premise and cloud or cloud only platform
  • Advanced Connectivity with the possiblity of building your own Connector
  • API access

The tool has the following additional limitations:

  • Uncertaintity in the product – due to its short life and ofter overlooked by Insight users
  • Cost to purchase the subscription resctricts the number of connections you can run
  • Silverlight only (i.e. IE is needed!)

Web Services and Console Apps

Web services (from an integration perspective) are custom applications designed to allow two or more systems to talk to each other’s API and allow the transfer of data between them. They are bespoke applications that will need to be written by developers who have knowledge (either working or documented) of both API’s and are specifically designed to do one task.

Console Applications are small (and streamlined) programs that are written to carry out a specific tasks – in a data integration environment, they are usually used for simple or complex data processing tasks; these tasks are usually scheduled via Windows Task Scheduler to run out of hours or every hour or so to update data systems (i.e. performing complex calculations and updating the source data set)/

Both these tools are bespoke – so they will be designed and built to meet specific requirements.

Web services are more aimed at Data Integrations where data needs to transfer between one or more target systems to keep them synchonised; they could be used for Data migrations between heavily bespoke systems where third-party tools fall short of connectivity.

Console Applications are designed to perform data processing in an addition to a running data integration integrations.

Summarise

I have given some points towards the data integration tools available and the data integration types they can be used with with some positives and limitations; the tools are just a sample of all the available tools to use (i.e. additional third-party products or other free tools), I just cannot list them all or give some overview when I have not personally had some involvement with. I hope these blogs help you choose the right tool for the right type of data integration.

Next Blog

In my next blog, I will highlight the main pointers and best practices that I use when undertaking data integration projects.

Thanks for reading! MW

1 thought on “Part 3: Which Data Integration Tool should I use?”

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s