Introduction To The Data Migration Framework (DMF) In .

1y ago
1.15 MB
21 Pages
Last View : 4d ago
Last Download : 1m ago
Upload by : Kaleb Stephen

Introduction to the Data Migration Framework (DMF) inMicrosoft DynamicsWHITEPAPER

Junction Solutions documentation 2012All material contained in this documentation is proprietary and confidential toJunction Solutions, Inc and subject to the nondisclosure provisions of theapplicable Junction Solutions, Inc agreement. This material is forinformational purposes only. Junction Solutions, Inc is not liable for anydamages in connection with the use of this information.No part of this documentation may be reproduced, stored in a retrievalsystem, or transmitted in any form or by any means, including, but not limitedto, electronic, graphic, mechanical, photocopying, recording, or otherwisewithout the prior written permission of Junction Solutions, Inc.This documentation is subject to change without notice, and JunctionSolutions, Inc does not warrant that the material contained in thisdocumentation is free of errors.Any errors found in this document should be reported to Junction Solutions,Inc in writing.Proprietary and ConfidentialSubject to ChangePage 2 of 212012

Table of ContentsIntroduction . 4Resources . 4Framework overview . 4DATA ENTITIES . 5Framework components. 6SOURCE DATA FORMAT . 6TARGET ENTITIES . 9PROCESSING GROUP .10MAPPING .10Framework process.14LOAD SOURCE DATA .14STAGING DATA VALIDATION .15PROCESS STAGING DATA TO TARGET ENTITIES .17Installation .20Proprietary and ConfidentialSubject to ChangePage 3 of 212012

Data Migration Framework WhitepaperIntroductionMicrosoft Dynamics AX 2012 has undergone some significant designchanges to the application and the table structures. Even though moving to anormalized data base schema allows for a more standardized data structure,it also makes data migration a much more complex topic.Historically data migration has been done through custom written importscripts created by the consulting team. They typically followed the path ofreading in data files; convert/map values and validate incoming data. Therewas no out of the box migration framework that would provide functionality toimport, validate and process records into the Microsoft Dynamics AX entities.This changed when Microsoft released its Data Migration Framework (DMF)in mid-2012. The new framework provides functionality to load the mostcommon data entities (e.g. Customer, Vendors, Items) utilizing a combinationof SQL SSI packages as well as AX business logic.This document will talk about the basic framework and components of theData Migration Framework. There will be a second whitepaper describinghow to modify existing and creating new entities.ResourcesFor more information about DMF, refer to the TechNet library in the followinglocation: 1.aspxFramework overviewThe Data Migration Framework (DMF) is a tool provided by Microsoft to makedata migration of complex data structures easier and more standardized.Other tools are available, but the DMF is definitely a big step forward tosimplify and optimize data migration.The general flow data elements take through the framework can be describedin four steps:Proprietary and ConfidentialSubject to ChangePage 4 of 212012

Data Migration Framework Whitepaper1. Prepare source data:Through manual or automated processes the source data files have to becreated. The framework currently only supports text files as source. Futurereleases will include support for other input formats; for example ODBC dataconnections.2. Load data into a staging table:The data is loaded into a staging table through a dynamically generated SSISpackage.3. Review/Validate data:The framework provides the ability to check imported value against existingreferenced values to ensure referential integrity.4. Process data to create AX entities:In the last step the imported data is processed and the AX internal datarecords/entities are created.Each of the steps is described in more detail in the following sections.Data EntitiesThe following entities are currently supported out of the box: Bill of materials (BOM)ContactsContact addressesCustomerCustomer addressesDimensionEmployeesEmployee addressesInventory journalLedger balancesMain accountOpen invoices (accounts payable)Open invoices (accounts receivable)Open sales ordersOpen purchase ordersPrice discProductProjectUnit of measureVendorVendor addressProprietary and ConfidentialSubject to ChangePage 5 of 212012

Data Migration Framework WhitepaperFramework ComponentsThe framework includes the following components which are discussed inmore detail in the following sections: Source Data Format:Describes the formatting characteristics of the import file structure. Target Entities:Defines the final Dynamics AX table records created during the final dataprocessing. Processing Groups:Provides an organizational setup for grouping import files to be processedtogether sequentially. Mapping:Outlines the field mappings between the source file and staging tables.Source Data FormatThe source data format allows the user to specify parameters defining theimport file. Multiple formats can be setup; however it is recommended that thesame data format is used across different files. Support exists to accept bothfixed-length and character delimited files.The source data format allows us to setup various parameters regarding thetype of source file that is processed.Source Data Formats Form – General TabProprietary and ConfidentialSubject to ChangePage 6 of 212012

Data Migration Framework WhitepaperUse the following table for information on the Source Data Format Form –General tab:FieldTypeDescriptionThe medium of import; currently “File” is selected bydefault and cannot be changed.File formatDefines if the file is character delimited or if every columnis fixed length.Indicates the first row in the file is header information.Character indicating the next column/row in the file.First row headerRow/ColumndelimiterText qualifierCharacter used to encase text, for example doublequotes. Any delimiter symbols found with the textqualifiers will be ignored and parsed as part of the text.Example: Lucky Luke, “123 Street Ave, Suite 5”,California(NOTE: Since the entire address is surrounded bydouble quotes (the text qualifier), the comma after “Ave.”is not considered a column delimiter)Regional settings(FastTab fields)Multiple valueseparator(FastTab)Proprietary and ConfidentialSubject to ChangeAllows specifying specific language settings if the data isloaded in any other format than default language, dateand time format.A source file column can potentially contain multiplevalues. A typical example of this are multiple phonenumbers assigned to a customer or contact. Theparameter allows setting the character used to separatethem.Important: This character has to be different than thecolumn delimiter to ensure the values are treated as onecolumn. The system currently does not validate thissetup.Page 7 of 212012

Data Migration Framework WhitepaperThe Application tab contains application specific setup information thatdetermines how imported data elements are processed.Use the following table for information on the Source Data Format Form –Applications tab:FieldDimensioncodeChart ofaccountdelimiterDescriptionThis property allows enabling the financial dimensions used inthe source data.Selects the character that is used to separate the financialdimensions in the source file.Uses the same constraint as the “Multi value separator”; Itmust be different than the row delimiter.DimensionSetupIf the setup below is chosen, a column in the source file wouldhave to contain the following value:Warehouse-PleasantonNamesequenceThis parameter is used for the global address book entry todetermine the order and format the name is going to bedisplayed.Proprietary and ConfidentialSubject to ChangePage 8 of 212012

Data Migration Framework WhitepaperTarget EntitiesThe Target entities form shows the relation between staging table and thefinal entity (the Dynamics AX record(s)).The staging table is a raw intermediate collection of data imported from thesource data file. The end result can be an entity that exists as multiplerecords (e.g. customer and addresses). The staging table is a flatrepresentation of all potential data elements. When processing the stagingdata, the system splits the data and creates the necessary AX records.The linkage between the two elements is done through a mapping that canbe automatically generated or manually added through the “Modify targetmapping” form. Since the mappings are rarely changed, this will not be coverin the current document.The entity class can be used to execute additional business logic duringprocessing the staging data. A typical example is the creation of thenecessary global address book entries when creating a customer. Entityclasses and their functionality will be covered in detail in the next documentlooking at creating/modifying existing entities.Proprietary and ConfidentialSubject to ChangePage 9 of 212012

Data Migration Framework WhitepaperProcessing GroupOnce all the setup has been completed, a processing group can be created.Each processing group can contain one or multiple entities that will beprocessed together. An example would be to process customer and addressinformation in one run.The processing group form gives the user the options to load source data intothe staging table(s), validate imported data and process/re-process thestaged data into the target entities.It is important to notice that entities will be processed in the order they wereadded to the processing group. This has to be taken into account whenimporting dependent entities such as “the customer has to exist beforeaddresses can be imported”.Each entity requires the source data format to be specified. This ensures thatthe source data is properly read and processed.MappingThe next and most critical step is to have the correct mapping between thesource data file and the staging table in AX. The mapping will ensure allvalues are properly imported into the correct table fields.If the source file already exists, it can be selected as sample file and the“Generate source mapping” function can be used. This function will read thefirst row of the file and generate the mapping based upon the field names.Any file that is used as sample file therefore has to include headerinformation.If the function cannot find a match based upon the given structure nomapping is generated and the user has to manually create the mapping byusing the “Modify source mapping” function.Proprietary and ConfidentialSubject to ChangePage 10 of 212012

Data Migration Framework WhitepaperThrough simple drag and drop the relation between the source and stagingentity can be established. If a user prefers a table view of the existing/missingmappings the detailed view can be opened by selecting “Mapping details”.Proprietary and ConfidentialSubject to ChangePage 11 of 212012

Data Migration Framework WhitepaperAdditionally, the detailed view allows setting up simple value conversions thatare applied during import; e.g. convert the country value of US to USA.If there is no template or source file available, the “Generate source file”function will start a wizard to generate a basic template.The Wizard guides the user through a few quick steps to determine whichfields should be included in the template.Proprietary and ConfidentialSubject to ChangePage 12 of 212012

Data Migration Framework WhitepaperOnce the fields are selected, the “Generate sample file” button will generate atext file that can be imported into Excel to serve as a template base:Proprietary and ConfidentialSubject to ChangePage 13 of 212012

Data Migration Framework WhitepaperFramework ProcessThe previous section described the required setup to process a source fileinto the target entities. The following section describes the actual processingsteps: Load Source Data:Loads the source data file into the intermediate staging tables. Staging Data validation:Validates the staging table records for referential integrity. Process staging data into target entities:Executes the final processing for converting staging data into actualDynamics AX table records.Load Source DataAfter the processing group setup is completed, the framework is ready to loadthe source data into the staging table(s).It is possible to load data for the same entity in multiple iterations; an examplewould be to load US customers first, then Canadian. For this purpose everytime a data file is loaded a Run ID is assigned. The Run ID is assigned toevery record and allows for the described separation.Through the “Get staging data” function the Run ID as well as the specific fileused for the import can be selected.Proprietary and ConfidentialSubject to ChangePage 14 of 212012

Data Migration Framework WhitepaperThere are two options to process the file. The “Run” option will import the fileimmediately and use the client’s local security policy & resources. The “Runon AOS” allows adding the import job to the batch queue and execute it at alater point and time. It is important to mention is that the AOS has to havevisibility to the file location as well as the AOS user has to have the properpermission to read from the location.Staging Data ValidationEvery time a new job is executed, a new record in the execution history iscreated. The execution history can be reviewed from the processing groupand will enable the following tasks: View the log of the import: Allows review of potential errors View the staging data: Allows validation of imported records Copy the data to target: Allows you to process the staged data to thetarget entityIf errors occurred during the import the staging status will show “Error” andthrough the log function the user can review any potential issues. However,there are certain errors that are not logged in the current version. Some ofthese include duplicate primary keys or an invalid file format. These errorshave to be resolved in the source file before reloading the data.Proprietary and ConfidentialSubject to ChangePage 15 of 212012

Data Migration Framework WhitepaperExample of an error that may occur: A Base Enum value that cannot beconverted. This error is logged.Other than validating the format of the data, there are no referential integritychecks executed during the import. Reviewing and validating the actual datais an additional step and can be done through the “View staging data”function.The “Validate all” function will loop through all the records and validate eachfield that has a link to related table. The result is presented in an info log tothe user.Proprietary and ConfidentialSubject to ChangePage 16 of 212012

Data Migration Framework WhitepaperThere are three possibilities to correct errors.1. The data elements can be corrected in the source file. This isadvisable if the file is going to be used to load the data into anotherenvironment or to load the data at a later stage again.2. The data can be manually corrected on the “View staging data” file.This is helpful if there are only a few issues to correct and or thenumber of records is limited.3. The staging data can be mass updated through custom AX jobs. Asthe staging tables are standard AX data tables they can be modifiedthrough code like any other entity.It is important to notice that the framework will not prevent the user fromprocessing staging data to target entities even though there are errors in theimported data. If the data is not carefully reviewed and validated the potentialissue of referential integrity arises.Process staging data to target entitiesIf there are no errors remaining the staging data can be processed to thetarget entities. The “Copy data to target” function will complete the process byreading the staging data and creating the actual AX records.The staging data is processed based upon the assigned Job ID during theimport. This allows processing the parts of the staging data that are ready forprocessing, but not the ones still worked on. For example all US customersare reviewed and ready; but Canadian customers are still being validated.Proprietary and ConfidentialSubject to ChangePage 17 of 212012

Data Migration Framework WhitepaperAdditionally it is possible to only process a subset of a specified job bychanging the “Run for” property to "Criteria". Rows with previous errors: Only records that had errors during theimport will be processed. Rows selected by user: Only records selected in the “View stagingdata” will be processed.Leaving the property at “All” will process all staged records for the selectedJob ID.Proprietary and ConfidentialSubject to ChangePage 18 of 212012

Data Migration Framework WhitepaperA second form similar to the form used during the import of the staging datawill be presented. The user has the ability to process the data immediately orsend the function to the batch queue where it can be executed on the AOS ata later time.After the function is complete, the processed records are available in AX. Anyerrors during the import are recorded in the execution history log and can bereviewed there.Proprietary and ConfidentialSubject to ChangePage 19 of 212012

Data Migration Framework WhitepaperInstallationThe Data Migration Framework (DMF) is available for download onMicrosoft’s Information source portal. The current framework is only availablein a beta version; a release date for the final version has not yet Once the executable is downloaded it has to be extracted into an appropriatefolder. It is important to run the setup.exe with administrator privileges on theAOS server.The installation procedure will: Create a folder that contains the AX model used to add the necessaryelements to AXInstall the required DLLs in the client and AOS bin folderCreate various demo files for testing/reviewIf the DMF should be used from a different client, it is possible to copy thefollowing DLLs into the client bin folder (C:\Program Files (x86)\MicrosoftDynamics crosoft.Dynamics.AX.DMF.SSISHelper.dllTo install the required objects in AX, the model created must be installed

Target Entities The Target entities form shows the relation between staging table and the final entity (the Dynamics AX record(s)). The staging table is a raw intermediate collection of data imported from the source data file. The end result can be an entity that exists as multiple records (e.g. customer and addresses). The staging table is a flat