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
![image](http://pp2.s3.amazonaws.com/7c65b333500d4c78/eba225e3ac3c466b821e262b61701cef.png)
VSD: https://docs.google.com/open?
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
![image](http://pp2.s3.amazonaws.com/76942a268a4f45cf/81e70d0b23b74705b5d46929536962ae.jpg)
No Solution yet
Add Tables
![image](http://pp2.s3.amazonaws.com/76942a268a4f45cf/5c914fc159ff4fc393af3cef8e978c92.jpg)
Field Chooser
![image](http://pp2.s3.amazonaws.com/76942a268a4f45cf/93045297fbf94d8eac08734eea1bafa1.jpg)
MainAccount
Field Name | Field Description |
| Chart of accounts |
| Main account |
| Name |
| Main account type |
| Reference ID |
![image](http://pp2.s3.amazonaws.com/76942a268a4f45cf/2a195e814ce74a34944859fb5551e195.jpg)
Sequence:
1. MainAccount – Publish Selected
Result:
Dynamics AX – Main Account
![image](http://pp2.s3.amazonaws.com/76942a268a4f45cf/6cd658a070764ac985b9e7357683e88b.jpg)
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.
Hi Alex,
ReplyDeleteNice work.... Really i appreciate you..
Can you tell me how to tick the "Dont Allow Manual Entry" through excel import.
Thanks
Mansoor
Hi Alex, did you tried to import Main Accounts' Totals to Excel? ( DimensionAttributeValueTotallingCriteria ). It doesn't work for me...
ReplyDeleteHi Mansoor,
ReplyDeleteYou can control from the Main account setup form, where you can avoid the manual entry process.
Thanks,
Nvn
Thanks a lot, this's saved my life time, though I couldn't find the way to import data about Do not allow manual entry CheckBox. Do you know how to do that?
ReplyDeleteHi Everyone,
ReplyDeletevery useful topic.
I have a problem with publishing data via excel add-in.
I have 5 columns in excel: Chart of Account/Main account/Name/Main account type/Reference ID
When I publish data (using pubblish button) account Name changes, Main account also but I all my new accounts don't pop up in the AX. All Main accounts numbers that I changed in excel haven't change in AX.
Do you have any idea why I can't do this?
Best regards,
Maciej
Nice blog. Can you tell me if the Main accounts and financial dimensions data model depicted in your Visio file is AX 2012 R2? We are using 2012 R2 and see a table LedgerStructure which seems to do the work of the LedgerChartOfAccountStructure table.
ReplyDeleteHi Everyone,
ReplyDeleteThanks Alex for sharing this. It was really helpful.
I have a requirement of adding two new fields (start date and end date) to the SalesTable table. I was able to create these two fields successfully and also I am able to create new records with these two fields from sales order form. When I am trying to create a new record from the excel file, record is getting created successfully but start date and end date fields are not getting updated.
Is there anything I am missing?
Hi guys, I try to publish it, but I won't appear on AX. What could be missing?
ReplyDeleteThank you for sharing such beautiful information with us . I hope you will share some more info about Tax. Please keep sharing .
ReplyDeleteBusiness Accounting Services