DEV Community

Victor Karanja
Victor Karanja

Posted on

From SQL to Power BI: Building Your First Connected Dashboard

#sql #database #beginners #tutorial

Introduction

Modern organizations generate and store huge amounts of data every day. This information may come from sales transactions, customers, inventory systems, websites, and business applications.

However, storing data is only the first step. The real value comes from transforming raw data into meaningful insights that support better decision-making.

This is where SQL databases and Microsoft Power BI work together.

SQL allows analysts to access, query, and prepare data stored in databases, while Power BI helps transform that data into interactive dashboards and reports.

In this article, we will explore how to connect Power BI to a SQL database, using PostgreSQL as an example, both locally and through a cloud database such as Aiven PostgreSQL.

By the end of this guide, you will understand how to:

  • Connect Power BI to PostgreSQL
  • Import database tables
  • Understand Import and DirectQuery modes
  • Connect to cloud databases securely
  • Model data relationships
  • Prepare data for dashboard creation

Understanding the Data Workflow

A typical data analytics workflow looks like this:

SQL Database
      │
      ▼
SQL Queries
      │
      ▼
Clean Dataset
      │
      ▼
Power BI Connection
      │
      ▼
Data Modeling
      │
      ▼
Interactive Dashboard
      │
      ▼
Business Insights
Enter fullscreen mode Exit fullscreen mode

A database stores the information, SQL prepares the data, and Power BI turns it into visual stories.

What is Power BI?

Microsoft Power BI is a business intelligence and data visualization platform used to analyze data and create interactive reports and dashboards.

Organizations use Power BI to:

  • Monitor business performance
  • Analyze trends
  • Track key performance indicators (KPIs)
  • Create automated reports
  • Support strategic decisions

Power BI connects to many different data sources, including:

  • SQL databases
  • Excel files
  • CSV files
  • Cloud databases
  • APIs
  • Data warehouses

Why Connect Power BI to a SQL Database?

Connecting Power BI directly to a database provides several advantages:

Access to Real-Time Data

Dashboards can reflect updated information from the organization's database.

Advanced Data Analysis

Analysts can combine SQL queries with Power BI features to perform deeper analysis.

Automated Reporting

Reports can refresh automatically without manually updating files.

Better Decision Making

Clean and visualized data helps organizations make informed decisions.

Connecting Power BI to a Local PostgreSQL Database

PostgreSQL is one of the most popular open-source relational database management systems.

Connecting Power BI to a local PostgreSQL database requires only a few steps.

Step 1: Launch Power BI Desktop

Open Power BI Desktop on your computer.

From the Home menu, select:

Home → Get Data
Enter fullscreen mode Exit fullscreen mode

Step 2: Select PostgreSQL Database Connector

From the available data sources, choose:

PostgreSQL Database
Enter fullscreen mode Exit fullscreen mode

Power BI provides connectors for many database systems.

Step 3: Enter Connection Details

A connection window will appear.

Enter your PostgreSQL database information.

Example:

Server:
localhost

Database:
SalesDB
Enter fullscreen mode Exit fullscreen mode

The server name identifies where the database is running, while the database name identifies the specific database you want to connect to.

Step 4: Choose Connection Mode

Power BI provides two main connection options:

Import Mode

Data is loaded into Power BI's memory.

Advantages:

  • Faster dashboard performance
  • Better for smaller and medium-sized datasets
  • Allows offline analysis

DirectQuery Mode

Power BI sends queries directly to the database.

Advantages:

  • Near real-time data
  • Useful for large databases

Disadvantages:

  • Performance depends on database speed

For beginners and portfolio projects, Import Mode is usually the best option.

Step 5: Enter PostgreSQL Login Details

Provide your database credentials:

Username

Password
Enter fullscreen mode Exit fullscreen mode

After successful authentication, Power BI will connect to your database.

Step 6: Load Tables into Power BI

The Navigator window will appear.

Select the tables you need.

Example:

☑ Customers

☑ Products

☑ Sales

☑ Inventory
Enter fullscreen mode Exit fullscreen mode

Click:

Load
Enter fullscreen mode Exit fullscreen mode

Your PostgreSQL data is now available inside Power BI.

Connecting Power BI to a Cloud PostgreSQL Database (Aiven)

Many organizations use cloud databases instead of local servers.

Aiven PostgreSQL provides managed PostgreSQL databases hosted in the cloud.

Connecting to a cloud database requires additional security settings.

Step 1: Collect Database Connection Information

From your Aiven dashboard, collect:

  • Host name
  • Port number
  • Database name
  • Username
  • Password

Example:

Host:
your-database-host

Port:
5432

Database:
sales_database
Enter fullscreen mode Exit fullscreen mode

Step 2: Download SSL Certificate

Cloud databases require secure connections.

Aiven uses SSL certificates to protect communication between applications and databases.

The certificate can be downloaded from the Aiven console.

Step 3: Connect Power BI to Aiven PostgreSQL

In Power BI:

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

Enter:

Server:
Host + Port

Database:
Database Name
Enter fullscreen mode Exit fullscreen mode

Step 4: Configure Secure Connection

Provide the required authentication information.

SSL provides:

  • Encryption of data during transmission
  • Protection against unauthorized access
  • Verification between client and server

Without encryption, sensitive business information could be exposed during transfer.

Loading Data and Creating a Data Model

After connecting successfully, Power BI can load tables such as:

  • Customers
  • Products
  • Sales
  • Inventory

A professional dashboard requires a proper data model.

Understanding Data Relationships

Relational databases usually contain multiple connected tables.

Example:

Customers
    |
    |
Customer_ID
    |
    |
Sales
    |
    |
Product_ID
    |
    |
Products
Enter fullscreen mode Exit fullscreen mode

Common relationships include:

Customers → Sales

Connected using:

Customer_ID
Enter fullscreen mode Exit fullscreen mode

Products → Sales

Connected using:

Product_ID
Enter fullscreen mode Exit fullscreen mode

Products → Inventory

Connected using:

Product_ID
Enter fullscreen mode Exit fullscreen mode

Key Data Modeling Concepts

Primary Key

A primary key uniquely identifies every record in a table.

Example:

Customer_ID
Enter fullscreen mode Exit fullscreen mode

Foreign Key

A foreign key connects one table to another.

Example:

Customer_ID in Sales Table
Enter fullscreen mode Exit fullscreen mode

Relationships

Relationships allow Power BI to correctly filter and aggregate information.

For example:

A sales dashboard can show:

  • Revenue by customer
  • Sales by product
  • Inventory levels

Creating Your First Dashboard

Once the data model is ready, you can create visual reports.

Common Power BI visuals include:

Cards

Display important numbers:

  • Total Sales
  • Total Customers
  • Total Profit

Bar Charts

Compare categories:

Example:

Sales by Product Category
Enter fullscreen mode Exit fullscreen mode

Line Charts

Analyze trends:

Example:

Monthly Revenue Growth
Enter fullscreen mode Exit fullscreen mode

Maps

Analyze geographical information:

Example:

Sales by Region
Enter fullscreen mode Exit fullscreen mode

Using SQL Queries Before Loading Data

Instead of importing entire tables, analysts can write SQL queries to prepare data.

Example:

SELECT
    product_category,
    SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_category;
Enter fullscreen mode Exit fullscreen mode

Benefits:

  • Faster reports
  • Cleaner datasets
  • Reduced transformation work

Common Connection Problems

Authentication Errors

Check:

  • Username
  • Password
  • Database permissions

Server Not Found

Verify:

  • Server address
  • Port number
  • Database status
  • Network connection

Slow Dashboards

Improve performance by:

  • Removing unnecessary columns
  • Optimizing SQL queries
  • Creating proper data models
  • Avoiding unnecessary visuals

Best Practices for Power BI and SQL Projects

1. Clean Data Before Visualization

Good dashboards require accurate and reliable data.

2. Build Proper Relationships

Avoid combining everything into one large table.

3. Write Efficient SQL Queries

Only retrieve the data needed for analysis.

4. Keep Dashboards Simple

Focus on answering business questions.

5. Document Your Work

Include:

  • Data source
  • SQL queries
  • Data cleaning steps
  • Dashboard purpose
  • Business insights

Conclusion: Why SQL Skills Matter for Power BI Analysts

SQL is one of the most important skills for Power BI analysts.

Although Power BI provides powerful visualization tools, SQL gives analysts direct control over the data behind their reports.

With SQL, analysts can:

  • Query required datasets
  • Filter information
  • Perform calculations such as SUM, COUNT, and AVG
  • Combine multiple tables
  • Prepare clean datasets for visualization

By combining SQL + PostgreSQL + Power BI, analysts can transform raw database records into meaningful dashboards that support business decisions.

The journey from SQL to Power BI is not only about creating attractive charts — it is about turning data into insights.

Top comments (0)