Many companies do not archive master data regularly. A new Microsoft Dynamics 365 ERP project is the prime opportunity to bring over only current master data records and leave behind the customers, vendors, and inventory that are no longer relevant to your business, have been discontinued, etc. For example, do you need to migrate customers with no transactions in the last 3 years? Tidying up your master records can greatly reduce the amount of data to migrate. It’s important to determine ways to identify these records, using specific groups or flags that can be incorporated into the extraction step.
One thing is for sure, data migration to the cloud will be more complicated and take longer than you originally expect. The earlier you can start planning what and how to migrate, the better.
If you prefer videos, check out this 5-minute video where I discuss data migration readiness with one of my colleagues:
The migration of core data is usually automated through some sort of intermediary such as Excel. Data is extracted from the old system, put into Excel, where it is mapped to the fields in the new system. Often the new system may have new fields that will not have come across from the old system. These are generally added to the spreadsheet so that they can be imported at the same time.
The basic master data that is always migrated:
- Customer Master Data
- Vendor Master Data
- Inventory Master Data
NUMBERING SCHEMA
In older Microsoft Dynamics systems like GP, NAV, and AX, it was general practice to create customer, vendor, and item numbers that were meaningful in some way – a code that in some way identified the record to the observer. Consultants still argue over whether using meaningful or random meaningless codes are best practice, however, we find clients generally have a firm opinion on whether they want to go through a renumbering exercise and either way their decision on this is usually easily accommodated. If you do wish to renumber your master records, this is something you can plan for in preparation.
The other common practice was to create codes that allowed for the classification or sorting of the records. For example, incorporating a location identifier in a customer code schema. These days, with the power of search and sort inherent in Microsoft Dynamics 365 products, we generally recommend against embedding search and sort keys in the master code.
OPEN TRANSACTIONS
The transactional data that is always migrated includes:
- Open AR invoices or balances
- Inventory on Hand
- Open Purchase Order Lines
- Open Sales Orders Order Lines
- Work in Progress
We recommend clients take the opportunity to tidy up balances in the old system. Write off small balances, pay down open vendor invoices, and, where applicable, write off obsolete inventory.
CHART OF ACCOUNTS AND GL BALANCES
One of the common areas of change is the company’s chart of accounts. With the power of dimensional accounting, companies coming off a system with a fixed coding structure, or even those who have simply outgrown their old coding systems, will want to seriously reconsider the structure of their chart of accounts.
If you are not familiar with the way a dimensional chart of accounts works, take some time to research this. Ask your partner how a dimensional chart might work for you.
It is common for companies to bring over general ledger balances from their old system so that they have prior period/prior year comparative reporting from the financial accounts. Many code up their trial balances for the previous years and bring each month in as a single journal.
If you go through a major overhaul of your chart of accounts, and you want these historical comparatives, you will need to reclassify your historical financial transactions. There is a natural trade-off between the effort to do this recoding and the benefit of having those comparatives in your reports.
HISTORICAL TRANSACTIONAL DATA
Prima facie, you will be leaving your detailed transactional history behind. It is rare and unusual for organizations to bring across transactional history, like sales and purchasing history, into their new systems. These days, the recommended approach for leveraging historical data is to use a data warehouse. Historical data is pushed into the data warehouse, new data is pushed into the data warehouse, and any reports that cross past and present are generated from there.
MIGRATION COMPLEXITY
The scenarios above are reasonably simple. Many organizations we work with grapple with migration scenarios that are a lot more complex. For example, Sikich works with a lot of equipment manufacturers that sell their products with service agreements. This means as-built Bills of Materials along with their service history will need to be migrated since the build and repair history of past sales are fundamental to their future business.
Many organizations struggle with the thought of losing detailed sales and purchasing history. We recommend working with your partner to find ways of optimizing the visibility of historical data.
The general steps for data migration are as follows:
- IDENTIFY SYSTEMS AND DATA.
If you haven’t already, take an inventory of all the current systems you have in place today that will be replaced by the new ERP. Identify data from each system that will need to be migrated. Develop a spreadsheet that will track what we’re extracting and from where. - CLEANSE.
Review your data for correctness and completeness. A common example is customer addresses – are the addresses current and correct? International addresses are a common area of incomplete data.
Duplication is another area we can focus on during our cleaning phase. It’s less common with customers but more common with inventory, especially consumables.
- EXTRACT.Develop a plan for how you will extract the source data. Even if the target format is not yet decided, you can develop a starting point for extracting data. For each source system, determine how you will extract data – via direct database queries, export from within the application, or something else. Ideally, this step will eventually be automated and easily repeatable, as it will be run several times before cut over.
Next, incorporate your extraction process criteria to exclude data identified in the cleaning step as not needed. Make dates for cutoff parameters in your queries so they can be easily updated.
Another important activity that can be determined during this step is timing. How long does each extraction take and in what sequence does it need to run? For some data, extraction will take place earlier in the cut over process, such as master data, but for others, such as open transactions, you may have a small window to extract from the source system and provide for import to the new ERP system. Knowing how long each step takes will be valuable when planning these cut over activities.
Lastly, test the extract and look at the results. Sometimes data extracted from older systems can come over with additional embedded characters or spaces that need to be cleaned up.
Remember, cloud data migration is a great time to purge and archive info from the old system and strategize on how you’d like to handle things moving forward. If you are ready to make a move to cloud ERP, and looking for expert advice on best next steps, please contact us.
