Connecting Power BI to SQL Databases: Local and Cloud-Based

Introduction

Modern data analytics workflows require efficient connections between data storage platforms and visualization tools. Business Intelligence tools such as Microsoft Power BI allow analysts to connect directly to SQL databases, transform data, and create interactive dashboards for decision-making.

SQL databases can be hosted in different environments, including:
Local databases hosted on a personal computer or organizational server
Cloud databases hosted by managed database provider.

Part 1: Connecting Power BI to a Local PostgreSQL Database

A local PostgreSQL database provides a database environment that runs directly on a computer. It is commonly used during development, testing, and small-scale analytics projects.

Step 1: Installing and Preparing PostgreSQL Locally

PostgreSQL is installed locally to provide a database environment for storing the dataset.
After installation, the PostgreSQL server runs on the local machine and is ready to accept database connections.

The local database connection details includes:
Host:, localhost, Port: 5432
Username: postgres
Password: ********

Step 2: Connecting Local PostgreSQL to DBeaver

DBeaver is used as the database management tool to connect to the PostgreSQL server.

Creating the connection:
Open DBeaver
Select:
Database

• New Database Connection
• PostgreSQL Enter the PostgreSQL connection details.

The connection are tested to confirm successful communication between DBeaver and the local PostgreSQL server.

Step 3: Creating a Database for the Project
After establishing the PostgreSQL connection, a database is created to store the dataset.
The created database is then used as the storage location for importing the dataset.

Step 4: Loading the Dataset into Local PostgreSQL

The dataset was loaded into PostgreSQL using the DBeaver import wizard.
The import process involves:

• Selecting the project database.
• Choosing the dataset file.
• Configuring import settings.
• Mapping columns.
• Executing the import process.

DBeaver automatically creates the required table structure and loads the dataset records into PostgreSQL.

Step 5: Validating the Imported Data

After the import process is completed, the dataset is opened in DBeaver to verify that the records have been successfully loaded.
The validation included checking:

• Table structure
• Column names
• Number of records
• Sample data entries

Step 6: Connecting Power BI to Local PostgreSQL

After loading the dataset into PostgreSQL, Power BI is then connected to the local database.

In Power BI Desktop:
Home

• Get Data
• PostgreSQL Database

The PostgreSQL connection details were entered:

Server:localhost:5432, Database: database_name

Authentication is completed using the PostgreSQL username and password.

Step 7: Loading Data into Power BI

After successful authentication, Power BI displays the available PostgreSQL tables.
The data was then prepared using Power Query before creating dashboard visuals.

Part 2: Connecting Power BI to Cloud-Based PostgreSQL Using Aiven

Cloud databases provide scalable and secure database solutions without requiring users to manage physical database infrastructure.
Aiven PostgreSQL is used as the cloud database platform.

Step 1: Creating an Aiven PostgreSQL Database
An Aiven PostgreSQL service was created to host the dataset.
Aiven provided the required connection information:
Host address
Port number
Database name
Username
Password
SSL certificates

Step 2: Connecting Aiven PostgreSQL to DBeaver

DBeaver is configured to connect to the Aiven PostgreSQL database.
The connection details provided by Aiven are entered:
Host:Aiven server address
Port:5432
Database:Database name
Username:Username

Step 3: Adding SSL Certificate Configuration

Aiven requires SSL encryption to establish a secure connection between the database client and the database server.

The SSL certificates provided by Aiven included:

• CA Certificate
• Client Certificate
• Client Key The certificates are added in DBeaver under: Connection Settings
• SSL The SSL mode is configured according to Aiven requirements.

Step 4: Importing the Dataset into Aiven PostgreSQL

After establishing the secure connection, the dataset is imported into Aiven PostgreSQL using DBeaver.

The same import process is followed:

• Select database
• Import dataset
• Configure column mapping
• Execute import

The dataset is successfully stored in the cloud PostgreSQL database.

Step 5: Connecting Power BI to Aiven PostgreSQL

After loading the dataset into Aiven, Power BI is connected to the cloud database.
In Power BI:
Home

• Get Data
• PostgreSQL Database The Aiven server details are entered: Server: Aiven Host Address Database: Database Name

Step 6: Loading Aiven Data into Power BI

After authentication, Power BI displays the available tables from Aiven PostgreSQL.
The dataset is then selected and loaded into Power BI for analysis.