A critical requirement of any successful Microsoft Dynamics CRM deployment is of course legacy data migration. In this article one of our MVP’s, John O’Donnell from Crowe Chizek talks about the challenges of data migration and data quality in Microsoft CRM. And yes, he will answer the question he has answered many times on the newsgroups, I have data in system x….how can I get it into Microsoft CRM?
In this article I will assume you are a consultant at a new client site and are in the process of getting ready to implement Microsoft Dynamics CRM 3.0. While you will be learning the clients business requirements I recommend you should also be thinking about their data migration needs in Microsoft CRM even in this early stage of the project.
A surprising Microsoft CRM newsgroup fact
The Microsoft CRM newsgroups have been around since 2003 and in all that time I have seen and responded to many questions along the lines of how can I get my old data into Microsoft CRM. However of more importance is this. I cannot recall anyone asking a single question about data quality. Does everyone simply assume the client is providing good data?
Determining the quality of a client’s data
If life were perfect, each new CRM deployment would have no legacy data to be migrated. Instead your users would start entering data from scratch when the CRM system goes live. Your new CRM system would be starting with no data at all and you would have the opportunity to control the new data users entered. Ideally you have put enough controls in place to ensure good quality data is captured. However in reality you are going to have to deal with some legacy data. Before you migrate a clients existing data into Microsoft CRM you have to determine the quality of the data but what does data quality mean?
Validated data such as Addresses
By this I mean data where names and addresses are spelt correctly. Where the users have actually entered each address rather than just Bob in San Francisco! Hopefully users have taken the time to enter phone numbers and email information as well. You should look at the previous system and see if any controls were put in place to make sure users entered all address information. If a system only required a first and last name, then in many cases that will be the only data entered.
Users of a CRM system will only enter the minimum information required.
Is the data you are about to import current? If an address was entered four years ago, is it still valid? People regularly change email addresses. Can you be sure this data is correct and up to date? Do you really want to populate a brand new Microsoft CRM system with data that is not even current?
Correct field information
Imagine that your client has been running some form of CRM system for 5 years. Every time a user entered a new contact they were required to populate a category field which was a drop down list of 20 values. Did anyone perform any checks to make sure they were entering the right values rather then just selecting the first value in the list because it’s faster?
When you speak to a new client and ask what they want to import from their legacy systems they will usually, or in most cases answer – all of it!! In reality, when your new system goes live you need to ensure the reports that come out of it represent valuable data and not the fact the bob talked to a customer for two minutes five years ago!
Imagine you have five year old data with activities like phone calls etc. One approach might be to add this activity detail to a Microsoft CRM Note entity. By doing this you still have the information. However you are not populating the new CRM system with data that is rarely needed. Only by talking to your client can you determine the best approach.
Involve the client in your data migration adventures!
When working at a new client site you should always make sure the client realizes the importance of getting the right legacy data into your Microsoft CRM system. Here are some points to consider
The client may not know the quality of his company’s legacy data
A client may tell you that their data is more or less perfect therefore it will only take a day or so to import. It is your job as a consultant to validate and prove to him that he is either right or wrong. Early in a project (ideally in the first week) you should document every data source and whether it needs to be fed into Microsoft CRM. At this point you hopefully will be able to take a look at the data and see if you can see any problems with it.
This process can also benefit your design of the new CRM system. When working with a client you should always try to demonstrate the proposed CRM system with some of their legacy data already loaded into it as soon as you can so they can envisage what their legacy data will look like in the system. This will also help them understand what the final implementation will look like. This also may trigger some memories such as oh we discovered that before three years ago users were not populating field x correctly etc.
The client may not realize the complexity of migrating their legacy data
Only you can know how long it will take to get data into Microsoft CRM. Therefore you need to gain an understanding of the clients data as quickly as possible so you can convince them that their requirements to import one million records from 10 cross referenced data sources may not be achievable in eight hours!
Data cleansing is expensive
If you determine that a client’s data needs cleansing make them aware immediately. The client needs to know about this in case it will incur additional project expense. Cleansing may mean using an outside company to check it or even validate it against a current address database such as one from the United States Postal Service.
Your client may not understand garbage in garbage out
In your clients eyes they have given you a data source which they have said is correct and validated. They want that data imported in and don’t ask any questions!
What do you think will happen when Microsoft CRM goes live and the client begins running reports and performing data extracts. At this point they will quickly realize that maybe their data was not perfect at all. Of course at this point they will also be looking around for their implementing consultant to work out why you did not point out their data is faulty!
As the consultant on the Microsoft CRM project it is your job to help your client validate their legacy data before importing it to Microsoft CRM. If the client tells you their legacy data is perfect you should either get them to sign a document saying that or explain you want to spent some time (at their expense) just checking their data. Again if the client will not pay for your time to check their data because it is perfect, get them to sign a document saying that they refused a data quality audit. Also, if a client realizes their data is not perfect they may need to scale back their data migration ambitions.
Do not be afraid to switch data sources!
At a client I worked at, the project specification stated that I would import data from a legacy Goldmine system into Microsoft CRM. While this would normally have worked I discovered that users had not been using the Goldmine system correctly therefore the data was less than perfect. At this point I requested a meeting with the client and said I was not happy about importing bad data from Goldmine into Microsoft CRM. After explaining and demonstrating my concerns we concluded that a better approach was to pull in contact data from their accounting system (which had high quality data) and then pull in matching notes and activities from Goldmine.
Communication is important for a successful data migration
At any new client site you need to find the people who can help you. In the previous case I needed to find the company goldmine expert who could help me quickly determine the level of data quality. If such a person is not available you are back to manually scanning the databases to get a look at existing data.
As a Microsoft Dynamics CRM consultant you want to deliver the best possible solution to your client. You want to make sure they are entering valid data moving forward. However in most cases you are going to have to migrate legacy data. You have the same responsibility to your client to ensure you can get the best value out of this old data. If that means the data needs to be analyzed / cleansed and manipulated then you need to communicate this to your client at the earliest opportunity. Who knows you may even find some gold lying in their old data when you start mining it… :o)
After all this you should now have fully cleansed data that both you and your client are happy with. Here are the options for importing the data.
Use the Microsoft CRM built in data import tools
In Microsoft Dynamics CRM 3.0 you have the option of importing data from a CSV file by going to Tools – Import etc. In this release of Microsoft CRM you can import the following record types.
You may think that this can only be used for small data sets but this is incorrect. I have used the tool to import 15,000 contacts with complete success. The tool also lets you link your imported contacts to existing accounts if you perform some additional setup. While the import tool works well there are some issues with things like none standard characters and also problems with importing data which has too few commas in a CSV file. This article will help troubleshoot this issue: http://support.microsoft.com/default.aspx?scid=77295.
On the subject of commas, if you are using a CSV file for your import make sure you do a search and replace to get rid of the commas before you export to CSV otherwise CRM will think there are additional columns of data. May seem obvious but you never know. :o)
The built in data import tool in Microsoft CRM is the simplest ones to use, at least for basic information. For anything more complex you will have to go to some of these additional options
Build your own data import tool
If you or your team has .NET coding skills perhaps a viable data migration option would be to build your own system. This gives you maximum flexibility and therefore is a good option. It also is an excellent option if you are looking to gain familiarity with the Microsoft CRM SDK. Way back in version Microsoft CRM 1.0 I had to write a Goldmine to CRM migration tool. This meant learning the Goldmine database structure, learning the Microsoft CRM 1.0 SDK and also gaining additional skills on .NET and C#. The experience was well worth it as it gave me familiarity with the CRM SDK. However what if you do not have .NET coding skills or employees with those skills?
Use the Microsoft CRM Data Migration Framework
If you do not have access to .NET coding skills but have skilled Microsoft SQL Server people this may be the option for your data migration into Microsoft CRM. This tool lets you preload a number of SQL tables then run stored procedures to take that data and have it fed into Microsoft CRM. Microsoft also gives templates for common CRM systems like ACT and Goldmine to help you get your data into Microsoft CRM. The Data Migration Framework is not the easiest tool to use but with the right skills will let you get the job done
Use 3rd party tools like Scribe Insight for Microsoft CRM
If you take the time to read my posts on the newsgroups you will see a common answer to the question of getting data into Microsoft CRM. The usual answer would be to consider a data import license for Scribe Insight.
Scribe still needs SQL skills but the SQL skills needed are more basic than the Data Migration Framework. If you are happy running queries against SQL you should take a look at Scribe as it may make your data migration life much easier. In fact my only concern with Scribe is that they have never sent me a company polo shirt even though I have probably recommended their product literally thousands of times. :o)
As you can see there are many ways to get legacy data into Microsoft CRM. Keep in mind building your own migration tools is an option but of course it takes time, therefore my usual answer for data migration is to check out Scribesoft first and see if they can quickly meet your data migration needs for a reasonable cost.
Before diving in though and performing your data migration, remember this. In many cases the client does not know how good or bad their data is. You have the responsibility to protect them from bad data. Do not be afraid to question the quality of their data and also be aware that the earlier you start an analysis of their data the more time you have to correct any problems.
A Microsoft CRM system is only as valuable or useful as the data it exposes.
John O’DonnellMicrosoft CRM MVPCrowe Chizek and Company LLC
John, this is an awesome article -- I will need to refeer it to some of our partners :)
I have specific requirement : I need to transfer data from Sql Server 2005 to Microsoft CRM 3.0 on daily basis without manual interaction (by some batch process, or so).
I would be grateful to you if u can help me out.
Thanks in advance.
If you wanted to feed data in to CRM daily you can either write code yourself or look at tools from scribesoft.com which will make it easier to do a nightly batch update
Microsoft CRM MVP
Can you provide the URL at the Microsoft site that allows me to download the Goldmine conversion templates?
What is the name of the microsoft data tool you are referring to get data from GoldMine and ACT! ? Is that DMF ?
Microsoft has the DMF or data migraiton framework that has templates to let you get data from Goldmine and Act into Microsoft CRM. However another approach would be to use tools from scribesoft.com as they will be easier to use.
Last option would be to write your own code which would require knowledge of the goldmine database schema for example (there is a doc on the web outlining the entire goldmine schema)