Power BI Data Set refresh failure Underlying error code: -2147467259

[Anonymous]

I am working with a customer, in a managed services setting, where I have recently been contacted on a support ticket.

The issue is that the dataset for some of their reports fails on REFRESH.

Specific error message below.

Underlying error code: -2147467259

Underlying error message: The key didn't match any rows in the table.

DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259

Microsoft.Data.Mashup.ValueError.Key: [Schema = "dbo", Item = "PBI_SalesHist_Reps"]

Microsoft.Data.Mashup.ValueError.Reason: Expression.Error

Microsoft.Data.Mashup.ValueError.Table: #table({"Name", "Data", "Schema", "Item", "Kind"}, {})

Cluster URI: WABI-US-EAST2-redirect.analysis.windows.net

Activity ID: 1780f2e9-47f0-44dd-b7ea-b7743ab4c57f

Request ID: 9c25c8e4-82ce-d822-291d-962e5f8a7a23

Time: 2021-09-02 13:15:58Z

On closer investigation, here are more details:

1. Customer has data in an ON PREM SQL server source system, with a number of reporting views built there for reporting purposes
2. They ALSO have an Azure SQL server managed instance where some additional data resides
3. They have created a report that pulls data from BOTH sources. It is s single dataset but multiple sources now.
4. They DO have an On Premises PBI gateway installed, that successful refreshes data from the on prem environment.
5. However, for some reason, someone configured a refresh on the Managed Instance through the on prem gateway.  The configuration shows no errors in PBI.com in the workspace settings.  However, I believe this might be the root cause of the data refresh failing?
6. I have suggested to the client as a troubleshooting step, recreate the one or two tables they are hitting in the Managed Instance, into the on prem environment, and remove the MI variable in the data set and refresh.  They have hesitated on wanting to do that
7. I know that PBI does not require a gateway when refreshing from ANY cloud based source.
8. I cannot however find anything definitive that says a refresh through a on Prem gateway to a managed instance CANNOT WORK, or would not work, etc.
9. I do feel this may be the cause, but am looking for definitive guidance and preferable something documented that I can show the client that this will never work.

Thanks in advance.

[GilbertQ]

Thanks for the update, that does appear to be weird.

Can you confirm you have got the latest On-Prem gateway installed?

[GilbertQ]

Hi @Anonymous 

The issue is with the underlying data source missing keys on a relationship in the table PBI_SalesHist_Reps.

In order to get data from a cloud and on-prem source it all has to go through the gateway in order to get it to refresh successfully. The way it is configured is correct.

Ask them to make sure that all the data is in the underlying source tables and to test it by running Power BI desktop and then refresh.

[Anonymous]

Forgot to mention... my first troubleshooting step was to download the dataset into PBI Desktop and refresh it... it refreshes successully there.  No issues.  

[PierreZ]

I had the exact same pb, it was due to a user access right pb on the SQL machine database. I created a new windows user, gave the good access rights on the SQL server then use that same user in powerbi service & it worked right away. No more refresh issue with underlying ids. Actually before it could access the SQL server but not the tables because of rights access.