Search code examples
powerbipowerbi-desktoppostgresql-9.2powerbi-datasource

Cannot connect on-premise gateway with postgres on PowerBI

With powerbi desktop I can easily connect with postgres database on server(192.168.1.5:32100) running on a port and a database_name. But doing the same with PowerBI on premise gateway I could not connect when adding a data source on gateway setting(gateway cluster). Below screenshot for reference. The first picture is of PowerBI desktop and the second one is on gateway settings. Is it something with the port?If yes I was able to connect on PowerBI desktop, if no then is there any way or am I missing something. Thanks in advance for the help. enter image description here enter image description here

Solution

  • Method 1

    1. Install the Npgsql provider on the server that's hosting the data gateway (https://www.nuget.org/packages/Npgsql/4.0.2)

    2. Reboot the server

    Method 2

    Connecting to PostgreSQL database using ODBC Connector

    1. Using the below link download the latest version of ODBC connection for PostgreSQL database and Install https://www.postgresql.org/ftp/odbc/versions/msi/
    2. After installation completed, open the Power BI Desktop and choose Get Data
    3. Instead of selecting postgresql database, this time we will choose ODBC. Select ODBC and click on Connect
    4. A window will open and select “None” as Datasource Name (DSN)
    5. In the same window, we have connection string. Here we have to provide proper connection details to connect postgresql database. We won’t be able to click “OK” until we have provided the correct connection string.
    6. To get the proper connection string use this link. https://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/
    7. We have given the below connection string and able to click on “OK” Connection String: Driver={PostgreSQL ANSI(x64)}; Server=localhost; Port=6433; Database=foodmart
    8. Enter the credentials and select the tables
    9. Create a report and publish in Power BI service
    10. Login to Power BI service and open settings of a report dataset
    11. Expand gateway connection and now we can see that gateway is enabled and it is connected local gateway
    12. Expand the data source credentials and enter the database credentials
    13. Now go to datasets and click on refresh icon of the dataset
    14. Dataset will get refreshed using the gateway

    Should now be able to use the PostgreSQL data source type when creating the data source in Power BI Service.

    Thanks,