Connecting Power BI to SQL Databases: A Comprehensive Guide

Table of Contents

1. What Is Power BI?
   • What Power BI Actually Is
   • Why Connect Power BI to a Database — Not Just a Spreadsheet?
   • What Is a SQL Database?
2. Connecting Power BI to a Local PostgreSQL Database
3. Connecting to Aiven — A Cloud PostgreSQL Database
4. Loading Tables and Building the Data Model
5. Why SQL Skills Are Important for Power BI Analysts

Before we dive into all this, take a moment to think about your own data setup:

1. How many different sources does your organization use? (Excel, Google Sheets, CSV exports, CRM systems?)
2. Do multiple people ever overwrite each other’s work?
3. How long does it take to update reports or dashboards?

If you answered “more than once” or “too long” to any of these, connecting Power BI to a SQL database could save you hours or even days, every month.

1. What Is Power BI?

If you’ve ever looked at a large spreadsheet full of rows and columns, you know how difficult it can be to quickly understand what the data is actually telling you. Important insights can easily get buried in raw numbers, making it hard to see patterns, trends, or key business metrics.

Look at this Excel sheet for example and see how hard it is to interpret the data.

This challenge exists whether you’re a business owner, a student learning data analytics, or a professional working with large datasets.

This is where Power BI comes in.

Power BI is a business intelligence tool developed by Microsoft that allows users to connect to multiple data sources, transform and analyze data, and build interactive dashboards and reports.

Using visual elements such as charts, maps, tables, and KPIs, Power BI turns raw data into clear, actionable insights that are easy to understand.

While Power BI can connect to many different data sources such as Excel files and cloud services, one of the most common and powerful integrations is with SQL databases.

Many organizations store their operational data in relational databases like PostgreSQL, MySQL, or Microsoft SQL Server. These databases contain structured information such as transactions, customer records, product inventories etc.

The Role of SQL Databases in Analytics
SQL stands for Structured Query Language.
A SQL database organizes data into tables, which look similar to spreadsheets(rows and columns) but with strict rules about what each column can contain and how tables relate to each other.

You use SQL, the language, to ask questions of the database, like: "Show me all properties in Nairobi that sold for more than 10 million shillings this year."

For analytical workloads, SQL databases serve as the foundation from which BI tools like Power BI extract, transform, and visualize data.

Whether it is customer records, product catalogues, sales transactions, or inventory logs, the database acts as the main storage of business data.

Why Connect Power BI to a Database not Just a Spreadsheet?(for example, a file created in Excel)

Many businesses start by storing their data in Excel spreadsheets. For small datasets, this works fine.

However, as the volume of data grows, spreadsheets quickly become difficult to manage.

Let us consider a property management company tracking 5,000 rental units across multiple cities. Their data includes tenant records, property details, rental payments, and transaction histories. Managing all of this in a single spreadsheet would be inefficient and prone to errors.

A SQL database solves these problems in several ways

• Scalability: Databases can store millions of rows of data without slowing down.
• Multi-user access: Multiple users can read and update data simultaneously without overwriting each other’s work.
• Structured data integrity: Data is stored in well-organized tables with defined relationships and rules. For example, every property can be linked to a valid agent ID, preventing incomplete or inconsistent records.
• Automated reporting: Power BI can connect directly to the database and refresh reports automatically, ensuring dashboards always reflect the most recent data.

In the next section, we’ll walk through how to connect Power BI to a SQL database step by step.

Throughout this guide, we use Power BI Desktop — that is where all the connecting, modelling, and building happens.

2. Connecting Power BI to a Local PostgreSQL Database

"Local" simply means the database is installed on your own computer.

Step 1: Open Power BI Desktop

• Launch Power BI Desktop and navigate to the Home tab.
• Click Get Data.

Step 2: Select PostgreSQL as the Data Source

• In the Get Data window, search for PostgreSQL database and select it.

Step 3: Enter the Server and Database Details
In the connection dialog box, enter the following information:

Server: localhost
Database: lux_sales

If PostgreSQL is installed locally, the server's name localhost refers to your own computer.

Power BI will then prompt you to select a connection mode:

• Import Mode – copies the data into Power BI for faster analysis.
• Direct Query Mode – queries the database in real time without storing the data in Power BI.

Most beginners use Import Mode because it provides faster performance and is easier to work with during analysis.

Step 4: Enter Credentials
Next, Power BI will ask for database authentication details:

• Username
• Password

These are the same credentials used to log into the PostgreSQL database.
After authentication, Power BI establishes the connection to the database.

Step 5: Select Tables in the Navigator

Once connected, the Navigator window displays the available tables in the database.

Users can preview the tables before loading them into Power BI.

Select the required tables and click Load.

The tables will now appear in Power BI’s Data View and will be available for analysis.

3. Connecting Power BI to a Cloud PostgreSQL Database (Aiven)

In many organizations, databases are hosted in the cloud instead of on local machines. One platform that provides managed PostgreSQL databases is Aiven.

With managed services like this, organizations do not need to worry about maintaining servers, backups, or updates. Instead, they can focus on analyzing their data.

A typical analytics workflow might involve several tools:

• Aiven - hosting the database in the cloud
• PostgreSQL - storing the structured data
• DBeaver - for exploring and querying the database
• Power BI - for building dashboards and visualizations

Step 1: Obtain Database Connection Details from Aiven

• To connect Power BI to a cloud database, you first need the connection details provided by Aiven.
• Inside the Aiven dashboard, you will find the following parameters: Host, Port, Database name, Username, Password

It would look like this:

These details are required for any tool connecting to the database, including Power BI or DBeaver.

Step 2: Connect Power BI to PostgreSQL:

• Click Get Data
• Select PostgreSQL database and you will get this pop up

• Enter the connection details from Aiven:

Host → pg-3b04de5f-millicentisika-2259.j.aivencloud.com
Port → 11547

pg-3b04de5f-millicentisika-2259.j.aivencloud.com:11547

The database you should input the database you want to work on.
In this example, the database I wanted to use for analysis was luxsales.

• Choose Import mode for data connectivity.
• Click OK.
• Power BI will then prompt for authentication.

Step 3: Download the SSL Certificate

Most cloud databases require secure SSL connections.

You may encounter this error while trying to connect:

This occurs because Aiven enforces secure SSL connections.

Database tools such as DBeaver automatically trust the certificate, but Power BI requires manual validation.

To resolve this, download and install the SSL certificate provided by Aiven.

Step 3.a: Download the Certificate from Aiven

• Go to console.aiven.io
• Open your PostgreSQL service
• Navigate to the Overview tab
• Scroll to Connection Information
• Click Download CA Certificate

This downloads a file named:

ca.pem

Step 3.b: Convert the Certificate Format

Windows requires certificates in .crt format.

Locate the downloaded file (usually in Downloads).

Rename the file from:

ca.pem to ca.crt

To do this:

• Right-click the file
• Select Rename
• Change the extension to .crt

If Windows hides extensions:

• Select Save as type → All Files
• Manually type: ca.crt

Step 3.c: Install the Certificate in Windows

• Double-click the ca.crt file.
• Click Install Certificate.
• Select Local Machine, then click Next.
• Choose Place all certificates in the following store.
• Click Browse.
• Select Trusted Root Certification Authorities.
• Click OK → Next → Finish.
• You should see the confirmation message: The import was successful.

Step 3.d: Restart Power BI and Reconnect

• After installing the certificate, close Power BI Desktop completely.
• Reopen Power BI.

Connect again using the same PostgreSQL connection details.

Server: pg-3b04de5f-millicentisika-2259.j.aivencloud.com:11547
Database: luxsales
Mode: Import

Once the connection is successful, Power BI will display a Navigator window where you can select tables and load them for analysis.

This works whether the PostgreSQL is on local computer or on cloud service like Aiven. The certificate installation simply allows your computer to trust secure connections from Aiven’s servers. Power BI then connects directly to the cloud PostgreSQL database over the internet.

4. Loading Tables and Building the Data Model

Once connected to the database, the next step is to load the required tables and establish relationships between them.

Step 1: Select Tables in the Navigator

After connecting to PostgreSQL (local or cloud), the Navigator window displays all available tables in the database.

For this example, we will load the following tables:

• customers
• products
• sales
• inventory

Select all four tables and click Load.

Step 2: Understanding the Data Model

Once loaded, Power BI stores the tables in its data model. To view and manage relationships between tables, navigate to the Model View by clicking the model icon on the left sidebar.

What Is a Data Model?
A data model defines how tables relate to each other.
In a well-designed model:

• Each table has a primary key (a unique identifier for each row)
• Tables are connected through foreign keys (columns that reference primary keys in other tables)

For example:

• The sales table contains a customer_id column that links to the customer_id in the customers table
• The sales table contains a product_id column that links to the product_id in the products table

Step 3: Creating Relationships in Power BI

Power BI automatically detects some relationships, but you may need to create or modify them manually.

Common relationships in this model:

• sales.customer_id → customers.customer_id (many-to-one)
• sales.product_id → products.product_id (many-to-one)
• inventory.product_id → products.product_id (one-to-one or many-to-one)

Relationship Cardinality

Each relationship has a cardinality that defines how rows in one table relate to rows in another:

• One-to-Many (1:*): One customer can have many sales transactions
• Many-to-One (*:1): Many sales belong to one customer
• One-to-One (1:1): One product has one inventory record (less common)

Power BI displays the cardinality on the relationship line with symbols like 1 and *.

Why Relationships Matter

Properly defined relationships enable Power BI to:

• Filter correctly across tables: When you select a customer, Power BI automatically shows only their sales
• Aggregate accurately: Calculating total sales per customer requires the relationship between sales and customers
• Avoid duplicate counts: Without relationships, metrics like total revenue might be calculated incorrectly

Star schema is the most common modeling pattern and looks like this

      Customers

         |

         |

     Sales (Fact Table - Center)

       /   \

      /     \

Products   Inventory

1. Why SQL Skills Are Important for Power BI Analysts

Although Power BI provides powerful visualization tools, SQL skills remain essential for analysts. SQL enables them to efficiently retrieve, manipulate, and prepare data stored in relational databases before using it in visualization tools like Power BI.

Key SQL capabilities include:
i) Retrieving Data
Instead of exporting entire datasets, analysts can use queries such as SELECT to extract only the columns and rows relevant to their analysis.

Example:
SELECT *
FROM sales
WHERE order_date >= '2025-01-01';

ii) Filtering Data
SQL allows analysts to filter large datasets before importing them into Power BI.
Using conditions like WHERE, analysts can narrow results based on specific criteria such as dates, locations, or product categories.

Example:
SELECT *
FROM customers
WHERE country = 'Kenya';

iii) Aggregating Data
Analysts often need summary insights rather than raw data.

SQL provides functions such as SUM(), COUNT(), AVG(), MIN(), and MAX() to calculate totals, averages, and other statistical measures.

When combined with GROUP BY, these functions allow analysts to summarize data by categories such as product type, department, or sales region.

Example:
SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

iv) Preparing Data for Visualization
SQL helps transform raw tables into structured datasets suitable for dashboards.
Examples include:
• Joining tables
• Creating calculated fields
• Building views
• Filtering unnecessary records

By performing these steps directly within the database, SQL ensures that the data being imported into dashboard tools like Power BI is already clean, organized, and ready for analysis.

This not only improves performance but also allows analysts to build more accurate and efficient dashboards.

Conclusion

Connecting Power BI to a SQL database allows analysts to move beyond static spreadsheets and work directly with structured, scalable data sources.

Whether the database is running locally or hosted in the cloud using services like Aiven, the process follows the same process: connect to the database, load the relevant tables, model the relationships, and build visual insights.

By combining SQL for data preparation and Power BI for visualization, analysts can build powerful dashboards that turn raw data into actionable business insights.