最新消息:ww12345678 的部落格重装上线,希望大家继续支持。

Microsoft Dynamics AX 2012 Data Import Export Framework + Excel Add-in

网络文摘 William 4916浏览 0评论
Microsoft Dynamics AX 2012 Data Import Export Framework + Excel Add-in
 
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. 
 
Walkthrough
 
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
 
Excel Add-in
 
 
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
 
Processing group 
 
 
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
 
Select Tables
 
 
The number of mandatory fields from DMFProductEntity table gets added to Excel spreadsheet
 
Excel book
 
 
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
 
Data
 
 
I could also manage multiple data sources in the same spreadsheet if needed
 
The result of the publishing is success as shown below
 
Publishing status 
 
 
Great! So I could populate the contents of DMFProductEntity Staging table using Excel Add-in
 
DMFProductEntity table
 
 
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
 
Select Tables
 
 
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
 
Exception
 
 
Specifically EndRecId and StartRecId RecId reference fields in DMFDefinitionGroupExecution table are not handled properly in QueryDataSourceMetadataCollection.cs
 
Exception
 
 
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
 
Relation
 
 
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
 
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”
 
Data
 
 
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
 
Publishing Details
 
 
So I’ll go ahead and also add DMFExecution table to Excel Add-in
 
Select Tables
 
 
Excel book
 
 
For DMFExecution table record I’ll provide the following data
 
Data
 
 
And successfully publish this data
 
Publishing Details
 
 
After appropriate DMFExecution table record has been published I will come back to DMFDefinitionGroupExecution table record and republish it again
 
Publishing Details
 
 
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
 
Execution history 
 
 
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
 
Validate
 
 
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
 
Data
 
 
Execution history
 
 
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)
 
Code reference
 
 
After I do the final correction and specify file Path field with non-empty field I can complete data import
 
Data
 
 
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
 
Publishing Details
 
 
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
 
Infolog
 
 
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
发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址