DEV Community

Mburu
Mburu

Posted on

How to Connect Power BI to Aiven PostgreSQL Using ODBC (Step-by-Step Guide)

#aiven #powerbi #luxdevhq #postgressql

Introduction

I tried connecting Power BI directly to a PostgreSQL database on Aiven and run into this frustrating error:

The remote certificate is invalid according to the validation procedure
Enter fullscreen mode Exit fullscreen mode

I fixed it and this article explains the steps I took to connect to my postgre database: I used ODBC (Open Database Connectivity)

This is the step by step process on how I did it:

  • Install the PostgreSQL ODBC driver
  • Configure a secure connection - with SSL
  • Connect Power BI to your Aiven PostgreSQL database
  • Load your dataset (customers, products, sales, inventory)

What is ODBC

ODBC is like a translator between Power BI and your database. Instead of Power BI connecting directly like this,

it fails on SSL issues and that`s where we use ODBC:

  • Handles SSL properly
  • Manages drivers
  • Ensures stable connections

Step 1: Install PostgreSQL ODBC Driver

Download driver

Download the psqlODBC (64-bit) version

Install

  • Run the installer
  • Keep default settings
  • Finish installation

🖥️ Step 2: Open ODBC Data Source Administrator

  • Press Windows + S
  • Search:


ODBC Data Sources (64-bit)

  • Open it

Step 3: Create a New DSN

Go to: System DSN tab

Click: Add

Select:


PostgreSQL Unicode(x64)

Step 4: Enter Aiven Connection Details

Now fill in your database info:

Basic Settings

  • Data Source Name: AivenPostgres - Any name is fine

  • Server: paste your Aiven Host name
    e.g mine is

    pg-21504f75-mburuboniface.c.aivencloud.com

  • Port: Paste your port here

  • Database: your database name


defaultdb

  • User Name: your user name
    e.g.

    avnadmin

  • Password:


your_password

Step 5: Configure SSL

Click “SSL” tab

Set:

  • SSL Mode: require

  • Root Certificate: Browse and select your downloaded:


ca.pem

Where to get ca.pem?

From your Aiven dashboard:

  • Open your PostgreSQL service
  • Go to Connection Information

  • Download:

    • ca.pem

Step 6: Test the Connection

Click: Test

If everything is correct: Connection successful

If it fails:

  • Check port
  • Check SSL file
  • Check credentials

Step 7: Connect Power BI Using ODBC

Now go to Power BI:

👉 Steps:

  1. Open Power BI Desktop
  2. Click Get Data
  3. Search:


ODBC

  1. Select it
  2. Choose your DSN:


AivenPostgres

  1. Click OK

Step 8: Load Your Tables

You should now see your tables:
Example

  • customers
  • products
  • sales
  • inventory

Select all --> Click Load

Step 9: You can now visualize your data

Top comments (0)