Dynamics AX 2012 Main Accounts Import using Excel Add-in

Dynamics AX 2012 Excel Add-in – Main Accounts Import

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

Challenge: Data model changes in Microsoft Dynamics related to high normalization and introduction of surrogate keys made import of main accounts more complex.

Solution: Microsoft Dynamics AX 2012 ships with Chart Of Accounts AIF Web Service (ChartOfAccountsService) which can be used in integration scenarios. However Chart Of Accounts AIF Web Service can’t be used in Excel at the moment for import of main accounts. In order to import a main account using Excel the appropriate tables will be used.

Assumption: The assumption is that appropriate reference data such as main account categories, etc. was created in advance.

Data Model:

Table Name	Table Description
Ledger	The Ledger table contains information which is used for the accounting transaction of a company. The chart of account, fiscal calendar, and accounting currency of a transaction are decided by the Ledger record.
LedgerChartOfAccounts	The LedgerChartOfAccounts table represents a logical grouping of account and dimension combinations when tracking financial data.
LedgerChartOfAccountsStructure	The LedgerChartOfAccountsStructure table specifies which account structures are used by the chart of accounts.
MainAccountCategory	The LedgerAccountCategory contains the financial categories that accounts fall into. The categories are assigned to accounts to allow easier grouping of accounts on financial statements.
MainAccount	The MainAccount table contains account values that do not include dimension attribute values.
MainAccountLegalEntity	The MainAccountLegalEntity table contains properties for main accounts which are only valid in the context of a legal entity.
DimensionHierarchy	The DimensionHierarchy table contains information about a dimension set and an ordered set of dimension attributes.
DimensionHierarchyLevel	The DimensionHierarchyLevel table represents the usage of a dimension attribute in a dimension hierarchy.
DimensionAttribute	The DimensionAttribute table contains the dimension definition. It is an entity-backed list for use in the dimension subsystem.
DimensionAttributeValue	The DimensionAttributeValue table contains the values, such as dimension codes, for a specific dimension.
DimensionAttributeLevelValue	The DimensionAttributeLevelValue table contains the usage of a dimension attribute value in a given dimension hierarchy.
DimensionAttributeValueGroup	The DimensionAttributeValueGroup table represents a group of values for the specific dimension set.
DimensionAttributeValueGroupCombination	The DimensionAttributeValueGroupCombination table represents the usage of dimension code groups in a dimension code combination. This allows a group to be reused in multiple combinations.
DimensionAttrValueCOAOverride	The DimensionAttrValueCOAOverride table holds the ActiveFrom, ActiveTo, IsSuspended and EmplId overridden values for the dimension attribute values in the context of a chart of accounts.
DimensionAttrValueLedgerOverride	The DimensionAttrValueLedgerOverride table holds the ActiveFrom, ActiveTo, IsSuspended, and EmplId overridden values for the dimension attribute values in the context of a ledger.
DimensionAttributeValueCombination	The DimensionAttributeValueCombination table contains information about accounts and various dimensions combinations that are used. Anything that uses dimensions will hold reference to a record on this table.

Data Model Diagram:

Main accounts and Financial dimensions

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

Red area highlights tables forming Chart of Accounts and Main Accounts data model

Green area highlights tables forming Chart of Accounts and Accounting Structures data model

Blue area highlights tables implementing Financial Dimensions data model

Walkthrough:

Connection

Document Data Sources

Dynamics AX Error

No Solution yet

Add Tables

Field Chooser

MainAccount

Field Name	Field Description
	Chart of accounts
	Main account
	Name
	Main account type
	Reference ID

Sequence:

1. MainAccount – Publish Selected

Result:

Dynamics AX – Main Account

SQL Trace:

Main Account

- MainAccount (INSERT)

- MainAccountCategory (INSERT)

- DimensionAttributeValueCombination (INSERT)

Dimension

- DimensionAttribute (INSERT)

- DimensionAttributeTranslation (INSERT)

- FinancialTagCategory (INSERT)

- DimensionAttributeDirCategory (INSERT)

Dimension Value

- DimensionFinancialTag (INSERT)

- DimensionAttributeValue (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 main accounts into Microsoft Dynamics AX 2012. Although Chart Of Accounts AIF Web Service (ChartOfAccountsService) can’t be used at the moment in Excel for import of main accounts, appropriate tables can be used instead. Excel template can be created and used for import of main accounts.

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, Main accounts.

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.