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)!
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.
Figure 1- Data Export Service
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.
Figure 2 – 2 – Stage synchronization
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:
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.
Figure 3 – Typical use cases
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.
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:
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.
Figure 4 – Synchronized Sales Data export profile
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.
Figure 5 – Database tables
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:
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 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.
– Narinder Singh