Source: Dev.to

Introduction

In most modern organizations, data is one of the most valuable assets. Companies collect large amounts of information from sales systems, websites, customer platforms, and operational databases. To make sense of this information, businesses use tools that can transform raw data into clear insights. One of the most widely used tools for this purpose is the Microsoft Power BI platform.

Power BI is a business‑intelligence and data‑visualization tool developed by Microsoft. It allows users to:

• Connect to different data sources
• Analyze data
• Create interactive dashboards and reports

These reports help organizations monitor performance, understand trends, and support decision‑making. Power BI is commonly used by data analysts, business managers, and decision makers because it can present complex data in simple visual forms such as charts, tables, maps, and dashboards.

Most organizations store their operational and analytical data in SQL databases. SQL databases are designed to store large amounts of structured data in tables and allow users to query, filter, update, and analyze data efficiently using Structured Query Language (SQL). They are reliable, secure, and scalable, making them a staple in business systems such as sales platforms, customer‑management systems, and inventory systems.

Connecting Power BI to a database allows analysts to access this stored data directly. Instead of manually exporting data into spreadsheets, Power BI can retrieve the data automatically, refresh it when the database changes, and build dashboards that always reflect the latest information.

This article explains:

1. How Power BI connects to SQL databases
2. How to connect to a local PostgreSQL database
3. How to connect to a cloud PostgreSQL database (e.g., Aiven)
4. How the loaded data is modeled for analysis

Power BI Desktop Interface

Power BI Desktop is the main application used for building reports and dashboards.

You can download the Power BI Desktop app here.

Connecting to a Local PostgreSQL Database

1. Open Power BI Desktop.

2. On the Home tab of the ribbon, click Get Data → More….

3. In the Get Data window, scroll down and select PostgreSQL Database, then click Connect.

   [Image: Get Data – PostgreSQL]

4. Enter connection details:

   • Server – The location of the database server (e.g., localhost or 127.0.0.1).
   • Database – The name of the database you want to connect to.

5. Click OK. Power BI will prompt for authentication.

   • Username – The PostgreSQL user created during installation.
   • Password – The corresponding password.

6. Click Connect.

7. The Navigator window appears, showing all available tables in the selected database. Choose one of the following actions:

   • Load – Import the data directly.
   • Transform Data – Open Power Query to clean or modify the data before loading.

Connecting to an Aiven PostgreSQL Cloud Database

1. Log in to Aiven and navigate to the dashboard of your PostgreSQL service.

2. Locate the Connection Information section. You will need:

   • Host name (e.g., myservice.aivencloud.com)
   • Port number (usually 5432)
   • Database name
   • User name and Password

3. Download the CA certificate (ca.pem) from the same Connection Information panel.

4. Convert the certificate to a .crt file (if necessary) and install it on your PC:

   • Open certmgr.msc (Windows Certificate Manager).
   • Right‑click Trusted Root Certification Authorities → Certificates → All Tasks → Import.
   • Browse to the ca.crt file, complete the wizard, and finish.

5. Open Power BI Desktop → Get Data → More… → PostgreSQL Database → Connect.

6. Fill in the connection fields:

   • Server – host_name:port_number (e.g., myservice.aivencloud.com:5432)
   • Database – Your database name

7. Click OK. When prompted for authentication, enter the Aiven user name and password, then click Connect.

8. Choose Load or Transform Data in the Navigator window, depending on your needs.

Note: Cloud providers like Aiven require SSL encryption. Installing the CA certificate ensures that the connection between Power BI and the PostgreSQL server is encrypted and that the server’s identity is verified.

Data Modeling in Power BI

After loading data, Power BI automatically detects relationships between tables based on matching columns (primary‑key ↔ foreign‑key).

• Automatic detection – Power BI creates relationships where column names and data types match.
• Manual creation – Drag a column from one table onto the matching column in another table in Model View.

In Model View, tables appear as connected boxes, visualizing how data flows between them.

Why Good Data Modeling Matters

• Accurate filtering – Ensures slicers and filters affect the correct tables.
• Correct calculations – Guarantees totals, averages, and other measures compute as intended.
• Meaningful visualizations – Enables charts that reflect true business relationships.
• Avoids duplicate values – Prevents double‑counting caused by ambiguous joins.

Example: Sales Analysis

By linking Sales.CustomerID → Customers.CustomerID and Sales.ProductID → Products.ProductID, you can:

• Slice sales by region or customer segment.
• Compare revenue across product categories.
• Calculate average order value per customer.

Summary

• Power BI Desktop offers a straightforward interface for connecting to both local and cloud PostgreSQL databases.
• Proper SSL certificate installation is essential when connecting to cloud services like Aiven.
• Once data is loaded, leveraging Power BI’s automatic relationship detection—or manually defining relationships—ensures robust data modeling, which is the foundation for accurate analysis and compelling visualizations.

Why SQL Matters for Power BI

Most business data lives in SQL databases. Before Power BI can visualize that data, it must be retrieved, cleaned, and structured properly. Good SQL practices reduce the amount of data that Power BI needs to import, improve performance, and simplify the data model.

1. Retrieve Data

Goal: Select only the columns you need.

-- Selecting only product name and price columns from the products table
SELECT product_name,
       price
FROM   products;

Why this matters in Power BI

• Reduces the amount of data imported
• Improves performance
• Makes the model easier to manage
• Avoids unnecessary columns

2. Filter Data

Goal: Pull only the rows you actually need.

-- Retrieving only sales from 2024 onwards
SELECT *
FROM   sales
WHERE  sale_date >= '2024-01-01';

Why this matters

• Reduces dataset size
• Speeds up report loading
• Focuses analysis on relevant data
• Avoids unnecessary processing inside Power BI

3. Perform Aggregations

Goal: Summarize data before it reaches Power BI.

-- Calculating total sales per product
SELECT product_id,
       SUM(quantity) AS total_quantity
FROM   sales
GROUP BY product_id;

Why aggregation in SQL is important

• Reduces data volume before loading
• Improves Power BI performance
• Simplifies data models
• Avoids heavy calculations in DAX

Joining Tables

Business data is usually stored across multiple tables. Joining them in SQL creates a single, clean dataset for Power BI.

SELECT c.customer_name,
       s.sales_amount
FROM   customers c
JOIN   sales s
       ON c.customer_id = s.customer_id;

Why this is important

• Combines related data into one dataset
• Reduces the need for complex relationships in Power BI
• Makes analysis easier
• Prevents duplication errors

Data‑Cleaning Basics

Raw data is often messy (missing values, duplicates, wrong formats). Cleaning it in SQL leads to more reliable Power BI reports.

Eliminating Duplicates

SELECT DISTINCT customer_id
FROM   customers;

Handling Missing Values

SELECT COALESCE(phone_number, 'Not Provided') AS phone
FROM   customers;

Why data cleaning matters

• Ensures data accuracy
• Improves report reliability
• Reduces cleaning work in Power BI
• Prevents errors in calculations

Calculated Columns in SQL

Create derived metrics before loading them into Power BI.

-- Calculate total sales per line item
SELECT product_name,
       price,
       quantity,
       price * quantity AS total_sales
FROM   sales;

Why calculated fields are useful

• Prepares key metrics before loading
• Reduces need for DAX calculations
• Keeps logic centralized in the database

Advanced SQL Operations

Power BI can benefit from more sophisticated SQL features.

Window Functions (e.g., running totals)

SELECT sale_date,
       SUM(sales_amount) OVER (ORDER BY sale_date) AS running_total
FROM   sales;

Other powerful tools

• Subqueries
• Common Table Expressions (CTEs)
• Data transformations (pivot, unpivot, etc.)

Bringing It All Together

Power BI is a powerful tool that transforms structured data into interactive dashboards and reports. By connecting directly to SQL databases, analysts can:

1. Retrieve only the necessary columns.
2. Filter rows to keep the dataset lean.
3. Aggregate and calculate metrics early, reducing the load on Power BI.
4. Clean and join data so the model is tidy and reliable.

SQL provides the foundation; Power BI builds the story on top of it. Strong SQL skills enable analysts to work more efficiently, produce accurate reports, and deliver better insights for decision‑making. When used together, SQL and Power BI form a powerful combination for modern data analysis and business intelligence.