DEV Community

Daniel Mutwiri Mbabu
Daniel Mutwiri Mbabu

Posted on

Connecting Power BI to a SQL Database

#sql #database #powerfuldevs #dbeaver

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.

  1. Import Mode
  2. Direct Query
  3. 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

  1. Create indexes on frequently filtered columns.
  2. Use star schema design.
  3. Minimize calculated columns in Power BI.
  4. 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

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

  1. 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

Log in to your Aiven Console and open your PostgreSQL service. Note the following:

  • 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.

Top comments (0)