Tuesday, February 18, 2014

Microsoft Dynamics AX 2012 R3 - New Data Import Export Framewrok Changes



Hi There!

I hope everybody is doing great! I had the opportunity to attend to the MS AX 2012 R# Tech Conference thanks to TriBridge.

In this post I would like to discuss the new Data Import Export Framework or DIXF. As you probably know by now, this new framework is shipped with both CU7 and R3.

So, what can new DIXF do? Well to start, one of the few new key features is that the DIXF runs on top of the SSIS service interface allowing incremental runs (UPSERT). Of course, it can import/export data, and Microsoft added the capability to compare and copy data between instances as well. In addition, the new DIXF version ships with the ability to choose different data sources such as text, MS Excel, and XML files.

Further, the new DIXF can be used to extract data directly from various ODBC*** sources such as SQL, MS Access, and MS Excel. This new additions will help us streamline our data migrations and data transfers much better.

***For ODBC types we are going to have to provide a connection string in order to simplify the data selection process. The one cool thing I saw was that we can create new rows under Mapping Details to add mandatory fields i.e. ACCOUNTNUM in case a specific legacy system does not include it.

When this scenario is true, the custom value provided can be automatically filled by a number sequence value (if we want to) by choosing the “AUTO” option in that specific row, which would take a new AccountNum from the numbering sequence system. However, we can also choose to have default values as in older versions.

In terms of the DIXF entities, the new DIXF ships with a 150 entities in comparison to the 78 (I think) it came with in earlier versions. These include master data, documents, journals, parameters, party, products, and configuration data.

Another cool addition is the addition of folder support. We are going to be able to move stuff around automatically (needs to be pre-defined) to different folders in our domain based on the operations we are executing.

The following are a few other additions:

Parallel Execution: Ability to dissect data in bundles (i.e. 1,000 rows / bundles = 100 rows per task).

This is particularly useful when large data loads need to take place. The tool provides the ability to allocate a group of records to tasks. This combination will create a bundle, and each bundle is independent of each other. See the following diagram for a visual representation of it:



Role Base Security: Provides a security framework for the different levels on an organization, this is built on top of the existing security framework (i.e. Production cannot import HR data).

Mapper Control: Allows flexible mapping between custom entities and staging objects. In addition, mapping effort is reduced when using AX friendly column names (i.e. ITEMID).

Custom Entity Wizard: We can compare data in different companies. This becomes specially interesting and useful to compare parameter data between a gold and test instances for example.  When using this tool to import data that contains discrepancies, the system inserts the data into a staging table where it is compared by another process in a specific number of companies and/or instances, and finally it gets updated.

At this point, a user can use the Comparison form to move records between different instances.

See the process in the following diagram:




NOTE: Sometimes the entity Wizard will only create a portion of the requirements and a technical consultant would have to finish the rest.

System Configuration Data: BI-Analysis and Reporting Services, Project Server Integration, EP, Batch Settings, Number Sequences, Email Parameters, AIF, System Service Accounts.

DIXF Import Process

The import Process us done by using an abstraction layer that uses SSIS behind the DIXF framework. Within this abstraction layer, we can add possible X++ customizations.

I asked the question on what would be the recommendation for migrating data from legacy systems – the following is what I could get from their recommendation (I was taking notes).There are two types of data migration architecture that consolidate both importing and cleansing data.

The first option is to have a middle tier that can process the data from a legacy system, to an external system and clean it before it goes to Microsoft Dynamics AX.




The second option is to do it directly import the data from a legacy system to Microsoft Dynamics AX. 




Microsoft recommends to keep the data cleansing business logic inside of AX. The reason is that Microsoft Dynamics provides a data migration framework that is both extensible and customizable. The framework provides entity classes that can be extended to a process specific needs. In addition to the entity classes, the framework also provides the ability to create custom staging entities for further processing prior to the final push to an entity. This can be depicted in the following picture:



The DIXF also provides a new error log preview function that allows a user to narrow down an error to the smallest unit possible to understand exactly where the error is occurring. This was not true in older versions of the DIXF. Further, the new DIXF also provides an Execution History function that allows a user to review and validate the staging data before the actual import to an entity.

DIXF Export Process

As mentioned earlier, because the DIXF also uses SSIS to export data from the framework, bulk exports can also be easily accomplished. In addition, as in older versions of DIXF, we can also generate our own source mapping and sample files. However, a cool new addition to the DIXF is that these files now can be of different types such as XLS, XML, Text, Tab delimited, Etc.

This approach sounds good and valid, however, in my mind here could be a double edge sword with the fact that XLS files might open the door for a few data consistency problems as this type of files can contain formulas. I would suggest to always understand your source files, especially our XLS ones.

DIXF Architecture

The following is the new DIXF architecture for R3.


Visit http://www.tribridge.com/ and learn about our Dynamics AX practice, services and focus, as well as our cloud services Concerto.




2 comments:

  1. Nice to hear from you :-) Good thoughts on data cleansing!

    ReplyDelete
  2. Just a note. When you access excel from X++, if there is a formula, the result is returned, not the formula.
    GTuttle

    ReplyDelete

Thank you for your thoughts. Your comment will appear in my blog shortly after review.

Have a great day!