Have you ever needed the ability to replicate Microsoft Dynamics 365 Online CRM data to a Microsoft Azure database for using Azure analytics tools without first needing to learn and understand a an ETL (Extract, Transfer, Load) tool?
Did you ever want to use Power BI based reporting over the entire Microsoft Dynamics 365 organisation data? Have you ever wanted a tool that can push Microsoft Dynamics 365 Online changes continuously in a matter of a few minutes without running managing scheduled jobs? Then the Data Export Service is the tool of choice for you!
The Data Export Service app made available in the Microsoft AppSource enables administrators to do just that and it’s free to use with Microsoft 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 synchronises 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 synchronises the entire Microsoft Dynamics 365 dataset initially and thereafter synchronises changes on a continuous basis. This assists with analytics and reporting of Microsoft Dynamics 365 data with Azure services, like Power BI or Machine Learning. With the Data Export Service customers and partners are then empowered to open up new possibilities to build custom solutions.
With the Data Export Service, Administrators can quickly set up data replication to a destination database with Export Profiles. Each Profile enables Administrators to easily choose a set of entities to replicate data from Microsoft Dynamics 365 to a destination database and thereafter the entire dataset is automatically created in the destination database and available for interrogation.
Multiple export profiles can be setup for simultaneous data replication to different databases allowing you to cater for different workload scenarios, such as a profile sales reporting and another for service reporting. It’s important to note that the dataset is synchronised in 2 stages, first with initial setup and then with continuous sync as changes happen in Microsoft Dynamics 365 which will replicate in a matter of minutes to the specified target database.
The Data Export Service minimises the technical and administrative complexity of deploying and managing a data export solutions by automatically managing both schema and data.
Key capabilities of the Data Export Service:
• Metadata changes such as addition or modification of fields are automatically synchronised (no need to manually reset metadata!).
• Full initial data synchronisation – also known as replication. Other products like Scribe Online have this function too.
• Push-based delta synchronisation as changes occur in Microsoft 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
Cases where DES 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 warehouse staging areas, or build machine learning models. In addition, a synchronised database can assist in reducing resource hungry queries for those advanced reporting requirements, this in turn reduces the workload on the main Microsoft Dynamics 365 system.
How to set it up
To install the Data Export Service go to the AppSource app page. Follow the steps to install the app and wait for the installation to finish. You must be a Microsoft Dynamics 365 administrator to install applications from AppSource. You can monitor the installation status in the Microsoft Dynamics 365 Admin Centre. Once installed, login to Microsoft 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 N:1 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 Microsoft 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 synchronise data with external systems.
Most of the out of the box entities are already enabled. For custom entities, you must explicitly do so.
Once the profile is activated, the data synchronisation will start – first with metadata sync, followed by initial data sync, then with continuous delta data sync.
During initial sync, the existing data in Microsoft Dynamics 365 for added entities is synchronised with the destination database.
After initial sync, your Microsoft Dynamics 365 system is monitored for changes will be synchronised going forward, and any create, modify or delete record change for entities added to export profile is automatically synchronised.
Metadata sync automatically creates and updates tables in the destination database. Tables will be created automatically for various entities such as Account, Lead, Opportunity, Product and Campaign entities. Refer to the TechNet documentation link here on details of all the tables that are created including metadata specific tables for Option sets.
To find out more about Data Export Service in Microsoft Dynamics 365, please get in touch
