Working with Entity Store and DIXF in AX

There is an excellent tool available for advanced reporting and BI capabilities that partners and customers should start exploring. In this post I would like to show how to work with Entity Store and DIXF in AX, which has been published shortly after the Technical Conference held in Seattle last year: https://www.daxrunbase.com/2016/02/24/technical-conference-day-1/

Working with the Entity Store and DIXF in AX takes some time to set up, but once it is running, it is acting as an up-to-date Data Warehouse for you, where you could utilize advanced features such as Clustered Columnstore Indexes, and make truly amazing visualizations which will please the eyes of your managers whom loves KPIs, and will make your teams more productive by knowing what to look for.

You can find the Public PowerBI built for the stock and items at JJ Food Service as an example of what could be achieved for visualizations built on data that is coming out of your AX environment:

The out-of-the-box AX 2012 R3 version of the Data Import Export Framework consists of a service, which we have installed next to our SSRS/SSIS instances. That service is handling data movement through SQL Server Integration Service packages from the source to the target database. Here is the main article for explaining DIXF, and the installation steps can be found as a subpage on MSDN:

https://docs.microsoft.com/en-us/dynamics365/operations/dev-itpro/lifecycle-services/ax-2012/user-guide-dixf

Please note that depending on what Release and Cumulative Update version you have, you need to apply a lot of hotfixes before/after installation on top of the DIXF framework to get it working correctly. They could be found on LifeCycle Services, the link below is filtered for the R3 version:

https://fix.lcs.dynamics.com/Issue/Results?q=DIXF&v=7_1&ct=1_3_5_4_2_6&ft=1_2_3&o=D&mode=Full&i=2_1_3

Also it is advised to be on the most recent kernel version, since the DLLs responsible for data exchange have also been modified.

https://blogs.msdn.microsoft.com/axsupport/2012/03/29/overview-of-microsoft-dynamics-ax-build-numbers/

There is a hotfix which changes the DIXF Configuration, here is the relevant part which you should make sure that it is available in C:\Program Files\Microsoft Dynamics AX\60\DataImportExportFramework\DMFConfig.xml file:

  <EDBOledbConnectionStringFormat>
    Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};User ID={2};Password={3};
  </EDBOledbConnectionStringFormat>

There are other issues with the framework as well when trying to publish values to external databases such as the Entity Store. We have worked out a fix with Microsoft for the database publishing, which is available on my GitHub. Make sure you get the class, import it in your environment, then do a Full CIL:

https://github.com/DAXRunBase/AX-2012-R3/tree/master/Data%20Import%20Export%20Framework

Once the Data Import Export Framework is ready, the next step would be to set up Entity Store. Microsoft has published a blog article and a whitepaper on what are your options, which is available here:

https://blogs.msdn.microsoft.com/dynamicsaxbi/2016/05/02/introducing-entity-store-for-dynamics-ax-2012-r3/

In our setup we have used a SQL Server 2014 Entity Store DB for Test, and an Azure SQL DB for Production environment. I can confirm that when you set up the connectivity, using SQL Server Native Client 11.0, ODBC driver 11 for SQL Server and ODBC driver 13 for SQL Server are all working correctly. The most important difference between them is how they communicate back for example error messages in case something fails. So if you face some issues and you cannot make out what the problem is, you could try to switch the System DSN to use another driver and see if you get back different results.

An important consideration for choosing what data to expose in the Entity Store is dependant on your SQL Server, since for the tables involved in the entity you must enable SQL Server Change Tracking, which creates some extra overhead to maintain the changes.

Once you have been able to create the Entity Store, you have configured your preferred DSN/driver and have published your first DIXF Entity to the external DB, the next step is to push the data. We are publishing our prices and promotions to Azure with a 4 hour schedule, using the Incremental push only setting, and we do populate the staging table too.

Please note that if you do not set up the Staging cleanup batch, your staging tables will quickly become very large and the publishing process will potentially slow down. We are emptying our staging tables on a daily basis.

Here is how the upload chunks and the data looks like in Azure:

We are now facing a minor issue that after a couple of weeks, the scheduled batch push starts failing with an “Exception has been thrown by the target of an invocation” error message, and publishing the record will be suspended, the record to be pushed will just keep accumulating. It is currently being investigated that what is the root cause of this sudden failure, maybe it is caused by something special in our data.

The temporary fix is to delete the values in the Entity Store table, the staging table in AX, remove the batch and the schedule, then do a new full push of the data. Once we find a permanent solution the article will be updated.

I can also recommend the following article series, which goes into great detail on setting up the whole featureset: