Database Diagrams for Microsoft Dynamics AX 2012 R2

Microsoft has recently launched a new website that hosts 30 database entity relationship diagrams for tables of Microsoft Dynamics AX 2012 R2...Read more http://www.axpulse.com/database-diagrams-for-ax-2012-r2/

Enabling Withhold tax functionality in AX 2012

Some of you might know that Withhold Tax is a country specific (GB, TH, SA, IE) functionality (except for limited functionality available on AP side out of the box) in AX 2012 like AX2009. In AX 2012, if you have opened up an LE with address associated with specific countries and WHT configuration key is on, you will be able to use WHT both on vendor and customer sides. However, there is a price to pay when you want to use WHT functionality in countries other than what MS has already given the localization for. For example, if you want to use WHT functionality for a customer in Egypt, besides enabling the WHT configuration key, open up an LE with Egypt address, you also need to set CountryRegionCodes property for various EDT, table, form control objects with the country code for Egypt. To enable WHT specific business logic, validations in code, you have to add the specific region code in statements like this:

if (SysCountryRegionCode::isLegalEntityInCountryRegion([#isoTH, #isoSA, #isoGB, #isoIE, ..)
{

}

Overall, it is really hectic to get the above updated on number of places in the AOT, otherwise you won't be able to leverage the complete WHT functionality in AX 2012.

ISV vertical configuration using Rapid Start Services

In an effort to push data to custom developed functionality (could be an ISV solution) in AX 2012 through rapid start services, I found rapid start really useful and extensible. In order to prove the concept, I used a sample scenario where a health care customer needs to fill in the data into Patients table through rapid start. I created couple of relevant objects in AOT as seen below:

In Rapid Start, I imported the metadata from my current AX instance using the Administer page. In the Design area, created a new functional area in rapid start with a question group and one question of type "Table" in it. As I had to load the Patients data into PatientTable, I used the Table type question that gave me the option to generate a template. Next I published the functional area.

In order to use the functional area, I had to create a template. So created a template named "Patients" and associate the functional area with it. Once this is done, created a new project in Configure area of rapid start as shown below:

Next step was to fill in the base information for my LE as shown below:

After that I was presented with the question that was created earlier for Patients data. On this page, I downloaded the template, filled it with necessary data and uploaded it.

Now the project was 100% complete and the configuration was ready to be loaded into target AX deployment. See the log results below.

For cross verification, below is what I got in the AX Patients form as expected :)

 

Partitions in AX 2012 R2

With the announcement of MS Dynamics AX 2012 R2 release on December 1st, 2012, MS has given a powerful feature in R2 called data partitions. Now in AX, there is a element above the company called "Partition". Just like with every new AX installation there is a default company "DAT", now there will also be a default partition called "Initial". This is really powerful feature as the companies now have the leverage to use an installed AX instance across multiple deployment sites. Couple of features associated with partitions are:

• System data like batch, AIF, workflow is shared between partitions. This has been achieved by introducing a new table property "SaveDataPerPartition" on these tables.
• Business data like UoM, Address Book is shared among companies within a partition only. A company can be associated with multiple partitions with the same name.
• Users only belong to a particular partition.
• Some drawbacks like intercompany does not work across partitions and companies across partitions can not be merged are also there. Cross company queries might not work across partitions.

Unpublish standard template or functional area in rapid start services for AX 2012

I recently come across a situation where I had to modify the default data given with Account Categories template in Rapid Start Services. Even with the administrative rights for the user, I was not able to unpublish the template or functional area (GL in this case). I then came to realize that MS has not given permissions to update the contents of default (hidden) templates like account categories. One can unpublish his own custom template of functional area but not the ones that come as default and for which there is no question present in the question group.

AX 2012 Data Migration Framework (beta) Configuration and Execution

Using the data migration tool is fairly simple; however, an understanding of different business entities and their data model is highly required to successfully migrate the data.

In this post, I will take a sample scenario to walk through the configuration and setup of data migration framework. We will consider couple of entities to migrate the data. Let’s assume that we need to migrate customer master data from a source system to AX 2012. Follow these steps to achieve the objective:

Navigate to Data Migration Framework module in AX 2012 application workspace. Open Setup>Target entities form.

The target entities out of the box available are listed in this form. You can select an entity and modify its staging to target mapping if you would like to (in case you have customizations in AX). Here, we are going with what is available out of the box. You can also validate the mapping after modifying it, view the target entity hierarchy, and target fields for any selected entity. You can also create a new record for a custom target entity.

Now we have to define the data format for our source data. Open Source data formats from Setup area and create a new record as shown below:

I have selected File format as Delimited with first row as header and values are separated with comma. Also marked the source file to be Unicode. It is often the case that primary entity might have multiple sub records associated with it. For example, a customer might have two addresses. In such situation, you can use the Role Separator to distinguish these multiple records.

Now the next step is to create processing group (Common>Processing group) for customer data migration. By definition, processing group is a logical grouping of entities that need to be imported in sequential order.

 

Add the entities into the processing group.

At this point, let’s use the generate source file wizard to generate the source customer file for import. You can also create your own source file as per the format specified in Source data formats.

Click Next. On the screen below, you have the option to only use the fields present in source for customer import or include other fields.

Click Generate sample file and the wizard will provide you a file as shown below. Save the file in a folder as later we will need to refer to that location.

Once the file is generated and saved, put some data in it and provide its location in the Sample file path field.

Our next step is to generate the source to staging mapping. Note that you get the infolog “Mapping has completed successfully”. At this point, you can also modify the source to staging mapping. In our case, we don’t have to.

Target mapping with corresponding staging table can also be viewed. You also have the option to run the business logic or validations in entity’s insert update or validateWrite methods at the time of import. Data preview for staging table can also be analyzed in the preview pane. This is how the data will look like in the staging table.

Next step is to push the data from source file to staging table.

A dialog will appear with auto generated job Id. Click OK and Staging data execution screen will appear.

Click Run. You can also run this job on AOS if you would like to. You can also preview the staging data here. After the job is executed, an infolog will appear mentioning that a record has been successfully created in the staging table.

Once the data is in staging table, it just the matter of pushing it to target (AX) entity’s table.

A dialog will appear asking for the job that was executed previously to push the data from source to staging. Select ‘Customer-6’ in this case.

Click OK. Target data processing screen will appear.

Click Run. After the job is executed, an infolog will appear mentioning the record is created in the target entity.

You can cross verify in ‘CustTable’ and corresponding staging table ‘DMFCustomerEntity’ for customer entity.

The framework gives really cool feature of exception logging. You can see any errors that might have occurred during the source-staging or staging-target job execution.

With this I close the post and hope it will give you a kick start leveraging other really powerful features of DMF.

AX 2012 Data Migration Framework (beta) Installation

In this post, I would like to shed some light on the Data Migration Framework recently published by Microsoft as beta. I will start with installation and configuration of the tool and later would like to share the experience of using the tool using development environment.

Open up the installation wizard using Setup.exe file that comes up with the tool.

As I am installing the tool on development machine, I have selected both Client and Server components. If you have AOS on different server and client on another, you will have to install the components separately on respective machines.

Select the file location where the setup has to put the files. I will leave it as default selected.

The installation wizard is now completed and setup log file is opened. Good to take a look at the setup log file at the end.

Now you have to update the buffer size in AOS configuration utility. The reason mentioned by MS is that the staging table (for corresponding target entity) used by the tool is highly normalized and the migration process takes extra processing capacity, so recommended to have the buffer size increased from default 24K to 48K. Please do remember to revert it back to 24K after the data migration is done.

After the AOS is restarted, you will have to import the model file (DataMigrationFramework.axmodel) found in “C:\Program Files\Microsoft Dynamics AX 2012 Data Migration Framework (Beta)” to target AX 2012. I will use AX 2012 Management Shell to import the file:

As recommended, restart the AOS. Open AX 2012 client and you will be presented with a checklist as we just imported a new model into model store. Again as suggested by AxUtil, we will select the option of “Compile and synchronize” from the checklist and click OK. This will take some time, so have a cup of tea until it gets finished (I had my lunch thoughJ).

After the compile and synchronize is completed, you should see a new module in AX named “Data migration framework”. In my next post, I will take a sample scenario to start configuring the framework and use it to import some data from a source system.