Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

I am not able to connect Postgre SQL DATABASE

Hi Friends,   I am not able to connect post gre sql db in power BI desktop. getting the trail mail error.   The connector is not  configured correctly. this connector requires one or more additi...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi Anonymous,

     

    From the error message, the issue is caused by that the connector is not be configured properly. Please refer to this article to configure connector again: Power BI Connectivity to PostgreSQL.

     

    Best Regards,
    Qiuyun Yu

markcaple's avatar
markcaple
New Member
9 years ago

Having tried to get this going for a few hours with no success decided to change the approach and use a postgreSQL ODBC driver and it worked straight away.

 

First get the driver from

 

https://www.postgresql.org/ftp/odbc/versions/msi/

 

I downloaded the psqlodbc_09_06_0100-x64.zip and ran psqlodbc_x64.msi

 

Now click on 'Get Data' in PowerBI and select ODBC. Choose  <None> in Data source name (DSN) and click on Advanced Options and enter a connection string which you can find at

 

https://www.connectionstrings.com/postgresql/

 

I used something like

 

Driver={PostgreSQL ANSI(x64)};Server=rds-postgresql-xxxx-warehouse.cftg4xxxxxt.ap-southeast-2.rds.amazonaws.com;Port=5432;Database=myDatabase;

 

My server is an AWS server. The most important part is the driver name which you can search for in your ODBC settings and you will see a warning icon by the side of the connect string until you get this right.

 

jaykilleen's avatar
jaykilleen
Advocate II
9 years ago

Further to your excellent answer. You can create a blank query and put in (replace hostname with your server ip address or name (ie google.com or 172.217.25.174) and database_name with the name of your database.

 

This requires the ODBC driver installed as per @markcaple. A good way to double check (what I did) if things are working is to use PGAdmin4 and try and connect using that. It comes with everything built in and/or has a Stack Builder to bring in things like npgsql or the OLDB drivers so that things can work. Whereas PowerBI says it can be done in the brochure and then ... ... ...

 

You will probably need admin rights so if you are an analyst in a big enterprise with an IT department then probably just uninstall and use something else. Sounds like Tableau works OOTB.

 

let
  Source = Odbc.DataSource(
    "
      Driver={PostgreSQL ANSI(x64)};
      Server=hostname;
      Port=5432;
      Database=database_name;
    ", 
    [HierarchicalNavigation=false]
  )
in
  Source