When using Power BI with Invantive Cloud, we regularly notice that Power BI does not always work as conveniently as imaginable. Therefore, on behalf of the user community, we actively try to bring these issues to the attention of our users. This also happens through the Power BI community.
Should you wish to express support for an improvement suggestion, we would appreciate it. Support can be expressed by clicking the thumbs up button next to the question or (even better) if the problem is recognized, briefly indicate your own business impact.
The following points are still open, in descending order of importance:
Improve scalability PowerBI.com with OData to avoid " An existing connection was forcibly closed by the remote host."
Downloads on Power BI Desktop, Power Query and Azure Data Factory work reliably. However, larger datasets on PowerBI.com give intermittent error messages such as:
Data source error
OData:
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.
Support our idea with an upvote to improve this at:
https://ideas.powerbi.com/ideas/idea/?ideaid=b9362940-2e3e-ed11-97b0-281878deb618
Related are:
- https://community.powerbi.com/t5/Service/Dataflow-refresh-fails-with-An-existing-connection-was-forcibly/td-p/970591
- https://community.powerbi.com/t5/Service/Refresh-passed-in-the-desktop-but-failed-in-the-service-message/td-p/331543
- https://community.powerbi.com/t5/Service/Unable-to-read-data-from-the-transport-connection-An-existing/td-p/1151009
More information on download problems can be found in the Dutch overview on https://forums.invantive.com/t/powerbi-com-odata-download-issue-an-existing-connection-was-forcibly-closed-by-the-remote-host/2803
Increase default timeout.
Power BI uses a low timeout of 10 minutes for OData downloads. However, real-time downloads with Invantive Cloud are often longer. Each new user must be trained to override the default settings in the Advanced Editor as described at https://forums.invantive.com/t/avoid-timeout-error-on-power-bi-odata-download/1857. This leads to regular production outages if not done.
Please support our idea with an upvote to improve this at:
https://ideas.powerbi.com/ideas/idea/?ideaid=989055f7-6495-ec11-826d-501ac50aa35e
Reduce download volume.
Power BI can reduce the download volume up to 100x by sending a request along to omit empty values. On Twinfield this leads to huge improvements, on Exact Online for example the reduction in download is between 10% and 50%.
Please support our idea with an upvote to improve this at:
https://ideas.powerbi.com/ideas/idea/?ideaid=0ce41af1-6595-ec11-826d-501ac50aa35e
Limit download volumes and download times.
Power BI and PowerBI.com do not consider cache settings when downloading data. As a result, often hundreds of megabytes must be downloaded repeatedly. This costs time, bandwidth and money. Other solutions such as even the simple https://access-odata.com do take this into account.
Report development in particular would benefit from this.
See also:
https://ideas.powerbi.com/ideas/idea/?ideaid=b5ac99c4-563d-ec11-a3ee-281878bd4128
Avoiding PowerBI internal loops / aggressively re-downloading every time
Frequently, the PowerBI Service restarts the same download hundreds of times despite download success. As a result, numbers are not updated, and an account will be blocked over time based on the Fair Use Policy.
It is not predictable when which account will fall prey to the Power BI Service problem. We would like to see the number of attempts limited and logging made more transparent.
See also:
Automatic use IN-clause for faster processing with more administrations
Power BI currently sends all possible values to Invantive Cloud using or filter. This is not efficient. Better is to combine into an IN.
See also:
Pass duplicate and/or redundant filters
Power BI currently sends along all values blindly in an or filter. This sometimes leads to queries with dozens of identical filters. An example of this is:
With the end result being the Invantive SQL statement:
select t.*
from ExactOnlineREST.Financial.GLAccountsBulk@eol t
where ((((((((((((((((((([Division] = :w1) or ([Division] = :w2)) or ([Division] = :w3)) or ([Division] = :w4)) or ([Division] = :w5)) or ([Division] = :w6)) or ([Division] = :w7)) or ([Division] = :w8)) or ([Division] = :w9)) or ([Division] = :w10)) or ([Division] = :w11)) or ([Division] is null )) or ([Division] is null )) or ([Division] is null )) or ([Division] is null )) or ([Division] is null )) or ([Division] is null )) or ([Division] is null )) or ([Division] is null ))
limit 1000
See also:
https://ideas.powerbi.com/ideas/idea/?ideaid=fc1d58df-6195-ec11-826d-501ac50aa35e
Display meaningful OData error instead of always the same non-specific error
Power BI can not extract an OData-error from downloaded HTTP data unless the error is returned before the actual download has started. The non-specific is described on https://forums.invantive.com/t/datasource-error-odata-invalid-json-a-comma-character-was-expected-in-scope-array-every-two-elements-in-an-array-and-properties-of-an-object-must-be-separated-by-commas/1125.
The improvement is to better look for an error and extract it accordingly.
See also:
https://ideas.powerbi.com/ideas/idea/?ideaid=448e7b93-897f-ed11-a76e-281878bd0909