Skip to main content

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.

Popular posts from this blog

AX - How to use Map and MapEnumerator

Similar to Set class, Map class allows you to associate one value (the key) with another value. Both the key and value can be any valid X++ type, including objects. The types of the key and the value are specified in the declaration of the map. The way in which maps are implemented means that access to the values is very fast. Below is a sample code that sets and retrieves values from a map. static void checkItemNameAliasDuplicate(Args _args) { inventTable inventTable; Map map; MapEnumerator mapEnumerator; NameAlias nameAlias; int counter = 0; ; map = new Map(Types::String, Types::Integer); //store into map while select inventTable { nameAlias = inventTable.NameAlias; if (!map.exists(nameAlias)) { map.insert(nameAlias, 1); } else { map.insert(nameAlias, map.lookup(nameAlias) + 1); } } //retrieve fro

AX - How to use Set and SetEnumerator

The Set class is used for the storage and retrieval of data from a collection in which the values of the elements contained are unique and serve as the key values according to which the data is automatically ordered. You can create a set of primitive data types or complex data types such as a Class, Record or Container. Below is sample of a set of records. static void _Set(Args _args) {     CustTable       custTable;     Set             set = new Set(Types::Record);     SetEnumerator   setEnumerator;     ;     while select custTable     {         if (custTable && !set.in(custTable))         {             set.add(custTable);         }     }     if (!set.empty())     {         setEnumerator = set.getEnumerator();         setEnumerator.reset();         while (setEnumerator.moveNext())         {             custTable = setEnumerator.current();             info(strfmt("Customer: %1",custTable.AccountNum));         }     } } Common mistake when creating a set of recIds

Approve Workflow via email using template placeholders #Dyn365FO

Dynamics 365 for Finance and Operations has placeholders which can be inserted into the instructions. Normally you would want this to show up in the email that is sent. One of the most useful ones is the URL link to the exact record that you are approving. In the workflow configurations use the placeholder and build up your message. Towards the end it has workflow specific ones. The URL token is %Workflow.Link to web% . For the technical people the token is replaced in this class WorkflowDocumentField. This is what I inserted into my email template. <BODY> subject: %subject% <BR> message: %message% <BR> company: %company% <BR> for: %for% <BR> </BODY> Should look like this. The final result looks like this. If you debug these are the place holders that are put together.