Schedule Refresh Error in Enterprise Gatweay - SQL on-prem combined with PBI Dataflows

[asmith]

I have a pbix file that contains SQL on-prem data and data from a Power BI Dataflow. While I can refresh it without fail in PBI Desktop, I am having a problem implementing a schedule refresh of the dataset once loaded into the Power BI service. 

I get the following error when I attempt to schedule a refresh:

A few things:

1. The Enterprise Gateway is working as expected; I am able to select it in the 'Gateway Section' of Power BI service. In other words, I believe the configuration is correct. I am NOT an admin in our Enterprise Gateway if that makes a difference.

2. In the 'Data Source Credentials', both my on-prem data and the Dataflows have credentials. I have clicked 'edit credentials' a few times as well.

3. When these two datasets are NOT combined in Power Query Editor (ie. neither merged nor appended), I can schedule the refresh in the Power BI service and the pbix file refreshes without error. 

4. Finally, I can confirm the Power BI Dataflows are automatically refreshing daily without failure. 

So, I'm hopeful someone can see an error I've made or a great suggestion but, at this point, I have to think that the marking of this issue being completed (ie. gateway allowing for the combination of on-prem and cloud data) might have been premature. 

[v-qiuyu-msft]

Hi @asmith,

Which data source does the dataflow get data from? Is it the same SQL database used in Power BI desktop when creating the report? 

Does the refresh only fail when you use Merge Queries or Append Queries for dataflow and SQL database? 

From internal trace, I find some errors, though you mention the report is refreshed successfully in Power BI desktop, I would suggest you double check it in Power BI desktop: 

1. Please go to table "DetailSales" check the measure "Top 10, Products", there is a error "Column 'Customer ID' in table 'DailySales' cannot be found or may not be used in this expression".

2. Please go to table "DailySales" check if anything is wrong with measure "Customers".

By the way, please update the on-premise data gateway to the latest version. 

Best Regards,
Qiuyun Yu 

[mike1337]

Hi, I am experiencing the same kind of a problem. My report combines dataflowa data with sql on prem via gateway data in a append query. The report refreshes without problems in Desktop, but fails in the Power BI service.

• Both the dataflow and the sql on prem query are directed to the same database on the same server.
• The gateway works ok for all other reports that do not use dataflows.
• My credentials seem to be alright:

• The error message below:

Underlying error code-2147467259 Table: ***[edited]***.

Underlying error messageThe credentials provided for the Web source are invalid. (Source at https://api.powerbi.com/powerbi/globalservice/v201606/clusterdetails.)

DM_ErrorDetailNameCode_UnderlyingHResult-2147467259

Microsoft.Data.Mashup.CredentialError.DataSourceKindWeb

Microsoft.Data.Mashup.CredentialError.DataSourcePathhttps://api.powerbi.com/powerbi/globalservice/v201606/clusterdetails

Microsoft.Data.Mashup.CredentialError.ReasonAccessUnauthorized

Microsoft.Data.Mashup.MashupSecurityException.DataSources[{"kind":"Web","path":"https://api.powerbi.co

m/powerbi/globalservice/v201606/clusterdetails"}]

Microsoft.Data.Mashup.MashupSecurityException.ReasonAccessUnauthorized

Cluster URIWABI-EUROPE-NORTH-B-redirect.analysis.windows.net

Activity IDd2f18f15-1f35-4a46-9300-c1dbee4f68e5

Request ID348efcca-616c-5f65-3060-773f4ab0ee5f

Time2019-01-18 14:35:22Z

Thanks!

[v-qiuyu-msft]

Hi @mike1337, 

I tested again on my side with append queries feature, the dataset can be refreshed on Power BI service. In your scenario, please click on Edit Credential for the data source Power BI, sign in with your Power BI account then manually refresh the dataset again to see if the same issue occurs. 

Best Regards,
Qiuyun Yu 

[mike1337]

Hi Qiuyun Yu, thanks for your anwser and sorry for the confusion.

Did you refresh the current version of the report?

I needed this one working for business reasons so as a temporary workaround I modified the report to use only dataflows and no db queries and indeeed it refreshes properly. As a permanent solution I would like to use both the dataflow and db query (that is because my query takes quite some time to compyte which results in a timeout if I do it via the dataflow).

I moved the version that combines db query with dataflow to my personal workspace and it still gives me the same error:

Underlying error code-2147467259 Table: ***.

Underlying error messageThe credentials provided for the Web source are invalid. (Source at https://api.powerbi.com/powerbi/globalservice/v201606/clusterdetails.)

DM_ErrorDetailNameCode_UnderlyingHResult-2147467259

Microsoft.Data.Mashup.CredentialError.DataSourceKindWeb

Microsoft.Data.Mashup.CredentialError.DataSourcePathhttps://api.powerbi.com/powerbi/globalservice/v201606/clusterdetails

Microsoft.Data.Mashup.CredentialError.ReasonAccessUnauthorized

Microsoft.Data.Mashup.MashupSecurityException.DataSources[{"kind":"Web","path":"https://api.powerbi.com/powerbi/globalservice/v201606/clusterdetails"}]

Microsoft.Data.Mashup.MashupSecurityException.ReasonAccessUnauthorized

Cluster URIWABI-EUROPE-NORTH-B-redirect.analysis.windows.net

Activity ID0b1fcac0-78f7-4889-98d0-190edeb28c31

Request ID5f0a7f50-ecd6-b677-126b-3e905f1f1b79

Time2019-01-22 11:05:49Z

I also did double check my credentials and they look alright. (The report that you checked that uses only dataflows works alright)

 Could you pelase check that?

Thanks!

Mike

[mike1337]

Hi Qiuyun Yu, thanks for your anwser and sorry for the confusion.

Did you refresh the current version of the report?

I needed this one working for business reasons so as a temporary workaround I modified the report to use only dataflows and no db queries and indeeed it refreshes properly. As a permanent solution I would like to use both the dataflow and db query (that is because my query takes quite some time to compyte which results in a timeout if I do it via the dataflow).

I moved the version that combines db query with dataflow to my personal workspace and it still gives me the same error:

Underlying error code-2147467259 Table: ***.
Underlying error messageThe credentials provided for the Web source are invalid. (Source at https://api.powerbi.com/powerbi/globalservice/v201606/clusterdetails.)
DM_ErrorDetailNameCode_UnderlyingHResult-2147467259
Microsoft.Data.Mashup.CredentialError.DataSourceKindWeb
Microsoft.Data.Mashup.CredentialError.DataSourcePathhttps://api.powerbi.com/powerbi/globalservice/v201606/clusterdetails
Microsoft.Data.Mashup.CredentialError.ReasonAccessUnauthorized
Microsoft.Data.Mashup.MashupSecurityException.DataSources[{"kind":"Web","path":"https://api.powerbi.com/powerbi/globalservice/v201606/clusterdetails"}]
Microsoft.Data.Mashup.MashupSecurityException.ReasonAccessUnauthorized
Cluster URIWABI-EUROPE-NORTH-B-redirect.analysis.windows.net
Activity ID0b1fcac0-78f7-4889-98d0-190edeb28c31
Request ID5f0a7f50-ecd6-b677-126b-3e905f1f1b79
Time2019-01-22 11:05:49Z

I also did double check my credentials and they look alright. (The report that you checked that uses only dataflows works alright)

Could you pelase check that?

Thanks!

Mike

[asmith]

Hi @v-qiuyu-msft

Apologies on my delayed response. A combination of the flu and heavy travel is the culprit, I'm afraid.

Okay, thanks again for your thoughts above. I first corrected the errors you cited above. Second, I asked our BI folks about the gateway and it turns out we were several months behind. We updated it as you requested and... the schedule refresh in the Power BI service is working properly. Morever, I confirmed with another user that it has been consistently for about a week now. 

So, many, many thanks for your help. It appears we're in good shape on this one. 🙂

Aaron

[v-qiuyu-msft]

Hi @mike1337, 

I would suggest you to create a support ticket to get dedicated support. 

@asmith Glad to hear the issue is resolved on your side. 

Best Regards,
Qiuyun Yu