Hi again! This is the second post on my new blog about Dynamics Ax and as promised I will deliver something interesting. We will speak about the Data Migration Framework and in particular how we can import ZipCodes and its relate information (States, Counties and Cities) creating a custom entity for that purpose.
This is my first winter in Germany and although it was not very cold for what I expected it was in fact the darker winter in 43 years!
[Der Spiegel - Dark winter article ]
Basically I haven't seen the sun in 5 Months. But today it was pretty sunny! That I have to say.
Anyway, the spring is comming and I can now say that I survived the german winter. This is quite a thing for someone that comes from south Spain :-) you only have to look below at the end of this post to know what I am talking about :-)
So let's speak about the Data Migration Framework.
As you should know the Data Migration Framework was released for Microsoft Dynamics Ax 2012 (Actually in version Beta 2.0 and working also with SQL Server 2012 and Ax2012 R2) as a tool to help with the migration of data into Microsoft Dynamics Ax.
After looking how it works and how it looks like, we in our company have decided to use it in order to standardize our migration procedures and deliver better and more predictave results in our data migration phases.
Basically what the framework does is to take data from a data source (for example a CSV file) and copy the data on a staging table before the data is migrated to the final target table. We can make a mapping from source to staging and also a mapping between staging and target. Between staging and target we can also use functions that would be executed to convert the values or even execute some needed logic before the value is copied to target.
You can download the framework from the Information Source WebSite and there you will find also some documentation and even a ilustrative video about how to use the framework.
Installing the framework should be not problematic following the instructions of the documentation and with Beta 2.0 and Dynamics Ax 2012 R2 there is a small issue in which one of the classes of the framework tries to use a table with name CreditCardCustNumber that is not anymore in this version of Ax.
In my case I just commented the code that use this table because it was not relevant for me but in your case you will have to check in you come to this problem.
After installing the framework you will be able to start using it with some of the entities that are already delivered like customers, vendors, customer addresses, vendor addresses, open sales orders, ... you can find the entire list in the documentation or in the Target entities form in the Data Migration Framework Area Page.
In our case we want to import ZipCodes and we dont have an entity for that so we will have to create our own entity to create records in the table LogisticsAddressZipCode but also in LogisticsAddressState, LogisticsAddressCounty and LogisticsAddresssCity in case we have to.
To make that we will use the provided Wizard to create the needed AOT Objects. So we use the option Create a custom entity for migration. This will launch the wizard.
We are asked to which table we want to import data. We select LogisticsAddressZipCode.
On the next step we are presented the names of the entity table, query and class objects that will be created. We can change it but we leave as proposed.
The result is a project with all the objects created. But we still have some work to do because we want also to create records on the tables LogisticsAddressState, LogisticsAddressCounty and LogisticsAddresssCity. For that we will create methods that will be executed when the system takes the values of State, County and City from Staging table to Target table. These methods will check if the values of State, County or City exists. If not a record would be created on the corresponding table.
For each method we will check if the corresponding record exists and if not we will create the record. At the end we will return a container which contains the values that we want to be available for the target table.
We have also to modify the method getReturnFields of our entity class so we indicate for each method which value of the target table will be initialized with the result of the execution of the function.
We need also to modify the entity table and create a field group per each function we have created with the same name of the function. If not we would be not able to use our functions on the modify entity mapping form later.
On the Target entities form I can setup now my new target entity.
And we can modify the fields mapping between staging table and target table clicking on the button Modify target mapping. Below the Mapping details view where we can specify the detail of the mapping.
And below the Mapping visualization. There we can see that our new methods are used between staging and target. Looks cool eh?
Now we will have only to create a Processing group which uses our new target entity for ZipCodes and map a source file like the one you can see below to the staging table and we would be able to import, first the data to the staging table and later to the target table.
So this is an example of what is possible with Data Migration Framework. Which is still on version Beta 2.0 but seems really useful to create a quite professional data migration procedure for our ax projects.
To end up with this post I give you all some sun from Spain, photo taken in Aguilas (Murcia), place where I used to spend my free time before deciding to come to Germany. Nice isn't? :-)
Next week is celebrated here in Germany the biggest IT trade show in Europe CEBIT. I hope I have time to drive there and maybe I can deliver another post about my visit there ;-)
So see you all until the next time!
