Introduction
You are data analyst in an organisation working on a large volume of data. Your company stores that data in a postgres SQL database for instance and you need to access that data and analyze it using the tool of your choice like Power BI, Tableau, Looker Studio etc, then you have to find a way to connect that visualization tool to the database to access that data.
Your data can be stored in a local database server or in a cloud managed platfrom like Aiven. Whichever the case, the guide below shows you how to connect to both.
Prerequsites
Before connecting your power BI to these databases ensure you have;
An Active Aiven account
A running service in Aiven, in our case Postgres SQL database service
A running postgres SQL database hosted locally
Power BI installed in your desktop
1. Connecting power BI to a local postgres SQL database
The process is straight forward, but first , you will need a tool like Dbeaver, a software that enables you to store and organise your data and also allows you to connect to not only one but multiple databases.
Step 1-open your dbeaver
press Cntrl+shift+N to open a new database connection in our case Postgres SQL
Step 2-Input your connection password and press okay , you can test the connection to see if it is valid, then press okay
Step 3-Right click on your connection name, and on the drop down click connect, make sure your connection name has a green tick
Once you have established that connection, you can now manually add your data from your machine to the database
Step 1-go to your database
Step 2-Import data
Step 3- choose the type of data
Step 4-Browse from your local files
Now launch your power BI , got to home , click Get Data , go to databases and choose postgres SQL database.
You will then be prompted to enter your connection details for the local postgres SQL you just created
click connect, from your navigator page, you can now select the files you want to work on directly from your database.
2. Connecting Power BI to a cloud-based postgres database in Aiven
Unlike a local PostgreSQL installation, Aiven databases are hosted securely in the cloud. Every connection is encrypted using SSL.
Why SSL matters
When Power BI connects to an Aiven PostgreSQL database, your data travels across the internet.
Without SSL:
Data could potentially be intercepted.
Credentials might be exposed.
Connections are vulnerable to man-in-the-middle attacks.
With SSL enabled:
Data is encrypted.
Passwords remain protected.
Power BI verifies that it is communicating with the legitimate Aiven server.
Downloading and configuring the SSL certificate
Step 1-Open your Aiven Postgres SQL service, make sure it's running
Step 2- download the CA certificate
Step 3- open windows, go to manage user certificates, Trusted Root certification
Step 4- Right click on the drop down certifcates, all tasks, then import data. You will then browse from your local files where your certificate is stored
Connecting to your power BI
The process is more or less the same with the local Postgres SQL. Your connection details are in your Aiven postgres SQL service.The server box details are (hostname:port) while the rest of the details are input directly.
Conclusion
Connecting Power BI to PostgreSQL is a straightforward process whether your database is hosted locally or in the cloud
The key distinction when connecting to Aiven is the use of SSL/TLS encryption.
Top comments (0)