Dynamics Community Forum Thread Details

Issues Implementing PBI Dataflows for Dynamics 365 | Connections Forcibly Closed

Hello,

I have Power BI dataflows connected to our Dynamics 365 environment for custom reporting.

The issue is, we are having to ping multiple tables and therefore create multiple dataflows for the semantic models of some reports.

To reduce query time we staggered the dataflows and separated our extraction and transformation layers.

Simple example, *Dataflow 1: data pull -> **Dataflow 2: data transformation.

*Dataflow 1 will contain some transformation, but limited to reducing the table size. (e.g., filtering rows and removing columns).

**Dataflow 2 will contain the heavy transformations including all the joins.

The refreshes are then scheduled with PowerAutomate.

e.g., 1. Refresh Dataflow 1. -> 2. Once Dataflow 1 Refresh Completed... -> 3. Refresh Dataflow 2. -> ... etc.

With this approach we are able to reduce the query times of the datapull tables/dataflows to sub 1 min 30 seconds.

Still, on some refreshes, the connection to Dynamics will be forcibly closed by host.

Ref. error code:

DataSource.Error: OData: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.:

Is there a better approach to mitigate the issue?

Regards!

Hi JM-26051826-0,

1) Issue
Intermittent failure of Power BI Dataflows when querying Dynamics 365 via OData, with error:
"DataSource.Error: OData: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host."
This occurs despite optimized, staged dataflows and staggered refresh orchestration using Power Automate.

2) Reason
The issue is typically caused by one or more of the following underlying factors:
- OData Endpoint Limitations (D365): Dynamics 365 OData endpoints are not designed for high-throughput, long-running, or highly parallel extraction workloads. Large or complex queries (especially with joins or expansions) can result in throttling or connection termination.

- Server-Side Throttling / Resource Governance: D365 enforces service protection limits (API throttling). Even staggered loads can exceed limits if multiple tables or dataflows are queried in short succession.

- Connection Timeout / Network Instability: Long-running queries or large payloads increase the likelihood of dropped TCP connections or gateway timeouts.

- Inefficient Query Patterns (Even if Split): Although transformations are split, multiple dataflows still independently hit D365, multiplying connection overhead and increasing the chance of intermittent failures.

- Pagination / Data Volume Issues: If dataflows are pulling large datasets without optimized pagination ($top, $skip, or incremental logic), connections may be terminated mid-stream.

3) Resolution
To mitigate and improve stability, implement the following best practices:
- Use Data Export Service / Azure Synapse Link (Preferred Approach): Instead of querying OData, replicate D365 data into Azure Data Lake or Synapse. This eliminates API throttling and significantly improves performance and reliability.

- Implement Incremental Refresh in Power BI Dataflows: Reduce data volume per refresh by only pulling new or changed records using date/time filters.

- Minimize Calls to D365 (Reduce Dataflow Fan-Out): Consolidate extraction into fewer base dataflows where possible. Avoid multiple dataflows hitting D365 in parallel or near-sequence bursts.

- Optimize OData Queries: Explicitly select only required columns, Apply strong filters at source, Avoid unnecessary expansions/navigation properties, Ensure server-side filtering is used (query folding)

- Increase Resiliency with Retry Logic: Modify Power Automate flow to include retry policies and delays when triggering dataflow refreshes.

- Introduce Buffering Layer (Staging Storage): Persist extracted data into a staging layer (e.g., ADLS, Dataverse export, or SQL) before transformation. This reduces repeated calls to D365.

- Monitor Service Protection Limits: Review D365 API usage and ensure requests stay within limits. If needed, space refresh schedules further apart.

- Use Enhanced Compute Engine (Power BI Premium): If available, enable Enhanced Compute Engine for dataflows to improve transformation efficiency and reduce repeated source hits.

- Check Gateway / Network Stability: If using an On-Premises Data Gateway, ensure it is properly sized and not causing connection drops.

Overall Recommendation:
The most sustainable solution is to move away from live OData extraction and adopt a replication-based architecture (e.g., Synapse Link or Data Lake export). This aligns better with reporting workloads and avoids connection termination issues entirely.

Rg,

Alexander

*Due to the complex and different possibilities of deploying Dynamics 365 I highly recommend not to setup the application without some expert/partner or support. (For more information contact me under anassl@inno-solutions.info or visit www.inno-solutions.de)

*The Information comes directly from the manufacturer or provider and are validated (not guaranteed) up to date of creation of the posting.

References: