Forum Discussion
Power Bi Report with Multiple sources Automatic refresh .
Hello
I had a report that just downloaded data from Cloud Microsoft Dynamics Business Central.
I have added a new table to it which is connect to a on prem SQL Server database table.
When it was just Business Central the scheduled download worked fine, now its both it does not refresh.
I think it trying to connect to Business Central via the gateway, but it doesn't need to do this.
How do I fix?
Regards
GrahamR99
Hello Anonymous
My Gateway settings match yours.
My dataset I think is the problem, please see screen shot below;
There is a red X for the Business Central connection.
Regards
GrahamR99
The red X next to business central connect was the key here; I had to create a connect to business central in the gateway, once this was done the download worked.
19 Replies
Did you setup a gateway connection for your on-prem source? Does your network require a proxy to reach the Azure cloud?
Hi GrahamR99 ，
Based on your description, it seems that your report has now added a on-premise data source SQL Server in addition to the original data source Microsoft Dynamics Business Central. Microsoft Dynamics Business Central is a cloud data source, so it‘s not necessary to install and configure a gateway for scheduled refreshe. But SQL Server is a on-premise data source and it is required. Gateway is essential if there are data sources that are available on-premises and not on the cloud. The gateway acts as the bridge between the Power BI Cloud Service and the on-premise data sources through a secured network. You can refer to the following blog to install and configure the gateway for scheduled refresh.
REFRESH DATA FROM AN ON-PREMISES SQL SERVER DATABASE
Also, you can review the content in the following links for more details of every step...
1. Install an on-premises data gateway
Install an on-premises data gateway
The Power BI Gateway; All You Need to Know
2. Configure gateway in Service
Manage your data source - SQL Server
3. Configure schedule refresh
One more thing to note is that your report contains both on-premises and cloud data sources, please select the option Allow user's cloud data sources to refresh through this gateway cluster to ensure that you can merge or append on-premises and cloud data sources in the same query.
Best Regards
Hello Anonymous
I have a gateway already setup, and I have other reports that connect to the same on-prem source.
I think it trying to connect to the cloud using the gateway, is that correct?
There is a setting in the gateway management page to allow refresh of cloud data sources through the gateway. In essence you are providing free compute resources to Microsoft (in addition to paying for the capacity). You may or may not want to do that.
We have seen scenarios where a mix of on-premise and SharePoint sources required this to be enabled.
Hi GrahamR99 ,
Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? Or you can share your solution and mark it as Answered. Thank you.
Best Regards
Hello Anonymous
We have updated the gateway software on the server and we have a better error message, but it still does not update.
Can you help with the error message?
This is the error message;
Last refresh failed: Thu Dec 09 2021 10:44:16 GMT+0000 (Greenwich Mean Time)
Unable to connect to the data source undefined.Hide detailsUnderlying error code: -2147467259 Underlying error message: Dynamics365BusinessCentral: Request failed: The remote server returned an error: (500) Internal Server Error. (The JWT token is not valid because its validity period has ended. CorrelationId: ef52de47-81fb-4f03-93bb-68c07c26b3ee.) DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259 Microsoft.Data.Mashup.ValueError.DataSourceKind: Dynamics365BusinessCentral Microsoft.Data.Mashup.ValueError.DataSourcePath: Dynamics365BusinessCentral Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error Microsoft.Data.Mashup.ValueError.Url: https://api.businesscentral.dynamics.com/v2.0/PRODUCTION/OData/DimensionSetEntries?company=Trading
Cluster URI: WABI-NORTH-EUROPE-redirect.analysis.windows.net Activity ID: bfc06239-e6b1-4cd5-8701-382375ea1046 Request ID: 29915a7a-ebd7-ba17-a0d3-e075cf59552c Time: 2021-12-09 10:44:16Z Hello
I'm still having the same issue, can no one help me with this?
Regards
GrahamR99
