Monday, March 19, 2018

Data Export Service and Dynamics 365 online - Introduction


This is part-1 of a multi-series blog on Data Export Service and Dynamics 365 online. This blog is an introduction to and discussing the use case for Data Export service.

The performance impact of Reporting and Advanced Analytics in Dynamics 365 -
Once the Dynamics 365 application goes live, the transaction and operational data gets generated and stored in the instance. Business would like to run reports on the stored data and be able to do analysis comparing data from different periods, calculating averages and other advanced analysis. 

Generating Reports in Dynamics 365 online can be complex and sometimes it can hinder performance of the system when large # of users are running complex voluminous reports. 

As a general rule Dynamics 365 is NOT a Data Warehouse Reporting application. With Dynamics 365 online it becomes imperative to limit its usage as one (Data warehouse).

There are multiple options to overcome the above restraints as discussed below –

1. Copy and restore the Dynamics 365 online instance into another (sandbox) reporting instance – Than run reports on the new instance which does not affect the current production users.
– This is a manual process and may not be ideal for ongoing daily reporting / analysis.

2. Sync data from Dynamics 365 online instance to Azure SQL and run reporting and analytics from within Azure SQL
One of the option provided by Microsoft for this is the Data Export Service with Dynamics 365 online.

What is the Data Export Service?


Data Export is an add-on service made available as a Microsoft Dynamics 365 (online) solution that adds the ability to replicate 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 Azure SQL Server on Microsoft Azure virtual machines. Data Export intelligently synchronizes the entire Dynamics 365 schema and data initially and thereafter synchronizes on a continuous basis as changes occur (delta changes) in the Microsoft Dynamics 365 (online) system.”

What are the possible use cases for Dynamics 365 data in Azure SQL?

1. As a Reporting database - it reduces the constraints on the Production instance. Azure SQL can be scaled to meet the performance needs for Reporting and Analytics.

2. For Advanced Analytics using Power BI – (I will be discussing more about this in my blogs)

3. For building machine learning models – SQL Server Machine Learning Services work with Azure SQL databases and can be used for developing Machine Learning models.

As the need for AI and ML grows so will the demand for accessing the data in a fast, secure and scalable manner. Enterprise will stand to benefit using the Data Export Service and getting access to the data from Azure SQL service.

4. Providing read access to data to 3rd party vendors / partners. Multiple data export service profiles can be set-up to export data to 1 or more Azure SQL database

Pre-requisite for Data export service –

1. Azure SQL Database subscription or Azure SQL Server on Azure VM
2. Azure Key Vault subscription in the same tenant as Dynamics 365 to allow Data export service to secure access Azure SQL
3. Dynamics 365 online 2016 or later version with the Data Export solution installed. (Note: Data Export Service will not work with sandbox instances)

Installing the Data Export solution –

1. From within Dynamics 365 go to Settings > Customization's > Dynamics marketplace and search for Data Export Service. Click on Get it now and enter the necessary details

2. The Data Export Service app window opens and your current instance is selected as default. Agree to the terms

Select / Connect to Dynamics 365 instance


3. The solution will be installed within Dynamics 365 as a managed solution

Managed solution

And you will see the Data Export button in the System tab

Button

Please leave your comments and any other use case that you may consider for Data Export Service.

Thanks for reading.

Mihir Shah
365WithoutCode