Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I'm trying to get the on-premises data gateway to run on a Windows Server VM hosted on Azure, to connect to our on-premises Postgres database.
The problem is when I try to refresh the dataset on PBI service. It always fails with a message "The key didn't match any rows in the table", e.g.:
Underlying error code: | -2147467259 Table: org_site. |
Underlying error message: | The key didn't match any rows in the table. |
DM_ErrorDetailNameCode_UnderlyingHResult: | -2147467259 |
Microsoft.Data.Mashup.ValueError.Key: | [Schema = "d1272", Item = "org_site"] |
Microsoft.Data.Mashup.ValueError.Reason: | Expression.Error |
Cluster URI: | WABI-WEST-EUROPE-B-PRIMARY-redirect.analysis.windows.net |
What is interesting is that on every attempt it fails with the same message, but the table on which it fails changes each time - in the example above it failed on "d1272.org_site".
I've installed npgsql on the server (tried both versions 3.2.7 and the latest 4.0.7)
I installed dBeaver and pgAdmin on the server and both connected to the on-premises Postgres db successfully with the same credentials as configured in the Gateway.
All connection tests show green on the Gateway app running on the server, as well as on the PBI service.
I can successfully refresh the dataset from PBI Desktop and publish that to our PBI service, but of course I'd like to schedule these refreshes.
Any tips on how to dig deeper into what's going wrong?
Solved! Go to Solution.
Thanks, Gilbert, but that didn't help very much.
In the end I found the solution, which might help others.
The user account I was using to connect the PG had its default schema set to "public", but the data for the PBI reports were all in another schema "d1272".
I set up another db user account whose default schema ("default search path") was "d1272", and now it all works.
It seems to me that there is a bug somewhere in the pipeline from the PBI service, via the on-premises data gateway, to PostgreSQL that does not preserve the explicit schema names in the source data tables when running the refresh queries.
The fact that the error message states:
Microsoft.Data.Mashup.ValueError.Key: [Schema = "d1272", Item = "org_mother_site"]
it was definitely ignoring the schema name while executing the query.
Thanks, Gilbert, but that didn't help very much.
In the end I found the solution, which might help others.
The user account I was using to connect the PG had its default schema set to "public", but the data for the PBI reports were all in another schema "d1272".
I set up another db user account whose default schema ("default search path") was "d1272", and now it all works.
It seems to me that there is a bug somewhere in the pipeline from the PBI service, via the on-premises data gateway, to PostgreSQL that does not preserve the explicit schema names in the source data tables when running the refresh queries.
The fact that the error message states:
Microsoft.Data.Mashup.ValueError.Key: [Schema = "d1272", Item = "org_mother_site"]
it was definitely ignoring the schema name while executing the query.
Thanks for letting us know that cause of the issue!