Data migration is one of the most critical and challenging phases of a new ERP implementation project. This feature is not just about transferring data from one system to another. It is a phase where data is cleansed, structured and possibly reworked to ensure that the data transferred is relevant and accurate.
Business Central offers many tools to upload data before a new Go-live. In this post, I will cover the various tools, how to choose the best tool depending on your project and how to plan this delicate phase.
Formulate a data migration plan.
Users need a plan to migrate data in an ERP implementation. Failing to do so will slow down the entire project because poor data will affect user testing and training. A data migration plan can be complex in large companies where data is siloed by division and business function. For example, the marketing and sales teams might share customer data, but the first will likely have more contacts in their database because they also manage prospects and leads.
Regardless of the specific company requirements, a data migration plan must include at least three elements:
The data to migrate
The team responsible for extracting, cleansing, formatting and uploading the data.
A timescale.
For most new implementations, a simple data migration plan will look like the table below:
Data | Team Responsible | Expected Data for UAT1 | Expected Data for UAT2 | 100% Data Migrated by date |
Customers | Sales | 10% | 80% | 01/02/2023 |
Vendors | Purchasing | 15% | 85% | 01/02/2023 |
Ship-To Addresses | Supply Chain | 10% | 80% | 01/02/2023 |
Items | Supply Chain | 5% | 95% | 01/02/2023 |
How to cleanse your legacy system data
Data cleansing is the most critical part of a data migration plan. We cleanse data to avoid having obsolete or incorrect data in our new ERP. Bad data can seriously affect a new ERP implementation, like inaccurate reporting or even preventing an entire module from functioning. For example, bad inventory data can block the entire WMS module.
Most teams object that data cleansing is a costly activity. Therefore they prefer uploading data as it is and cleansing it in the new ERP as they go along. This approach might appear to enable a fast project delivery, but it also exposes the company to higher costs later. Fixing bad data before go-live is cheaper than fixing it after because Business Central will help you validate the quality of the data imported. Also, correcting an error caused by bad data after go-live can be highly costly, like sending the wrong statement to a customer or showing incorrect amounts in a VAT statement.
There are six main data cleansing activities:
Remove obsolete data.
A new ERP is an excellent opportunity to remove redundant data. For example, delisted products or customers who placed their last order years ago.
Remove duplicates.
Contacts and products can easily be duplicated in old systems or basic accounting software.
Structure and formatting.
The data structure can differ between the two systems. For example, some applications allow adding text to email fields or phone numbers but Business Central doesn't. Data formatting also includes managing regional settings, for example, dates format and decimal separators.
Deal with missing data.
A missing address, customer records or a product with no volume or weight are examples of missing data.
Manage data outliers.
Outliers are values within a dataset that vary significantly from the others. Outliers may indicate variabilities in a measurement, errors, or exceptions that are either excluded from the data migration or managed differently.
Validate data.
The last step is to validate your data after all the rework. For example, check that the payment methods assigned to all customers exist in the payment methods list.
Decide what not to migrate.
An essential element of the data migration strategy is to decide what not to migrate. For example, do you need to bring over your new ERP customers who placed their last orders ten years ago? Also, delisted items for which there is no inventory or old suppliers are all examples of obsolete data. The execution of the data migration plan is easier when the dataset includes only relevant and up-to-date records.
Understand Business Central data structure.
After sorting out the legacy system's data, it's time to understand Business Central's data structure so we can move to the plan's second phase: the data import.
Business Central data is managed in a relational database, like most ERPs. Relational database systems use a model that organizes data into tables, rows and columns. Typically, columns represent categories of data, while rows represent individual records. For example, customers in Business Central are stored in the Customer table (Table 18). In table 18, each customer represents a row, and the columns are the fields available to store specific data about a customer, for example, a city or a postcode.
A relational database means that different tables are linked through a relation; this relation is typically between one or more fields of two or more tables. Let's look at the relationship in Business Central between the customer table and the ship-to-address table.
The customer table is the parent, and the ship-to address table is the child. The relation between the two tables is between the Ship-to code field of the customer table and the Code field of the ship-to address table.
The connection between these two tables will not work without a unique primary key. A primary key defines a unique value that identifies a specific table record and can be made of one or more fields. For example, the customer table has only one primary key, the customer number. The ship-to code primary key is made of two fields, the customer number and the ship-to code.
A primary key of more than one field allows for storing records in a user-friendly format. For example, what if more than one customer has a ship-to code in London? We can safely create many ship-to addresses using the code London; these will be linked to the correct customer through the customer number shown in the picture below.
Business Central data migration tools
There are two ways to migrate data to Business Central, the assisted setup or the configuration packages. The main difference between the two methods is that the first is used for simple data migration tasks that include only the essential datasets, for example, customers and vendors. The configuration services can migrate any business data in Business Central with limitations only on transactional data. I.E., we cannot upload past sales; for that, we need to use the opening balances functionality that I will cover in another post.
Migrate business data using the assisted setup.
The data migration tool in the assisted setup section is an easy way to migrate essential datasets into Business Central. Search for assisted setup, click the list page link and then select Migrate business data.
On the next screen, we can choose the source of our data. One of the options is to migrate data from other accounting software using an extension.
We can select the source of our data on the next screen. The default is Excel, but we can choose more if we have the extension.
I have the Quickbooks data migration tool in my sandbox, but more extensions can handle other accounting software or ERPs.
For this example, I choose Import from Excel and click OK. The next page shows the following four steps.
Download the Excel template
Fill in the template with your data.
Specify import settings.
Upload your data.
Let's look at step number three as it shows "Optional, but Important". This step allows us to automatically populate specific fields to the data we will upload. This is necessary because Business Central, like other ERPs or accounting software, includes specific fields in most data tables that are unlikely to be present in a data upload. Examples of such fields are posting groups that determine the link between general ledger accounts and business transactions.
If we click on Settings, a page opens showing the option to default templates to our master data records. Templates are typically used to default posting groups fields to customers, items and vendors. Still, we can use templates to set up default values to other fields, like a default payment method for our vendors or a price group for our customers.
Once the spreadsheet template is filled in and the templates configured, we can upload the data. Business Central will show a confirmation page with a summary of the records after we upload the spreadsheet.
If your data is more complex and you must import more than three master data sets, the assisted setup is not the right solution. In this case, we have a much more comprehensive tool to upload data in Business Central.
Configuration Services
The configuration services allow uploading data into Business Central using multiple tables that are not available in the assisted setup. For example, the chart of accounts and price lists.
The configuration service functionality includes two features, the configuration package and the configuration worksheet.
Configuration Packages
A configuration package is the first element of the configuration services. The package contains a header and multiple lines. A default configuration package is automatically created when a Business Central environment is provisioned. The package differs for each localization; you can see below a default package code GB.ENG.EVALUATION in my sandbox.
A package contains the tables and the table fields to import in the lines where each line is a database table. In the picture below, you can see the package lines and the main columns that show the table fields count and the database records.
Let's look at the first row. The first two columns identify the database table; in my example, table No. 3, Payment Terms. The next columns show the number of fields available in that table, the fields included and the fields to validate. At the end of the page, I have a field for package errors that will populate if there are errors in the data imported; lastly, I have the number of database records that already exist in the payment terms table. In the picture below you can see field database records show 11, which means I have eleven payment terms.
The best way to use configuration services is to create one or more packages and add lines, then decide which fields to include for each table we import.
Let's start with a simple package for sales and receivables. I start from the configuration package list and click new to create a new package. I named my package SALREC and added the product version and the language code for my reference. I exclude the configuration tables for now.
Now I can add tables in my package lines. For this example, I want the customer, customer price group and price list, which is made of a header and a lines table. My configuration package now includes four lines, one for each table.
You can see that while table 6, the customer price group, has only eight fields, other tables have many more, like the customer table, which contains one hundred fields. Other tables have even more fields. The good news is that you only need a few fields to upload data in Business Central. Therefore, we can exclude fields from the package line and make our Excel template simpler to populate.
I click on field No. of Fields Available in the second line of my package, the customer table to open the list of fields. The page will open in view mode, so I click on Edit List and deselect the fields I don't need. In the picture below, I deselected some fields, but I left the name and address fields in the package.
Note that some fields show a relation table; for example, the field City relates to the table Postcode and the field Ship-to Code is linked to the Ship-to Address table, as I explained in my previous example.
Uploading data through a configuration package requires some planning. We cannot write any value in a field with relation to another table, and sometimes, we need to upload the data in the related table first and then upload the main table.
This concept might be counterintuitive at first, but it will make sense as we go through an example. For my package, I need to upload data in this order:
Upload table Postcode and include the field City
Upload table Customer and leave the field Ship-to empty
Upload table Ship-to Address
Upload table Customer a second time to update the Ship-to code
Once we have our plan and the package is ready, we can export the Excel templates. There are two options for exporting Excel templates. The first is to export the entire package; we will get an Excel file with one sheet for each table if we use this option. The second is to export one or more lines at a time.
I have four tables in my package, and all tables have related fields except the Customer Price Group table; therefore, I will use this table first. After I export table 6 to Excel, I get a file in my download folder. The file exported includes the eight fields selected as columns.
I create three records in my table; Gold, Silver and Platinum groups, as shown below.
The next step is to upload this file to Business Central to create these three new customer price groups. I save my Excel file and go back to the configuration package. Then I select table 6 and click on Import from Excel.
My data is uploaded after I click OK on a confirmation message. My package line now shows a value in field No. of Package Records. You can see that I have three records, my silver, gold and platinum codes.
Note that field No. of Database Records still shows zero because the data is still in the package lines, and we must complete a last step before we can push our data in Business Central.
The last step is to apply the configuration package data so that the fields' values are saved in the database. I run the action Apply Data under the Functions menu to apply the package data.
If there are no errors in my file, I will get this message that confirms my three new customer price groups have been inserted.
My configuration package lines are updated, and I have zero package records and three database records.
If I open the page Customer Price Group, I can see the three records inserted via the configuration package. I can now move to upload the customer table and use one of the three customer price groups in the customer price group field. Business Central will validate that the customer price group field matches one of the three records in the customer price group table. If, for example, I upload a customer with a price group Diamond, I will get an error.
Configuration Worksheet
The configuration worksheet is a page that allows a logical arrangement of the configuration package lines. It provides additional functionalities that help users manage data migration where multiple teams work together.
In the picture below, you can see an example of a configuration worksheet where I have lines arranged by groups and areas, and I also assign a user to specific package lines.
I can use the same actions on the configuration package lines, like export and import from Excel.
Closing thoughts
Data migration requires time, and accuracy is critical for a successful implementation. However, the tasks are time-intensive, and fixing problems caused by bad data after go-live is costly. Make a plan, identify key resources responsible for the execution of this plan and use the above tools to cleanse, format and validate company data well before the go-live date.
Regards
Alfredo.
Do you want to train with us?
Click below to check our training programs.
