Data extraction and synchronization made easy

Have you ever wanted an easy ability to replicate Dynamics 365 (online) CRM data to an Azure database for using Azure analytics tools without the added complexity of managing an ETL tool? Did you ever want to use Power BI based reporting over the entire Dynamics 365 organization data? Have you ever wanted a tool that can push Dynamics 365 (online) delta changes continuously in a matter of a few minutes without running frequent scheduled jobs? Then the new Dynamics 365 – Data Export Service is just the right service for you. The new Appsource app Data Export Service enables administrators to do just that and it’s free to use with Dynamics 365 (online)!

What is the Data Export Service?

The Microsoft Dynamics 365-Data Export Service is a free add-on service made available on Microsoft AppSource that synchronizes Microsoft Dynamics 365 (online) data to a Microsoft Azure SQL Database store in a customer-owned Microsoft Azure subscription. The supported target destinations are Microsoft Azure SQL Database and Microsoft SQL Server on Microsoft Azure virtual machines. The Data Export Service synchronizes the entire Dynamics 365 data initially and thereafter synchronizes delta changes on a continuous basis as they occur in the Microsoft Dynamics 365 (online) system. This helps enable several analytics and reporting scenarios on top of Dynamics 365 data with Azure services, such as Power BI or Machine Learning and opens up new possibilities for customers and partners to build custom solutions.

 

 

With the Data Export Service, you can quickly set up data replication to a destination database with export profiles in a matter of a few minutes. Each export profile provides an easy ability to choose a set of entities to replicate data from Dynamics 365 to a destination database and thereafter the entire data is available in tables automatically created in the destination database. You can set up multiple export profiles simultaneously to replicate data to different databases to cater to different workload scenarios, such as one export profile can replicate data for sales reporting and another one can replicate data for service reporting. Data is synchronized in 2 stages, first with initial sync and then with continuous delta sync as changes happen in Dynamics 365 with a push mechanism within a matter of few mins.

 

 

The Data Export Service simplifies the technical and administrative complexity of deploying and managing a data export solution – automatically managing both schema and data. Below are the key capabilities of the Data Export Service:

• Metadata changes such as addition or modification of fields are automatically synchronized
• Full initial data synchronization
• Push-based delta synchronization as changes occur in Dynamics 365. Delta changes are pushed in a matter of a few minutes
• Built-in recovery for addressing transient failures
• Delete log table to track deletes along with their timestamps
• Timestamps to compute delta based on sync time
• Monitoring and diagnostics on sync progress
• Scalable, reliable, and secure cloud service operated by Microsoft
• APIs for programmatic management of export profiles

Use cases it can help

Having your data in Azure SQL database enables several possibilities, such as to set up an operational database for reporting with Power BI, build a staging area for data warehouse, or to build machine learning models. In addition, a synchronized database can also help offload any read-only workload centric queries, which  reduces load on the main Dynamics 365 system. The key use case that the Data Export Service can help is to build scalable operational or enterprise BI reporting with Power BI in Azure backed by Azure SQL database.

 

 

How to set up

To install the Data Export Service, go to its AppSource app page. Follow the steps to install the app and wait for the installation to finish. You must be a Dynamics 365 administrator to install applications from AppSource. You can monitor the installation status in the Dynamics 365 Admin Center. Once installed, login to Dynamics 365 as administrator and from the Settings menu, select “Data Export.” You are then taken to the data export profiles list, which displays all your available export profiles.

How to create a Data Export Profile

Creating a Data Export Profile is an easy wizard driven process. All it requires is to select the entities and m:n relationships with a few other additional profile specific settings such as name, table prefix, or enable delete log.

Prior to creation of the export profile, there are a few important prerequisites. They are:

1. Any entity to be added to an export profile must be enabled with change tracking
2. The destination database connection string must be maintained in an Azure Key Vault URL to ensure secrets are securely managed. The Key Vault URL must exist in the same tenant as Dynamics 365. Refer to the How to set up the Key Vault URL link below on how to use a script template to automate the Key Vault URL creation.

For more information on how to enable change tracking, refer to the link below Use change tracking to synchronize data with external systems. Most of the OOB entities are change tracking enabled. For custom entities, you must explicitly do so.

The screenshots below describe the steps to create the export profile.

Step 1- Provide basic information

 

 

Step 2 – Select entities

 

 

Step 3 – Select relationships

 

 

Step 4 – Create and Activate

 

 

Export profiles, once activated, starts the data synchronization – first with metadata sync, followed by initial data sync, and then with continuous delta data sync. During initial sync, the existing data in Dynamics 365 for added entities is synchronized with the destination database. After initial sync, delta sync starts, and any create, modify or delete record change for entities added to export profile is automatically synchronized.

With Refresh, you can see the latest progress on synchronization with status and notification counters.

 

 

Metadata sync automatically creates and updates tables in the destination database. Here we see tables have been automatically created for various entities such as Account, Lead, Opportunity, Product & Campaign entities using the “sales” prefix provided in the Sales Data export profile. Refer to the TechNet documentation link below on details of all the tables that are created including metadata specific tables for Optionsets.

 

 

 

Metrics for data synchronization during initial sync and delta sync are shown in terms of notification counters. A notification encapsulates a record change – it maps to create, modify, or delete change of a record. There are 3 main metrics that are captured:

1. Total notifications show all notifications to be processed for data synchronization.
2. Successful notifications show all notifications that have been processed successfully and the corresponding insert/update/delete change that has been synchronized to the destination database.
3. Failed notifications show all notifications that have failed during processing.

Notification counters at the end of initial sync show a count for all records synchronized from Dynamics 365. For any errors that occurred during synchronization, you can click Download Failed Records to obtain a URL to connect with Azure Storage Explorer to see the log of failures and address the reason behind those failures such as database is down or out of space. Refer to Resolving synchronization issues link below on more details on how to analyze the error logs and various error recovery techniques as well best practices to avoid running into synchronization errors.

Reporting with Power BI

Reporting with Power BI is one of the key use cases that can be enabled with the Data Export Service. To facilitate easy creation of the end-to-end solution with the Data Export Service, we have partnered with Power BI team to build solution templates for Dynamics 365 using the Data Export Service. Solution templates help in the easy setup of an end-to-end reporting solution with Power BI in the customer’s subscription. More information for Dynamics 365 solution templates powered by the Data Export Service is available here.

In the future, we will be releasing more of such solution templates to cater to reporting scenarios across service and marketing.

Summarizing, the Data Export Service can empower administrators to easily replicate Dynamics 365 (online) data into Azure SQL database. This enables full power over CRM data for various analytics and reporting use cases and get past the limits in Dynamics 365 for reporting and analytics.

We welcome your suggestions to improve the service. Please provide your suggestions at CRM ideas portal.

 

Useful Links

• AppSource App – Data Export Service
• Technet documentation
• How to set up the Key Vault URL
• Data Export Service APIs
• Power BI Dynamics 365 for Sales Management solution template
• Power BI Dynamics 365 for Project Service Automation solution template
• Use change tracking to synchronize data with external systems
• Resolving synchronization issues

 

 

– Narinder Singh