Gateway running on Azure VM fails to refresh from on-premises PostgresQL

[Anonymous]

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?

[GilbertQ]

Hi there

What I would do is to enable the verbose logs on the Power BI Gateway where you have it installed on the VM.

Then attempt to refresh the dataset.
There should be a wealth of information in there giving you more details as to where the error is.

[Anonymous · Accepted 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.

[GilbertQ]

Thanks for letting us know that cause of the issue!