Power BI Desktop ODBC Connectors to PostgreSQL with Direct Query Enabled

[Anonymous]

Greetings,

I'm trying to set up Power BI Embedded with a web page. I am using Postgres and I need to use Direct Query due to the size and volume of data. I have understood achieving this I must, create an ODBC connection to Postgres from Power BI, but I am unable to connect to an ODBC connection with Postgres that would allow me direct query mode. It does not look like the ODBC connector in Power BI supports it. 

Can anyone help solve this please as I have spent endless time on forums and anything around this is a few years old or getting pointed to a proprietary customer connector to be used that I am not paying for.

Any help would be greatly appreciated. 

[Anonymous · Accepted solution]

I managed to resolve this, for anyone in 2020 reading this, you do not require an ODBC connector to use an On-premise data gateway, the docs say it is not supported anyway. I used the standard Postgres Connector offered out the box, I installed this driver here:

Release Npgsql v4.0.10 · npgsql/npgsql · GitHub

I used the GAC method and everything worked. There is a number of old posts saying you need an ODBC driver or a customer connector and it's false, in my experience. 

[Anonymous]

@amitchandak I have the latest version of npgsql and have established an ODBC connection with the SSL installed. I have tried online with other forums and this area seems to be largely undocumented and very difficult to do/work out. The problem with the Power BI ODBC it does not allow for Direct Query access.

Here're the steps I have taken thus far:

• Created a DSN in Windows with the Postgres UNICODE 64x driver
• Created the relevant SSL certs as per official docs
• Used an ODBC data source, select the DSN I want to use and then no Direct Query option works.

[Anonymous]

@amitchandak Thank you for responding, yes I have Npgsql installed, and I am currently working my way through an SSL install and will try it again, I'll update when I have tested everything and got it installed.

[amitchandak]

@Anonymous , Make sure Postgresql is SSL enabled

You have installed the latest version of https://github.com/npgsql/Npgsql/releases

and also you need to register SSL certificate with windows. Only then  postgresql will work and Direct queru will work with postgresql connector