Freigeben über

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

  • Artikel

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.

Data Management workspace

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.

create new DB for export

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

list of Entity export DBs

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.

Configure change tracking option

There are several change tracking options that can be used.

Option What this means
Enable primary table As you are aware, an Entity is comprised of several tables. In case you are interested in tracking changes that happen to the primary table of the entity, choose this option. When this option is chosen, all changes that happen to primary table are tracked (therefore the corresponding record is inserted or updated to the destination DB) While data from the entire entity is written to the destination table, the system triggers the insert or update option only when the primary table is modified. 
Enable entire entity Choose this option if you want all changes to the entity (including changes to all the tables that comprise the entity) to be tracked and corresponding updates to be made to destination 
Enable custom query This option enables a developer to provide a custom query that would be run by the system to evaluate changes. This option is currently not enabled in the system

 

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.

confirm publish schema

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.

Export definition

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.

Comments

  • Anonymous
    September 16, 2016
    I created several Projects to export Entitys. The tables on the SQL-DB get created correctly when I publish them, but whenever I export data to those tables 0 rows are transmitted.Is there a preview button somewhere, so I can make sure that the Entities actually hold some data?
    • Anonymous
      September 19, 2016
      several things to look into..1. Export function in data management workspace is company related. So, when you created the export job, make sure you created it for a company that contains data2. You can preview data of an entity using the OData endpoint. Say, if you want to explore data of Entity Applicant, you can enter the following in a browser window... http:///Data/Applicants. However, this would only work for Data entities that are public
  • Anonymous
    November 08, 2016
    Great Post Milinda !!! Extremely useful.One of my client uses Dynamics 365 for Operations (AX7) and I have used the concept of Data Entities (with Datawarehouse and cubes) instead of Entity Store to display reports on Power BI. Does the client need to buy Power BI Pro license separately because I understand that users get Power BI Pro license with Dynamics 365 for Operations (AX7) ?
  • Anonymous
    March 18, 2017
    when we are exporting (Full push) the data to Azure SQL in recurring jobs, it is erroing on truncating the table before exporting the data into Azure SQL. We are exporting the data for 3 companies. 1 company works fine where the volume of data is around 60,000 records. But for other two companies, the volumes are more then 100,000 records. Where the volumes are over 100,000 records the recurring batch is failing to truncate the data before exporting.This is causing issue, can you please let us know what is the fix for this.
  • Anonymous
    March 26, 2017
    Great post Milinda. Just reading this post now as we have recently configured a new SQL Azure database and exported the entities. I am getting issues when trying to enable change tracking for SalesOrderHeaderEntity and SalesOrderLineEntity. The message says "Entity sales order is not supported for change tracking as it doesn't have any primary index or alternate key ". But SalesOrderHeaderEntity does have SalesOrderNumber under Keys node of the data entity, it is referring to the SalesID field on SalesTable, which is an alternate key on SalesTable. Do you have any clue why it is not working or is there anything we might be missing in configuration ?Thanks,Baber.
    • Anonymous
      March 26, 2017
      Just found the issue in Issue search under LCS. Below is the link: https://fix.lcs.dynamics.com/Issue/NotFixed?bugId=3799523&qc=d771f0be8c0977b671f7d519dd7dd47c8593e662d7339a6defc0ea78b2bbef37 You also mention the following in the above post : "While data from the entire entity is written to the destination table, the system triggers the insert or update option only when the primary table is modified". Just want to understand why the system triggers the insert or update only when the primary table is modified, there can be cases where some field is changed not in a primary table, so how can I get the change tracking for those fields. Any ideas ?Thanks,Baber.
  • Anonymous
    April 05, 2017
    Hi! i am working with a client implementing D366FO and will probably use bothe the Entitity Store and Analyitical Workspaces and BYODB scenario's. A third one may be to provide ad-hoc (aided) self service reporting on AX data with PowerBI.com and desktop outside of the AX application. Is it possible to do this directly against the Entity store, or do I need tot do this against the BYODB ES? What are my options and what is best practice?Regards, Martin
  • Anonymous
    June 15, 2017
    Is Odata being used to push data from the AX7 Entity store to the Azure DB?
    • Anonymous
      July 24, 2017
      I am also curious about what technology is being used to move data from AxDB to the BYODB solution - my understanding is that it was SSIS in SQL Azure that consumes the D365 OData endpoints. Is that accurate? If so, are there any performance considerations?
  • Anonymous
    July 11, 2017
    Hi Milinda,I have created list of data entities in my Virtual machine(Contoso Environment). Now I would like to use Dynamics 365 feature “Configure Entity export to database” to export data entities to Azure environment. Do we need to open any port or any configuration required in Azure/VM ?Thanks,Parag
  • Anonymous
    October 09, 2017
    Is there anyone that has gotten this BYOD to work in the real world?Does anyone know how to resolve the error:Entity Purchase orders is not supported for change tracking as it doesnt have any primary index or alternate keyEntity Purchase orders is not supported for change tracking as it doesn't have any primary index or alternate keyhttps://community.dynamics.com/ax/f/33/t/231438