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

Import document handling (attachment) files #MSDyn365FO

Out of the box you have limited data entities for migrating attachments. If you search what is already in the AOT, you will see a few various examples. I suggest you look at the LedgerJournalAttachmentsEntity as it is the simplest and cleans to copy from. I wont go into detail but I will give a quick run down of what it looks like. Use the DocuRefEntity as your main datasource. It does most of the work for you. Set your table you want to import for as the child datasource Add the Key You will need to add the postLoad method. There is minor code to update the virtual field FileContents. Below is an export I did for the general journal attachments. The import zip structure should be the same way. It will create the usual artifacts such as the excel, manifest and package header xml files. You will see a Resources folder under that. If you drill down to the resources you will see the attachments. This is an export and it used the document GUID for uniqueness. The other thing is the extensi