Export Dynamics AX”7” Entities to your own Azure SQL Database

We are happy to announce the availability of the feature to export Dynamics AX Data Entities to your own database with hotfix KB 3175368 and KB 3175369 available for download immediately. This feature will also be included with the next Dynamics AX platform update.

With this capability, Administrators have the ability to “configure your own database” and export one or more of the 1700+ Entities available in Dynamics AX. This feature enables you to;

1. Define one or more SQL Azure databases for exporting Entity data from Dynamics AX
2. Export all the records (full push) or export only the records that changed – ie. incremental push
3. Use rich scheduling capabilities of Dynamics AX batch framework to enable recurring exports
4. Access the entity database using T-SQL and even extend the database by adding more tables yourself.

The hotfix requires Dynamics AX May-2016 update. So you do need to upgrade to May-2016 upgrade to enable this functionality if you have not done so already.

Built-in Entity store vs. exporting Entities to your own database

If you followed the series of blog posts on PowerBI integration in Dynamics AX”7”, you may have following questions

• Why would I need to provision my own Database and export Dynamics AX entities. Can’t I use the Entity store provisioned within AX
• Why can’t I add my own tables to the Entity store provisioned with Dynamics AX
• Why can’t I connect to the Entity store using T-SQL – why must I use PowerBI to connect to Entity store

Let’s understand the context of these questions first...

You may be familiar with the feature we introduced in AX2012R3 in May-2016. With this feature, an Administrator can create a new SQL Azure (or SQL Server) database and provide the connection information in the Data management area page. The Administrator was able to choose AX2012 R3 Entities and stage them in the database. We called this database the Entity Store and it could be used for Power BI and Cortana Intelligence Suite (CIS) integration. You could also access this database using any of the tools that supported T-SQL and it could be used for integrating with other BI tools or for application integration scenarios.

In Dynamics AX”7”, we want to look at integration scenarios closely and optimize the architecture pieces as suited for each scenario. As you are aware, we are embracing Power BI as a key Operational reporting capability. In the May update, Entity Store is provisioned with every AX environment and is pre-configured for Power BI and CIS integration.

If you are not using Power BI in your organization, or if you are using the Entity Store for integration with third party tools, you may prefer the “bring your own database” option similar to what is available in AX2012 R3.

So this is why you asked the question in the first place – and now you have the ability to export Entities to your own database similar to what you had in AX2012 R3

Let’s examine the functionality in detail.

Create a SQL Azure database

Prior to starting this option, you would need to create a SQL Azure database using Azure Management portal.

In case of “one box” developer environments, you can create a database in the local SQL server database. But this is strictly for development and testing purposes only. In case of production environments, you do need to create a SQL Azure database.

You should also create SQL user account to login to the database. Write down the server name, Database name and the SQL user ID and the password. You will need these for the next step.

If you are using this functionality for integration with a BI tool, you should consider creating a SQL Azure premium database. Premium databases support Clustered Column Store indexes (CCI) which improve the performance of read queries that are typical in analytical workloads.

Configuring the export Entity option

Launch Dynamics AX client and navigate to the Data Management area page. Once in the Data Management area page, you can select the new Tile Configure Entity export to Database” option.

When selected, if you have configured any databases, you will see a list. Else you would need to configure a new database. Select New and enter a unique Name and a Description. Note that you can export Entities into multiple databases.

Next you will enter the connection string as follows

Data Source= <Logical Server Name>, 1433; Initial Catalog= <your DB name> ; Integrated Security=False; User ID= <SQL User ID> ; Password= <Password>

Logical server name should be similar to the nnnn.database.windows.net and you should be able to find the logical server name from the Azure Management portal.

After you enter the connection string, click the Validate button and make sure that the connection is successful.

Create Clustered Columnstore indexes option optimizes the destination database for select queries by defining Clustered Columnstore indexes (CCI) for entities copied from Dynamics AX. However, at this point, CCIs are supported only on Azure SQL premium DBs, therefore, in order to enable this option, you should have created a SQL Azure premium database.

On successful completion, you will notice the database you have configured for Entity export listed as follows

Next you can publish one or more Entities into the newly created database by selecting the Publish option from the menu.

Publishing Entity schema to database

Publish form enables several scenarios including;

1. Publishing of new Entities to the database
2. Deleting already published entities from the DB (in case you want to re-create the schema)
3. Comparison of published entities with Entity schema in Dynamics AX (ie. in case new fields get added to Dynamics AX in the future, you can compare the fields with your Database schema)
4. Configuring Change tracking functionality that enables incrementally updating your data

Let’s consider each of the options below;

Publish: Publish option defines the Entity database schema on the destination DB. When you select one or more entities and select the publish option, a batch job is started to create the Entities in the destination database. When the database definition job is complete, you will be notified by a message in the InfoLog (ie. the bell icon on top right)

Actual updating of data happens when you export data. At this point you are only creating the schema.

Drop entity option deletes the data and the Entity definition from the destination DB.

Compare source names option lets you compare the Entity schema in destination with the Entity schema in Dynamics AX. This option is used for version management. You can also use the compare option to remove any of the unwanted columns from the destination table.

Configure Change tracking

Change tracking is a feature provided with SQL Server and in SQL Azure that enables the database to track changes being performed on tables. Change tracking is used by the system to identify changes made to tables as transactions are performed in Dynamics AX.

Change tracking option in the Publish form enables you to configure how the changes are tracked on the underlying entity.

There are several change tracking options that can be used.

In order for Change tracking functionality to work, you do need to enable change tracking option in the Dynamics AX Database.

In case you re-publish and Entity that exists in the destination database, the system warns you that existing data will be deleted as a result of the new operation.

When you confirm the publish operation, system publishes the schema to the database subsequently and you would be notified on completion. Back in the publish screen,

by choosing the Show published only option, you can display only the entities that were published to a given destination database. Publish function creates the entity schema in the database. You can navigate to the Database and see the table schemas created along with corresponding indexes.

Export data into your Database

Once entities are published to the destination database, you can use the Export function in Data Management workspace to move data. Export function enables you to define a Data movement job that contains one or more Entities.

Export form is used for exporting data from Dynamics AX into many target data formats. This is the same form that you would use to export data into a CSV file. With the hotfix, this form has the ability to support SQL Azure databases as yet another destination.

When adding an entity for data export, you have the ability to choose incremental export (called “incremental push”) or full push. In order for incremental push to work, you do need to enable Change tracking in the Dynamics AX database and specify an appropriate change tracking option as described above.

You can create a data project with multiple Entities and it can be scheduled to execute using Dynamics AX batch framework. You also schedule the data export job to run on a recurring basis by selecting the Create recurring data job option.

Should I use entity store or bring my own DB?

Here’s our guidance on Entity store and "Bring your own DB" option

If you are planning on using Power BI with Dynamics AX data, you should plan to leverage Aggregate measurements staged in the Entity Store provided with Dynamics AX.

If you are working on data integration scenarios or working with other BI tools, you should use Export Entities to database option available as a hotfix with update1.