Forum Discussion

JChai's avatar
JChai
Advocate I
1 year ago

Dataset with Dataflows fail to refresh when authenticate with new account on Gateway Connection

I just wanted to share a finding that has puzzled our team while working with dataflows on Power BI Service. In hope that this can help out others who are facing the same issue. 

 

The story has to do with refresh failure on Power BI Service when semantic model using at least one Dataflows data source published was connected to a Dataflows Connection On-Premise Gateway authenticate with a Group account. The interesting part is when a personal account is being use for authentication, the refresh ran successfully. 

 

The team eventually found out that the refresh failure has to do with Shared Workspace permission issue after spending numerous time and effort investigating and collaborating with different departments such as checking the licenses assignment, admin roles, authentication on data source settings, etc.

 

To be precise, any Account (No matter Group or User account) will need to be at least a VIEWER role in the Workspace that consists of the Dataflows. If not, the error as shown below will be thrown when running refreshes.

Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"The key didn't match any rows in the table."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Key","detail":{"type":1,"value":"[workspaceId = \"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx\"]"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"Expression.Error"}},{"code":"Microsoft.Data.Mashup.ValueError.Table","detail":{"type":1,"value":"#table({\"workspaceId\", \"workspaceName\", \"workspaceType\", \"Tags\"}, {})"}}],"exceptionCulprit":1}}} Table: DUMMY_TABLE.

This error, in my opinion, is really useless as it has nothing to do with KEY issue. The team even created a new report with only one dataflows table to prove this, as no relationship is being defined, thus there shouldn't be a KEY issue. 

 

I think the error need to be more precise and human friendly, and this issue should be documented on the Power BI documentation so that user can search for this issue easily without having to spend so much time on the investigation and research.

 

In conclusion, the account uses for authentication Dataflows Connection On-Premise Gateway should have access to all Workspaces that has the Dataflows. 

Dataflow
Show and Tell

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi JChai - Ensure that the account used to authenticate the Dataflows connection via the On-Premise Gateway has access (at least Viewer) to all workspaces containing the Dataflows.
    It’s crucial that the permissions for the On-Premise Data Gateway and Dataflows connections are aligned with the workspace permissions, otherwise, it can lead to refresh failures, as you experienced.

    As part of best practices, always double-check the Workspace permissions for any user or service account used in connection to Dataflows, especially when setting up On-Premise Gateway connections.

    Hopefully, this issue will be more visible in the community, and others will benefit from your detailed experience!