Guido Leenders on 27 Sep 2022 06:33:09
For OData4-downloads, users experience great reliability when using Power BI Desktop. When the same load is moved to Azure Data Factory, it also works great. However, with larger datasets (starting at 10 MB), OData-downloads intermittently fail with the error:
Data source error
Unable to read data from the transport connection:
An existing connection was forcible closed by the remote host.
DataSourceKind = OData
DataSourcePath = https://bridge-online.cloud/naam/odata4/Twinfield.Twinfield.GeneralLedgerDetailsV3@tfd
OData: Unable to read data from the transport connection:
An existing connection was forcibly closed by the remote host.
The exception was raised by the IDataReader interface.
Please review the error message and provider documentation for further information and corrective action.
Table: GeneralLedger (Test).
Cluster URI: WABI-NORTH-EUROPE-redirect.analysis.windows.net
Activity ID: f2282333-c403-4b78-9362-b10e081cd568
Request ID: 7b3d9747-b8d1-9c4f-1b67-1feb49d1779f
Time: 2022-09-27 04:50:57Z
It seems to relate strongly with the size of the dataset. Switching to Power BI Premium often helps, as does reducing the dataset. However, reduction is not always realistically possible. There are two known causes:
- PowerBI.com downloads the whole set from the OData4-producer, ultimately HTTP 200 status. Within one minute after that, it seems to process the data and then crash. And retry the download again. And again. And again. It reports this (vague) error above.
- PowerBI.com downloads the whole set from the OData4-producer, ultimately sends a RST_STREAM on HTTP/2. It does not try again and report the (vague) error above.
The problem has been occurring for a number of years and limits the use of PowerBI.com in larger enterprise scenarios, as well as larger accounting firms.
Other samples include:
- https://forums.invantive.com/t/in-power-bi-robaws-connector-odata-unable-to-read-data-from-the-transport-connection-an-existing-connection-was-forcibly-closed-by-the-remote-host/439/2 (Dutch)
I would welcome increasing the scaleability of the OData4 consumer to handle OData4-data sets above 100 million characters more gracefully.