Introduction
Power BI is a business intelligence and data visualization tool that enables users to transform raw data into interactive dashboards and insightful reports.
PostgreSQL is a robust, open-source relational database management system widely used for storing and managing structured data.
Connecting Power BI to PostgreSQL allows analysts and business users to access live data, perform advanced analysis, and create data-driven visualizations for informed decision-making.
In this guide, you will learn how to connect Power BI to PostgreSQL in two common environments: a local PostgreSQL database running on your computer and an Aiven Cloud PostgreSQL database hosted in the cloud.
Establishing a Local PostgreSQL Database
The first method of connecting Power BI to a data source is by linking it to a local PostgreSQL database hosted on your computer.
Step 1: Open Power BI Desktop
Launch Power BI Desktop and select Blank report.
Step 2: Select Get Data
On the Home tab in the top-left corner of Power BI Desktop, click Get Data. This opens a window displaying the wide range of data sources that Power BI supports.
From the list of available data sources, search for or scroll to PostgreSQL Database, select it, and then click Connect to proceed.
Step 3: Enter the Database Connection Information
After selecting PostgreSQL Database, the PostgreSQL Database connection window will appear. Enter the required connection details as follows:
Server: localhost (or localhost:5432 if specifying the default PostgreSQL port) also 127.0.0.1:5432 works.
Database: postgres (replace this with the name of your PostgreSQL database)
Once you have entered the required information, click OK to proceed to the authentication step.
Step 4: Enter Your Database Credentials
After clicking OK, Power BI will prompt you to authenticate using your PostgreSQL database credentials.
Username: Your PostgreSQL username (for example, postgres)
Password: Your PostgreSQL password
Step 4: Load the Tables
Once the connection is successful, Power BI will open the Navigator window.
This window shows all the tables within the database.
The sample table is:
- Kenya_Crops_Cleaned_Final
- After selecting the required tables and clicking Load, Power BI imports the data from the local PostgreSQL database into the Power BI model. The loaded tables appear in the Data and Model views, where you can inspect the data, create relationships between tables and begin building reports and dashboards. This completes the data import process and prepares the dataset for analysis and visualization.
Integrating Power BI with an Aiven PostgreSQL Cloud Database.
Cloud-hosted databases provide a secure and scalable way to store and access data from anywhere.
Integrating Power BI with an Aiven PostgreSQL cloud database enables users to connect directly to cloud-based data, import tables, and create interactive reports and dashboards.
This integration supports real-time data analysis, centralized data management, and seamless business intelligence without relying on a local database.
Step 1
Open Aiven.io in your web browser and sign in to your account. If you don't already have an account, create one by selecting the Free plan and completing the registration process.
Once the service has been provisioned, ensure it is running by checking that its status is displayed as Running before proceeding.
Step 2
- With the PostgreSQL service now running, the next step is to connect it to your preferred database management system (DBMS) so you can create, manage, and query your database using Aiven's connection string.
- Enter the connection details provided by your Aiven PostgreSQL service into your database management system. These include the Host, Port, Database Name, and Username. Once the details have been entered, you can establish a connection to the cloud database.
Step 3
- Ensure that the Aiven PostgreSQL service is running. A successful connection is typically indicated by a green check mark next to the database name in your database management system (DBMS).
- Download the CA/SSL certificate provided by the Aiven service. This certificate is used to verify the server's identity and establish a secure, encrypted connection between your DBMS and the cloud database.
- An SSL certificate acts as a digital credential that authenticates the server and helps protect data transmitted over the network.
Step 3.1 Installing the SSL Certificate into the Windows Certificate Store
- Open the Windows search bar and type Manage computer certificates, then select the matching result to launch the Certificate Manager. Alternatively, press Win + R, type certlm.msc, and press Enter to open the Certificate Manager directly.
- In the left-hand navigation pane of the Certificate Manager, locate and expand the Trusted Root Certification Authorities folder.
- Right-click the Certificates subfolder under Trusted Root Certification Authorities, select All Tasks, and then click Import to launch the Certificate Import Wizard.
- In the Certificate Import Wizard, click Next. When the file browser opens, change the file type filter in the bottom-right corner from X.509 Certificate to All Files (.). This allows the ca.pem file to be displayed and selected for import.
- Select the ca.pem file and click Next. Accept the default certificate store location, continue through the remaining prompts, and then click Finish to complete the import. If Windows displays a security warning asking for confirmation, click Yes to proceed with installing the certificate.
Step 4: Accessing the Aiven PostgreSQL Cloud Database from Power BI
Launch Power Bi desktop and open blank report
Go to the Home tab and click Get Data. Select More, then choose PostgreSQL database from the list of available data sources.
Enter the Host and Port details provided by your Aiven PostgreSQL service into the Server field
Conclusion
Connecting Power BI to both local and cloud-based PostgreSQL databases is an essential skill for modern data professionals. While local database connections provide a convenient environment for development and learning, cloud-hosted platforms such as Aiven demonstrate how data is securely managed and accessed in real-world environments.
Mastering both local and cloud database connectivity ensures you are well-equipped to work with modern data platforms and create insightful, data-driven reports that support informed decision-making.
Top comments (0)