Import Ledger transactions/balances using Excel Add-in – AX 2012

Purpose:The purpose of this document is to illustrate how to use Microsoft Dynamics AX 2012 Excel Add-in for import of ledger transactions.
Challenge: Data model changes in Microsoft Dynamics related to high normalization and introduction of surrogate keys made some imports more complex. In fact the data model forming General Journal was not dramatically changed and import principle remains the same – populate the journal and then post the journal. However some information which is usually automatically generated in Microsoft Dynamics AX 2012 Rich Client by means of number sequences such as voucher number will have to be provided.
Solution: Microsoft Dynamics AX 2012 ships with General Journal AIF Web Service (LedgerGeneralJournalService) which can be used in integration scenarios. Also General Journal AIF Web Service can be used in Excel for import of ledger transactions.
In order to import ledger transactions using Excel the mentioned AIF Web Service will be used.
Assumption: The assumption is that appropriate reference data such as main accounts, etc. was created in advance.
Data Model:

Table Name	Table Description
LedgerJournalTable	The LedgerJournalTable table contains all the defaulting and state information pertaining to a single journal. The transaction details of a journal are managed in the LedgerJournalTrans table.
LedgerJournalTrans	The LedgerJournalTrans table contains the transaction detail information that pertains to a single journal. The individual transaction lines are also referred to as voucher lines. The journal is a record in the LedgerJournalTable table.

Data Model Diagram:

Walkthrough:
Connection

Add Data

Field Chooser

Accounting structure

LedgerJournalTable

Field Name	Field Description
	Journal batch number
	Name
	Description

LedgerJournalTrans

Field Name	Field Description
	Journal batch number
	Voucher
	Date
	Company accounts
	Account type
	LedgerDimension
	LedgerDimension.MainAccount
	LedgerDimension.Department
	Description
	Debit
	Credit
	Currency

Sequence:
1. Publish All
Result:
Dynamics AX – General Journal

Dynamics AX – General Journal lines

Dynamics AX - Posting

SQL Trace:

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 ledger transactions into Microsoft Dynamics AX 2012. General Journal AIF Web Service (LedgerGeneralJournalService) can be used in Excel for import of ledger transactions. Excel template can be created and used for import of ledger transactions.
Imparted from Here

Find out which version of Microsoft Dynamics AX you are using [AX 2012]

In some cases, the tasks that you can perform in Microsoft Dynamics AX depend on the version of the product that you are using.

This topic explains how to identify which version of Microsoft Dynamics AX 2012 R2 or Microsoft Dynamics AX 2012 R3 that you are using. For information about earlier versions of Microsoft Dynamics AX, see Overview of Microsoft Dynamics AX build numbers.

1. To find the version number, in the upper-right corner of any page in Microsoft Dynamics AX, click the Help icon, and then click About Microsoft Dynamics AX. The version number is the application number, for example 6.2.1000.4051.
   • If the application number starts with 6.3, you are using Microsoft Dynamics AX 2012 R3.
   • If the application number starts with 6.2, you are using a version of Microsoft Dynamics AX 2012 R2. Make a note of the number and go to step 2.
2. Refer to the following table to determine which version of AX 2012 R2 corresponds to the application number that you wrote down in step 1.

Application number	Version
6.2.1000.4051	Cumulative update 7 for Microsoft Dynamics AX 2012 R2
6.2.1000.1437	Cumulative update 6 for Microsoft Dynamics AX 2012 R2
6.2.1000.156	Cumulative update 1 for Microsoft Dynamics AX 2012 R2

AX 2012 – Data Migration Framework: Step-by-step setup

of course you need to download the Data Migration Framework from InformationSource. After having downloaded the Data Migration Framework, the next step is to extract the contents of the Zip file.
At this point you will want to launch the Setup for the Data Migration Framework. 

Moving from here a decision must be made on what is to be installed. 

Since I’m making the choice to install both Server and Client on a development instance of AX 2012 I will leave both checked. It’s worth to note the message contained within the first dialog box, around the fact that the server part must be installed on an AOS.
Moving along we must select the path in which the setup will install the application files to. 

After this, you should reach the final screen, indicating that the setup program has completed it’s task. 

When clicking on the finish button, the installation log file opens up for you. It’s worth scanning through to see if there are any errors. 

Now that we have completed the install, we need to double check that we have the Data Migration Framework bits ready for our use. Do this by browsing to the location in which you had chosen previously. In doing so, you should see a folder with similar contents as shown below. 

Now that we have verified that install completed successfully, lets move towards a point in the install documentation on TechNet. 

Specifically there is a warning, that states: “Because the staging environment is highly normalized and may require significant processing bandwidth, we recommend that you increase the Maximum buffer size setting for your environment while you migrate data. Use the Server configuration utility to set the value.” Lets take the advice of Microsoft and do just that.
This means you need to launch the server configuration utility, go to the database tuning tab, and change the buffer size from 24, to say 48. 

You will notice that in making such a change Microsoft warns of doing so. Since we are doing this on purpose, we can ignore this warning. However it’s valid to point out that after your done with the data migration framework, it’s best to change this setting back to 24. Further, you might find that 48 is still not big enough and you will need to increase further.
After doing this, and clicking apply you should take the option of allowing the configuration utility to restart the Microsoft Dynamics AX 2012 AOS instance. 

Now we have to move to importing the model file that came as part of the install bits for this offering. We perform this install either by using the PowerShell command console, or theAXUtil application. I’ve made the choice of using the AXUtil application. 

Finally after a successful install of the model file, to your target instance of Microsoft Dynamics AX 2012, you should launch AX. In doing so, it will detect a new model as been installed, and ask how you wish proceed. For this, we want to make the choice of ‘Compile and synchronize’ and then click ok. 

After the successful completion of the full compile and synchronization of the database, next action to take is a full CIL compile. This should take roughly 15 +/- minutes to complete. After completing this step, your instance of AX 2012 has the newly released Data Migration Framework deployed and setup. You should see the something similar to the following when done. 

Now that we have the Data Migration Framework deployed and setup.

Imparted from here

How to: Remove (Uninstall) a Model [AX 2012]

You can remove a model from a model store. However, it is recommend remove a model only if you want to permanently delete it.

You can use either Windows PowerShell cmdlets or the AXUtil command-line utility to perform this task.

Caution

Deletion of a model or layer may cause significant loss of business data, and also loss of metadata. When you run a command to delete a model, we recommend that you use the –Details or /verbose parameter to list the elements that may be deleted. In this way, you can determine the effect of the deletion on your environment. ·    Business data may be lost if the database schema is changed. The database schema is changed when tables or fields are removed. ·    Element IDs may be lost. If element IDs are lost, existing business data may become invalid. ·    Orphaned metadata elements in other models or layers are deleted.

If you want to upgrade a model or replace a model with a newer version, we recommend that you import the model over the existing model. For more information about how to import models, see How to: Export and Import a Model. We recommend this approach, because element IDs and element handles are assigned at installation. Therefore, if you delete a model, and then reimport that model or a newer version of it, element IDs and element handles are randomized, and data integrity can be affected. For more information about element IDs and element handles, see Maintaining Installation-Specific Element IDs and Element Handles.

Validate permissions

• Validate that you have appropriate permissions to work with the model store:
• Administrative permissions on the local computer
• System Administrator rights in Microsoft Dynamics AX
• In Microsoft SQL Server:
• Membership in the Securityadmin server role on the SQL Server instance
• Membership in the db_owner role in the Microsoft Dynamics AX database

Uninstall a model (Windows PowerShell)

1. On the Start menu, point to All Programs, point to Administrative Tools, and then click Microsoft Dynamics AX Management Shell.
2. At the Windows PowerShell command prompt, PS C:\>, type the following command, and then press ENTER.

Windows PowerShell

Uninstall-AXModel -Model <Modelname>

This command uninstalls the specified model. By default, a confirmation request is displayed.

For more information, see Uninstall-AXModel.

Uninstall a model (AXUtil)

1. On the Start menu, click Command prompt.
2. Navigate to the directory for the management utilities. Typically, the location of this directory is %ProgramFiles%\Microsoft Dynamics AX\60\ManagementUtilities.
3. At the command prompt, type the following command, and then press ENTER.

4.  axutil delete /model:<name>

Modules names changes in MS Dynamics AX 2009 Vs. AX 2012

MODULES IN AX 2009	MODULES IN AX 2012
Inventory Management	•       Inventory Management •       Product Information Management
Accounts Payable	•       Procurement and Sourcing •       Accounts Payable
Accounts Receivable	•       Sales and Marketing •       Accounts Receivable
General Ledger	•       General Ledger •       Budgeting •       Fixed Assets •       Compliance and Internal controls
Cost Accounting	•       Cost Accounting
Bank	•       Cash and Bank Management
CRM	•       Moved to Sales and Marketing module
Master Planning	•       Master Planning
Production	•       Production control
Product Builder	•       Moved to Product Information Management
Shop floor Control	This Module is split into three and can be accessed from Home, Human resources and Production Control Modules. •       Time and Attendance     – Human resources. •       Clock In and Out – Home •       Manufacturing execution – Production        control.
Human Resource	•       Human Resources
Questionnaire	•       Moved to Home
Expense Management	•       Travel and Expense
Projects	•       Project Management and Accounting (PSA)
Service	•       Service Management
Basic	•       Organizational Administration
Administration	•       System Administration

Imparted from

Dynamics AX functional consultant - job

Dynamics AX functional consultant

Shabaka Net - Riyadh, Saudi Arabia

Job Description

To prepare the functional analysis, gap fit with Dynamics AX, determining modification requests to Dynamics AX and writing specifications or their development.

To implement Dynamics AX from A to Z on client site.

Skills

· At least two years of working experience with Dynamics AX Finance, Trade and Logistics is required with experience doing hands-on Configuration of Dynamics AX Finance, Trade & Logistics, Production / Retail / Project accounting as a functional.

· Excellent verbal and written communications skills.

· Ability/Experience to establish priorities, work independently, and proceed with objectives without supervision.

Education

Business or Information Technology / engineering or equivalent.