If you've ever wondered how a Power BI dashboard gets its data from a "real" database instead of a spreadsheet, this walkthrough covers both ends of that journey: connecting to a local PostgreSQL database and connecting to a cloud-hosted PostgreSQL database on Aiven, including the SSL certificate step that trips a lot of people up.
This was done as part of a data analytics project analyzing sales data for a car dealership (JCars Logistics), but the connection steps here apply to any dataset.
Connecting Power BI to a Local PostgreSQL Database
Step 1: Set up PostgreSQL locally and create the database in DBeaver
Created a new connection in DBeaver pointing to localhost
Created the database and table matching the dataset's schema
Imported the Kenya_crops_dataset CSV file into the table using DBeaver's import tool
Verified the data by querying it directly in DBeaver
Step 2: Connect from Power BI
With the database and data already set up via DBeaver, open Power BI Desktop:
- Get Data → More → Database → PostgreSQL database
- Enter localhost:portnumber as the server, and your database name
- Choose Import or DirectQuery mode
- Enter your local database username and password
Step 3: Load and verify
Once connected, select your table(s) in the Navigator window and click Load. Power BI will pull the data in, and you can confirm row counts match what you saw in DBeaver.
That's the local setup, straightforward because there's no network encryption to configure. The cloud version adds one extra layer: SSL.
Part 2: Connecting Power BI to a Cloud PostgreSQL Database (Aiven)
Step 1: Create a PostgreSQL service on Aiven
Log into Aiven, create a new PostgreSQL service, and wait for it to provision. Aiven gives you a host, port, username, password, and database name, plus a downloadable SSL certificate (ca.pem), since Aiven requires encrypted connections by default
Step 2: Load your data into the Aiven database
Rather than connecting Power BI directly for data loading, I used DBeaver as a SQL client to manage the database and import data:
- Created a new connection in DBeaver using the Aiven host, port, username, and password.
Make sure to test the connection first.
- Created a table matching my dataset's schema
- Imported the Jcars.CSV file directly into the table using DBeaver's import tool
- Verified the data by querying it directly in DBeaver
Step 3: Install the SSL certificate
This is the step that's easy to get wrong. Rather than pointing every application separately at the ca.pem file, I installed it once at the operating system level:
- Downloaded ca.pem from the Aiven console
- Opened Manage User Certificates (Windows) → Trusted Root Certification Authorities
- Imported the ca.pem file there
Once installed this way, any application on the machine — including Power BI trusts and verifies the certificate automatically, without needing to browse to the file each time.
Step 4: Connect Power BI to Aiven
- Get Data → PostgreSQL database
- Enter the Aiven host and port (not localhost this time)
- Enter the database name
- Enable the encrypted connection option, since the certificate is already trusted at the OS level; Power BI verifies it automatically
- Authenticate with your Aiven username and password
- Select your table(s) in the Navigator and click Load or Transform.
Local vs Cloud: What's Actually Different
The actual Power BI connection dialog is nearly identical either way; the real difference is the SSL certificate step for the cloud database, and once that's installed at the OS level, it's a one-time setup rather than something you repeat per connection.
Once the data was loaded, I built a Power BI dashboard to analyze revenue, gross profit margin, and delivery performance for JCars Logistics. The main takeaway from this part: connecting Power BI to a cloud database isn't meaningfully harder than a local one; it just adds one certificate step that only needs to be done once.
Top comments (1)
Great work Joan