"Unable to connect to data source undefined" for all but one workspaces

[mddd]

Hey all,

I.e. in workspace 1 I can create a dataflow, publish a report and then successfully update it (the dataset) both manually and by a schedule. But in workspace 2 (all other workspaces actually both existing and new ones) when I create exactly the same dataflow and publish exactly the same report the dataset doesn't get updated with the below error. Seems like the service can't find the workspace when updating the dataset (tried connecting by both workspaceId and workspaceName). Any suggestions on possible causes and fixes?

The datasource is up and running (using MSSQL table for the test but it's the same with all other datasource types I tried), permissions are set, privacy level is set to organizational in both desktop and service, gateway is latest version. I can successfully refresh all dataflows in all workspaces both in desktop and service.

Код базовой ошибки: -2147467259
Сообщение о базовой ошибке: The key didn't match any rows in the table.
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.ValueError.Key: [workspaceName = "БПМ"]
Microsoft.Data.Mashup.ValueError.Reason: Expression.Error
Microsoft.Data.Mashup.ValueError.Table: #table({"workspaceId", "workspaceType", "Data", "workspaceName", "Tags", "ItemKind", "ItemName", "IsLeaf"}, {})
URI кластера: WABI-WEST-EUROPE-redirect.analysis.windows.net
Идентификатор действия: 42df8869-791a-4340-8a50-dc0d9da23206
Идентификатор запроса: 1bf035e3-5f8c-fe4b-c19d-63a12f1d75f9
Время: 2021-04-19 07:13:18Z

[mddd · Accepted solution]

Hi @GilbertQ,

Finally got this resolved with the help from MS support. Dataflows over gateway is not a supported scenario. You have to connect to a dataflow directly.

[GilbertQ]

Hi @mddd 

From the error it says that "The key didn't match any rows in the table."

If you could make sure that you have got all the data and that it all matches?

[mddd]

Hi @GilbertQ

Thanks for the reply.

Yes, when doing refresh in desktop I get all the data from both dataflows.

My guess is the message rather says about workspace table and it doesn't even get to extracting actual data.

Microsoft.Data.Mashup.ValueError.Key: [workspaceName = "БПМ"]
Microsoft.Data.Mashup.ValueError.Table: #table({"workspaceId", "workspaceType", "Data", "workspaceName", "Tags", "ItemKind", "ItemName", "IsLeaf"}, {})

[GilbertQ]

Hi @mddd 

Could you also check to make sure you are running the latest version of the gateway?

[mddd]

Hi @GilbertQ

Yes, it is the latest version.

But something strange happened lately. I can now update the test report that belongs to the same workspace as the troublesome dataflow (i.e workspace 2) but still get the same error in the other workspace where I also published it to for the test (i.e workspace 1). When I re-publish the report as new to both workspaces both new reports throw the error just as before. This leads me to believe there may be some issues on the service side.
Some screenshots to illustrate the workspace 2 behavior.

[GilbertQ]

Hi @mddd 

That would indicate that there is an inner join on a table and there are missing keys

If you can look at any tables where there are joins?

[mddd]

Hi @GilbertQ

No joins anywhere just a single MSSQL table.

The only table in the dataflow.

let
Source = Sql.Database("srv_name", "db_name"),
Navigation = Source{[Schema = "dbo", Item = "DimPrice"]}[Data]
in
Navigation

The only query in the report.

let
Source = PowerBI.Dataflows(null),
#"4a7ae7b1-7d7a-4a9e-8437-3d7e3744cc15" = Source{[workspaceName="БПМ"]}[Data],
#"abe3f9d3-d483-4c07-be4f-af628d01e3be" = #"4a7ae7b1-7d7a-4a9e-8437-3d7e3744cc15"{[dataflowName="issue"]}[Data],
DimPrice1 = #"abe3f9d3-d483-4c07-be4f-af628d01e3be"{[entity="DimPrice"]}[Data]
in
DimPrice1

[GilbertQ]

Hi @mddd 

I cannot see anything else which would be causing the issue, I would log a support ticket https://powerbi.microsoft.com/en-us/support/pro/

[mddd]

Hi @GilbertQ 

Waiting for a reply from MS support now. Will update the thread as soon as the issue is resolved.

Thanks for your help.