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:

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

https://blogs.msdn.microsoft.com/intel/archives/185

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.

2016-05-03_1852_001

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.

2016-05-03_1844

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

2016-05-03_1851

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

2016-05-17_1612

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.

2016-05-17_1614

I did a quick simple one here.

2016-05-17_1618

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.

2016-05-17_1619

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

2016-05-17_1618_001

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.

6 comments:

sjafry said...

Hi,
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.

Jakob.bjerg-heise@scales-group.com 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 Jakob.bjerg-heise@scales-group.com or call +45 53588495 if you would like an on-line demo.

oracle financials Training said...

Hi,
It's a very nice article,Surely this will help to people of MySQL Community. I Appreciate for your work,Keep move on with new articles providing useful information.Our institute is best in oracle Ebs and financials training.for more inf just go through our website.
Thank you,
Oracle EBS training

Unknown said...

Nice to read your article!very informative post. So, please keep posting.
Power BI Online Training
Power BI Online Training bangalore

nutana meka said...

This is very Nice article surely it's very useful information Power BI Online Training