Data migration is usually the most complex and underestimated area of any ERP implementation. This chapter makes you understand data migration requirements, managing the data migration scope, identifying the tools and techniques for data migration, and data validation.
The following topics are covered in this chapter:
- Understanding data requirements and challenges
- Defining what data needs to be migrated
- Source systems involved
- Managing the scope of data migration
- Questions to ask during scoping
- Leading data migration requirements
- The battle of history
- Tools, techniques, and development considerations
- Data extraction and cleansing
- Using the data migration framework
- Building a repetitive process
- Data validation
- Testing scripts
- Engaging business for validation
- Using migrated data during testing
Rightsizing the scope is the first step towards successful data migration. Often, customers have either not considered data migration at all or have unreasonable expectations regarding the requirements. Even if the original sales proposal has explicit data migration requirements that have been identified, many of the project team members and stakeholders may not be aware of what was specified or may not agree to the mentioned scope. Hence, it is important to facilitate a scoping exercise with the project team and document the mutually agreed results.
The following section covers a few tips for the scoping of data migration and educating the business stakeholders.
The following set of questions can be asked during the scoping exercise:
- What do I need to keep the business running? Define the business goals with that question in mind, and then approach the issue of what information needs to be migrated to meet these goals or what solutions can be provided to meet the goal without migrating the data. For example, I need to be able to collect my receivables and run aging for customers—that's the business goal. This means you need to only migrate Open AR for the customers along with the due date.
- Is there an alternate way to live without bringing the existing data over? Reporting out of legacy system or a data warehouse and defining a manual process, if it is going to be used only for inquiries over a short period of time, are some potential alternatives.
- Every record that needs to be migrated comes at a cost. Is this cost justified? It's not a question of whether it can be done. Is it worth it?
- Do you trust the data present in the legacy systems? Do you want the new system to have the same issues that you are trying to solve in the current system?
- How many records are involved? Ensure that the ball park numbers of record counts are defined for each area during scoping/requirements (for example, 4 million products, 200,000 customers, 2000 open orders, and so on). This will help you select the right tools.
- How often will you be asked to retrieve this data?
- Identify the business needs clearly. You can avoid the cascading effect and carve out the critical pieces of data that you need frequently, to limit the scope. For example, just migrating the open balance of each customer invoice rather than trying to bring the complete line item detail requires less effort. If a customer service needs the invoice line detail to research a customer issue that happens once a month on an average, the detail generally would not be worth the effort to try to migrate it.
Do your homework on the proposed data migration and validate your assumptions with the business rather than asking the open ended question "What data do you want to migrate?" The following table is an example that you can use as a starting point to help validate the decisions to be agreed upon in a data migration requirements session:
Additionally, you can leverage the Dynamics AX data migration requirements spreadsheet within Microsoft Sure Step to identify and plan to migrate your data. This spreadsheet is intended for the consulting team to use internally. However, it can also be used as a tool to facilitate the data migration requirements gathering and can subsequently be used throughout the project lifecycle to confirm whether each migration data element has been identified, and that the process has been defined, developed, and tested. The following table image shows an example of the columns and data that are important for the scoping session. Additional columns help you manage the development, testing, and final move to the production system:
As I stated at the beginning of this chapter, business stakeholders often expect their shiny, new Dynamics AX system to have their historical data from their legacy system. Anything less would mean that they have less information now rather than more, right? Part of the data migration planning process is educating the business stakeholders on the cost of that mentality and focusing the business on the information, which is driving business decisions, servicing customers, and providing analysis.
In principle, you should avoid migrating historical transactions, such as posted sales invoices, posted purchase orders, individual general ledger transactions, inventory transactions history, and so on. The effort to cleanse and transform the data for Dynamics AX is an expensive proposition and takes the resources away from the goal of designing and developing improved processes within Dynamics AX. More importantly, most of this data doesn't really drive the business decisions or even worse, it provides inaccurate views of the business due to bad data and/or processes from the legacy system.
Certainly, the historical transactional data is needed for either regulatory, business analysis, or customer satisfaction reasons. However, there are other solutions available rather than agreeing to migrate the legacy data into Dynamics AX tables. Some cheaper solutions that I have used in the past to satisfy customer needs around history are given as follows, and can be used depending on the size of the dataset and the requirements around how the data will be used:
- Run the reports and save them in a shared folder or on a SharePoint site as a PDF file. This will be useful for reports that must be kept for regulatory purposes such as financial statements.
- Export the data to Excel or Access to a read-only folder that only specific users can access. Smaller datasets where you want to query, filter, or sort the data in different ways are a good match for this.
- Leverage an existing data warehouse to meet the reporting/analysis requirements.
- Set security on the legacy system to read-only and do historical lookups there. Make sure that support contracts and an exit strategy are part of any discussions around this option so that the customer is not paying for multiple systems indefinitely. This is a good option for a stable legacy system where support is still available (without paying a hefty annual support price) and also helps ease the transaction for the legacy system support vendor.
- Create new AX tables that replicate legacy data tables and pull in the data without having to do a mapping or cleansing process. Reports or inquiries can be developed over this data, which is often cheaper than the programming required to clean and normalize the data to AX requirements.