Dynamics 365 AX – Creating Data Entities for BYOD Azure Database including those without a natural key

We, BI professionals, sometimes take for granted that we can access the database of an ERP system quite easily, especially one that is made by Microsoft. However, the live Dynamics (365) AX database cannot be assessed directly currently.

The diagram above shows that the Primary AX DB is copied into the Secondary AX DB for reporting purposes. However, the Primary and Secondary DB cannot be accessed directly in the Live system. The Entity Store, however, can be accessed either via OData feeds or by synching with an Azure Database.

Creating a Data Entity

Creating a Data Entity is easy. Start by logging into your VM. Start Visual Studio 2015 as an Administrator. 

Next, create a new Project. Change the model to one that inherits Application Suite. This usually is required to be able to access the AX tables. Set “Synchronize Database on Build” to True.

Next, click Add new item. Select Data Model -> Data Entity. This starts the Wizard. 

In the wizard, select a datasource (this means AX table). In this example, we shall select the LineOfBusiness table due to its simplicity and that it has a regular key. Leave everything else as default. Here is what the fields mean:

As you can see, all fields are required.

At the next screen, select the fields you need from the Primary table (LineOfBusiness) making sure the key field is selected. (It is one of the mandatory fields) This will ensure that you can turn on change tracking for this data entity.

Build the project.

After the build, in Dynamics AX, select the Data Management workspace and click on Data entities.

Select the Data Entity and test out the Change Tracking functionality to make sure it works.

To view the OData field, use the following URL:

https://{Base URL}/data/{Data entity public collection name}

E.g. https://usnconeboxax1aos.cloud.onebox.dynamics.com/data/EBCLineOfBusinesses

Creating a Data Entity from a Datasource without a natural key

When creating a Data Entity from a table without a natural key, a workaround is required.

First, create a data entity using a substitute (like LineOfBusiness). Create a similar data entity as in the previous section. Then, drop the data source, fields and indexes and replace them with the ones you need.

2 advantages of this approach:
  1. You do not need to customise the base table by overlaying as this method may be deprecated in the future.
  2. You do not run the risk of affecting the input of data into the base table by changing the key index of the table just to be able to run the data entity wizard.

Modify two things: 

The Data Entity: Change the data source and the entity key

Data Entity: Before

Data Entity: After

The Staging Table: Change the fields and the Staging index. This is to allow the sync of the Staging table with the Azure DB.

Data Entity Staging Table: Before

Data Entity Staging Table: After

Change the Title fields of the Staging Table:

Title fields: Before

Title fields: After

Rebuild and synchronise the project and now your data entity is ready for consumption as OData feed or to sync with an Azure DB.

Configure Azure connection

More detail of this step can be found here.

First, if you have not done so, create an Azure database and setup the firewall access to your development machine. Make a copy of the Azure DB Server name.

In the Data Management workspace in Dynamics AX, click on “Configure Entity export to database”.

Then enter the connection string of the Azure DB.

It should be in the format:

Data Source={azure.database.windows.net},1433;Initial Catalog={database};Integrated Security=False;User ID={userid};Password={password}

Validate the connection: and click Publish.

This creates the table in the Azure DB.

Exporting data

In this final step, create the Export job and export the data to the Azure DB.

In the Data Management workspace, click on Export.

Configure the export by selecting the Azure connection and the data entity. Then click Export.

Data is successfully exported.

To set up a recurring data synchronisation, click on Create recurring data job.

In Summary

To create a new data entity, simply use the wizard to create one from an AX table. However, for tables that do not have a natural key (e.g. only RecId), a workaround is required which is described in this post.

After the data entity has been created, Dynamics AX is configured to export to an Azure DB and an export job created to synchronise the data.

Leave a Reply

Your email address will not be published.