Data Migration for Microsoft Dynamics AX 2012

Microsoft Dynamics® AX 2012

Data Migration for Microsoft Dynamics AX 2012

White Paper

Microsoft Dynamics AX 2012 includes many different ways to import data into the system. Because the Microsoft Dynamics AX 2012 data model was normalized, choosing an import method can be challenging. This document provides guidance about which import method to choose for specific situations.

Date: March 2012

www.microsoft.com/dynamics/ax

Patrick Nelson, Solution Architect

Send suggestions and comments about this document to . Please include the title with your feedback.

Table of Contents

Introduction 3

Data complexity in Microsoft Dynamics AX 2012 3

Options for importing data into Microsoft Dynamics AX 2012 4

Microsoft Dynamics AX import 4

Microsoft Excel Add-in for Microsoft Dynamics AX 5

Using document services with the Excel Add-in to import data 5

Using the Excel Add-in to import tables 5

RapidStart Services 7

AIF, document services, and custom code 9

Examples of using AIF to import global address book–related data 10

Customer import 10

Vendor import 15

HCMWorker (employee or worker) import 16

Product-item data management services 19

Recommended import methods for specific tables and entities 26

Performance considerations 28

Introduction

Microsoft Dynamics® AX 2012 must be populated with configuration and master data records before an organization can start using it. To minimize data duplication and enable change tracking in Microsoft Dynamics AX, the underlying database schema is quite complex. This complexity can present challenges to system integrators and developers who are migrating data from other systems into Microsoft Dynamics AX.

Data is likely to be imported many times during the life cycle of a system; for example, when you migrate from another system, set up a new test or demonstration environment, or add new master data. This document is only intended to describe how to perform the initial data load into a new system.

The following table describes the types of data that can be imported or set up in the system.

Type of data / Description /
System configuration data / Data used to set up the Microsoft Dynamics AX environment. This data is not considered part of data migration, but part of your initial setup and configuration. Microsoft Dynamics ERP RapidStart Services can be used to set the system configuration data.
Parameter data / Data specific to each module in the environment. This data is typically migrated from a test or staging environment into a production environment. It is not imported from another ERP system.
Master data / Entities that describe the parties, locations, products, and activities that are referenced by entities that document and record business events. Examples of this type of data are customers, vendors, products, ZIP Codes/postal codes, payment methods, and delivery methods.
Transactional data / Entities that document business events and record their economic value. Examples of this type of data are ledger transactions, customer transactions, and inventory transactions.

Data complexity in Microsoft Dynamics AX 2012

The complexity of the data model in Microsoft Dynamics AX 2012 has increased significantly from previous versions, both because the data model was normalized, and because of other functionality added in Microsoft Dynamics AX 2012. When you import entities, such as customers, vendors, or sales orders into the system, those entities can map to multiple tables in the database. The options that you can use to import a specific type of entity vary, based on the complexity of the data model that underlies that functionality.

Complexity can also be caused by the need to conform to the principles that underlie the Microsoft Dynamics AX data model. The following table describes core concepts that impact data model complexity.

Core concept / Impact /
Table inheritance / Table inheritance is a concept that recognizes and represents generalized and specialized relationships between data entities. In Microsoft Dynamics AX 2012, tables can inherit, or extend, from the tables that are situated above them in a hierarchy. A base table contains fields that are common to all tables that are derived from it. A derived table inherits these fields but also contains fields that are unique to its purpose. Each table contains the SupportInheritance and Extends properties, which can be used to control table inheritance. When you import a table that inherits from a base table, you must also import the base table. This is most easily accomplished through code.
Date effectivity / Valid time state tables can be used to store and track an object’s history over its lifetime in Microsoft Dynamics AX 2012. Valid time state tables are often used to track changes to an entity, which requires that some of the data about an entity be stored in a separate table. It is important that you not access valid time state tables directly, but instead use a service abstraction.
Views and normalization / A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object. What is stored in the database is a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view. Views are used extensively in Microsoft Dynamics AX to access highly normalized data.
Views are employed by services that reference these constructs to help simplify the programming model and data access patterns.
One-to-many relationships / Many Microsoft Dynamics AX entities allow for one-to-many relationships. For example, the global address book allows an unlimited number of addresses and an unlimited amount of contact information to be associated with each party. One-to-many relationships can make it challenging to present information in a tabular format such as a worksheet, where all contact information for a particular party (customer or vendor) is frequently typed on a single line.
Financial dimensions / A financial dimension is a data classifier created from the parties, locations, products, and activities in an organization. The master entities, such as customers, vendors, and workers, contain default financial dimensions that help populate default transaction information.
The underlying storage for dimensions and the associated rules and combinations is complex, and the logic to properly associate dimension attributes with a master entity must be done by calling the correct APIs rather than populating data directly into tables.
Surrogate keys and record IDs / Most tables in Microsoft Dynamics AX have a surrogate key, usually the record ID (RecId) of the table, which is used as the primary key. Tables related to the original (parent) table usually have the surrogate key of the parent as a foreign key. RecIdThis provides the benefit of being able to rename items and show friendly names in the user interface while maintaining the data integrity of the stored information. Because RecIds are allocated by the system as data is inserted, maintaining primary key/foreign key relationships without using a service abstraction or APIs is quite difficult and prone to error.

It is often helpful, before you import data into Microsoft Dynamics AX, to review E/R diagrams of the data structures that you will be working with. We recommend that you use either the Application Analysis Tool that is available on InformationSource or the Reverse Engineering tool.

Options for importing data into Microsoft Dynamics AX 2012

This section describes the functionality that is available in Microsoft Dynamics AX 2012 for importing data.

Microsoft Dynamics AX import

Microsoft Dynamics AX import (available from System administration > Common > Data export/import > Import) is the primary mechanism for transferring data between different Microsoft Dynamics AX instances.

You cannot use this import method to migrate data from another ERP system or an older version of Microsoft Dynamics AX. To use this functionality and correctly create definition groups, you should have a clear understanding of the underlying data model. No business logic can be invoked to validate or otherwise modify data when you use this functionality. It simply writes the data into the tables exactly as it is in the import file.

For more information, see the following resources:

·  Import initial setup data

·  Create definition groups for import and export

Microsoft Excel Add-in for Microsoft Dynamics AX

The Microsoft Dynamics AX Add-in for Microsoft® Excel® is a powerful tool for viewing and editing data in Microsoft Dynamics AX 2012. You can use the Excel Add-in with document services or to directly access tables. However, to perform full create, read, and update operations, the data and metadata that are being imported must be organized in very specific patterns. Because of these restrictions, not all tables or services are supported.

Using document services with the Excel Add-in to import data

Only document services that meet the following requirements can be used with the Excel Add-in to create or update data:

·  Replacement keys – The root level of the document service (the parent data source) must have unique indexes other than RecId. These may be in the form of a non-RecId primary index or a replacement key.

·  Related replacement keys – Each field within the service that is a RecId-based foreign key must relate to a table that specifies a replacement key.

·  Relationship direction – When parent/child relationships exist in the underlying query associated with the service, only relationships originating on the child element and pointing to the parent may be used.

·  Query and service consistency – Document services are based on an underlying query that defines the data contract used in the service at the time that the service is generated. To be used with the Excel Add-in, the query and service contract must be synchronized. The Excel Add-in uses this query definition to perform read operations when refreshing data into the workbook. Because of this, any overrides to the read method, or any extension of the schema beyond what is in the underlying query, will not be reflected in the service.

·  View support – Views may be used within document services to provide an easier-to-use data model for end users. However, any service that uses a view must have the PrepareForXXXExtended methods implemented to define the correct order of operations on save.

Because of these constraints, the Excel Add-in can be used only with the following document services to update, create, and delete data:

·  BudgetTransaction

·  EMSMeterReading

·  EMSSubstanceFlow

·  GeneralJournal

·  ProductionPickingList

·  ProjectHourJournals

·  SysImportBusSector

·  VendGroup

·  VendRequestSignup

Using the Excel Add-in to import tables

Only tables that meet the following requirements can be imported directly by using the Excel Add-in:

·  Visible identity – There must be a unique index on the table that does not contain RecId as a component. This may be either the replacement key or the primary index.

·  Valid references – All relations in the relations collection for the table that refer to other tables via RecId must be related to tables that have a replacement key specified.

You can import data into the following Microsoft Dynamics AX 2012 tables by using the Excel Add-in.

Table name / Table name /
BANKCENTRALBANKPURPOSE / LEDGERALLOCATEKEY
BANKGROUP / LEDGERALLOCATIONBASISRULE
BANKTRANSACTIONTYPEGROUPHEADER / LEDGERALLOCATIONBASISRULESOURCE
BANKTRANSTYPE / LEDGERALLOCATIONRULE
BANKTRANSTYPEGROUPDETAILS / LEDGERALLOCATIONRULEDESTINATION
CURRENCYLEDGERGAINLOSSACCOUNT / LEDGERALLOCATIONRULESOURCE
CUSTBANKACCOUNT / LEDGERBALANCECONTROL
CUSTCOLLECTIONLETTERLINE / LEDGERIMPORTMODE
CUSTCOLLECTIONLETTERTABLE / LEDGERJOURNALTXT
CUSTINTERESTWAIVELIMIT / LEDGERSYSTEMACCOUNTS
CUSTLEDGER / MAINACCOUNTCATEGORY
CUSTTABLE / MAINACCOUNTTEMPLATE
DIRADDRESSBOOK / OMOPERATINGUNIT
DIRADDRESSBOOKPARTY / PAYMDAY
DIRORGANIZATIONBASE / PAYMDAYLINE
DIRPARTYTABLE / PAYMSCHEDLINE
DIRPERSON / PAYMTERM
DLVTERM / PRICEDISCADMNAME
ECORESCATEGORYHIERARCHYROLE / PRICEDISCGROUP
ECORESCATEGORYHIERARCHYTRANSLATION / PRODUNITTABLE
ECORESSTORAGEDIMENSIONGROUP / PROJCATEGORY
EXCHANGERATE / REASONTABLE
EXCHANGERATECURRENCYPAIR / RETURNACTIONDEFAULTS
EXCHANGERATETYPE / RETURNREASONCODEGROUP
FORECASTMODEL / ROUTEGROUP
INVENTBATCH / ROUTEOPRTABLE
INVENTDIMSETUPGRID / SALESQUOTATIONTYPEGROUP
INVENTITEMGROUP / SMMCAMPAIGNSELECTION
INVENTLOCATION / SMMENCYCLOPEDIAITEMS
INVENTMODELGROUP / SMMSALESUNIT
INVENTSITE / SMMTMCALLLISTTABLE
JOURNALIZINGDEFINITION / STATREPINTERVAL
KANBANQUANTITYPOLICY / VENDBANKACCOUNT
KANBANQUANTITYPOLICYDEMANDPERIOD / VENDGROUP
LEANPRODUCTIONFLOWMODEL / WRKCTRCAPABILITY

RapidStart Services

RapidStart Services can be used to populate basic configuration data records in a new environment, and also some master data (customers and vendors). RapidStart Services cannot be used to import transaction data.

Although you can use RapidStart Services to manage customers and vendors, the templates for customers and vendors are quite large. If the volume of customer and vendor data to be imported is small, you may be able to use the customer and vendor templates in RapidStart Services. However, if you have large lists of customers or vendors, you should consider using Application Integration Framework (AIF).

For more information, see InformationSource.

The following functional areas can be set up by using RapidStart Services:

·  Applicability Model

·  Bank Account Management

·  Base Information (Organization, Ledger Currency)

·  Customer and Price Discount

·  General Ledger Setup

·  Manage Collections

·  Manage Customers

·  Manage Fixed Assets

·  Manage Inventory

·  Manage Product Categories

·  Manage Vendors

·  Organization Information

·  Process Customer Invoices

·  Process Customer Payments

·  Process Vendor Invoices

·  Process Vendor payments

·  Purchase Order Management

·  Purchase Quotation Management

·  Sales Order Management

·  Sales Quote Management

·  Tax

·  Vendor Price and Discount

At the time of writing, the following are the supported tables in RapidStart Services that correspond to the preceding functional areas.

Table name / Table name /
AddressCountryRegionGroupBLWI / InventModelGroup
AssetAcquisitionMethod / InventParameters
AssetActivityCode / InventPosting
AssetBonus / InventSite
AssetBookTable / Ledger
AssetBookTableDerived / LedgerAllocateKey
AssetCondition / LedgerAllocateTrans
AssetDepBookTable / LedgerAllocationBasisRule
AssetDepBookTableDerived / LedgerAllocationBasisRuleSource
AssetDepreciationProfile / LedgerAllocationRule
AssetGroup / LedgerAllocationRuleDestination
AssetGroupBookSetup / LedgerAllocationRuleSource
AssetGroupDepBookSetup / LedgerBalanceControl
AssetGroupDepBookSetupBonus / LedgerGainLossAccount
AssetLedger / LedgerJournalTxt
AssetLedgerAccounts / LedgerParameters
AssetLocation / LedgerSystemAccounts
AssetMajorType / LineOfBusiness
AssetParameters / LogisticsLocation
AssetPropertyGroup / MainAccount
AssetTable / MainAccountCategory
BankCentralBankPurpose / MainAccountTemplate
BankParameters / PaymDay
BankTransactionTypeGroupHeader / PaymDayLine
BankTransType / PaymSched
BankTransTypeGroupDetails / PaymSchedLine
CashDisc / PaymTerm
CurrencyLedgerGainLossAccount / PriceDiscAdmName
CustCollectionLetterLine / PriceDiscGroup
CustCollectionLetterTable / PriceParameters
CustFormletterParameters / PurchParameters
CustGroup / PurchRFQParameters
CustInterest / ReasonTable
CustInterestFee / ReturnActionDefaults
CustInterestVersion / ReturnReasonCode
CustInterestVersionDetail / ReturnReasonCodeGroup
CustInterestWaiveLimit / SalesParameters
CustLedger / SalesQuotationTypeGroup
CustLedgerAccounts / StatRepInterval
CustParameters / StatRepIntervalLine
CustPaymModeTable / TaxAuthorityAddress
CustTable / TaxData
DirPartyTable / TaxExemptCodeTable
DlvMode / TaxGroupData
DlvTerm / TaxGroupHeading
EcoResCategory / TaxItemGroupHeading
EcoResCategoryHierarchy / TaxLedgerAccountGroup
EcoResCategoryHierarchyRole / TaxOnItem
EcoResCategoryHierarchyTranslation / TaxParameters
EcoResProductDimensionGroup / TaxPeriodHead
EcoResProductDimensionGroupFldSetup / TaxReportPeriod
EcoResStorageDimensionGroup / TaxTable
EcoResStorageDimensionGroupFldSetup / VendExceptionGroup
EcoResTrackingDimensionGroup / VendGroup
EcoResTrackingDimensionGroupFldSetup / VendLedger
ExchangeRate / VendLedgerAccounts
ExchangeRateCurrencyPair / VendParameters
ExchangeRateType / VendPaymModeTable
InventDimSetupGrid / VendTable
InventItemGroup / WMSJournalName
InventLocation / WMSParameters

AIF, document services, and custom code

AIF provides the capability to integrate Microsoft Dynamics AX with other systems inside and outside the enterprise. AIF provides this capability by enabling the exchange of data through formatted XML. This formatted XML is referred to as a document, and each document contains data and business logic. Documents are based on a document class and defined by using Microsoft Dynamics AX.