Forum Discussion
PostgreSQL connection through On-premise Data Gateway
Hello,
for ODBC connection to postgres you need to get installed ODBC driver for postgres;i am using PostgresSQL Unicode(x64).
After that you have 2 options:
1) create user DSN via ODBC data source administrator (C:\Windows\System32\odbcad32.exe). In that case the connection string for ODBC data source is "dsn=dsn_name" where dsn_name represents the user dsn created via tool above
2) alternative option for connection string is: driver={PostgreSQL Unicode(x64)};server=server_name;port=5432;database=db_name
where server_name represents a server name or its IP, db_name is name of database.
For using on-premise gateway you need to use ODBC connection to postgress on power bi desktop. Then you need to configure data sources using ODBC the same way on on-premise gateway.
I am using above approach for connection to multiple different postgres databases.
I hope it helps.
Kind regards
M
Hello,
Thanks for your reply.
I'm not finding anything about how to do that in the Documentation, could you give some advice? For example, I don't know how to build the "connection string" for the ODBC connection.
Thank you,
Kind regards.
Hello,
for ODBC connection to postgres you need to get installed ODBC driver for postgres;i am using PostgresSQL Unicode(x64).
After that you have 2 options:
1) create user DSN via ODBC data source administrator (C:\Windows\System32\odbcad32.exe). In that case the connection string for ODBC data source is "dsn=dsn_name" where dsn_name represents the user dsn created via tool above
2) alternative option for connection string is: driver={PostgreSQL Unicode(x64)};server=server_name;port=5432;database=db_name
where server_name represents a server name or its IP, db_name is name of database.
For using on-premise gateway you need to use ODBC connection to postgress on power bi desktop. Then you need to configure data sources using ODBC the same way on on-premise gateway.
I am using above approach for connection to multiple different postgres databases.
I hope it helps.
Kind regards
M
Hi martina,
I've chosen the second option and I've just tried to access the PostgreSQL through ODBC in Power BI Desktop, it works properly!
Unfortunately this is not the solution I prefer because it forces me to create new queries and rebuild all my data model... too much time consuming.
I will try to connect directly to PostgreSQL through the personal-gateway instead of on-premise.
Anyway, I will tag your message as solution because at least it works in PBI Desktop (for any future reader, I haven't checked the solution on PBI Service).
Thanks a lot,
Kind regards.Hello, to avoid recreating all queries you can just create ODBC connection for of them and then via advanced editor modify the source (i am using select query in SQL statement)
postgres:
Source = PostgreSQL.Database("server", "db_name", [Query="select …. "])
to ODBC:
Source= Odbc.Query("driver={PostgreSQL Unicode(x64)};server=server_name;port=5432;database=db_name", "select … ")
or (if not SQL statement used)
Source= Odbc.DataSource("driver={PostgreSQL Unicode(x64)};server=server_name;port=5432;database=db_name",...
Kind regards
M
May I know which postgresql odbc driver to install on gateway server?
I am run win server 2012.
Greatly appreciate it!