Tuesday, March 22, 2016

OCT 19 Microsoft Dynamics AX 2012 DIXF – Composite Entity Primer (XML format)


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]>
 
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. 

Author: Alex Anikiiev, PhD, MCP

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

Export a copy of the standard user acceptance testing (UAT) database

 Reference link: Export a copy of the standard user acceptance testing (UAT) database - Finance & Operations | Dynamics 365 | Microsoft ...