Lets first define the terms
Aiven - This is a rental and maintenance cloud based data platform that provides fully managed,open-source data infrastructure
PostgreSQL - Is a digital store room for for data
PowerBi - Is a tool by microsoft that turns boring lists of numbers into beautiful,easy to understand charts.
Setting up AIVEN
Go to your web browser and search AIVEN or use the link below to login
Managed PostgreSQL as a service | Aiven
Aiven for PostgreSQL – Managed Postgres database service with Postgres extensions, database forking, connection pooling. Get started now.
aiven.io
Create an account or login in if you already have an account
When you login the first page looks like this
On the Upper right corner you can see a white tab called "Create Service" click on it it will take you to page that looks like below
It will take you a page where you will be able to select the following;
- Service tier - You can choose a free tier ,developer or proffesional.
- Cloud - You can select a specific cloud provider and region on the Professional tier.
- Plan
- Service Basics
After setting this up click on create service
You will see the page that appear as the following below . It might take a few minutes to set up . Make sure the service is up and running as shown below
Set Up SSL in Power BI
To connect Power BI securely to a database like Aiven for PostgreSQL, you need to use an SSL connection. Think of SSL as a private, encrypted tunnel. It ensures that when Power BI reads your data, no hackers can spy on it or steal it.
Go to your database console in Aiven and find the database connection details and find Look for CA certificate and download it
Go to your search bar in the task manager and search for manage certificates . Click open and a page like looks like below will open
Scrol and find the Third Party Root Certificates right click on certificates and a small pop up will appear select all tasks the import . A page like below will appear.
Click next . It will take you to where you search for the file you want to import from you machine . Go to where you downloaded your certificate and select it if you don't see it change the file name from all to .crt or .pem since the certificate is a .crt or .pem file.
Connect to Power Bi Desktop
Click your search bar and look for Power Bi and open your Power Bi desktop
When your Power Bi desktop open choose on Blank report
Go to get Data --> More --> Database then choose PostgreSQL Database
A page that looks like below will pop up
the above server and database are filled with the information from your Aiven Database console
This how the Aiven Database console looks like
In the Server field in your Power Bi fill the following information in the order below
Hostname:Port
eg
(pg-257b3f7e-kamwetiryan-a65c.h.aivencloud.com:13314)
enter your database name for my example it is
defaultdb
Click Ok and the following page appears
Choose on the dataset you want if you had loaded data in your Database click on it then load it to the Power Bi Desktop and begin working on the data.
Top comments (0)