Power Bi Report with Multiple sources Automatic refresh .

[GrahamR99]

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

[GrahamR99 · Accepted solution]

Hello @v-yiruan-msft 

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

[v-yiruan-msft]

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

[GrahamR99]

Hello @v-yiruan-msft

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 details

Underlying 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

[lbendlin]

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.

500 indicates that the error lies with the data source. Have you spoken to your D365 team?

[GrahamR99]

Hi @lbendlin 

I can connect to the data in Power BI desktop, why would it work there and not in the portal?

When you say D365 team do you mean Microsoft or the internal team?

[lbendlin]

Your internal IT team that manages your tenant.

[GrahamR99]

Hello 

I'm still having the same issue, can no one help me with this?

Regards

GrahamR99

[v-yiruan-msft]

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

Configure scheduled 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

[GrahamR99]

Hello @v-yiruan-msft

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?

[v-yiruan-msft]

Hi @GrahamR99 ,

Could you please provide some screenshots with your Gateway Cluster Setting and Dataset settings info just like below screenshot? Before share them with us, please mask the sensitive data for them. Thank you.

Did you checked the option "Allow user's cloud data sources to refresh through this gateway cluster"? 

Gateway Cluster Settings

Please make sure the related data source(SQL Server) has been included in the dataset...

Dataset settings

In addition, please make sure the credential info of all data sources are correct and configure schedule refresh properly.

Data source credentials and configure schedule refresh

Refresh On-Premises Data in Power BI using On-Premises Data Gateway

Best Regards

[GrahamR99 · Accepted solution]

Hello @v-yiruan-msft 

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

[GrahamR99 · Accepted solution]

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.

[lbendlin]

The red x for Business Central is ok, just an indication that no gateway is required for that connection.  Your actual issue is that the gateway cannot reach the SQL server. Firewalls?

[lbendlin]

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.

[lbendlin]

Did you setup a gateway connection for your on-prem source?  Does your network require a proxy to reach the Azure cloud?

[GrahamR99]

Gateway setup. I think it uses a proxy to connect to Azure.

[lbendlin]

You need to specify the proxy in /m/netfx45 config

[GrahamR99]

Hello @lbendlin 

Can you provide more detail please? Is there any documentation?

[lbendlin]

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-proxy

[GrahamR99]

Hello @lbendlin 

I just come to configure the Gateway and found out we don't have a Proxy server.

Is there anything else that would stop the refresh?

Regards

GrahamR99