DEV Community

Nelly Triza
Nelly Triza

Posted on

A Beginner's Guide to Connecting Power BI Local and Cloud-Based (PostgreSQL on Aiven)

#powerfuldevs #learning #datascience #productivity

Power BI becomes even more powerful when connected directly to SQL databases. Instead of working with static Excel files, SQL databases allow us to store, manage, and analyze large datasets efficiently. In this article, I'll demonstrate how to connect Power BI to both local and cloud-based SQL databases using PostgreSQL.

Project Objective

The objective of this tutorial is to:

  • Connect Power BI to a local SQL database.
  • Connect Power BI to a cloud-hosted PostgreSQL database on Aiven.
  • Import data into Power BI.
  • Transform and clean the data using Power Query.
  • Prepare the data for dashboard development.

--- Requirements

Before getting started, ensure you have:

Power BI Desktop installed.
PostgreSQL installed locally (optional for local connections).
An Aiven account with a PostgreSQL service.
Your database credentials.
A dataset to import.

Part 1: Connecting Power BI to a Local SQL Database

Step 1: Open Power BI Desktop

Open Power BI Desktop.

Step 2: Click Get Data

Navigate to:

Home > Get Data
Enter fullscreen mode Exit fullscreen mode

Select:

PostgreSQL Database
Enter fullscreen mode Exit fullscreen mode

Step 3: Enter Your Database Details

Provide:

  • Server name
  • Database name

Example:

Server: localhost
Database: jcars
Enter fullscreen mode Exit fullscreen mode

Click OK.

Step 4: Authenticate

Enter:

  • Username
  • Password

Select:

Database Authentication
Enter fullscreen mode Exit fullscreen mode

Click Connect.

Step 5: Load the Data

Power BI will display the available tables.

Select the tables you wish to import and click:

Load
Enter fullscreen mode Exit fullscreen mode

or

Transform Data
Enter fullscreen mode Exit fullscreen mode

if you need to clean the data before loading.

Insert Screenshot: Navigator Window

Part 2: Connecting Power BI to a Cloud-Based SQL Database (Aiven)

Step 1: Create an Aiven PostgreSQL Service

Log in to your Aiven account and create a PostgreSQL service.

Take note of the following details:

  • Hostname
  • Port
  • Database name
  • Username
  • Password

Insert Screenshot: Aiven PostgreSQL Dashboard

Step 2: Copy Your Connection Details

A typical PostgreSQL connection looks like:

Host:
xxxx.aivencloud.com

Port:
12345

Database:
defaultdb

Username:
avnadmin

Password:
********
Enter fullscreen mode Exit fullscreen mode

Step 3: Open Power BI

Navigate to:

Home > Get Data > PostgreSQL Database
Enter fullscreen mode Exit fullscreen mode

Enter:

Server:
hostname:port

Example:
xxxx.aivencloud.com:12345

Database:
defaultdb
Enter fullscreen mode Exit fullscreen mode

Step 4: Authenticate

Select:

Database Authentication
Enter fullscreen mode Exit fullscreen mode

Enter:

  • Username
  • Password

Click Connect.

Step 5: Select Tables

Once Power BI successfully connects to the database:

  • Select your tables.
  • Click Transform Data.

Part 3: Cleaning Data Using Power Query

After importing the data:

  1. Remove unnecessary columns.
  2. Rename columns appropriately.
  3. Replace invalid values such as: 
Unknown
NULL
Blank values
Enter fullscreen mode Exit fullscreen mode
  1. Change data types.

Examples include:

Column Data Type
Units Sold Whole Number
Unit Cost Whole Number
Revenue Whole Number
Discount Decimal Number
Order Date Date
Delivery Date Date

Part 4: Creating Measures in Power BI

Examples of useful DAX measures include:

Total Cars Sold 

Total Cars Sold =
SUM('Jcars Clean'[Custom Units sold])
Enter fullscreen mode Exit fullscreen mode

Total Revenue 

Total Revenue =
SUMX(
'Jcars Clean',
'Jcars Clean'[Custom Units sold] *
'Jcars Clean'[Clean Unit Selling Price]
)
Enter fullscreen mode Exit fullscreen mode

Total Gross Profit 

Total Gross Profit =
SUMX(
'Jcars Clean',
(
'Jcars Clean'[Clean Unit Selling Price] -
'Jcars Clean'[Clean Unit Cost]
) *
'Jcars Clean'[Custom Units sold]
)
Enter fullscreen mode Exit fullscreen mode

Part 5: Building Your Dashboard

After creating the measures, you can develop interactive visuals such as:

  • KPI Cards
  • Clustered Bar Charts
  • Line Charts
  • Scatter Charts
  • Maps
  • Pie Charts
  • Tables
  • Slicers

Examples of dashboard analyses include:

  • Total sales revenue.
  • Branch performance.
  • Vehicle type performance.
  • Monthly sales trends.
  • Customer analysis.
  • Delivery performance.
  • Profitability analysis.

Advantages of Using SQL Databases with Power BI

Connecting Power BI directly to SQL databases offers several benefits:

  1. Improved data management.
  2. Better scalability.
  3. Faster refreshes.
  4. Centralized storage. Especially because you can't lose your data
  5. Seamless integration with business intelligence tools.
  6. Real-time reporting capabilities. You can always do some adjustments at your convenience.

Conclusion

Power BI's ability to connect to both local and cloud-based SQL databases makes it an excellent tool for business intelligence and data analytics. Whether you're working with a PostgreSQL database hosted locally or a cloud-hosted solution like Aiven, the connection process is straightforward and allows you to build dynamic and insightful dashboards with ease.

By combining SQL databases, Power Query transformations, and Power BI visuals, you can turn raw data into meaningful business insights and support data-driven decision-making.

Top comments (0)