Synchronization problems in AX 2012 R3; Try disable track changes.

If you are installing a ISV solution into a Contoso environment, and you suddenly see that you cannot synchronize, and you cannot see why:

Here is a tip that works for me;

Disable the track changes in the specified tables;

Then the synchronization is able to perform. After synchronization you can enable it again.

If you want to switch on/off in SQL, then this is the script for Switching off;


To switch them on again;


Happy Daxing !

How to evaluate Dynamics AX ISV solutions

Dynamics AX 2012 R3 have truly evolved into an enterprise solution, and the functional width solves most requirements that any customer “really need”. But there is always the 80% / 20% rule that applies and there are requirements that is not solved by the standard Dynamics AX. We often see extended domain specific requirements in relation to finance, sales, procurement, logistics and production. To solve this, customers have the option to create customizations or to try to find VAR/ISV solutions that solves this. (VAR= Value Added Reseller. ISV =Independent software vendor) Creating your own large customizations can result in high risks, and the option to buy a “ready to use” ISV solution then becomes interesting.

What I wanted to give to the Dynamics Community is my list of how I evaluate ISV-solutions as a VAR. I basically just use a word document with a set of chapters, and the topics to evaluate here is from a consulting perspective. The idea is to have a formalized way of making evaluations, and also use the ISV/representatives to fill in the information for you.

1. Executive summary
{Yup. There is always someone to report to. }

2. Product Introduction
{Then I write a brief introduction to the product, describing the overall area that it solves.}

3. Background for evaluation
{Here I write why this product was evaluated in the first place.}

4. Vendor/Distributor information – general
{Then I record some general information about the company that offers the solution. Just to be sure that the company behind the solution will exists in the future. The following table can be used}

Company name
Sales manager
Turnover 2014
Results 2014
Number of employees
Number of customers
Number of references

5. Vendor/Distributor information – product

{The ISV-“mothership” may be large, but it is interesting also to check out the team that is organized around the product. I therefore have a secondary evaluation around the team developing and maintaining the ISV solution}

Product manager
Product turnover
Number of customers
Number of developers

100% dedicated

Part time dedicated

Number of consultants

100% dedicated

Part time dedicated

Support resources/routines

6. Pricing

{Describing product pricing, enhancement and also flexibility. Also include a case study with implementation. }

7. Marked/Cost savings potential
{ Describing the current marked potential, also reflect according to existing customer base. I also use a calculation sheet to show investment, potential, margins, number of sales each year, training costs, start up issues etc}

8. Known Competitors
{Describing any known competitors to the ISV solution and what the main advances this product have.}

9. Versions and language
{Describing supported versions and language of Dynamics AX. I also want to record how support is handled as new versions and upgrades go along}

10. Access to software
{Describing how the product is accessible from the ISV and how it is distributed}

11. Technical Installation guide

{Describing the quality of the installation guide, and I also try the installation to check how easy it is}

12. Application setup guide
{Describe the quality of the application guide, and I also try the application to check that it actually work}

13. Scenario testing
{In this chapter, we will test the different functional aspects of the product. The processes that is tested is the most common scenarios that we expect and have experienced in the field. Normally divided into a section per feature.}

14. Solution footprint
{The solution footprint is very important to evaluate, because this tells us how costly upgrades and applying hotfixes will be. We want to see high footprint on SYS elements for Dynamics AX. A way to easily evaluate this, is to count the number of SYS overlaying’s that exists and how many new elements that have been introduced. I therefore use a table like this}

{If there is a high number of SYS objects customized, I know that cumulative updates from Microsoft will be more costly.}

15. Best practice deviations
{To evaluate the coding quality, then evaluating all best practice deviations that can be found. Dynamics AX have building tools for this, and if there is a high amount is BP errors and warnings, it means that the code quality it not where it should be. }

Also get any documentation if the product have a CfMD certification (Certified for Microsoft Dynamics)

16. Support and training
{Describe how the product is supported from the vendor. There will always be questions and issues, so we need to know how this is handled. Often ISV solutions are purchased from VAR’s, and then it is good to know how this financially is handled. Also if there are any training included in the offering.}

17. Internal requirements
{Describe needed competence and needed training needed internally to implement, deliver and use this ISV-solution. }

18. Road map
{Show what plans there are for the product, what their priorities, what are their policies around upgrades, etc}

19. Marketing
{If you are a VAR, that want’s to include a ISV solution offering, it is important to understand how the ISV will support you in promoting the solution. Show what marketing material the ISV have and how you can use it. Do they have marketing insight they in the local market. Also find out how the ISV will support and drive market campaigns, participate in events, demos, etc with people and or marketing funds (money)}

ISV solutions play a big role in unlocking all of the possibilities that Dynamics harnesses. When analyzing your Dynamics ERP investment, it’s imperative that you also analyze your ISVs so that you can make the best decision for your business, both in the short and long-term. These topics will lead you toward making better evaluations while looking for an ISV solution. Feel free to use them, and extend them when needed.

AX7/AX 2012 Retail Omni-channel Hub-and-Spoke Architecture

The simplest definition of “hub and spoke” is that it is a model for integrating the ERP system used at a company’s headquarters with the systems used by its subsidiaries and branch offices. This blogpost discusses approaches on how to benefit from the fast innovation cycle that Dynamics AX have, without starting a new big-bang ERP implementation project.

Implementing any ERP system is a huge investment. It is not uncommon that thousands of hours is required before you can start to see the benefits. The cost of performing a large scale upgrade simply cannot be justified. It is therefore common to see companies implementing Dynamics AX, and then waits a few years before they take the effort to upgrade to newer releases. In my experience I see that it is most common to jump over a major release.

The innovation cycle that we see with Dynamics AX, new ground breaking technology is being released every year. Much of this new innovation is directly related to new possibilities in having true Omni-channels. The Dynamics AX Retail solution became mature in release AX 2012 R3. But there is a lot of companies still using AX 2009, any they have a well working systems for financials, procurement, supply chain and order management. They don’t want to upgrade, but they are still missing the ability to benefit from becoming a true Omni-channel retailer. The challenge is that the competition will be stronger and more mature.

I have met several customers that are implementing AX 2012/AX7 only for the Omni-channel, while keeping their AX 2009/legacy system taking care of the traditional ERP-processes. This approach focuses on limiting the implementation scope to only cover the Omni-Channel Retail vision. The idea is to only use AX 2012 on the retail channels. This means that retail stores, WEB, retail hierarchies, call-center is features that the AX 2012 (green) is covering. Then an integration between the legacy system (AX 2009) and AX 2012 is done using DIXF, that seamlessly connects the systems.

What I like about the Dynamics AX retail, is that what we currently have in AX 2012 is a very stable and is built for supporting a much faster implementation cycle. This means we can faster take advantage of new technologies. The AX for retail support the N-1 approach. This means that we can upgrade the AX 2012 parts to AX7, without making changes to the POS or the e-commerce systems. We can also in a timely fashion roll-out updated version to new channels, without being concerned of disrupting the existing channels.

I can share a personal experience with this approach. A customer came to us that had Dynamics NAV as their legacy system, and wanted to roll-out Dynamics AX POS to several countries. From the time we did the kick-off, and until the first store was up and running it took only 8 hardworking weeks. We used the Hub-and-Spoke approach, and it shows that it is a quick and achievable way to get return-on-investment fast.

So my advice is; Start your Omni-Channel journey today. Don’t wait until you have upgraded your legacy systems, because you can do that later.

Happy DAX’ing friends, and thanks for reading my blogposts!

AX suggestion: XML Columns and XML indexes

In AX we have the following datatypes we can use in table fields.

But in MS SQL server there is a XML data type. With the XML data type we can store XML documents in the SQL Server database. And we can create columns and variables of the xml type and store XML instances in them. we can also create XML-indexes that speeds up searching on the XML contents. As seen here, I have manually added a XML column, and a XML primary index.

The Xml datatype allows us to perform several operations on the xml data from within t-sql. Although this is not very fast, it’s often better than round-tripping and doing the xml parsing in an application like Dynamics AX. Take a look at the following blog-post for a sample SQL- Querying XML attributes from XML Columns.

Why would this be interesting for the Dynamics AX ?

Let’s say we want to have the possibility to dynamically add new fields and information to an item/customer/BOM, but we don’t want to make customizations. One possibility would then be to have a XML field or a related table that contains a XML datatype column. In this column new fields and values could be stored inside the XML. By having some generic code that is extracting the XML values into fields or computed columns, would mean that we could provide a generic way of letting the user interact with dynamic fields, and that the user could add the fields as wanted on the fly without customizations. And still have the search, sort and filter capabilities.

I would like to use it for storing metadata like searchable retail product attributes. Since the XML format is a bit generic additional actions and events could also be stored inside the XML document. This could be functional triggers and workflows to be executed. It could even be specified down to the lowest record level.

So what is the difference from having XML’s in an ordinary text field ? One difference is the ability to use XML indexes, and to have search, filter and sort capabilities on the values stored inside the XML, without parsing the entire XML. More information on this is available here. Then the user could work with dynamic fields as it was real fields.

The first step in exploring this possibility is to have XML columns and XML indexes available in AX.

So my question to Microsoft is “Can we get the XML datatype and the ability to create XML indexes in AX 7.X ?

At least I think it is an interesting idea for the future.

DAX2012R3CU9 – DIXF – Automate import/export without customizations

The Microsoft Dynamics AX 2012 Data Import/Export Framework(DIXF) is an AX module import and export data in Microsoft Dynamics AX. We often use it in data migration projects to load legacy data from old systems. I was wondering if I could use DIXF as an automated integration, without any customizations. I wanted to see if I could have a folder where new customers are dropped in a folder, and then the DIXF automatically picked up the file, and imported it.

My first step is to have a small and minimalistic Excel sheet, that users can paste in the new customer records. This is how my Excel sheet looks like:

Most of these customers exists from before, but the last record is a new customer that don’t exists in my database.

The recommended process of setting up an import/export process is described here.

The first step is to create a source data format:

I then determine what entity to use, and create a target entity

When I do this, the mapping is done automatically for me, and I don’t have to understand all the database related complexity.

My next step is to create the processing group


I then click on the Entities in the processing group, and I select my created entity and that I want to use my created Excel source data format. I also select a sample file to see if the mapping is OK.

I then just check the mapping from Excel to the staging format, and make the necessary corrections.

My next step is to go back to the processing group, and to make the necessary batch job for automatic processing.

As you see here, I set the “type” to Directory, that DIXF will scan for new files. I also specify directories for processing, completed and error. I have therefore created the following directory structure for each integration:

The other important thing is the “Execute target step”. This this used for also executing the step that transfers data from the staging table to the target tables.

I then want this to be work in batch, so I enable the batch processing.

And then I need to wait for an entire minute……… I then saw that the file was moved from the 1_new folder, and ended up in the ¤_Completed folder.

I also see in the execution history, that the files was imported into the staging tables, and then imported into the target tables.

In my customer overview, I now see that I have a new customer, but is also made sure that other related data as addresses, and phone etc was created.

This concludes how you can use DIXF to automatically import data. What I can now do to import data, is just to create my Excel file, and then dump it into the right folder (.\1_New), and then the batch system take care of the test.

If you wonder all entities that are “out-of-the-box” supported from Microsoft, then take a look here. If still something is missing, you can always ask a developer to assist in creating the DIXF entities you need.

Happy DIXF’ing J


DAX 2012 R3 – Retail Channel POS reports

Both ePOS, mPOS and the upcoming AX7 CloudPOS have a built-in feature for showing small reports and KPI’s directly on the POS. These reports are not running against the Dynamics AX database, but against the retail channel database connected to that specific terminal.

The great thing about these reports, is that it does not require any heavy development to define these reports. They are just XML report definitions, that queries the database for specific columns or stored procedures. This means that we easily can create new reports based on the direct reporting requirements. In the report, shown below a report called “Sales by staff” is shown. Here the staff name, number of transactions, sales amount and average sales amount is shown.

The report can also be shown as graphics.

The report definitions are located in the RetailàSetupàChannel report configuration.

We see here the report definition, and it can look a bit cryptic. But let’s format the XML to better understand the definition. We here see that the retail report basically have 3 sections. The dataset, parameters and the reportcharts.

As we can see here, the dataset is referring to a stored procedure, with 3 parameters; Channel, start-date and end-date. If we open the SQL stored procedure we can see the exact implementation of it.

We see here that the actual source tables are an inner join between retailTransactionTable and retailStaffTable. We can also see that the amount is “retailTransactionTable.paymentAmount”. This amount is the amount of what the customer is actually paying inclusive taxes.

But a US-based customer of me asked if it is possible to make some minor changes, and just show the reporting exclusive taxes. To do this, it means that we must do some changes. We cannot use the existing standard stored procedure. We could surely create new stored procedures, but rolling this out to hundreds of terminals would take a lot of time. But we have the option to use an actual query in the report XML definition instead.

The first step is to try to create a SQL that retrieves the sales amount minus taxes. As far as I have interpreted the table retailTransactionTable, it seems that the field I can use is the -1*[NETAMOUNT]. Here is the query tested directly in SQL Manager, and I have “yellowed” out the differences compared to the script used in the stored procedure CRT. GETSALESBYSTAFFREPORT

It seems to be working, and the next step is to create a new XML report definition. I therefore took the report “101 Sales by staff”, and created a “101_US Sales by staff”, with the following XML report. Here I have colored the differenced to the original report in yellow. As you can see here, I use a query, instead of a stored procedure.

I also localize the labels in the report

To have the new report available to the users, I need to run the CDX Async job to send data to channels.

  1. In AX, go to Retail > Periodic > Data distribution > Distribution schedule

  1. Select job 1110, click Run now

  1. Wait couple of minutes for the job to finish

I then have the new retail report available.

Then I get my POS report, where the amounts is without taxes J

Happy DAX’ing !