Microsoft Dynamics CRM Data Import
CRM MVP Ronald Lemmen returns as a guest blogger. You can read more from Ronald at his blog.
This post will dive deep into the possibilities for Data Import for Microsoft Dynamics CRM. Discussed will be the options which Microsoft gives for importing data in both the versions 3.0 as well as 4.0, which third party tools are available and what the possibilities are for creating your own custom build data import. Directly related to Data Import, is data cleansing. In this post I will discuss possible data cleansing as well.
What I won’t discuss in this post, is the most commonly used approach for getting data into CRM: Manual. While planning your data migration, don’t forget about the option to manually enter the data into the system. Depending on the amount of data, it might be cheaper to enter all data by hand. Also when you have too much data to import by hand, look at the rare data. Maybe some entities can be entered by hand. If you do want to bulk import data and will not go the manual route, then make sure you read on.
Disclaimer: All the information on Microsoft Dynamics CRM 4.0 is based on RC0 which means that it is still subject to change.
Microsoft Solutions
Microsoft offers multiple options to import data into your CRM implementation. These options are:
- Bulk Import Wizard
- Data Migration Framework / Manager
- Data Migration Wizard for Microsoft Office Outlook with Business Contact Manager
- Outlook Client
Bulk Import Wizard
The Bulk Import Wizard is available from the User Interface as you can see on the image below.
This feature allows the end user to import the following entities:
- Contact
- Account
- Leads
- Campaign Responses
The end user can upload csv (comma-delimited values) files or text files delimited with semicolons, colons or tabs. Data to be imported can only be of one specific entity, although lookup data can be uploaded as well when the guid is specified.
Examples for when to use the Bulk Import Wizard are:
- Exports from Outlook to a .csv file
- Acquired marketing lists
Changes with Microsoft Dynamics CRM 4.0
The Bulk Import Wizard has been improved in Microsoft Dynamics CRM 4.0. The list of entities to import has been extended with almost all system entities as well as custom entities. Also, the mappings created between the source data and the CRM system can now be saved and reused in subsequent import activities. Another new feature is the ability to see the list of imported records after the import has succeeded. Based on this list actions can be executed like Quick Campaigns and even deleting the records if necessary. The last major improvement is the ability to enrich data which is already in the system.
Data Migration Framework
The second tool which Microsoft offers is the Data Migration Framework. This tool can be used to import data if any of the following scenarios do apply:
- Migrate data from multiple sources
- There are records which are related to others within the same or another entity
- Notes and / or attachments need to be migrated
- Data needs to be cleansed before importing into the CRM system
The image below describes the approach of the Data Migration Framework. The first step is to create and initialize the CDF database. This is a database which will temporarily store the data from the source systems before it is imported into the CRM system. The source data can be extracted from multiple sources and be loaded into the CDF database. The process of moving data from the source to the CDF database is not part of the Data Migration Framework. The most common approach is using DTS or SSIS, but basically any technique can be used to import data into the CDF database. Once the data is in this temporary database, then the data can be transformed and eventually cleansed. After this phase, the data can be migrated into the CRM System.
This application does support the following out of the box entities:
|
Account |
Discount |
Note |
Product |
|
Address |
Discount List |
Opportunity |
Quote |
|
Annotation (including attachments) |
E-mail |
Opportunity Product |
Quote Product |
|
Case |
Fax |
Order |
Sales Literature |
|
Competitor |
Invoice |
Order Product |
Subject |
|
Contact |
Invoice Product |
Phone Call |
Task |
|
Contract |
Lead |
Price List |
Unit |
|
Contract Line |
Letter |
Price List Item |
Unit Group |
All out of the box and custom attributes can be imported, with some exceptions like the created on/by and modified on/by attributes.
Data Migration Wizard for Microsoft Office Outlook with Business Contact Manager
This is an application is part of the Data Migration Framework as well. This is especially designed for migrating data from Outlook with Business Contact Manager to Microsoft CRM 3.0. Since no other source data can be migrated with this tool, I will not further discuss this tool.
Changes with Microsoft Dynamics CRM 4.0
The new version of Microsoft Dynamics CRM does also offer a new version of the Data Migration Framework. Not only is the tool renamed to Data Migration Manager, also the functionality has changed. The tool now has a user friendly interface next to the command line tools and also custom entities can now be imported. Furthermore new functionality like the data maps and duplicate detection can be used while importing the data through the Data Migration Framework.
Look at the blog of Philip Richardson [1] to see a demo of how to use the Data Migration Manager in version 4.0.
Outlook Client
A very easily overlooked option is to use the Outlook Client. There is a synchronization tool build in the Outlook client which does copy the data from Outlook to the CRM server. This approach is not very scalable, but could be enough for small data imports of contact data. Add-ons like the CardScan solution do use this approach to load data into Microsoft Dynamics CRM.
3rd Party Migration Tools
If the Microsoft Solutions do not offer you the required functionality, then you can look at third party applications. There are multiple tools available. The most common ones are the ImportManager as sold by CRM Extensions and c360 and the Scribe Insight application. Both will be discussed here. Other tools can be found in the links section [2]. In the same links section, you will also find references to the comparison between ImportManager and Scribe Insight as tested by Sandor Schellenberg [3].
CRM Extensions / c360: ImportManager
Both CRM Extensions as well as c360 offer the ImportManager with identical functionality. This application allows imports from one or more data sources, create new or update existing records of a given entity in the CRM database. This could be any customizable entity or a custom entity. Also in the same process relationships between the imported record and other entities can be created. While importing data, duplicate detection rules can be applied to determine if a record is new or should be updated.
There are connectors created for the data sources ODBC and text files. Other connectors can easily be created by a developer to suit the needs of the customer. This application also offers a scheduling option as well which makes it very usable for repeating imports as well.
With the release of CRM 4 new functionalities will be released as well. The ImportManager will then be a full Client / Server application which allow this application to be used for CRM Live and Hosted environments as well.
Scribe: Scribe Insight
Scribe Insight is an application which exists already for a long time. It offers the same functionality as ImportManager and more. This is a CRM migration application with connectors to many different applications including Salesforce.com, ACT, GoldMine and of course Microsoft Dynamics CRM.
One of the reasons why this application is widely used, is the possibility to create two-way migration functionality. Data can be imported in Microsoft Dynamics CRM and can also be exported to be used in any other application. Another reason to choose this application can be because of the wide range of possibilities in modifying data. Scribe offers so called functions in which data manipulation can take place. Within these functions the developer has many possibilities to calculate or create the correct value for a specific attribute.
Scribe does offer training to quickly learn how to use this application. This training is strongly recommended because of the many possibilities with this application.
Scribe will release the adapter for the newest version about two weeks after the RTM release of Microsoft Dynamics CRM 4.0.
Custom Migration Application
The latest option is to create a migration application in house. Advantages of creating your own migration application include:
- Full control of the application
- Ability to adjust performance settings based on data
- Relatively easy to include exceptional business logic
The obvious disadvantage is of course that knowledge about the CRM web services is required and also knowledge about other techniques required to perform the data migration. Some possible approaches are discussed in this chapter.
Web Services
The most obvious approach is to create a .NET application which uses the CRM web services to import data into CRM. The SDK [4] is a very good starting point. Basically the developer would read data from the source application, create an instance of the entity to be created, fill the attributes with the correct values (could be calculated or looked up in another system), add the record to the CRM system. If needed a check can be performed to see if the record is already in the system.
This approach does allow a lot of possibilities for performance improvement. If data needs to be requested from another source, it could be all loaded while the application loads instead of a per record request. Also the connection with the Microsoft Dynamics CRM web service allows several performance settings. Make sure to look at the settings for UnsafeAuthenticatedConnectionSharing. A link to the blog of Aaron Elder is included[5] with more information about performance settings for the web service.
SSIS – Web Services
If you like SQL solutions more, then you can leverage a new feature which is available if you’re database is running on MSSQL Server 2005. This version includes the possibility to create SSIS packages which can communicate with web services. How to use the web services task in SSIS is described on Technet[6].
BizTalk – CRM Adapter
Another possible approach is to connect CRM with BizTalk. This is very valuable if BizTalk is already used within the organization. Microsoft has developed a BizTalk adapter for Microsoft Dynamics CRM 3.0[7]. This adapter allow you to perform the web service calls directly from within BizTalk. The advantage of this approach is that you can create this solution with minimal knowledge of the CRM application. A disadvantage though is that it is very hard to optimize the performance when using the BizTalk Adapter.
Data Quality
A topic which is directly related to Data Import, is the quality of the data which is imported. The quality is one of the most crucial success factors of any CRM project. A lot of checks can be performed while importing data, especially checking for duplicates. Microsoft Dynamics CRM 3.0 does not offer any out of the box tools for duplicate detection, but there are some good alternatives. Also some of the third party tools for importing data do include duplicate detection functionality.
3rd Party Tools
The most known third party add-on for data quality is the c360 Data Quality Center add-on. The core functionality of this add-on is duplicate validation and address validation. The address validation verifies the address using US Postal Code standards. The address are verified, corrected and enhanced by this engine. The duplicate detection software does check for duplicates against the entities Lead / Account and Lead / Contact. Duplicates can be detected while saving a record or can be scheduled for a nightly batch. Detection of a duplicate is done by using match code rules which can be defined by administrators. The Data Quality Center is part of the c360 Core Productivity Pack[8].
Another supplier of a Data Quality add-on for Microsoft Dynamics CRM 3.0 is Pitney Bowes. They have developed a similar add-on which also does address verification and duplicate management. [9]
Microsoft Dynamics CRM 4.0
With the release of Microsoft Dynamics CRM 4.0, Microsoft is making a large step in terms of data quality. Instead of relying on a third party add-on, the duplicate checking is now included within the software. The functionality which Microsoft has included in the product is very similar to the duplicate checking as supplied by c360. It does allow the administrator to set up rules and based on these rules the system will recognize a potential duplicate and inform the user. Also batches can be run which do show a list of potential duplicate records.
Philip Richardson has created a short screen cast which does show the duplicate detection like an end user[10].
Conclusion
In this article we have seen that there are many different approaches and applications available for performing a data import into Microsoft Dynamics CRM. For each different business case another approach or application is the correct one. When the 3.0 version of the CRM product is used within your organization, then a third party application is quickly needed. The release of Microsoft Dynamics CRM 4.0 will allow a lot more out of the box, so for relatively simple data migrations can be easily done without third party software or a custom build application. If the migration is larger though, or specific requirements need to be fulfilled, then you will still need to rely on a third party solution or create your own solution.
Links for more information
[1] Demonstration of how to use the Data Migration Manager in Microsoft Dynamics CRM 4.0 http://www.philiprichardson.org/screencasts/titan/DataMigration/DataMigration.html
[2] URL’s for 3rd party import applications:
– Scribe Insight http://www.scribesoftware.com/microsoft-dynamics-crm.asp
– c360 ImportManager http://www.c360.com/ImportManager.aspx
– CRM Extensions ImportManager http://www.crmextensions.com/Page.aspx?id=66673538-2ae2-db11-9607-0003ff3b5e8e
– Inogic Import and Conversion Tools http://www.inogic.com/dynamics_crm_solutions.htm
– Vertical Marketing Inc. Goldmine to Microsoft CRM Convertor http://crmsuperchargers.com/GMtoMSConverter.htm
[3] Comparison between Scribe Insight and ImportManager by Sandor Schellenberg
– Part 1: http://www.friendlycrmonster.com/2007/03/scribe-insight-vs-c360-import-manager.html
– Part 2: http://www.friendlycrmonster.com/2007/03/scribe-insight-vs-c360-import-manager_27.html
– Part 3: http://www.friendlycrmonster.com/2007/04/scribe-insight-vs-c360-import-manager.html
– Conclusion: http://www.friendlycrmonster.com/2007/04/and-winner-is.html
[4] Microsoft Dynamics CRM 3.0 SDK http://www.microsoft.com/downloads/details.aspx?familyid=9C178B68-3A06-4898-BC83-BD14B74308C5&displaylang=en
[5] CRM Web service performance improvement by Aaron Elder http://www.invokesystems.com/cs/blogs/mscrm/archive/2006/06/01/28.aspx
[6] How to use the web services task within SSIS http://technet.microsoft.com/en-us/library/ms140114.aspx
[7] BizTalk – Microsoft Dynamics CRM 3.0 Adapter http://www.microsoft.com/downloads/details.aspx?FamilyID=4628FCA6-388D-45BC-A154-453B920DBCB8&displaylang=en
[8] c360 Data Quality Center http://www.c360.com/DataQualityCenter.aspx
[9] Pitney Bowes Group 1 Software Data Quality Adapter http://www.g1.com/Products/Customer-Data-Quality/CDQ-Platform-For-MS-CRM/
[10] Screen cast on Duplicate Detection in Microsoft Dynamics CRM 4.0 http://www.philiprichardson.org/screencasts/titan/DuplicateDetectionCTP3/DuplicateDetectionCTP3.html
