Monday, 23 May 2016

AX 2012 R3 Entity Store

Recently there was a release of the entity store for AX 2012 R3. This is a great tool that makes data warehousing simple and close to real time. Traditionally with a data warehouse, you would have to do full updates. This is fine for overnight process but when it comes to regular updates, it is a bit much to ask.

Here are the links to Microsoft blogs of the release announcement:

The concept uses DIXF to do incremental updates to the secondary entity store database. This is done using change tracking which is enabled at the database level.

One thing to note is, the entity store database should ideally be on a different server. Allowing you to have SQL 2014 or latest SQL 2016 installed. The entity store uses column store indexing which is super fast. Have a read of the whitepaper in the above links. Below is a diagram of what is intended.


You can download a KB or wait for the next CU to be released soon.

After downloading the KB installation file – you will get a kernel and an application update folder.


The hotfix contains a few extra related KBs. Nothing major and should be able to identify any conflicts pretty quick.


Now that it is installed, you have a couple extra menu items.


Before you can start setting up the entity store. You have to create the ODBC connection to an empty database. This is a manual process (ie. create it in SQL Management Studio).

Once you have done that, you can set up a connection. This is the same as the normal DIXF connection but defaulting to ODBC to make it simpler. Click the Validate to make sure it is OK.

Then click on the Publish, which launches the entities to publish.


I did a quick simple one here.


In the Manage refresh schedule menu, you can start to push the data. A couple of nice features on there.

To do incremental or full push of data.

Skip the staging table (good because you don’t want to have too much data in your production database).

I haven’t figured out the last flag yet. Maybe next time.


Looking at SQL it maintains the update with the DIXF execution id.


In my opinion this is really exciting. It is bringing regular incremental updates, a data warehouse which you can extend to build on. Then it is a matter of using PowerBI to do your reporting and presentation.


sjafry said...

Can we use same entity store DB to be populated from different AX databases? like we are running AX in distributed environment, on AX DB is in US location and other in UK location, both works independently as both have their own DB and AOS. can entity store DB help in consolidating two DBs.

Munib Ahmed said...

yeah. that shouldnt be a problem.
Connectivity speed might be an issue.

My company has some BI solutions that they can help with around distributed BI implementation. Let me know if you want me to put you in touch. said...

Hi Munib,

It may be interesting for you to know that it is now possible to make real data extraction from AX7. We believe that our recently relased AXtractor is the first of its kind for AX7. You no longer have to rely on Chrome table browsers, data enitites, Odata or other limiting (crippling) factors for making real BI on AX7. Mail me on or call +45 53588495 if you would like an on-line demo.

syeda sana Mehmood said...

I have made custom entity. When i simply choose incremental push without batch processing it push data to entity store but when i schedule it to batch processing using recurrence it say my customized entity does not support incremental push.Moreover, It is also not supporting push only on batch jobs. Is it due to change tracking? Or what can be done to schedule custom entity on batch jobs? Urgent help required.

Munib Ahmed said...

Normally the modifieddatetime and modifiedby properties are set on the table.
It should have a primary key on the table.
That is pretty much all that is required to enable change tracking.