Introduction
When I first started using Power BI, almost all of the datasets I worked with came from Excel files. As I continued learning more about business intelligence, I realised that most real-world applications store their data in databases instead.
To understand how Power BI works with databases, I decided to try two different scenarios. First, I connected it to a database running locally on my machine. I then connected it to a cloud-hosted PostgreSQL database on Aiven to see how the process differed. While the local connection worked almost immediately, the cloud connection introduced SSL certificates, something I had never configured before.
During the process I encountered a certificate trust error that initially prevented Power BI from connecting. After troubleshooting the issue and finding the solution, I thought it would be useful to document the entire process so that anyone facing the same problem can resolve it much faster.
Connecting Power BI to a Local SQL Database
If your database is running locally,for example SQL Server Express or PostgreSQL installed on your computer,the connection process is relatively straightforward. Since the database is on the same machine, there is no SSL certificate configuration required. In my case, Power BI connected successfully as soon as I entered the correct server details and authentication credentials.
Steps
Open Power BI Desktop.
Select Home - Get Data - More...
Search for SQL Server or PostgreSQL Database.
Click Connect.
Enter the server details.
- Server: localhost
- Database: your database name
Choose your authentication method.
Click Connect and either select Load or Transform Data.
Because the database was running locally, there were no certificate or encryption issues to configure. The connection worked immediately, making this the easiest part of the process.
Connecting Power BI to Aiven PostgreSQL
Unlike the local database, connecting to Aiven required a few additional steps. Since the database is hosted in the cloud, the connection uses a public hostname and requires SSL encryption to keep the communication secure.
Getting the Connection Details
The first thing I did was open my PostgreSQL service on Aiven and copy the connection details. These included the hostname, port number, database name, username and password. I also downloaded the CA certificate because it would later be needed for SSL.
Testing the Connection in DBeaver
Before opening Power BI, I decided to test the connection in DBeaver first. I prefer using DBeaver for troubleshooting because it gives much clearer error messages whenever something isn't configured correctly.
Inside the SSL settings, I imported the CA certificate downloaded from Aiven and changed the SSL mode to Require.
After saving the settings, I tested the connection again and it connected successfully. This confirmed that the database itself was working correctly before moving to Power BI.
Importing Data into PostgreSQL
Since my data was originally stored in a flat file, I used DBeaver's Import Data wizard to create a table and populate it. The wizard automatically mapped most of the columns, making the import process straightforward.
After the import completed, I checked the row count to make sure every record had been transferred successfully.
Connect Power BI to the Aiven database
In Power BI Desktop, go to Get Data - PostgreSQL database
In the Server field, enter your host and port together, separated by a colon: your-host.aivencloud.com:16392
In Database, enter your database name (e.g. defaultdb)
Click OK, then choose Database authentication and enter your username and password
The SSL Certificate Error
This was the only part of the exercise that actually slowed me down. After entering the connection details in Power BI, I expected the connection to work because DBeaver had already connected successfully.
Instead Power BI displayed an error to my surprise.
At first, I assumed I had entered the wrong password or hostname. After checking everything several times, I realised those details were correct because the exact same connection was already working in DBeaver.
After doing some research, I discovered that Windows simply didn't trust Aiven's certificate authority. The issue wasn't with PostgreSQL or my credentials, it was with Windows certificate validation.
After importing the certificate into Windows' Trusted Root Certification Authorities store and restarting Power BI, I retried the connection. This time it connected immediately and displayed all the available tables.
Creating Basic Measures
Once the tables had been loaded into Power BI, I created a few simple DAX measures to confirm that everything was working correctly.
Total Revenue =
SUM(Jcars[revenue])
Total Units Sold =
SUM(Jcars[units_sold])
Gross Profit =
[Total Revenue] -
SUMX(
Jcars,
Jcars[unit_cost] * Jcars[units_sold]
)
The measures calculated successfully, confirming that Power BI was reading the PostgreSQL data correctly. From this point onwards, I could build visuals exactly as I would when working with any other data source.
Conclusion
Before working on this exercise, I expected connecting Power BI to a cloud database to be much more complicated than connecting to a local database. After completing both, I realised that the overall process is almost identical. The biggest difference is that cloud databases require SSL because the connection is made over the internet.
The only challenge I encountered was the certificate trust error. Once I understood that Windows simply didn't trust Aiven's certificate authority and imported the CA certificate into the Trusted Root store, the connection worked without any further issues.
Besides learning how to connect Power BI to different database environments, this exercise also gave me a much better understanding of why SSL certificates are important and how cloud-hosted databases secure their connections.
Top comments (0)