Simplify data migrations
I have done a lot of data migrations, but to be honest, I do not like the complete data and code migration.
So yes, I do the code migration, but the migrated code is, from my point of view, just a library with possible reusable solutions. We always refactor this code, but that will have an impact on data migration. So, how can we do it correctly? We create the new master data, and when this task is completed, you must migrate to the next topics. The trick is that you also must add the customizations from above topics on the data entities. This trick will even work for AX 2009. So, here is an overview of all that needs to be exported.
What should you export?
- Customers
– Sales tax exempt numbers
– Customer definitions
– Customer details V2
– Customer bank accounts
– Customer postal addresses
– Global address book V2
– Contacts V2
– Party contacts V3 - Vendors
– Sales tax exempt numbers
– Vendors V2
– Vendor bank accounts
– Vendor postal addresses
– Global address book V2
– Contacts V2
– Party contacts V3 - Products
– Products V2
– Product variants V2
– Product master configurations
– Released products V2
– Released product variants V2 - Sales orders remain delivery
– Sales order headers V2
– Sales order lines V2 - Purchase orders remain delivery
– Purchase order headers V2
– Purchase order lines V2 - Trade agreements
– Excel plug in - On hand (Available + reserved)
– Movement Journal and Excel plug in - Vend trans Open
– General Journal and Excel plug in - Cust Trans Open
– General Journal and Excel plug in - Bank accounts
– General Journal and Excel plug in - P&L balance
– General Journal and Excel plug in
In case you have more parts to import, check the templates, this is a good start.
Data Entities vs SQL Views
As you can see, there are a lot of Excels to create. So, can we automate that? Yes, we can! But you must understand how the data import framework works in D365FO. You must know that any data entity is also a view on SQL. So, start a Tier 1 dev box and export those views to a SQL create script. Let’s look at the following examples.
In the next picture, you can see how the SQL View looks like the Visual Studio Entity.
Now, you must export the view from SQL Studio.
Let’s export, YEH!!
Next step is to import those views in AX 2012. I strongly recommend changing the view names to the functional names from the above list (use an underscore for the spaces). After creating all those views on AX 2012, the last step is to create an export file in Excel format.
The Source format will be your AX 2012 Prod Database and your target will be Microsoft Excel. Select your views.
In this example, I did not have the view names renamed to the functional entity name (example is from the start from my learning curve ). And at the end, you get a nice Excel with multiple tab pages, which you can easily copy to your import source files. You would preferably use functional names, so the tab pages are more in line with the import Excel files names.
Let’s import
After you copy the tab page to their dedicated Excel sheets, copy those back in the zip file and finally import, it will look like this. (Note: the zip file is created by first exporting the data from D365FO. This will result in the empty Excel file in the zip file.)
Some advice
Create consistency queries in AX 2012 and check below topics. It is better to find them here than when you are importing it in D365FO.
- Is the invoice account of the customer account also migrated?
- Is the primary vendor of the item migrated?
- Are all contact persons migrated related to the customers and vendors?
- Do we have obsolete addresses on the sales and purchase orders?
– This happens when the address of the customer or vendor is updated and after that the sales or purchase order did not get updated. - Are all customers or vendors in relation to customer and vendor transactions open migrated?
- Data time: Of course, this view will not match so you must adapt the queries. Issues like from and to date
Another painful thing is the table inheritance, the table ID in D365FO can be different than in AX 2012
The relation roles were also something like that
Cust and vend trans open scope
Only open sales lines
Only open purchase lines
On hand, yes, we can simulate a little FIFO. The Invent Sum table has a modified Date Time. This tells us when that item was touched for the last time.
Out of the box, the sales line number cannot be imported, so you must extend the Sales order lines V2 Entity. You should also look at the marking between sales and the Purch line. My advice is to import the Purch lines first, and after that the sales lines. On the sales line entity, search for the related Purch line. When the Purch line is not found, this does not mean it is an error, because the related Purch line could be skipped on data migration. Also be sure the formulas are available before importing the sales and Purch lines, otherwise your inventory transaction values have the wrong values…
If something goes wrong and you need to fix your errors, a previous blog can help you to export your errors into Excel and then import them again.