Using Power BI with PostgreSQL and Kondado

Using Power BI with PostgreSQL and Kondado

Most Data Analytics stacks comprise three stages: An ETL tool + a Data Warehouse to centralize data + a visualization or BI tool. Here we will show how to use Microsoft Power BI as a BI tool, together with PostgreSQL as the Data Warehouse and Kondado as your ETL tool.

1. Registering Your PostgreSQL as a Destination in Kondado: First, you need to login to Kondado and register your database as a destination to receive data from your tools. To register your PostgreSQL as a destination, follow the step-by-step shown in our documentation.

2. Creating Your Data Sources: Data sources are the sources or tools from which you want to extract data to centralize in your data repository. To register your data sources in Kondado, visit our wiki and follow the respective step-by-step instructions.

3. Creating your first integration: Now that your data destination and data sources are registered, you can create your first integration by following the platform onboarding steps.

4. Entering Power BI: If you haven't yet, download the Power BI desktop (free version) and follow the tool's step-by-step guide to register.

5. Meeting the prerequisites for connecting Power BI with PostgreSQL: As a prerequisite for connecting with the PostgreSQL database in the desktop version of Power BI, Microsoft requires the installation of the NpgSQL provider:

Download the recommended version 4.0.10, which can be obtained here through the file “Npgsql-4.0.10.msi“ .

Open the downloaded file and follow the installer's step-by-step guide. On the “Custom Setup” screen, select the option “Npgsql GAC Installation” and complete the installation:


If your PostgreSQL database is hosted by AWS, click here to download AWS's public key.

Convert the downloaded key into a certificate by accessing and using the settings from the image below:

foto 2.webp

When you click on “Convert Certificate” in the previous step, a file will start downloading.

Open the Windows command center by searching for “mmc” in the start bar:

foto 3.webp

Click on “Add/Remove Snap-in”:

foto 4.webp

Select “Certificates” and then click on “Add”:

foto 5.webp

Select “Computer Account” and click on “Next”:

foto 6.webp

Select the “Local Computer” option and click on “Finish”:

foto 7.webp

Note that “Certificates” will appear in the right box. Click on “Ok”:

foto 8.webp

Click on “Certificates”, and right-click on “Trusted Root Certification Authorities”. Select “All Tasks” and click on “Import”:

foto 9.webp

In the window that opens, click on “Next” and then “Browse”.

Go to the folder where you saved the certificate, and change the file format to “PKCS #7 Certificates”. You should then select the certificate file and click on “Open”:

foto 10.webp

In the certificate import wizard, click on “Next” and then on “Finish”. You will receive an alert notifying that the import was successful.

6. Connecting Postgres to Power BI:

  • On the Power BI home page, click on “Get Data” in the top bar:
foto 11.png
  • Select the “Database” option in the side menu:
foto 12.png
  • Then choose the “PostgreSQL database” option from the list and click on “Connect”:
foto 13.webp
  • Fill in the server data and database name and select the “DirectQuery” option so that your data is updated in real-time. Click on “OK”:
foto 14.webp
  • Enter the database user name and password. Click on “Connect”:
foto 15.webp
  • Now you will need to choose from a list which tables from your PostgreSQL database you want to bring into Power BI (remember to search by the table names you defined when creating your integration in Kondado, as per step 4). Just check the box next to the table names and click on “Load” once all are selected:
foto 16.webp
  • Done! Now your PostgreSQL is connected to Power BI and your data will be available for viewing or transforming whenever you need:
foto 17.webp