Purpose: The purpose of this document is to illustrate how you could potentially leverage Excel Add-in in conjunction with Microsoft Dynamics AX 2012 Data Import Export Framework.
Challenge: Microsoft Dynamics AX 2012 Excel Add-in allows to interact with the business data using the power of Excel for “light-weight” data scenarios. For data import/export and integrations scenarios it is recommended to use Microsoft Dynamics AX 2012 Data Import Export Framework. Data Import Export Framework also does support native Excel data sources to populate Staging tables. Staging tables fields are typically based on extended data types which would make it technically possible to invoke Excel Add-in dynamic Lookups when populating Staging tables data to potentially reduce the number of data errors even before the data hits Staging tables.
Task: In this document I’ll describe how you could potentially populate Data Import Export Framework Staging tables using Excel Add-in. This will give you an interesting perspective at possible synergies between different approaches.
As it was mentioned at the beginning Excel Add-in allows you to interact with Microsoft Dynamics AX 2012 business data. In particular dynamic Lookups and Filters in Excel Add-in could come very handy when populating Data Import Export Framework Staging tables to reduce the number of data errors during initial data entry
Let’s consider Products data import scenario using Microsoft Dynamics AX 2012 Data Import Export Framework. I’m going to use a standard Products template and first off I’ll create a processing group called “Product” as shown below
I’ll then add Product entity to the processing group and specify Excel as Source data format
Select entities for processing group
As you can see I also supplied Excel file Product.xls, but in fact in this walkthrough I want to populate the contents of Staging table using Excel Add-in. For this purpose I’ll open up Excel and add DMFProductEntity using Add Tables function
The number of mandatory fields from DMFProductEntity table gets added to Excel spreadsheet
Then I’ll populate required values. At this point I could leverage dynamic Lookups provided by Excel Add-in for fields bound to appropriate extended data types and/or Filters to filter my dataset as needed
I could also manage multiple data sources in the same spreadsheet if needed
The result of the publishing is success as shown below
Great! So I could populate the contents of DMFProductEntity Staging table using Excel Add-in
But before I can complete data import process in Data Import Export Framework I also need to populate some records in framework tables, specifically DMFDefinitionGroupExecution table which has a reference to the entity and JobID for data operation
My next step will be to also add DMFDefinitionGroupExecution table to Excel Add-in and populate required data there
However when I try to add DMFDefinitionGroupExecution table to Excel Add-in I will see the following error
Microsoft Dynamics AX Office Addin – Error
This happens because of exception thrown in Microsoft.Dynamics.Framework.Metadata.AX assembly
Specifically EndRecId and StartRecId RecId reference fields in DMFDefinitionGroupExecution table are not handled properly in QueryDataSourceMetadataCollection.cs
The reason for this is because EndRecId and StartRecId fields are bound to a generic AX table (Common) in DMFDefinitionGroupExecution table relations
DMFDefinitionGroupExecution table - Relations
For example, this is how EndRecId field based Relation looks like
In this walkthrough to pass through (bypass) EndRecId and StartRecId fields and display DMFDefinitionGroupExecution table metadata in Excel Add-in I used Visual Studio debugger.
However in the real world I would likely have to write X++ wrapper Web Service or handle creation of appropriate DMFDefinitionGroupExecution table record directly in X++
Okay, now DMFDefinitionGroupExecution table metadata is available in Excel Add-in
And I can try populate the data in there. Please see the data below, plus I could explicitly specify a default Staging status = “Not started”
After trying to publish this data I’ll find out that I’ll also need to populate the data in DMFExecution table which is another framework table
So I’ll go ahead and also add DMFExecution table to Excel Add-in
For DMFExecution table record I’ll provide the following data
And successfully publish this data
After appropriate DMFExecution table record has been published I will come back to DMFDefinitionGroupExecution table record and republish it again
At this time all supporting framework tables have been populate with appropriate data and we can switch to Microsoft Dynamics AX 2012 and review populated Staging data
When I click on View staging data I can review the actual data sitting in Staging table
View staging data
Just to double check the data I can run Validation
And now I’ll be ready to Copy data to target
Copy data to target
However I can’t see my job listed for execution on Target data execution screen
Target data execution
This is because I didn’t specify Staging status = Ended for DMFDefinitionGroupExecution record. Data Import Export Framework data import consists of 2 steps (which in fact can be automated into 1 step if needed) and the system keeps track of 2 statuses: Staging status and Target status. It is logical that you can only execute Copy data to target for jobs which successfully completed import of data into Staging table
Staging status = Ended
So I’ll need to update Staging status = Ended in my DMFDefinitionGroupExecution table record
So now my job will show up on Target data execution form as shown below
Target data execution
However Run button is disabled due to the fact that I didn’t specify file Path field and it is “” (empty)
After I do the final correction and specify file Path field with non-empty field I can complete data import
Please note that I specified a fake non-empty value for Path field just to satisfy the data model constraint (Path field value must be specified), and in the case if data import is done completely using Data Import Export Framework Path field value will be populated based on Source path on Get staging data step
Finally I can complete data import and move data to target
Target data execution
Copy data to target
After successful data import the following infolog will be displayed
And then you can review the results of data import in Microsoft Dynamics AX 2012. In my case Product will be created in Microsoft Dynamics AX 2012
Summary: This document describes how you could potentially leverage Excel Add-in in conjunction with Microsoft Dynamics AX 2012 Data Import Export Framework. Specifically my goal was to populate Data Import Export Framework Staging tables using Excel Add-in and better explain the mechanics of how Microsoft Dynamics AX 2012 Data Import Export Framework works. This also gives you an interesting perspective at possible synergies between different approaches for data import in Microsoft Dynamics AX 2012.
Tags: Microsoft Dynamics AX 2012, Data Import Export Framework, DIXF, Data Migration Framework, DMF, Excel Add-in, Lookup, Filter, Data Import, Data Migration.
Note: This document is intended for information purposes only, presented as it is with no warranties from the author. This document may be updated with more content to better outline the issues and describe the solutions.
Author: Alex Anikiev, PhD, MCP