The ONLY Guide you NEED to connect SQL Databases to POWERBI

Introduction

Integrating SQL databases with Power BI is one of the most effective and widely adopted approaches for creating powerful, scalable business intelligence solutions. With Power BI, you can easily connect to various relational databases such as Microsoft SQL Server, Azure SQL Database, PostgreSQL, MySQL, Oracle, and many others. This seamless connectivity enables you to analyze massive datasets with ease and build dynamic, interactive reports and dashboards.

In this one particular I accessed data sitting in a database (both local and in cloud), for this case it will be postgresql database. After accessing the database, then connecting it powerBi.

Must have

1. PostgreSQL must be installed and running on your computer (or a local/server machine you can access). Ensure you are able to access the following while installing the Postgres.

• Host (usually localhost or 127.0.0.1)
• Port (default is 5432)
• Database name
• Username (e.g., Postgres)
• Password(of the database created)

1. Aiven account and in it install postgresql
2. DBeaver(Universal database software)
3. PowerBi Desktop

Local Database

• A local database is a data storage system that resides directly on your device or a restricted local network rather than on a remote cloud server. In this case it resides in our system device.

Step 1: Dbeaver and Database set up

• Open DBeaver.
• Click Database - New Database Connection - Select PostgreSQL.

• Fill in the same details (for your local database): Server Host, Port, Database, Username, Password.
• Click Test Connection. If it says "Connected", you’re good.
• Expand your database to confirm you can see the tables. Once this works, you can connect the exact same details in Power BI.

• Inside your database you can create schema so that you can import or insert data in your database as a test experiment. In this case I uploaded a jcars.csv dataset.

• To import data into your postgresql database, right click on the schema, where there will be an option to import data.
• Select input files > browse(to get data in the pc) > proceed and finish.

Step 2: Connecting local database to PowerBi

• Open Power BI Desktop.
• Click Home > Get Data > Database > PostgreSQL database.

• In the connection window, fill in: - Server: localhost (or 127.0.0.1 or your server name) eg 127.0.0.1:5432
• Database: Your database name eg postgres
• Click OK.
• Enter your Username eg postgres and Password when prompted. -Click Connect.

Step 3: Load Your Data

• In the Navigator window, you will see all available tables and views. Select the tables you want to use.
• Click Load or Transform Data (to clean/edit first in Power Query).

A cloud-based SQL database such as Aiven

Step 1: Test Connection in DBeaver

• Open DBeaver.
• Click New Connection - Select PostgreSQL - Next.
• Fill in the details from Aiven account, you simply copy and paste:

• Inside your database you can create schema so that you can import or insert data in your database as a test experiment. In this case I uploaded a jcars.csv dataset.
  

• To import data into your postgresql database, right click on the schema, where there will be an option to import data.
  Select input files > browse(to get data in the pc) > proceed and finish.

Step 2: Open Power BI Desktop

• Go to Home > Get Data > Database > PostgreSQL database.

• Enter the connection details:
• Server: yourhost.aivencloud.com:port
(Example: pg-abc123.aivencloud.com:port provided)
• Database: defaultdb (or your database name)
• Click OK.

• Enter your Username and Password when prompted.

• In the Navigator window, select the tables you want - Click Load if it is clean (or Transform Data so that you clean and analyze it).

• In my case as below it was unable to connect. To solve this issue you must download the CA certificate

• The CA certificate is in the Aiven account
  

• In your search bar seach manage user certificate then follow the steps below

• Windows Trusted Root Certification > Right click on the certificates > All task > Import > Next > Browse > Change file types to all files > Click on the downloaded certificate > Next > Finish > Yes > Ok
  Under CA Certificate, browse and select ca.pem file you downloaded from Aiven, import then finish installing.
  

• Close your powerbi and enter the details again. This time it will connect to the database
  

Conclusion

Connecting database data to Power BI completely eliminates the need for manual, error-prone spreadsheet exports . By centralizing information, organizations create a reliable single source of truth . This ensures consistent metrics and unlocks scalable, automated business intelligence.