Business Central data source error - An existing connection was forcibly closed by the remote host.

[CampBI]

Hello Everyone, Asking for help, Is there anyone have an experience in resolving Power bi connected to MS Business Central Dynamics 365 having issues with the scheduled refresh/ on demand refresh and Desktop app refresh with  this Data source error: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.. the exception was raised by the IDataReadwr interface.? Thank you, I was blocked by this issue..your advice and suggestions would definitely help…

Background: We are pulling data from MS Dynamics 365 Business Central using Webservices. 

[v-hjannapu]

Hi @CampBI,
Thank you for posting your query in Microsoft Fabric Community Forum. Also, thanks to  @GilbertQ  , for those inputs on this thread. 

 Unable to read data from the transport connection An existing connection was forcibly closed by the remote host  usually happens when Power BI tries to connect to Business Central, but the connection breaks in between. Since you are using Web Services to pull data, there could be a few reasons:

If you are pulling a large amount of data at once, the connection may time out. Try reducing the data or breaking it into smaller parts. Business Central may close the connection if the request takes too long or if too many requests come at once.

Sometimes, a firewall or proxy between Power BI and Business Central may block the connection. You can check with your network/IT team for this. If you are refreshing in Power BI Service, make sure your on-premises data gateway is installed properly, updated, and has access to Business Central.

It’s also possible that Business Central service was down or busy when the refresh happened. You can check the Microsoft 365 Service Health portal if you have access.

Gothroygh with below document link hope it may resolve the issue :
https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/webservices/web-service-pbi...
https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-troubleshooting-refresh-scenarios
Hope this helps if you have any queries we are  happy to assist you further.
Regards,
Harshitha.

[CampBI]

is there any way to update the timed out up to 5 hours, currently the refresh schedules from power bi service and Desktop refresh was about 3 hours or more.. we tried to rebuild the dashboard but still getting errors...regarding on the on premises data gateway, where I could find this?

[v-hjannapu]

Hi @CampBI,
Thanks for followup

In Power BI Service, the refresh timeout depends on your workspace type.

• If you are using a Pro workspace, the refresh will fail after around 2 hours.
• To go beyond that, like up to 5 hours, you need to use Power BI Premium like P1 or Fabric capacity. That allows refreshes for up to 5 hours.

In Power BI Desktop, there's no fixed timeout, but still, the connection can break if Business Central closes it from their side due to long processing.

Now, regarding the on-premises data gateway If you are pulling any data from on-premise systems like SQL Server or files on your local server, then you need a gateway. But if you are using only Business Central online, usually gateway is not required.

Anyway, you can check it like this:

Open Power BI Service → click the ⚙ gear icon → Manage gateways

Or you can download and install the gateway from this link:
https://www.microsoft.com/en-us/power-platform/products/power-bi/gateway

Once it's installed, just sign in and map your data sources. Since your refresh is taking more than 3 hours and still failing, I suggest trying to reduce the data load or split your queries if possible like filtering data by date or breaking large tables into parts.

If you have any Issues, please feel free to post back.
Thank you

[CampBI]

Yes you are right we are connecting through Webservices online without on premises data gateway. We only have Pro license and I guess it's not possible to get premiuim as per the cost. We tried to minimize the coulmns and query steps but still getting this errors.

Could you suggest or by any chance do you know how to connect via API to Business Central? or having Power Apps connected first to Business Central then Power Apps to Power Bi? Thank you.

[v-hjannapu · Accepted solution]

Hi @CampBI,

Since you are using Business Central Online with Pro license, the 2-hour refresh limit in Power BI is expected. To go beyond that, Premium is needed, but I understand it may not be possible right now.

Instead of Web Services, you can try using Business Central APIs they’re lighter and may reduce timeout issues.
You can connect in Power BI using the API link like this "https://api.businesscentral.dynamics.com/v2.0/{tenant_id}/{environment}/api/v2.0/".

Just use Web or OData connector and sign in with your Business Central account. And about PowerApps yes, you can pull data from Business Central to Power Apps, then connect Power BI to that data (like from Excel or SharePoint). But please check licensing once for Power Apps.

I suggest trying the API method first, as it’s simpler and works well in many cases.

Best Regards,

Harshitha.

[CampBI]

Hi @v-hjannapu Thank you for this. I don't have background connecting using the API is this needs admin access? Thank you.

Can I use dataverse as database if the API is not set up? Could this help on the refresh real time?

[v-hjannapu]

Hi @CampBI,
Yes, to connect using the API, you might need some help from your admin mainly to get the tenant ID, environment name, and to confirm API access is allowed in Business Central. Once that’s done, you can connect from Power BI.
Kindly refer to the below documentation links for better understanding:
https://learn.microsoft.com/en-us/dynamics365/business-central/admin-powerbi-setup
https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/api-reference/v2.0/

coming to dataverse  yes, if your data comes into Power Apps, you can use Dataverse as a middle layer. Power BI can connect to Dataverse using DirectQuery, which helps in near real-time updates. But setup might be a bit complex and may need proper licensing.
go through with the below document hope it may resolve your Issue:
https://learn.microsoft.com/en-us/power-apps/maker/data-platform/data-platform-powerbi-connector?tab...
https://learn.microsoft.com/en-us/power-apps/maker/data-platform/use-powerbi-dataverse

Hope this helps if you have any queries we are  happy to assist you further.
Best regards,
Harshitha.

[v-hjannapu]

Hi @CampBI.,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Harshitha.

[v-hjannapu]

Hi @CampBI,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Harshitha.

[v-hjannapu]

Hi @CampBI,
I wanted to follow up and see if you have had a chance to review the information that was shared. If you have any additional questions or need further clarification, please don’t hesitate to reach out. I am here to assist with any concerns you might have.

Regards,
Harshitha.

[CampBI]

Hi Again, I would like to know how this API set up to Power Bi? For our existing report

[GilbertQ]

Hi @CampBI 

Can you please have a look at this page to confirm that you have got all the settings configure and what happens if you retry the refresh? What happens? Enabling Power BI integration with Business Central - Business Central | Microsoft Learn

[CampBI]

thank you for this, I do'nt have any experience connecting API pages but I would reached out to support team regarding this suggestion