Planning for data import, export & migration with AX 2012

With every Microsoft Dynamics AX 2012 implementation, a very import topic, that involves a lot of planning, and technical work, is around the process of migrating data. This includes a lot of exporting, importing, transformation and so on.

Like all task relating to an AX 2012 implementation, these should be governed and defined based on functional business requirements and discovery. That is what should drive the choice being made, for how specific entities are imported, migrated or even entered into AX 2012.

To that end, and with this in mind, lets look at some new content on MSDN, specifically on this topic.: Plan data import, export, and migration [AX 2012]

From the article:
"This topic describes the tools and strategies to use when planning to import or export Microsoft Dynamics AX data. It describes how to plan to migrate data from one enterprise resource planning (ERP) system to another. Finally, it describes performance and security considerations for data import and export."

This is actually a very well thought out, and put together article, and I highly recommend it be used as a source when in reference to such task, when working in this area of a AX 2012 project.

With that said, there are some understandings that we need to circle back around to, to allow business requirements and customer needs to drive what is completed, in regard to such task.

One broad stroke statement that is not always applicable from this article is the following.:
"We[MSFT] recommend that you do not migrate transactional data or historical data to Microsoft Dynamics AX. Instead, close all open transactions before migrating, if it is possible. Maintain the database that contained your previous transactional data for reporting and compliance purposes."

This, as many of you senior consultants understand full well, is a great statement, and if it's possible really helps reduce the cost of data migration efforts in a project. However, it's hardly always true, and would say that the above statement is typically not what takes place for open transactions on any AX project.

Instead, what we try to push for, in the above statements place, is based on volume for open transactions. With that, if the volume is not to large, then a two birds, with one stone event can occur.

Instead of having to write data migration logic, this can be used as a training opportunity and get the open transactions into the new AX 2012 instance. There still can be an argument that even if there is a perceived large amount of volume to such data, that it's cheaper and better ROI to have more hands entering in the open transactions than writing migration code.

Why would that be? The reason is, any time spent, and therefore budget dollars burned, on the process of data migration is all throw away once the go live takes place. This means it looses it's value, as soon as the new AX 2012 instance is up and running.

So the trick, for adding the most value to such projects, is to perform the least complex, and spend the least amount of time possible in this area. It's not the the point that it should be overlooked, I'm not stating that at all. What I am saying, is that whatever the solution for migration is, it should be defined based on the understanding that the work will be throw away as soon as the go live takes place.

Now, on larger implementations, most likely all of the options that you see listed in the above document will be used. There is no single way to achieve importing all entities, instead, each should be defined, based on volume, complexity, and level of transformation that needs to take place. The process should be as fast as possible, and least amount of time and therefore budget dollars. Simple is king of value, and rules over it in this area with an iron fist.

Another point I would like to bring up, that is not listed in the above article, is around RapidStart Services and it's impact on data migration, specifically around reference and setup data. With the release of RapidStart Services for AX 2012, this too should be considered, weighted and then valued for helping create data entities, and for importing other entities via.

I will leave with this, always look for the most simplest approach when migrating data. Only be as complex as the requirements and customer constraints force you to be. Sometimes the most elegant solution, is the worst in value, when it comes to the task related to data import, export & migration.

Finally, I will reference back to this post in future articles, as I review some of the options in detail. Talk about the real world impact, by example, and give some food for thought to help better understand the value of the choices made on real projects.

That's all for now, till next time!



"Visit the Dynamics AX Community Page today!"

Microsoft Dynamics AX 2012 Excel Add-in

With the release of Microsoft Dynamics AX 2012, the lines between the Rich Client, the Portal as well as Microsoft Office are going to be blurred. This is out-of-the-box ability, that replaces a lot of older ways for doing things.

One such topic, is the old Microsoft Excel Export / Import Wizard for Microsoft Dynamics AX. You can see this, in the Microsoft Dynamics AX 2009 menu system, here.:


With the release of Microsoft Dynamics AX 2012, this no longer exists! For some, I'm sure this will be a little shock, however this is a good thing.

The older Excel Import, honestly was very slow and if you had any records of any size, then you just really would rather create X++ scripts to import data.

Now, with Microsoft Dynamics AX 2012, and the new Excel Add-in for Microsoft Dynamics AX, scripts are a thing of the past. Let me say, this new process is very fast, and is designed so that a developer or architect is really not needed to import a lot of data, into AX.

To help show this process, I have created the following walk through that shoudl give an idea of what it takes to work with the new Dynamics AX Add-in for Excel, and get data into Microsoft Dynamics AX 2012 using this new, and great tool!

First, you have to launch Microsoft Office Excel 2010, and of course have the Excel Add-in installed. I'm going to assume this is already in place, and lets say, for the sake of keeping this simple, that I need to add some Delivery Mode data.

This data, lives in the DlvMode table within the Microsoft Dynamics AX AOT. So to get started, lets click on the Connection button, from the ribbon, and make sure we are in the company account and instance we care about.


(Server specific information, blanked out on purpose.)

We see, that we have the ability to override here, and point to what AOS instance we want to connect too, as well as within that what company entity.

Now moving forward from here, lets go and add the DlvMode table, by clicking on the add data, then add table.



Once we select to Add tables, then we can select the DlvMode table, and actually use the Filter field at the bottom, so we only see that table to select. Also notice the fact, that the process created a new worksheet, in this workbook, just for our DlvMode AX table.



Here we can add and remove fields, using the Field Chooser, on the ribbon. Here I've added a few fields, just to give you an idea.



Now moving forward, I will only have the key field Mode of Delivery as it is required, and then Description field.



I then took and added new rows, which gave me three records within the table, of the Excel worksheet. From here, I added my three new DlvModes.

At this point, we now have the data we need, and want to get this into Microsoft Dynamics AX 2012 instance. With this, we simply take and Publish Data, then Publish All. In doing this, we could have selected only one record to publish, but for now lets just publish all of them.

With doing this, we see the following worksheet that was generated, that shows the status of the process.



Having this, we can see that everything went great, and was able to upload the data. Also, we now see, on the DlvMode worksheet, that our new data, and the existing data that was already in the DlvMode table, now appears.



Going into Microsoft Dynamics AX 2012, we can still hit Ctrl+D, and it will open the new Developer workspace. Here we can expand the AOT, and go to our DlvMode table. Right click, and table browse, and see that our data we just created, exists within Microsoft Dynamics AX 2012.



So with the release of Microsoft Dynamics AX 2012, a lot fo great new things come as part of it. More native Office integration, with very little effort, and very little technical knowledge needed.

That's all for now, but this is very exciting stuff, and we are just getting starte within the great new release.

Till next time!

Update: I wanted to make sure an update this post, so that you would have the latest information about the Excel Add-in, in case you hit this post, from a search engine. Make sure and check out the revisited post for the Excel Add-in located here: AX 2012 - Excel Add-in Revisited



Visit Hillstar Business Intelligence (www.HillstarBI.com) in order to truly unlock your data trapped in your Microsoft Dynamics investment. With our value driven business intelligence strategy Hillstar help you transform into a data informed company.


"Visit the Dynamics AX Community Page today!"