Export Data from D365 FO using Synapse Link

Introduction to Azure Synapse Link for D365FO

Microsoft Introduced Azure Synapse Link for Dataverse to export data from D365 finance and operations apps into Azure Synapse Analytics or Azure Data Lake Storage Gen2. The current version supports both D365FO Entites and raw tables. The previous technology such as Bring Your Own Database (BYOD) and ‘Export To DataLake’ needs to be transitioned to ‘Synapse Link for Dataverse’. The aim is to unify the approach to export the data from the Dynamics 365 platform, regardless of whether it is D365CE or D365FO. The new feature provides scalability, high availability, and disaster recovery capabilities.

The Azure Synapse Link for Dataverse allows you to choose standard and custom finance and operations entities and tables. It supports continuous entity and table data replication, including create, update, and delete (CUD) transactions. It provides an option to link or unlink the D365FO environment to Azure Synapse Analytics and/or Data Lake Storage Gen2 in your Azure subscription without the need for external tools or configuration. Data is stored in the Common Data Model format, which provides semantic consistency across apps and deployments.

Features of Azure Synapse Link for Dataverse D365FO Data

Azure Synapse Link for Dataverse offers the following features that you can use with finance and operations data:

1. Both standard and custom Entities and Tables can be exported. The older techniques such as BYOD supported only Entities and ‘Export to Data Lake’ supported only raw tables. This feature provides supportability for both BUT the entity support is still being limited to a few entities. The supportability of the entities is growing with each release of D365FO.
2. Continuous replication of Data: Create, update, and delete (CUD) transactions of records are continuously pushed to Azure Data Lake. This feature doesn’t impact the performance of D365FO, unlike BYOD.
3. Possible to link or unlink the D365FO environment to multiple Azure Synapse Workspace and/or Data Lake Storage Gen2 in your Azure subscription. The Data Lake and Workspace should be in the same region as the D365FO environment.
4. The data can be exported in both CSV and Parquet Delta Lake format. Parquet Delta format is recommended due to its better read performance, support for ACID and Schema evolution/drift. ACID transactions ensure data consistency even in the face of concurrent read and write operations.
5. The number of tables that can be exported in the Export to Data Lake feature limit isn’t applicable in Azure Synapse Link for Dataverse.

It is crucial to carefully assess the cost implications when transitioning to Synapse Link. As the volume of data grows and the refresh frequency decreases, the Azure running costs associated with the Synapse Link feature can become notably high.

Data to export options for D365FO using Synapse Link.

There are three options to export D365FO Tables while exporting data using Synapse Link.

Option 1: Export to Azure Data Lake in CSV Format (BYOL CSV)

This option exports D365FO data to Azure Data Lake in CSV format within your Azure subscription. This mirrors the familiar “Export to Datalake” feature in D365FO. The data resides in Azure Data Lake, enabling downstream applications to build their data pipelines for optimal consumption. Organizations with dedicated Data and Analytics capabilities may find this option ideal. Costs are primarily associated with Data Lake storage, with additional expenses tied to downstream data read, copy, and transformation. Integration with Synapse and Apache Spark pool is optional for this option.

Option 2: Export to Azure Data Lake in Delta Parquet Format

Choose this option to export D365FO data to Azure Data Lake but in the Delta Parquet format. This format enhances efficiency, and query performance and supports ACID transactions and Schema evolution/drift. To enable this conversion, provisioning a “Synapse Workspace” and an Apache Spark pool within your Azure subscription is necessary for the CSV-to-Delta format transformation. Similar to Option 1, downstream applications retain the flexibility to customize their pipelines to suit their specific needs. Costs encompass Data Lake storage, Azure Synapse workspace, and Apache Spark pool expenses. However, it’s important to note that frequent delta conversions with a high volume of data changes might lead to substantial Apache Spark pool costs. Additionally, downstream data consumption and conversion expenses should also be considered. This option ensures enhanced performance and adaptability but requires careful monitoring to manage associated costs effectively.

Option 3: Export to OneLake

For a streamlined solution, opt to export D365FO data directly to OneLake. This approach minimizes the need for downstream applications to manage data conversions or copying processes. OneLake acts as a centralized repository, simplifying data access and utilization. This option is well-suited for organizations seeking a seamless and integrated data export process. Costs are associated with OneLake storage (This would be part of the Dataverse storage cost). Here you don’t have the Azure Synapse workspace and Apache Spark pool expenses. However, it’s important to note that OneLake support is Limited to D365 FO and CE entities.

Microsoft is actively working on integrating D365FO tables into OneLake, and we anticipate that this feature will be available in the near future.

Cost comparison for the options

Criteria	(Option1) Synapse link BYOL (CSV)	(Option2) Synapse link BYOL (Delta)	(Option3) Microsoft OneLake with Fabric
Ease of Administration	Customer managed PaaS resources – Storage account	Customer managed PaaS resources – Storage account, Synapse & Spark Pool	Managed by Microsoft
Security	Firewall support on storage account	Firewall support on storage account and synapse workspace	Managed by Microsoft
Query Performance	Data format: CSV No read-write contention when reading completed Incremental update folder.	Data format: Delta No read-write contention as Delta supports ACID Better-read performance	Data format: Delta No read-write contention as Delta supports ACID Better-read performance
End-to-End Data Freshness	Data freshness (Configurable): 15 minutes – 24 hours	Data freshness (Configurable): 15 Minutes – 24 hours	Data freshness: ~1 hour
Data Write and Storage Costs	Storage + Transaction cost	Storage + Transaction + Delta conversion – Spark pool cost	Dataverse capacity (entitlement + add-on)
Compute Cost	Downstream data pipeline cost	Downstream data pipeline cost	Fabric capacity cost

Setting up Data export from D365 FO using Synapse Link

Prerequisites for setting Synapse Link for D365 FO Data

• D365FO environment that’s version update 10.0.34 (PU 58) or later
• Microsoft Power Platform integration for the D365FO environment
• Enable the Sql row version change tracking configuration key.
• Access to Azure subscription with the following resource provision
  • Azure Gen 2 Storage account in the same region as the D365FO environment
  • Azure Synapse Analytics workspace in the same region as the D365FO environment
  • Azure Synapse Spark pool with version 3.1 or later

The following access is required to configure the Synapse Link.

More information can be found here

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-select-fno-data

Required Permission	Description
The user must have the Dataverse system administrator security role for the Dataverse environment	A D365FO environment is connected to a Dataverse environment. To enable Synapse Link, the user should be the administrator of the environment.
The User must have an Azure Data Lake Storage Gen2 account Owner and Storage Blob Data Contributor role access	The Synapse Link exports the data from Dataverse to a Blob storage using Microservice. The user who is configuring the Synapse Link should have Owner access to manage the Access control of the storage account.
The user must have a Synapse Administrator security role for Synapse Workspace	The Synapse Link connects to an Azure Synapse workspace. The user who connects these two services should be the workspace administrator. This Synapse workspace will also create a Spark pool instance to convert files from CSV to Parquet format.
The user should have User Access Administrator and Contribute permission on the resource group.	In the resource group where Azure Data Lake and Synapse workspace are present, users should be able to manage these resources, such as creating a Spark pool.

Connect Azure Synapse Link for Dataverse with Azure Synapse Workspace

The following links explain how to connect the Azure Synapse Link for Dataverse (which is connected to the D365FO environment) with your Azure Synapse Workspace.

• https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-synapse
• https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-data-lake
• https://tldr-dynamics.com/blog/setup-azure-synapse-for-dataverse

Azure Synapse Link for Dataverse FAQ

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/export-data-lake-faq

Summary

Azure Synapse Link for D365FO offers a seamless data export solution for reporting, analysis, and integration. Its scalability ensures efficient handling of growing data volumes without impacting D365 performance. Enhanced performance capabilities enable real-time data processing, analysis and Integration.

• The Integration Gap: Why 95% of Enterprise AI Projects Fail and How Azure Integration Services Solves It
• Microsoft Copilot Studio vs Azure AI Studio: A Feature Overview
• Leveraging Azure OpenAI and Cognitive Search for Enterprise AI Applications
• Logging and Monitoring strategy for Azure Integration Components using OpenAI
• Deploying Azure Logic Apps: PaaS vs. App Service Environment (ASE)
• Triggering a YAML Pipeline in Azure DevOps Automatically After Another Pipeline Completes