Tuesday, January 17, 2012

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

image


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

image

Document Data Sources

Dynamics AX Error

image

No Solution yet

Add Tables

image

Field Chooser

image

MainAccount

Field Name

Field Description

Chart of accounts

Main account

Name

Main account type

Reference ID

image

Sequence:

1. MainAccount – Publish Selected

Result:

Dynamics AX – Main Account

image

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.

8 comments:

  1. Hi Alex,

    Nice work.... Really i appreciate you..

    Can you tell me how to tick the "Dont Allow Manual Entry" through excel import.

    Thanks
    Mansoor

    ReplyDelete
  2. Hi Alex, did you tried to import Main Accounts' Totals to Excel? ( DimensionAttributeValueTotallingCriteria ). It doesn't work for me...

    ReplyDelete
  3. Hi Mansoor,

    You can control from the Main account setup form, where you can avoid the manual entry process.

    Thanks,
    Nvn

    ReplyDelete
  4. 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?

    ReplyDelete
  5. Hi Everyone,

    very 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

    ReplyDelete
  6. 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.

    ReplyDelete
  7. Hi Everyone,

    Thanks 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?

    ReplyDelete
  8. Hi guys, I try to publish it, but I won't appear on AX. What could be missing?

    ReplyDelete