Microsoft Dynamics AX 2012 DIXF – Composite Entity Primer (XML format) – Part1
http://ax2012dmfdataimport.blogspot.in/2014/10/microsoft-dynamics-ax-2012-dixf.html
Microsoft Dynamics AX 2012 DIXF – Composite Entity Primer (XML format) – Part1
Purpose: The purpose of this document is to illustrate how to use Dynamics AX 2012 DIXF for import of custom data. In this particular primer I'll be focusing on Entity type = Composite Entity using XML file format.
Challenge: Data model changes in Dynamics AX related to high normalization and introduction of surrogate keys made import of data more complex. Data Import Export Framework for Microsoft Dynamics AX 2012 was designed and developed to address this challenge. Data Import Export Framework for Microsoft Dynamics AX 2012 provides architectural foundation for data import process as well as it ships with the numerous standard templates covering most important types of business data.
Solution: Dynamics AX 2012 ships with the number of DIXF templates which can be used in data import scenarios. For import of custom data Data Import Export Framework "Create a custom entity for data import/export" wizard which assists you in creating of required DIXF objects infrastructure. A composite entity groups multiple related entities together. In my example composite entity will combine header (AlexTable) and lines (AlexLine) entities together.
Data Model:
Table Name
|
Table Description
|
AlexTable
|
Sample header table
|
AlexLine
|
Sample lines table
|
Data Model Diagram:
Sample Data Model
<![if !vml]>
<![endif]>
<![endif]>
Process Overview:
Walkthrough:
Project1
Please note that I initially created 2 tables to implement a data model for this scenario
AlexTable table
AlexLine table
Processing group: Alex
Custom entity wizard: AlexTable
Welcome
Select a table: AlexTable
Select code generation parameters
Please note that for the sake of simplicity I created a fake display menu item AlexTable
Display Menu Item: AlexTable
It is also important to mention that while creating DIXF infrastructure for AlexTable I specified/marked "Is composite entity" checkbox [V]
Fields in the target table
Wizard complete
During creation of DIXF infrastructure for AlexTable table I was asked to confirm adding relation. This is required to automatically create table relationships between your newly created Staging table and necessary DIXF framework tables
Confirm adding relation
After Custom entity wizard is completed DMFAlexTableEntity project will be created automatically
Project: DMFAlexTableEntity
Please note that DMFAlexTableEntityClass class, DMFAlexTableEntity table and DMFAlexTableTargetEntity query have been generated by Create entity wizard automatically
Class: DMFAlexTableEntityClass
Query: DMFAlexTableTargetEntity
Table: DMFAlexTableEntity
Please note that because we marked "Is Composite entity" checkbox the system added RowId field to Staging table automatically. RowId field is used to link related tables records (header and lines in my scenario) as well as by DIXF errors handling mechanism (error table)
The following standard templates entities in the Data Import Export Framework include a RowID field that can be used in composite entities: DMFSalesTableEntity, DMFSalesLineEntity, DMFPurchTableEntity, DMFPurchLineEntity, DMFBOMEntity and DMFBOMVersionEntity.
For the sake of simplicity in this walkthrough I'm not going to introduce a function to link header record and lines records based on RowId field, and will import the data unlinked as is. However please see example below of how such function is implemented in a standard DMFSalesLineEntity template
/Classes/DMFSalesLineEntityClass/Methods/generateSalesTableLink
[DMFTargetTransformationAttribute(true),DMFTargetTransformationDescAttribute("@DMF580"),
DMFTargetTransformationSequenceAttribute(1)
,DMFTargetTransFieldListAttribute([fieldStr(DMFSalesLineEntity,SalesId)])
]
public container generateSalesTableLink(boolean _stagingToTarget = true)
{
container res;
SalesTable salesTable;
SalesTableForm salesTableForm;
if (_stagingToTarget)
{
if(this.isCompositeEntity() && entity.RowId)
{
res = [(select firstOnly1 dmfSalesTableEntity
where dmfSalesTableEntity.RowId == entity.RowId
&& dmfSalesTableEntity.DefinitionGroup == entity.DefinitionGroup
&& dmfSalesTableEntity.ExecutionId == entity.ExecutionId).SalesId];
}
else
{
…
}
}
else
{
res = [target.SalesId];
}
return res;
}
|
Once you introduce this function you will also need to modify Source to Staging mapping to bring over RowId values from external source and then also Staging to Target mapping to add transformation (RowId -> RecId) based on this function
Please note that in my previous post I explained how you can implement such function using custom ID field which effectively plays the same role as RowId: http://ax2012dmfdataimport.blogspot.com/2013/03/microsoft-dynamics-ax-2012-dmf.html
Custom entity wizard: AlexLine
Welcome
Select a table: AlexLine
Select code generation parameters
Please note that in order to automatically add RowId field to a newly generated Staging table I
marked "Is Composite entity" checkbox
Fields in the target table
Wizard complete
I was asked to confirm adding relation again for DMFAlexLineEntity Staging table
Confirm adding relation
After Custom entity wizard is completed DMFAlexLineEntity project will be created automatically
Project: DMFAlexLineEntity
Please note that DMFAlexLineEntityClass class, DMFAlexLineEntity table and DMFAlexLineTargetEntity query have been generated by Custom entity wizard automatically
Class: DMFAlexLineEntityClass
Query: DMFAlexLineTargetEntity
Table: DMFAlexLineEntity
Target entities: Alex
Child entities: Alex
Now let's take a moment to speak about Source data formats
Data Import Export Framework does support XML format. And not only this, DIXF also allows you to use different types of XML structure: Element-based and Attribute-based
I'll start with Element-based XML structure. For this purpose I created XML1 Source data format shown below
Formats: XML1 Element-based XML structure
Once format is created now our goal will be to create appropriate XML Files for Consolidated data import. But before I do that I'll go ahead and generate Source files for each entity included into Consolidated entity
AlexTable: Generate source file
Generate source file wizard
Welcome
Display data
Please note that I selected XML type = XSD to generate XSD schema for AlexTable
Infolog
Here's how XSD schema looks like
AlexTable XSD schema
AlexTable XSD schema (text)
<?xml version="1.0" encoding="utf-8"?>
<xs:schema elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Document">
<xs:complexType>
<xs:sequence>
<xs:element name="AlexTableEntity">
<xs:complexType>
<xs:sequence>
<xs:element name="FieldA" nillable="true">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="FieldB" nillable="true">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="ID" nillable="true">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
|
After that I also Generated sample file with XML type = XML
Generate sample file: XML type = XML
Here's the file for AlexTable
AlexTableEntity file
AlexTableEntity file (text)
<?xml version="1.0" encoding="utf-8"?><Document><AlexTableEntity><FieldA>String</FieldA><FieldB>String</FieldB><ID>String</ID></AlexTableEntity></Document>
|
Now I'll repeat this procedure for AlexLine table
Generate source file: AlexLine
Generate source file wizard
Welcome
Display data
Please note that I first selected XML type = XSD to generate XSD schema for AlexLine
Infolog
Here's how XSD schema for AlexLine looks like
AlexLine XSD schema
AlexLine XSD schema (text)
<?xml version="1.0" encoding="utf-8"?>
<xs:schema elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Document">
<xs:complexType>
<xs:sequence>
<xs:element name="AlexLineEntity">
<xs:complexType>
<xs:sequence>
<xs:element name="FieldC" nillable="true">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="FieldD" nillable="true">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="ID" nillable="true">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
|
Finally I'll also generate sample file for AlexLine
Generate sample file: AlexLine
AlexLineEntity file
AlexLineEntity file (text)
<?xml version="1.0" encoding="utf-8"?><Document><AlexLineEntity><FieldC>String</FieldC><FieldD>String</FieldD><ID>String</ID></AlexLineEntity></Document>
|
Okay so now we know who the data in Element-based XML format should look like for AlexTable and AlexLine records
My next step would be to explore Attribute-based XML format. For this purpose I added another Source data format XML2 with XML structure = Attribute
Formats
Similarly to what I did for Element-based XML format I'll generate sample XML files for Attribute-based XML format scenario, so then we'll be able to compare 2 scenarios
Generate source file: AlexTable
Generate source file wizard
Welcome
Display data (XML type = XSD)
Infolog
AlexTable XSD schema
AlexTable XSD schema (file)
<?xml version="1.0" encoding="utf-8"?>
<xs:schema elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Document">
<xs:complexType>
<xs:sequence>
<xs:element name="AlexTableEntity">
<xs:complexType>
<xs:attribute name="FieldA">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:attribute>
<xs:attribute name="FieldB">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:attribute>
<xs:attribute name="ID">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:attribute>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
|
Display data (XML type = XML)
AlexTableEntity file
AlexTableEntity file (text)
<?xml version="1.0" encoding="utf-8"?><AlexTableEntity><AlexTableEntity FieldA="String" FieldB="String" ID="String" /></AlexTableEntity>
|
Generate source file: AlexLine
Generate source file wizard
Welcome
Display data (XML type = XSD)
Infolog
AlexLine XSD schema
AlexLine XSD schema (text)
<?xml version="1.0" encoding="utf-8"?>
<xs:schema elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Document">
<xs:complexType>
<xs:sequence>
<xs:element name="AlexLineEntity">
<xs:complexType>
<xs:attribute name="FieldC">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:attribute>
<xs:attribute name="FieldD">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:attribute>
<xs:attribute name="ID">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:attribute>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
|
Display data (XML type = XML)
AlexLineEntity file
AlexLineEntity file (text)
<?xml version="1.0" encoding="utf-8"?><AlexLineEntity><AlexLineEntity FieldC="String" FieldD="String" ID="String" /></AlexLineEntity>
|
As you can see we got 2 different sets of sample files for AlexTable and AlexLine when using 2 different XML formats (Element-based and Attribute-based)
Also please note that Generate sample file function is not available for Composite entity, but you can execute it individually for each entity included into Composite entity. The logical reason would be that when having multiple entities included into a Composite entity you don't know the nature of the relationships between those tables upfront. That's why when having individual sample files for each entity you can combine them into a single Consolidated file for data import
All right! Now we familiarized ourselves with data format and we can switch to the creation of Consolidated files
Next step for me is to specify entities for processing group. Please note that once I specify Alex Composite entity in the first row the rest of 2 records will be added automatically because the system knows that Alex Composite entity consists of AlexTable and AlexLine
Select entities for processing: Alex
I'll start with Element-based XML format scenario that's why I selected Source data format = XML1
This is how my Consolidated file for Element-based XML format looks like
AlexXMLElement file
AlexXMLElement file (text)
<?xml version="1.0" encoding="utf-8"?>
<Document><AlexTableEntity><AlexTableEntity_Id>1</AlexTableEntity_Id><FieldA>A1</FieldA><FieldB>B1</FieldB><ID>1</ID><RowId>1</RowId></AlexTableEntity><AlexLineEntity><AlexTableEntity_Id>1</AlexTableEntity_Id><FieldC>C1</FieldC><FieldD>D1</FieldD><ID>1</ID><RowId>1</RowId>
</AlexLineEntity></Document>
|
Please note that I specified RowId values for both header and line in order to be able to link them appropriately. Also I introduced "AlexTableEntity_Id" element to define a metadata relationship between AlexTable (header) and AlexLine (line). The idea is to introduce an element which names consists of the name of the parent/header entity (AlexTableEntity) and "_Id" and add this element to both header data and line data
The next step will be to specify this file for Composite entity in the list of Processing group entities and generate source mapping based on this file
Generate Source Mapping
We've successfully generated mappings for AlexTable and AlexLine entities
Let's review these mappings
AlexTable Mapping (Visualization)
AlexTable Mapping (Details)
I'll also map RowId field to bring over the information about how header links to line(s)
AlexTable Mapping (Visualization)
AlexTable Mapping (Details)
AlexLine Mapping (Visualization)
AlexLine Mapping (Details)
Similar to above I'll also RowId field for the line
AlexLine Mapping (Visualization)
AlexLine Mapping (Details)
Now we can test what we've got by using Preview function
Preview AlexTable
Preview AlexLine
We can successfully see the data retrieved for AlexTable and AlexLine entities, so now we can execute the actual data import process
Copy data to Staging
Create a job ID for the staging data job
Staging data execution
Staging:-AlexXMLElement
Infolog
Great! We've successfully brought the data from source XML file over to Staging tables
DMFAlexTableEntity Table browser
DMFAlexLineEntity Table browser
The last step in this scenario will be to copy data to target
Processing group: Copy data to target
Select a job ID to run
Target data execution
Target:-AlexXMLElement
Infolog
Success! We should be able to see the data in target tables now
AlexTable Table browser
AlexLine Table browser
This concludes the first scenario with Element-based XML file
Now we can switch to Attribute-based XML file
Select entities for processing: Alex
This time my Consolidated file will look different
AlexXMLAttribute file
AlexXMLAttribute file (text)
<?xml version="1.0" encoding="utf-8"?>
<AlexTableEntity>
<AlexTableEntity AlexTableEntity_Id="1" FieldA="A2" FieldB="B2" ID="1" RowId="1" />
<AlexLineEntity AlexTableEntity_Id="1" FieldC="C2" FieldD="D2" ID="1" RowID="1" />
</AlexTableEntity>
|
Please note that I still included the same information there, but in a different format. Thus I still have all data fields specified, RowID field and "AlexTableEntity_Id"
Generate Source Mapping
After we successfully generated Source mapping we can review them
AlexTable Mapping (Visualization)
AlexTable Mapping (Details)
Similar to all above I'll also map RowId field
AlexTable Mapping (Visualization)
AlexTable Mapping (Details)
AlexLine Mapping (Visualization)
AlexLine Mapping (Details)
Of course, I'll not forget about RowId field
AlexLine Mapping (Visualization)
AlexLine Mapping (Details)
Now we can test what we've got!
Preview AlexTable
Preview AlexLine
Everything shows up there fine! So we can proceed with Copy data to Staging
Copy data to Staging
Create a job ID for the staging data job
Staging data execution
Staging:-AlexXMLAttribute
Infolog
At this point we've successfully imported the data into Staging tables
DMFAlexTableEntity Table browser
DMFAlexLineEntity Table browser
Now we can copy data to target
Processing group: Copy data to target
Select a job ID to run
Target data execution
Target:-AlexXMLAttribute
Infolog
Now we can check the data in target tables
AlexTable Table browser
AlexLine Table browser
Success!
This concludes Attribute-based XML format scenario and this walkthrough!
Versions: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 Data Import Export Framework
Summary: In this document I explained how to use Data Import Export Framework in Microsoft Dynamics AX 2012 in order to import custom data (header and lines). This approach is especially recommended for large scale data migrations and allows for much better performance comparing to usage of Microsoft Dynamics AX 2012 Excel Add-in. Data Import Export Framework in Microsoft Dynamics AX 2012 already ships with numerous standard templates for most important types of business data. Please note that DIXF ships with more than 150 ready-to-go templates.
Tags: Dynamics ERP, Dynamics AX 2012, DIXF, Data Import Export Framework, Data Import, Data Conversion, Data Migration, Composite Entity, XML, Attribute, Element
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 concepts and describe the examples.
No comments:
Post a Comment