Connecting PostgreSQL to Power BI

Introduction

Power BI is one of the most popular business intelligence tools for data visualization and analytics. Combined with PostgreSQL, a powerful open-source relational database, you can create dashboards and reports. This guide will walk you through connecting PostgreSQL to Power BI using two approaches: a local PostgreSQL installation and Aiven's cloud hosted PostgreSQL service.

Connecting Local PostgreSQL to Power BI

Step 1.Installation

Download PostgreSQL from the official PostgreSQL website and follow the installation process. Download Power Bi from the Microsoft store. During installation, note that your user password and port number. If yours is local then the default port number is 5432.

Step 2. Preparing your database

Ensure your database contains the data you want to visualize. Make sure your PostgreSQL server is active.
typical default setting:

• Host: localhost

• Port: 5432

• Default database: postgres

• Username: postgres
  Then test connection.

Step 3. Connect PostgreSQL to Power BI.

Open Power BI and follow these steps:

1. Click on "Get Data" on the home ribbon.
2. In the Get data window, navigate to more > Database> PostgreSQL.
3. Click "Connect".

Step 4. Enter local connection details.

Fill the dialog:
Server: localhost:5432
Database: postgres (or name of your DB)

Click "OK"

Step 5. Enter Credentials.

• Username: your PostgreSQL username
• Password: your password
• Select “Use Encrypted Connection” if available
• Click "Connect".

Step 6. Load Data.

The Navigator window will display all available tables and views in your database. Select the tables you want to work with by checking the boxes next to them. You can preview the data by clicking on each table name.

Connecting Aiven PostgreSQL to Power BI.

Aiven is a cloud-based platform that provides fully managed services for open-source data technologies like databases and streaming services.

Step 1. Set up Aiven PostgreSQL.

If you don't have an Aiven account, sign up at aiven.io. Aiven offers a free trial to test their services.

1. Create a new PostgreSQL service:
2. Log into the Aiven console
3. Click "Create a new service"
4. Select "PostgreSQL" as the service type
5. Choose your cloud provider and region
6. Select a service plan based on your needs
7. Name your service and click "Create service"

Step 2. Retrieve Connection Information.

In the Aiven console, click on your PostgreSQL service to view its details. You'll find the connection information in the "Overview" tab:

1. Host: The service URI (usually in the format: name project.aivencloud.com)
2. Port: Usually a number or another assigned port
3. User: Default is "avnadmin"
4. Password: Click the eye icon to reveal the password
5. Database: Default is "defaultdb"

Step 3. Download the CA certificate.

Aiven enforces SSL connections for security. Download the CA certificate from the Aiven console:

• In your service overview, find the "Download CA cert" button
• Save the certificate file to a known location on your computer
• Note the file path for later use

Step 4. Connect Power Bi to PostgreSQL.

• Click "Get Data" from the Home ribbon

• Select "PostgreSQL database" and click "Connect"
  In the connection dialog, enter:

• Server: Your Aiven host address: Port number.

• Database: Your database name ("defaultdb").

Step 5.Authentication.

Enter your username and password from your aiven.

Step 6. Load data and transform data.

Just like with local PostgreSQL, the Navigator window will show your available tables and views. Select the data you need and click "Load" or "Transform Data" to begin working with your Aiven hosted data.

Conclusion

Connecting PostgreSQL to Power BI whether running locally or hosted on Aiven is simple once the correct drivers and SSL configurations are in place.
Local PostgreSQL connects using localhost and standard credentials.
Aiven PostgreSQL requires SSL certificates and cloud connection parameters.