Connecting Power BI to a SQL Database

Daniel Mutwiri Mbabu — Thu, 23 Jul 2026 08:53:03 +0000

Introduction

Oftentimes we need to clean, model, and report insights from data stored in databases. Sometimes the data can be stored in a local SQL database or in a cloud-based SQL database. Today we will dive into the nitty-gritty of this endeavor using Power BI as your reporting tool and a SQL database as our storage.

Understanding Data Connectivity Modes

To connect a reporting and analysis tool such as Power BI to a SQL Database, understanding how it connects to the database is crucial. There are three primary connection modes. Each serves a different purpose depending on your reporting needs.

Import Mode

Direct Query

Live Connection

1. Import Mode

How it works
Power BI imports all selected data into its in-memory VertiPaq engine.
Reports query the imported data instead of the SQL database.

Ideal when

Data changes daily or a few times per day.
You need high-performance dashboards.
Complex calculations and relationships are required.

Example
A sales database updates overnight, and management reviews reports each morning.

2. DirectQuery Mode

How it works
No data is stored in Power BI (except metadata and optional cache).
Every filter, slicer, or visual generates a SQL query against the database.

Ideal when

Data changes every few minutes.
Databases contain hundreds of millions of rows.
Data cannot be copied due to compliance or storage constraints.

Example
A call center dashboard showing live customer support activity.

Performance Tips

Create indexes on frequently filtered columns.

Use star schema design.

Minimize calculated columns in Power BI.

Reduce visual complexity.

3.Live Connection

How it works
Instead of connecting directly to SQL, Power BI connects to:

SQL Server Analysis Services (SSAS)
Azure Analysis Services (AAS)
Another Power BI semantic model

The semantic model handles calculations, relationships, and security.

Ideal when
An organization has a centralized BI team managing enterprise models.
Multiple reports should use the same governed data model.

Scenario	Recommended Mode
Fast executive dashboards	Import
Real-time operational dashboards	DirectQuery
Very large databases	DirectQuery
Enterprise semantic model already exists	Live Connection
Need both speed and fresh data	Composite Model

Connecting Power BI to a Local SQL Database

A local SQL Database runs on your local machine. Therefore, connecting to it is easy!
Prerequisites

SQL Server installed (e.g., SQL Server Express, Developer, or Standard Edition)
SQL Server service is running
You know:
Server name
Database name
Authentication method (Windows or SQL Server Authentication)
Power BI Desktop is installed

Step 1: Open Power BI Desktop

Launch Power BI Desktop.
Click Get Data on the Home ribbon.

Select SQL Server. (In this case, we are using a Postgres Server)

Click Connect.

Step 2: Enter Connection Details

You'll be prompted for:

Server and Database information.
For a local connection, input the server credentials as: 127.0.0.1:5432
where th 5432 is the port number

Step 3: Choose Connectivity Mode

You'll have two options:

Import (recommended)
DirectQuery

Step 4: Authentication

Windows Authentication (Most Common)
Uses your Windows login credentials.

Select: Windows > Then click Connect.

SQL Server Authentication (Authentication details you input while installing your local SQL server)

Choose: Database > Enter: Username and Password

Click Connect.

Step 5: Select Tables

Power BI will display all tables and views. Select the tables you need. Click Load to import them or Transform Data to clean them in Power Query before loading.

Connecting Power BI to a Cloud-based SQL Database

For this, the cloud service provider is Aiven.

Cloud databases like Aiven PostgreSQL offer scalable and flexible data storage but require secure internet-based connections. To protect data in transit, they use encryption. Before Power BI can connect, the local machine must trust the cloud provider's Certificate Authority (CA), allowing it to verify the database server's identity and establish a secure connection.

Step 1: Collect Aiven Connection Details

Host (e.g., pg-12345-project.aivencloud.com)

Port (usually 12345 or 5432)

Database name

Username

Password

CA Certificate

Step 2: Install the PostgreSQL ODBC/Npgsql Driver (if required)

Power BI uses PostgreSQL drivers to connect.
Download and install the latest PostgreSQL (Npgsql) driver.
Restart Power BI Desktop after installation.

Step 3: Trust the Aiven CA Certificate

Since Aiven uses SSL encryption, Windows must trust the Certificate Authority. This helps Power BI trust the Aiven Postgres server.

NOTE: To connect Power BI to the Database without the certificate, this error occurs.

Locate the downloaded ca.pem file.
Import it into the Windows Trusted Root Certification Authorities:

Click Import> In the import wizard, click Next> Browse for the downloaded CA file

Add Certificates for the Computer Account.

Step 4: Open Power BI Desktop ( Same as for local connection steps 1 to 3)
Select Home → Get Data → Choose PostgreSQL database.

Step 5: Enter the Server Details

Fill in the fields as per the data on your Aiven service page:
Server: hostname:port (pg-12345-project.aivencloud.com:12345)
Database: Database name (defaultdb)

Click OK.

Step 6: Authenticate - if connecting first time

Choose Database Authentication: Username > Password

Click Connect.

Step 8: Select Tables

Power BI will display the PostgreSQL schema. Choose the required tables or views.

Load to import the data or Transform Data to clean it in Power Query before loading.

Parting Shot...

Every dashboard begins with a connection. This is where the strength of Power BI comes in: Interconnectivity! Whether your data resides on a local server or in the cloud, mastering secure database connectivity equips you to spend less time troubleshooting and more time discovering the stories hidden within your data. After all, data only becomes valuable when it is transformed into meaningful insights.

How Excel is Used in Real-World Data Analysis

Daniel Mutwiri Mbabu — Sun, 07 Jun 2026 14:58:01 +0000

The eagle first learns to walk before it masters the sky. In the world of data analysis, Excel is often the starting point of that journey. It helps us clean, organize, analyze, and visualize data in ways that reveal valuable patterns and opportunities. What begins as simple rows, columns, and calculations soon transforms into a powerful analytical tool that enables users to uncover trends, solve problems, and make informed decisions based on data rather than assumptions.

I recently joined an official Microsoft Excel class. Although I had used Excel before and was required to learn it on the job due to its importance in business, I quickly discovered that there is always something new to learn. I joined the class believing I had a solid understanding of Excel, but I soon realized that beneath its simplicity lies remarkable sophistication. This balance between simplicity and power is what makes Excel such an indispensable tool.

One of the key lessons I have learned is that data analysis is not just about mastering formulas and features; it is also about understanding the business environment and the problem being solved. A good data analyst should possess both technical and business knowledge. Understanding what questions to ask is just as important as knowing how to manipulate and analyze the data.

Excel plays a vital role in several real-world applications.** In business decision-making,** organizations collect large amounts of data from sales, operations, customers, and employees. Analysts use Excel to identify trends, determine which products generate the most revenue, evaluate branch performance, and forecast future demand. These insights help businesses make informed decisions regarding inventory management, staffing, and investments.
Excel is also widely used in financial reporting. Organizations use it to compare actual spending against budgets, forecast revenues, evaluate investments, and identify potential financial risks. These analyses provide management with the information needed to make sound financial decisions.
In marketing, Excel helps measure the effectiveness of advertising campaigns, social media activities, and customer engagement efforts. By analyzing metrics such as website traffic, conversion rates, and customer acquisition costs, organizations can determine which strategies are delivering the best results and allocate resources more effectively.

One of the most valuable skills I have learned is data cleaning. Clean data forms the foundation of reliable analysis. Excel provides numerous tools and functions that support this process, including

COUNTIFS(), TRIM(), VALUE(), DATEDIF(), YEAR(), TODAY(), NOW(), IFERROR(), and SUMIFS().

Features such as Find and Replace, Conditional Formatting, Data Validation, Multi-Sort, and handling blanks help ensure data accuracy and consistency. Data cleaning also involves checking whether data is realistic and free from errors, such as identifying impossible employee ages or inconsistent entries.

Learning Excel has greatly increased my appreciation for data-driven decision-making. Whether in business, finance, marketing, or personal projects, I now recognize the importance of relying on data rather than assumptions. Excel has transformed the way I see information by showing me how raw data can be organized, analyzed, and converted into actionable insights. As I continue learning, I appreciate even more how a tool that appears simple on the surface can become a powerful means of solving complex real-world problems.

DEV Community: Daniel Mutwiri Mbabu

Connecting Power BI to a SQL Database

Introduction

Understanding Data Connectivity Modes

Connecting Power BI to a Local SQL Database

Connecting Power BI to a Cloud-based SQL Database

Parting Shot...

How Excel is Used in Real-World Data Analysis