DEV Community

Cover image for From My Machine to the Cloud: Connecting Power BI to SQL Databases; PostgreSQL (Local vs Aiven)
Neema Kirui
Neema Kirui

Posted on

From My Machine to the Cloud: Connecting Power BI to SQL Databases; PostgreSQL (Local vs Aiven)

#postgres #database #analytics #datascience

Introduction

I used to think "connecting to a database" was one skill. Turns out it's two: connecting to a database chilling quietly on your own laptop, and connecting to one living in the cloud, behind a login, in this case, an SSL certificate that will not let you in until you treat it with respect.

This week I did both. Same tool (Power BI), same dataset, two very different vibes.

Grab a coffee, here's the full walkthrough local PostgreSQL first, then Aiven's cloud version, side by side, screenshots and all.

Part 1: Local PostgreSQL → Power BI

Step 1 : Create a schema

Nothing fancy, just giving my table a home:


CREATE SCHEMA powerbi;
Enter fullscreen mode Exit fullscreen mode

Create schema

Step 2 : Import the dataset

Right-click the new schema → Import Data in DBeaver, point it at your CSV, and let the wizard do its thing.

Import wizard

Import done

Step 3 : Check the table landed properly

A quick peek at the columns to make sure nothing got mangled on the way in.

Columns showing

Step 4 : Connect Power BI

In Power BI Desktop: Get Data → Database → PostgreSQL database.

Powerbi local

In the Server field, type localhost (or 127.0.0.1) and your database name.

localhost
Enter fullscreen mode Exit fullscreen mode

Server

Choose Import, hit OK, and log in with your local username and password.

Loaded

Click Load. That's it. That's the whole local experience.

Part 2: Aiven PostgreSQL (Cloud) → Power BI

Now for the part that actually taught me something.

Step 1 : Grab your connection details

Everything you need lives on Aiven's Overview page: Host, Port, Database name, User, SSL mode.

Aiven console

Your service URI will look something like this (don't worry, this isn't a real password, Aiven masks it in the console):

postgres://avnadmin:••••••••@pg-xxxxxxxx-yourproject.c.aivencloud.com:22016/defaultdb?sslmode=require
Enter fullscreen mode Exit fullscreen mode

Step 2 : Import the dataset into Aiven

Same DBeaver wizard as before, just pointed at the Aiven connection instead of local.

CREATE SCHEMA powerbi;
Enter fullscreen mode Exit fullscreen mode

Import 2

Step 3 : Aiven's certificate.

Download the CA cert from the Overview page:

Cert download

Now here's the part that actually tripped me up: Power BI's PostgreSQL connector doesn't have a field where you paste in a certificate file path (DBeaver does, Power BI doesn't). Instead, on Windows, you install the cert into Windows' own certificate store, since Power BI leans on the OS to handle SSL validation.

download cert

Downloaded

Once Windows trusts it, Power BI trusts it too. No settings to toggle inside Power BI itself.

Step 4 : Connect Power BI to Aiven

Same dialog, new server string:

pg-xxxxxxxx-yourproject.c.aivencloud.com
Enter fullscreen mode Exit fullscreen mode

Database: defaultdb

Log in with your Aiven username and password. Since the cert is already trusted system-wide, no extra SSL prompts show up it just connects.

Aiven

Local vs Aiven: Side by Side

Step Local PostgreSQL Aiven PostgreSQL
Server address localhost Full Aiven host string
Port Default 5432 Custom port from Aiven console
SSL/certificate setup None Download CA cert, trust it via Windows Certificate Manager
Power BI dialog Identical Identical
Credentials prompt Username/password Username/password (SSL handled invisibly behind the scenes)

Same tool, same steps on the surface. The only real difference is one extra layer of trust you have to set up before the cloud database will even talk to you and once it's set up, it disappears completely. You never think about it again.

Top comments (0)