How to Set Up Automatic D365 Finance and Operations Data Replication to Azure Data Lake for Reporting?

Nitin Patil 20

Hi everyone,

We have a D365 Finance and Operations environment hosted in the Central US region. Our Accounts Receivable (AR) data, including customers and invoices, is integrated from an on-premises Oracle database into D365 FinOps.

I would like to automatically replicate the D365 FinOps data to an Azure Data Lake. The goal is to run analytical reports and data integration quality reports using this data.

I’m following the guidance from this Microsoft documentation: Azure Synapse Link for D365 Finance and Operations.

However, I’m struggling to find a clear step-by-step process. Specifically, I need help understanding:

What specific access and permissions are required in D365 FinOps and Azure to set this up?
What roles or resources should I request from the relevant departments to proceed?

Any detailed guidance or resources would be greatly appreciated.

Thank you!

phemanth 10,330 Reputation points Microsoft Vendor

2024-09-05T10:03:50.7733333+00:00
@Nitin Patil

Welcome to the Microsoft Q&A and thank you for posting your questions here

Enable the Export to Data Lake Feature:

Go to System Administration > Setup > Export to Data Lake in your D365 FinOps environment.

If the feature isn’t available, add &mi=DataFeedsDefinitionWorkspace to your environment URL to access the setup page.

Select Data to Export:

On the Choose Tables tab, select the data tables you want to stage in the data lake.

Add the selected tables and activate the data feed.

Set Up Azure Synapse Link:

Since the Export to Data Lake feature is being deprecated, it’s recommended to use Azure Synapse Link for a more integrated experience.

Follow the Synapse Link transition guide for detailed steps on setting up Synapse Link.

Access and Permissions:

System Administrator Role:

This role is essential for configuring and managing the Export to Data Lake feature.

It allows you to access the necessary setup pages and manage data feeds.

Data Management Permissions:

Ensure you have permissions to access and manage data entities within D365 FinOps.

This includes the ability to select and activate data tables for export.

Azure:

Azure Subscription Owner or Contributor:

You need permissions to create and manage resources in Azure Data Lake Storage and Azure Synapse Analytics.

The Contributor role is typically sufficient for setting up and managing these resources.

Storage Blob Data Contributor:

This role is required to manage data within Azure Data Lake Storage.

It allows you to read, write, and delete data in the storage account.

Synapse Administrator or Synapse Contributor:

If you’re using Azure Synapse Link, you’ll need permissions to manage Synapse workspaces and linked services.

The Synapse Contributor role is usually adequate for these tasks.

Requesting Roles and Resources:

From IT or Admin Departments:

Request the System Administrator role in D365 FinOps.

In Azure, request the Contributor role for the relevant subscription and the Storage Blob Data Contributor role for the Data Lake Storage account.

If using Synapse Link, request the Synapse Contributor role for the Synapse workspace.

For more detailed guidance, you can refer to the official Microsoft documentation on exporting data to Azure Data Lake.
Nitin Patil 20 Reputation points

2024-09-05T16:01:11.37+00:00

@phemanth

Thank you for your prompt response.

I am a System Administrator in D365 Finance and Operations and a contributor to our Azure subscription.

I’ve encountered an issue with the "Export to Data Lake" feature within D365 Finance and Operations. Upon launching the Export to Data Lake page, I receive a message stating that the feature is temporarily unavailable and to check back later. This message remains even after selecting a table (e.g., Customers - CustTable).

Additionally, under the Action Center, I see a message suggesting that I connect this Finance and Operations environment to a Dataverse instance. Is it necessary to connect to Dataverse when using the Synapse Link? If so, where can I find detailed guidance on how to accomplish this?

I've reviewed the transition guidance for the Export to Data Lake feature at the following link:

Transition Guidance

However, it doesn’t provide the step-by-step instructions I need to complete this setup.

I also attempted to follow the instructions in this link:

Azure Synapse Link Setup

But it lacks the specific steps I need to take.

Could you please provide any specific guidance?

Thanks,

Nitin
phemanth 10,330 Reputation points Microsoft Vendor

2024-09-09T11:28:54.5866667+00:00
@Nitin Patil

Here’s a detailed guide to help you transition to using Azure Synapse Link and connect your environment to Dataverse:

Understanding the Deprecation

The “Export to Data Lake” feature has been deprecated as of October 15, 2023, and will be available until November 1, 2024. Microsoft recommends transitioning to Azure Synapse Link, which offers enhanced performance and flexibility.

Connecting to Dataverse

To use Azure Synapse Link, you need to connect your Finance and Operations environment to a Dataverse instance. Here are the steps:

Prerequisites

Ensure you have at least 1 GB of Microsoft Power Platform database storage capacity. Validate the governance policy of your tenant in the Power Platform admin center. You need either the Global administrator role or the Power Platform administrator role.

Steps to Connect

Select Setup on the Power Platform Integration Tab:

Go to the Power Platform Integration tab in your Finance and Operations environment.

Select “Setup.”

Configure Dataverse Using a Template:

Follow the prompts to configure Dataverse using the provided template.

Confirm that you want to proceed with the setup.

Wait for Provisioning:

The provisioning process will take some time. Once completed, your environment will be connected to Dataverse.

Setting Up Azure Synapse Link

Once your environment is connected to Dataverse, you can set up Azure Synapse Link:

Access Azure Synapse Link:

Navigate to the Azure Synapse Link setup page in your Power Apps environment.

Select Finance and Operations Data:

Choose the tables and entities you want to export to Azure Synapse Analytics and Azure Data Lake Storage Gen2.

Continuous replication of entity and table data is supported.

Configure Data Export:

Configure the data export settings, including saving data in Parquet Delta Lake format for faster query response times.

here are the detailed guides you need:

Transition Guidance

Connect Finance and Operations to Dataverse

Azure Synapse Link Setup
phemanth 10,330 Reputation points Microsoft Vendor

2024-09-10T14:00:36.0933333+00:00

@Nitin Patil We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Nitin Patil 20 Reputation points

2024-09-10T14:37:33.73+00:00

@phemanth

Sorry for the delayed response—I should have provided an update yesterday. I haven't had a chance to try it yet, as I'll need Power Platform Administrator permissions to proceed. I'm still waiting on my networking team to grant those permissions.
Nitin Patil 20 Reputation points

2024-09-10T14:40:11.2366667+00:00

@phemanth I'll have an update by 09/12/2024.
phemanth 10,330 Reputation points Microsoft Vendor

2024-09-12T12:59:27.0866667+00:00

@Nitin Patil We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
phemanth 10,330 Reputation points Microsoft Vendor

2024-09-13T15:26:09.21+00:00

@Nitin Patil just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Nitin Patil 20 Reputation points

2024-09-13T16:15:49.24+00:00
@phemanth

The implementation paused briefly on Wednesday (09/11/2024) due to several questions from our executive team regarding the cost, security, and irreversibility of enabling the Dataverse feature. I needed to address these concerns before moving forward. My plan is to begin by enabling the feature with just two tables in the Tier 2 sandbox environment to evaluate its impact, and then progressively add more tables as we go.

Below are the details I shared with the executives, and we will be having a meeting today (09/13/2024) to discuss these points. Once all questions are resolved, I would like to proceed with the implementation. Please let me know if you'd like any additions or updates to the information provided.

Cost Analysis

Cost of Enabling Dataverse for D365 FinOps: The cost of enabling Dataverse integration is generally included in the licensing cost of Dynamics 365 apps. Purchasing a license for these finance and operations apps includes an additional 10 GB of Dataverse database capacity. Each user license also provides incremental database capacity.

Power Platform Environments: With each license purchase, you are entitled to one self-service sandbox environment and one production environment in Lifecycle Services. Initially, the Power Platform environment created does not include a Dataverse database and uses only 1 GB of database capacity. Once Power Platform integration is set up, a Dataverse database is added, consuming 3 GB or more of capacity.

Add-On Environments and Capacity: Additional sandbox environments purchased for Lifecycle Services do not come with extra Dataverse database capacity. For additional Power Platform integration needs, customers must purchase add-on Dataverse storage. Microsoft provides additional storage in increments, typically costing $40 per GB per month. (For our scenario, it is anticipated that we will not need additional Dataverse storage, as the estimated requirement for customer and invoice-related tables is less than 10 GB. Actual storage consumption will be confirmed post-migration. For precise pricing, consult Microsoft’s official pricing details or contact a Microsoft sales representative.)

Power Platform Costs: Registering Microsoft.PowerPlatform in the DEV subscription can be done at no additional cost using the Power Apps Developer Plan, which offers a free development environment. The Power Apps premium plan costs approximately $20 per user per month. (For our scenario, development incurs no cost, but production could cost $20 per month per user plus any additional charges for Dataverse storage if needed.)

ADLS2 Storage Costs: For Azure Data Lake Storage (ADLS) Gen2, the cost varies by storage tier:

Hot Tier: $0.021 per GB per month

Cool Tier: $0.016 per GB per month

Cold Tier: $0.0036 per GB per month

Transaction costs for each tier are:

At $0.065 per 10,000 write operations: $0.65 per month

At $0.13 per 10,000 write operations: $1.30 per month

At $0.234 per 10,000 write operations: $2.34 per month

Additional costs include read operations, geo-replication, outbound data transfer, and changing the default account access tier_. (Initially, we expect to remain well below $XX per month. Costs may increase as data volumes and usage patterns change. Regular reviews and updates to cost calculations are recommended.)_

Azure Synapse Analytics Costs: Querying data from ADLS2 using Azure Synapse Analytics costs $5 per TB of data processed.

2. Data Security

Data access will be secured using Role-Based Access Control (RBAC. Azure Synapse supports encryption in transit and at rest. As an Azure Data Solution Architect, I will ensure:

Encryption settings are enabled for data at rest and in transit

Access control and Azure roles are properly implemented

Row-Level and Column-Level Security are established

Dynamic data masking and conditional access are enabled

Data exfiltration protection is implemented to monitor and control data movement out of the Synapse workspace

May use ACL in parallel with RBAC

3. Irreversibility of Power Platform Connection

From my research, "irreversible" in the context of linking a Finance and Operations apps environment to a Microsoft Dataverse instance means that once the integration is established, it cannot be undone without significant consequences. Disconnecting the systems may cause inconsistencies: while the D365 data remains intact, the Dataverse environment will be impacted. Thus, "irreversible" indicates that reversing the integration results in permanent effects on Dataverse, though the D365 data remains unaffected.

4. Tier 1 Cost

I did not pursue this further as configuring the environment to work with Dataverse would require additional effort and potentially involve extra settings. However, it’s important to note that deleting the Dataverse environment will not affect the D365 data.
Nitin Patil 20 Reputation points

2024-09-13T16:29:00.9+00:00

@phemanth The implementation paused briefly on Wednesday due to several questions from our executive team regarding the cost, security, and irreversibility of enabling the Dataverse feature. I needed to address these concerns before moving forward.
phemanth 10,330 Reputation points Microsoft Vendor

2024-09-17T13:46:59.9733333+00:00
@Nitin Patil

It's great to hear that you're addressing the concerns of your executive team regarding the cost, security, and irreversibility of enabling the Dataverse feature. I'll provide some additional information to help clarify these points.

Cost Analysis

Your cost analysis is thorough, and you've covered the key points. To summarize:

The cost of enabling Dataverse integration is included in the licensing cost of Dynamics 365 apps.

Each user license provides incremental database capacity.

Additional sandbox environments purchased for Lifecycle Services do not come with extra Dataverse database capacity.

Microsoft provides additional storage in increments, typically costing $40 per GB per month.

Power Platform costs, including the Power Apps Developer Plan and premium plan, are also outlined.

Data Security

Your approach to data security is robust, and you've covered the essential points:

Role-Based Access Control (RBAC) will be used to secure data access.

Azure Synapse supports encryption in transit and at rest.

You'll ensure encryption settings are enabled for data at rest and in transit.

Access control and Azure roles will be properly implemented.

Row-Level and Column-Level Security will be established.

Dynamic data masking and conditional access will be enabled.

Data exfiltration protection will be implemented to monitor and control data movement out of the Synapse workspace.

Irreversibility of Power Platform Connection

Your explanation of the irreversibility of linking a Finance and Operations apps environment to a Microsoft Dataverse instance is accurate. Once the integration is established, it cannot be undone without significant consequences. Disconnecting the systems may cause inconsistencies, and reversing the integration results in permanent effects on Dataverse.

Next Steps

Now that you've addressed the concerns of your executive team, you can proceed with the implementation. As you mentioned, you plan to begin by enabling the feature with just two tables in the Tier 2 sandbox environment to evaluate its impact, and then progressively add more tables as you go.

Please let me know if you have any further questions or need additional clarification on any of these points.
Nitin Patil 20 Reputation points

2024-09-18T20:22:16.89+00:00

@phemanth

I have successfully integrated the Finance and Operations apps environment with a new Dataverse instance, creating a cohesive logical environment. I followed the guidance from the Microsoft Documentation. (https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/power-platform/environment-lifecycle-connect-finops-new-dv). Additionally, I have requested two Power Platform environments for our D365 Finance and Operations instances to ensure clear segregation of duties between them.
phemanth 10,330 Reputation points Microsoft Vendor

2024-09-19T11:36:04.06+00:00

@Nitin Patil Glad to know your issue has been resolved. Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.
phemanth 10,330 Reputation points Microsoft Vendor

2024-09-23T11:30:11.49+00:00

@Nitin Patil Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Nitin Patil 20 Reputation points

2024-09-23T18:26:20.4633333+00:00

@phemanth After successfully integrating the Finance and Operations apps environment with a new Dataverse instance, I followed the steps in the Power Apps to set up Azure Synapse Link using the instructions in this YouTube video.https://www.youtube.com/watch?v=NFOUkKPAWOo&list=PLzUAjXZBFU9NucnyOxBB-bHdhfaLiVVgn&index=3

I set up the Azure Synapse Link, and added the tables.

Upon reviewing the available tables, I realized that the Dynamics 365 FinOps tables are structured differently from those that are available in Azure Synapse Link in Power Apps. In D365 FinOps, we use the CustomersV3 entity, which is seen as CustomersV3(Account) and CustomersV3(Contact). This led me to select Account and Contact from the Azure Synapse Link setup page. However, it seems these selections might not correspond directly to the D365 entities I was expecting.

I selected the Account and Contact tables for syncing. The synchronization process started and completed successfully, but the record count was 0.

Could you provide guidance on how to get the D365 FinOps Entity data ? or confirm if there is a specific process to ensure the correct table mapping?
Nitin Patil 20 Reputation points

2024-09-23T20:13:31.74+00:00

@phemanth

I was unable to view the CustomersV3 entity in Azure Synapse Link.

To troubleshoot, I enabled change tracking for both the primary table and the custom query. However, the option to "Enable entire entity" was disabled, so I proceeded with enabling change tracking for the primary table and custom query.

Despite these steps, I still couldn't view the CustomersV3 entity in Azure Synapse Link. Could you advise on how to resolve this or if there are additional steps required to make the entity visible in Synapse Link?
phemanth 10,330 Reputation points Microsoft Vendor

2024-09-25T12:11:52.0766667+00:00
@Nitin patil

Check if the entity is enabled for data export and that change tracking is properly configured.

The CustomersV3 entity in D365 FinOps might not directly map to the Account and Contact tables in Azure Synapse Link.

Review the entity mappings and ensure that the correct tables are selected for synchronization. You might need to map custom entities manually.

Enable Change Tracking:

Ensure that change tracking is enabled for the CustomersV3 entity. This is crucial for data synchronization.

If the option to “Enable entire entity” is disabled, make sure that change tracking is enabled for all relevant fields within the entity.

Use Azure Synapse Link for Dataverse:

Azure Synapse Link for Dataverse allows you to choose both standard and custom finance and operations entities and tables.

Ensure that you are using the correct version of Azure Synapse Link that supports finance and operations data.

Refer to the Microsoft documentation on choosing finance and operations data in Azure Synapse Link for Dataverse for detailed steps and troubleshooting tips 1.

The documentation provides guidance on setting up and managing data synchronization, including entity selection and change tracking.

Troubleshooting Steps:

Check Service Health: Verify the health of the D365 FinOps and Azure services to ensure there are no ongoing issues.

Retry Synchronization: Sometimes, re-initiating the synchronization process can resolve temporary issues.

Contact Support: If the issue persists, consider reaching out to Microsoft Support for assistance.

Export to Data Lake in finance and operations apps for more details on data export and troubleshooting.

I hope these steps help you resolve the issue and successfully synchronize your D365 FinOps data with Azure Synapse Link. If you have any more questions or need further assistance, feel free to ask!

1: Microsoft Documentation - Choosing finance and operations data in Azure Synapse Link for Dataverse

2: Microsoft Documentation - Export to Data Lake in finance and operations apps
Nitin Patil 20 Reputation points

2024-09-25T20:02:37.53+00:00
@phemanth

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

I made a progress.I can now view the D365 Finance and Operations tables, and this option appears right under the Dataverse option, as shown in the screenshot below.

While I don’t see the entities, I can access the underlying tables such as "CustTable," "Tax1099Fields," and "Accountant_BR."

You are correct that the CustomersV3 entity in D365 FinOps does not directly map to the Account and Contact tables in Azure Synapse Link.

Currently, change tracking cannot be enabled for all Finance and Operations entities. I ran the Data Entity Row Version Change Tracking Validation Report within the Finance and Operations apps, and it showed that CustomersV3 failed the validation rules.

Additionally, I learned today that our partner vendor uses a custom entity built on top of CustomersV3. Since it’s derived from CustomersV3, I assume it would also fail the validation test. Please correct me if I’m wrong. For this reason, I am leaning toward using the base tables instead.

In my research, I found this link: Microsoft GitHub - Entity Util, and I plan to spend some time reviewing it. Could this resource help in understanding how an entity is formed using the base tables?

Given that I can access the base tables, I went ahead with a POC using CustTable. I selected CustTable, saved the Azure Synapse Link, and encountered two issues:

Advanced Options Issue:

The advanced options were enabled by default, with “Append Only” checked and “Partition by Year” set, and I couldn’t modify these settings. I later realized this is the default setup with D365 F&O, so I’m not too concerned about it. I know it will cost us more in terms of space and queries will me more complex.

Merge Error with CustTable:

With these advanced options enabled, the initial merge process (using CustTable) starts but eventually errors out. The error suggests creating a support ticket, which I did yesterday. The error message reads: "SPARK-808: Spark compute pool in Synapse workspace wsazjadevd365finops is insufficient. One or more tables could not be updated. Visit aka.ms/SynapseLinkErrors to fix"

I have configured the Spark pool according to Microsoft’s guidelines, but the error persists. CustTable has around 250K records, so it’s not exceptionally large.

I have also enabled change tracking on the CustomersV3 entity for its Primary table and custom query. However, the option to enable change tracking for the entire entity is grayed out, consistent with the validation report findings. Since I enabled change tracking on the Primary table, the CustTable was automatically enabled as well.

One other factor is that my Azure Synapse workspace is in a different region from D365F&O. D365 F&O is in East US, while the Synapse workspace, Storage account, and Spark pool are in North Central US. According to Microsoft, this configuration should only impact performance, not cause errors.

Any guidance on resolving the SPARK-808 issue would be greatly appreciated.

1 answer

phemanth 10,330 Reputation points Microsoft Vendor

2024-09-19T11:35:36.4666667+00:00
@Nitin Patil

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

**Ask:**We have a D365 Finance and Operations environment hosted in the Central US region. Our Accounts Receivable (AR) data, including customers and invoices, is integrated from an on-premises Oracle database into D365 FinOps.

I would like to automatically replicate the D365 FinOps data to an Azure Data Lake. The goal is to run analytical reports and data integration quality reports using this data.

I’m following the guidance from this Microsoft documentation: Azure Synapse Link for D365 Finance and Operations.

However, I’m struggling to find a clear step-by-step process. Specifically, I need help understanding:

What specific access and permissions are required in D365 FinOps and Azure to set this up?

What roles or resources should I request from the relevant departments to proceed?

Any detailed guidance or resources would be greatly appreciated.

Solution: I have successfully integrated the Finance and Operations apps environment with a new Dataverse instance, creating a cohesive logical environment. I followed the guidance from the Microsoft Documentation. (https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/power-platform/environment-lifecycle-connect-finops-new-dv). Additionally, I have requested two Power Platform environments for our D365 Finance and Operations instances to ensure clear segregation of duties between them.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Yes
Please sign in to rate this answer.
Nitin Patil 20 Reputation points

2024-09-19T13:47:57.5033333+00:00

@phemanth I am not yet complete, but I have successfully integrated the Finance and Operations apps environment with a new Dataverse instance.

Next, I need to complete several steps, including setting up the existing Azure Synapse Link I created and selecting the relevant tables from D365 Finance and Operations. Once these tasks are completed, I will finalize the integration.

Thank you for your support! (I have voted one of your suggestions because I am referring to it.)

Nitin Patil 20 Reputation points

2024-09-19T14:11:00.1333333+00:00

@phemanth Thanks for your support and patience. I am not yet complete, but I have successfully integrated the Finance and Operations apps environment with a new Dataverse instance.

Next, I need to complete several steps, including setting up the existing Azure Synapse Link I created and selecting the relevant tables from D365 Finance and Operations. Once these tasks are completed, I will finalize the integration, and mark it as answer.

phemanth 10,330 Reputation points Microsoft Vendor

2024-09-20T11:28:15.1766667+00:00

@Nitin Patil Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to Set Up Automatic D365 Finance and Operations Data Replication to Azure Data Lake for Reporting?

1 answer

Your answer