Dynamics AX 2012 Products Import using Excel Add-in

Dynamics AX 2012 Excel Add-in – Products Import

Purpose: The purpose of this document is to illustrate how to use Microsoft Dynamics AX 2012 Excel Add-in for import of products.

Challenge: Data model changes in Microsoft Dynamics related to high normalization and introduction of surrogate keys made import of products more complex. In order to create a product in Microsoft Dynamics AX 2012 both product definition and released product information will have to be provided. Please note that in Dynamics AX 2012 Rich Client product definition is automatically created when released product is created using decentralized approach.

Solution: Microsoft Dynamics AX 2012 ships with Item AIF Web Service (InventItemService) which can be used in integration scenarios. However Item AIF Web Service can’t be used in Excel at the moment for import of products. In order to import a product with product definition information using Excel the appropriate tables will be used.

Assumption: The assumption is that appropriate reference data such as item groups, etc. was created in advance.

Data Model:

Table Name	Table Description
EcoResProduct	The EcoResProduct table stores products and is the base table in the products hierarchy.
EcoResProductMaster	The EcoResProductMaster table stores product masters.
EcoResProductIdentifier	The EcoResProductIdentifier table contains a product identification that is available for users.
EcoResDistinctProduct	The EcoResDistinctProduct table stores products.
EcoResDistinctProductVariant	The EcoResDistinctProductVariant table stores product variants.
EcoResProductDimensionGroup	The EcoResProductDimensionGroup table contains information about a dimension group.
EcoResProductDimensionGroupProduct	The EcoResProductDimensionGroupProduct table stores information about relationships between products and dimension groups.
EcoResColor	The EcoResColor table stores color names.
EcoResSize	The EcoResSize table stores size names.
EcoResConfiguration	The EcoResConfiguration table stores configuration names.
EcoResProductMasterColor	The EcoResProductMasterColor table stores information about colors assigned to product masters.
EcoResProductMasterSize	The EcoResProductMasterSize table stores information about sizes that are assigned to product masters.
EcoResProductMasterConfiguration	The EcoResProductMasterConfiguration table stores information about configurations assigned to product masters.
EcoResProductVariantColor	The EcoResProductVariantColor table stores information about the colors that are assigned to product variants.
EcoResProductVariantSize	The EcoResProductVariantSize table stores information about the sizes that are assigned to product variants.
EcoResProductVariantConfiguration	The EcoResProductVariantConfiguration table stores information about the configurations that are assigned to product variants.
EcoResProductMasterDimensionValue	The EcoResProductMasterDimensionValue table is the base table in the product model dimension hierarchy.
EcoResProductVariantDimensionValue	The EcoResProductVariantDimensionValue table is the base table in the product variant dimension hierarchy.
EcoResProductDimensionAttribute	The EcoResProductDimensionAttribute table contains definitions of product dimension attributes (categories).
EcoResInstanceValue	The EcoResInstanceValue table contains the definitions of the instances of the components or products.
EcoResProductInstanceValue	The EcoResProductInstanceValue table contains definitions of values for the instances of attributes of a product.
InventTable	The InventTable table contains information about items.
InventTableModule	The InventTableModule table contains information about purchase, sales, and inventory specific settings for items.
InventItemLocation	The InventItemLocation table contains information about items and the related warehouse and counting settings. The settings can be made specific based on the items configuration and vary from warehouse to warehouse.
InventItemSalesSetup	The InventItemSalesSetup table contains the default settings for items, such as site and warehouse. The values are related to sales settings.
InventItemInventSetup	The InventItemInventSetup table contains the default settings for items, such as site and warehouse. The values are related to inventory settings.
InventItemPurchSetup	The InventItemPurchSetup table contains the default settings for items, such as site and warehouse. The values are related to purchase settings.
InventItemSetupSupplyType	The InventItemSetupSupplyType table contains information about the sourcing of items.
InventDim	The InventDim table contains values for inventory dimensions.
InventDimCombination	The InventDimCombination table contains variants of items. The variants are created as product variants that are based on product dimensions such as size, color, and configuration. These variants are replicated to the legal entity.

Data Model Diagram:

Products and Released Products

VSD: https://docs.google.com/open?id=0B3rbAZy5q2ExMWUzOGM4ZDEtNzE2OS00Yzk2LWIxYzEtOTkzNTU3M2RiZTMw

Red area highlights tables forming Products and Product Masters data model

Green area highlights tables forming Released Products data models

Blue area highlights tables implementing Product Dimensions data model

Walkthrough:

Connection

Add Data

Dynamics AX Error

Solution

- Add replacement key to DocuValue table

Dynamics AX Error

No Solution yet

Add Tables

Field Chooser

EcoResProduct

Field Name	Field Description
	InstanceRelationType
	Product number
	Product type
	Configuration technology

EcoResProductIdentifier

Field Name	Field Description
	Product number
	Products.Product number

EcoResProductDimensionGroupProduct

Field Name	Field Description
	Product dimension group.Name
	Product.Product number

InventTable

Field Name	Field Description
	Item number
	Product.Product number
	Item type
	Search name

Sequence:

1. EcoResProduct – Publish Selected

2. EcoResProductIdentifier – Publish Selected

3. EcoResProductDimensionGroupProduct – Publish Selected

4. InventTable – Publish Selected

Result:

Dynamics AX – Released Product

SQL Trace:

Product

- EcoResProduct (INSERT)

- EcoResDistinctProduct (INSERT)

- EcoResProductIdentifier (INSERT)

- EcoResProductTranslation (INSERT)

Product Master

- EcoResProduct (INSERT)

- EcoResProductMaster (INSERT)

- EcoResProductIdentifier (INSERT)

- EcoResProductDimensionGroupProduct (INSERT)

- EcoResProductMasterModelingPolicy (INSERT)

- EcoResProductTranslation (INSERT)

Product Master – Product Dimension (Size)

- EcoResProductMasterDimensionValue (INSERT)

- EcoResProductMasterSize (INSERT)

- EcoResSize (INSERT)

Product Master – Product Variant

- EcoResProduct (INSERT)

- EcoResDistinctProduct (INSERT)

- EcoResDistinctProductVariant (INSERT)

- EcoResDistinctProductTranslation (INSERT)

- EcoResProductVariantDimensionValue (INSERT)

- EcoResProductVariantSize (INSERT)

Released Product – Product

- EcoResProduct (INSERT)

- EcoResDistinctProduct (INSERT)

- EcoResProductIdentifier (INSERT)

- EcoResProductTranslation (INSERT)

- InventItemSalesSetup (INSERT)

- InventItemInventSetup (INSERT)

- InventItemPurchSetup (INSERT)

- InventTable (INSERT)

- InventTableModule [Sales] (INSERT)

- InventTableModule [Invent] (INSERT)

- InventTableModule [Purch] (INSERT)

- InventItemLocation (INSERT)

- InventItemSetupSupplyType (INSERT)

Released Product – Product Master

- EcoResProduct (INSERT)

- EcoResProductMaster (INSERT)

- EcoResProductIdentifier (INSERT)

- EcoResProductDimensionGroupProduct (INSERT)

- EcoResProductMasterModelingPolicy (INSERT)

- EcoResProductTranslation (INSERT)

- InventItemPurchSetup (INSERT)

- InventItemInventSetup (INSERT)

- InventItemSalesSetup (INSERT)

- InventTable (INSERT)

- InventTableModule [Sales] (INSERT)

- InventTableModule [Invent] (INSERT)

- InventTableModule [Purch] (INSERT)

- InventItemLocation (INSERT)

- InventItemSetupSupplyType (INSERT)

Released Product – Product Dimension

- EcoResProductMasterDimensionValue (INSERT)

- EcoResProductMasterSize (INSERT)

- EcoResSize (INSERT)

Release Product – Released Product Variant

- EcoResProductVariantDimensionValue (INSERT)

- EcoResProductVariantSize (INSERT)

- EcoResSize (INSERT)

- InventDim (INSERT)

- InventDimCombination (INSERT)

Summary: For the purposes of small or medium data migration (data conversion) where performance is not a concern Excel can be used for import of products into Microsoft Dynamics AX 2012. Although Item AIF Web Service (InventItemService) can’t be used at the moment in Excel for import of products, appropriate tables can be used instead. Excel template can be created and used for import of products.

Author: Alex Anikiev, PhD, MCP

Tags: Dynamics ERP, Dynamics AX 2012, Excel, Dynamics AX 2012 Excel Add-in, Data Import, Data Conversion, Data Migration, Application Integration Framework, Products.

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. It’s recommended that all Data Model changes introduced as a part of this demonstration will be removed once you complete data import exercise.