Dynamics AX 2009 - Data Migration / Conversion - PLEASE HELP!

Finance and Operations/AX General discussions
1

Hi All,

We’re implementing AX for the first time and have to migrate data from at least 6 finance systems and at least 3 HR systems.

We need to migrate Employees, Vendors, Customers, Projects, CoA, GL, etc, etc, however we’re a little unsure as to the options for automating a batch load.

Please assume that we have all our data extracted, transform and staged and ready to go into AX. This data will reside in final staging tables in a SQL staging database.

How do I get the data out of the SQL database and into AX?

We don’t want to load the data directly into the tables as this would undermine the business logic etc and we would have to know the underlying data structure/schema, not to mention this approach being supported.

We need to know the options for migrating the data, such as:

A) Write an X++ job that queries the data in the staging table and executes AX business logic for inserting the data

B) Use the .Net Business Connector for querying the data in the staging table and executes AX business logic for inserting the data

What are the options, what are the rules, what is the supported and non-supported approaches and was is the best practice here?

Nobody I have spoken to appears to know the straight forward / black and white answer on this topic.

Ultimately, we’re thinking of writing our own X++ Data Load/Migration APIs that we can use for bulk loading data, but it appears in most examples that you have to insert the data in a certain order directly into certain tables, rather than calling an AX class like ax.createEmployee(“James”,“May”,“16/04/67”,1234,“DepartmentX”,etc,etc).

Why is this such a difficult area in AX, surely there is a well documented best practice for bulk inserting data programmatically?

If anyone knows the true answer to this million dollar question, I’ll give them a whole british pound (and lots of beer)…

At your mercy…

A.

2

Hi,

I migrated many data for all our clients and I used the Data export/import definition groups.

You can make a definition group which includes all tables for the whole database, including settings, but I personally prefere to migrate “piece by piece” the data.

I simply put all data in Excel. Columns are the fields in AX tables. Then I save it as csv files and simply import in AX through a definition group with the fields in the same order as the columns in Excel.

My opinion is that for bulk loading you have to check all data anyway “piece by piece” first and then put all of it together.

If you think I can help you more, please feel free to ask me any questions.

Best regards

3

Hi,

Our issue is that our company aquires new companies, which leads to new systems very regularly so we need a repeatable, sustainable and scalable approach.

How scalable is the Excel import? How long does it take to load?

Excel has a limit of x thousand rows, what if I want to load 100’,000’s records? What if I have to migrate 2 seperate finance systems over a weekend, it would take a long time to do this in Excel would it not?

I need a scalable solution that allows me to migrate 100,000’s of records of different types over the course of a weekend.

Is the excel import a scalable and robust mechanism? What happens if one record fails, does it fail the whole batch? I don’t want that type of thing to happen for example.

Also, the use of this tool assumes you understand the underlying table structures, what if I do not?

A colleague also informed me that you have to know what fields to fill in for the record to show in the front end to make it a valid record - how do I find this type of thing out?

Apologies for rambling but there still appears to be a few gaps in our knowlege about converting data into AX.

Kind Regards,

A.