Data Migration Part II : Evaluation & Planning

As Antoine de Saint-Exupéry said, “A goal without a plan is just a wish.” In terms of Data migration that is so true,  according to Gartner, 83 % of data migrations fail outright or exceed their allotted budgets. A lot of this failure is attributed to a lack of planning.

Analysis of data migration projects over the years has shown that they meet with mixed results. While mission-critical to the success of the business initiatives they are meant to facilitate and support lack of planning structure and attention to risks causes many data migration efforts fail.
— Gartner, “Risks and Challenges in Data Migrations and Conversions,” February 2009, ID Number: G00165710

In the last blog, I mentioned about Data Migration stages. In this blog will do a deeper dive and look in the first 2 stages,  So let’s talk about what planning and Evaluation look like.

Evaluate: Data

1. Data Sources: Identify all different data sources for Data migration, often time Dynamics 365 implementation replaces more than one applications as such you might be migrating data from multiple applications. 
2. Data Quality: it’s important to analyze the quality of data at the source to avoid Garbage In, Garbage Out. Some of the common quality issues are
   1. Inconsistent and incomplete Data: inconsistency is a big indicator that there’s a data quality problem. When data has not been entered in the system correctly like missing zip code, province or other information remaining data could be of less value.
   2. Duplicates: In many circumstances, the same records might exist multiple times in a database, Common culprits are Account, Contacts.
   3. Inconsistent Formats: if source application did not have much validation the data will have more typos of errors, Address is the most common place to find with City and provinces being named differently.
   4. Obsolete Data:  Every day, people move, marry, and change their names. Since Peoples’ information is rarely static, obsolete data is a common challenge which is hard to rectify as well.
3. Data Volume: Volume of Data could be a key factor in determining Data migration Tool/Process and the Deployment Plan.
4. Operational Data vs Archival Data: Legacy applications when replaced will often have a lot of historical data, you might not want to bring all of this data into Dynamics 365 as this data might be not be used for day to day operation but useful for Data Analytics and Reporting Purposes. Identify what data needs to be in Dynamics 365.

Plan: Migration

1. 1. Data Migration Tool/Process: Deciding on Data Migration Tool could be tough considering so many third-party tools available.  When deciding on tools we have few options:
      • • Dynamics 365 Export & Import Utility: D365 has built-in export Import utility which can be used to import data from Flat files, supported format includes CSV, .txt, .xml, and .xlsx. this allows analysts to work with data in spreadsheet and import is after getting it into the expected format. Import Data Template can be used for creating the import files as it does the mapping of data as well as enforces some of the data type validations.
        • Data Migration utilities(ETL): Extract, Transform and Load is a type of tool which is built for data Migration. These tools can handle large data set migration. Kingswaysoft(SSIS), Scribe, Informatica, FTE, SmartConnect are commonly used Data Migration tools 
        • Integration: If the solution built includes a data integration between the system, It could be leveraged to do initial data migration/load. This is often only applicable when the data source is not a system being replaced.

      Tool Selection could be based on two main factors:

      1. Data Complexity: simple data set Data migration could be done using OOB Excel data Imports but If the data is coming from multiple sources, Data transformation is required or high Data volume then its best to use a data migration tool. Often time a seemingly easy looking data migration might end up being more complex.
      2. Teams Ability: The tool best suited for Data Migration is based on the skill set of the team. If Team has been using any of the data migration Integration tools like Scribe, FTE, Kingswaysoft I would stick with it.  the tool being used should be able to so upserts and automate the Data migration as per the mapping. My preferred tool for Data migration is SSIS coupled with Dynamics 365 connector like Kingswaysoft or CozyRoc.
2. Data Mapping: Data Mapping is the most important aspects of data Migration after actual Migration. Most of the data migration fails because of error and lack of data Mapping, majority of the time the focus is on ETL,  requirements phase rather than on mission-critical and error-prone data mapping.
   Data mapping tasks vary in complexity, depending on the hierarchy of the data being mapped, as well as the disparity between the structure of the source and the target. 
    
   Excel spreadsheets are extensively used in data mapping and were with anything excel related they are not free from errors and far from being a perfect solution. So how do we ensure that we get the data mapping Right? here are a few tips:
   1. Engage Business SME: Business SME’s are the only ones who truly understand the data and who can, therefore, be involved and decide the mapping.
   2. If mapping the same entity from multiple systems, identify the Master data source and map against it. 
   3. Data Mapping involved mapping fields between the source system and target system, In order to generate Source schema, I prefer to use Metadata Document Generator plugin in XRM ToolBox.
   4. Data mapping exercise for Dynamics 365 should provide the main three types of mappings:
      1. Entity Mapping: Mapping to denote what table from source map to which table at the destination. 
      2. Entity Field Mapping: Mapping to denote which source field is being mapped to which target field
      3. Configuration Data Mapping: to map the possible values like Optionsets and configuration lookups(account type, Case type, etc) 
   5. Data migration could be migrating multiple tables to an entity in D365 or vice versa. In such a case, it’s useful to supplement the Data mapping file with a Visio/diagrammatic representation of how entities are being mapped.
   6. A legacy system might not have values for all the new field which is created in the target system as such Data mapping should ensure that mandatory fields on the traget system are not left blank.
   7. Here is a sample Data Mapping template you may use. 
3. Data collision: As part of data quality check, you would have likely identified that same data exist in different data sources or even same source has duplicates of the same record, most common collision or duplicate data is with Accounts, Contacts, Profiles. There are a couple of approaches to deal with it, Duplicates could be merged at Source itself helping you avoid garbage In, garbage out, if duplicates are caused by data collision from different sources  we can uses a staging database to resolve it, Or my favourite Deal with this at the Destination system which in our case in dynamics 365. Either approach, we need to plan for it. 
4. Document Migration: If Document needs to be migrated, you need to take this into consideration while deciding on tools or approach to migrate them. A large set of the document associated with data should always stay in SharePoint vs the notes. consider migrating them to SharePoint. You may use this Kingswaysoft Sample for migrating documents.
5. Deployment Plan: Data Migration Deployment happens along with Application Go -Live and it can take significant time to complete. Effective Data migration plan is key to successful Go live and least application downtime. There are multiple strategies you can apply for it. I will discuss more on in my next blog on Data Load.

Hope you found this blog useful, Let me know what methods you have used in the past to be successful in your Data migration project.