Introduction
At some point in your data journey, you build something in a database and realise that the people who actually need to use that data are managers, clients, the rest of the team who are never going to open a SQL editor. They need charts. They need something they can click through, filter, and read without writing a single query.
That's where Power BI comes in.
Power BI is Microsoft's business intelligence and data visualisation tool. It takes data from various sources and turns it into interactive reports and dashboards, the kind that update automatically, respond to filters and can be shared across an entire organisation. Companies use it to track sales performance, monitor inventory, analyse customer behaviour, measure KPIs and make decisions based on what's actually happening in their data rather than what someone guesses is happening.
But Power BI doesn't create data on its own. It connects to where the data already lives and more often than not, that place is a SQL database.
SQL databases like PostgreSQL are built for exactly this kind of work. They store large volumes of structured data reliably, organise it into tables with clear relationships between them and allow multiple systems to read from and write to the same source at the same time. A business might have thousands of customer records, hundreds of thousands of transactions and a product catalogue that changes daily, all stored in one place, always current, always consistent.
This is precisely why connecting Power BI to a SQL database matters so much. Instead of exporting data to a spreadsheet, building a chart, and then repeating the whole process when the data changes, a direct database connection means your reports are always reading from the live source. When the database updates, the dashboard reflects it ,no manual steps, no stale numbers.
In this article, we'll walk through how to make that connection work: first with a local PostgreSQL database, then with a cloud-hosted one on Aiven.
Part 1 : Connecting Power BI to a Local PostgreSQL Database
A local database is one running on your own computer. If you've been learning PostgreSQL and building databases on your machine, this is where you start.
Step 1 — Open Power BI and click Get Data
Open Power BI Desktop and start with a blank report. On the Home ribbon at the top, click Get Data.
Get Data is on the Home ribbon, that's your starting point for any connection
Step 2 — Search for PostgreSQL
A window pops up listing every data source Power BI can connect to, there are a lot of them. Don't scroll. Type PostgreSQL in the search bar, select PostgreSQL Database from the results, and click Connect.
Search saves you from scrolling through the entire list
Step 3 — Enter your server and database details
A connection dialog appears asking for two things: the server name and the database name.
Server: localhost
Database: assignment
localhost tells Power BI that the database is on your own machine. The database name is whatever you named your database when you created it ,mine is assignment.
Below that, you'll see a Data Connectivity mode option with two choices:
- Import — Power BI copies the data into its own model. Faster to work with, great for most use cases.
- DirectQuery — Power BI queries the database live every time you interact with a report. Slower, but useful when you're working with very large or frequently-changing data.
For most projects at this level, Import is the right choice.
Enter localhost as the server, your database name, then choose Import
Step 4 — Enter your credentials
Power BI will ask for a username and password to authenticate. These are the same credentials you use when logging into pgAdmin, the PostgreSQL username (usually postgres) and the password you set during installation.
Enter them and click Connect.
Use the same username and password you'd use in pgAdmin
Step 5 — Select your tables and load
Once connected, the Navigator window opens. It shows every table in your database. Select the ones you want in my case, customers, products, sales and inventory and click Load.
Power BI imports them and they appear in the Data pane on the right side of the screen. Your local connection is done.
Part 2 : Connecting Power BI to Aiven (Cloud PostgreSQL)
Cloud databases work the same way in principle, but your connection details come from the cloud provider's dashboard rather than your local machine.
Aiven is a managed cloud database service, they host your PostgreSQL database on their infrastructure, so you don't have to worry about running it yourself. A lot of teams use this in production.
Step 1 — Get your connection details from Aiven
Log into console.aiven.io, open your PostgreSQL service, and go to the Overview tab. You'll find everything you need listed there:
-
Host — the address of the server (a long
.aivencloud.comURL) - Port — the port number Power BI will connect through
-
Database name — for Aiven this is typically
defaultdb -
Username —
avnadminby default - Password — visible from the Overview tab
Everything you need for the connection lives on the Aiven Overview tab
Keep this tab open you'll need to copy from it.
Step 2 — Enter the connection details in Power BI
Back in Power BI, go through the same flow: Get Data → PostgreSQL Database → Connect.
In the connection dialog, enter your Aiven host in the Server field and defaultdb (or your database name) in the Database field. Keep the mode on Import and click OK.
Paste the host directly from the Aiven console, it's a long string, easy to mistype
Step 3 — Enter your Aiven credentials
When the credentials screen appears, enter avnadmin as the username and the password from your Aiven Overview tab.
Use the credentials exactly as shown in your Aiven console
Click Connect. Power BI will authenticate and open the Navigator window, just like the local connection.
A note on SSL certificates
Aiven requires all connections to be encrypted using SSL (Secure Sockets Layer). SSL is a security protocol that encrypts the data travelling between your computer and the cloud server, so that even if someone intercepted it, they couldn't read it.
When you connect Power BI to Aiven, it needs to verify that it's talking to a legitimate server, not something pretending to be one. That verification is done using a CA certificate (Certificate Authority certificate), which you can download from your Aiven Overview tab.
If Power BI rejects the connection with an SSL error, download the certificate from Aiven, rename it from .pem to .crt, and install it into your Windows Trusted Root Certification Authorities store. After a Power BI restart, the connection will go through. You only ever need to do this once per machine.
Part 3 : Loading Tables and Building the Data Model
Once you're connected whether locally or via Aiven the next steps are the same.
Loading your tables
In the Navigator, select all the tables you need and click Load. Power BI imports them and they show up in the Data pane on the right. You can click into any table to see its columns and values.
Setting up relationships in Model view
Here's where a lot of beginners stop too early. Loading tables into Power BI is not the same as telling Power BI how those tables relate to each other. Without relationships, Power BI is just holding four separate spreadsheets, it can't combine them.
Click the Model icon on the left sidebar. You'll see your tables displayed as boxes, each showing their columns. This is where you define how they connect.
For a retail database like mine, the relationships look like this:
| From table | To table | Connecting column |
|---|---|---|
sales |
customers |
customer_id |
sales |
products |
product_id |
inventory |
products |
product_id |
You can create these by dragging a column from one table onto the matching column in another. Power BI will often detect some relationships automatically, but always check them manually.
The Model view shows how your tables link to each other the sales table sits at the centre
Why this matters
Once relationships are in place, Power BI knows how to cross-reference data across tables. If you build a chart showing total sales by customer name, Power BI can look up customer_id in the sales table, match it to the customers table and pull the right name automatically, every time.
Without the relationship, that lookup simply doesn't happen.
This pattern one central fact table (like sales) connected to surrounding dimension tables (like customers and products) is called a star schema. It's the standard structure for analytical data models and the reason Power BI reports can filter and slice data so fluidly.
Part 4: Why SQL Skills Matter for Power BI Analysts
Power BI is a capable tool, but there's something it can't do on its own: it can't make your data better than it arrives. Whatever goes into the model is what the dashboards are built on. If the data is messy, duplicated or incomplete when it lands in Power BI, the reports will reflect that and no amount of dragging visuals around will fix it.
That's the first reason SQL matters. Before data even touches Power BI, an analyst with SQL skills can clean it, shape it and make sure only the right rows are coming through.
Pulling only what you need
Loading an entire table into Power BI when you only need six months of transactions is wasteful. It makes the model heavier and the report slower. SQL lets you filter at the source, before anything is imported:
SELECT * FROM assignment.sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';
This is a simple example, but in a real dataset with millions of rows, this kind of query is the difference between a report that loads in two seconds and one that takes twenty.
Aggregating before loading
Power BI can calculate totals and averages after data is loaded, but there are times when it makes more sense to do that work in SQL first. If a stakeholder only ever wants to see total sales per product, there's no reason to bring in every individual transaction:
SELECT p.product_name, SUM(s.total_amount) AS total_sales
FROM assignment.sales s
JOIN assignment.products p ON s.product_id = p.product_id
GROUP BY p.product_name
ORDER BY total_sales DESC;
This query does the heavy lifting in the database and hands Power BI one clean, summarised row per product. The model stays lean and the report stays fast.
Joining tables before they reach Power BI
While Power BI has its own relationship system, there are cases where it makes more sense to join tables in SQL and load the result as a single, ready-to-use dataset. This is especially true when the logic behind the join is complex or when you want to be deliberate about which columns are exposed to the report layer.
Catching problems before they become dashboard problems
An analyst who knows SQL can inspect the data before loading it check for nulls, duplicates, unexpected values, mismatched IDs. These issues are much easier to handle in a SQL query than they are to track down after a dashboard is built and a stakeholder is asking why a number looks wrong.
-- Check for sales with no matching customer
SELECT s.sale_id
FROM assignment.sales s
LEFT JOIN assignment.customers c ON s.customer_id = c.customer_id
WHERE c.customer_id IS NULL;
This kind of query takes thirty seconds to write and can save hours of confusion later.
Using the Advanced Options SQL field in Power BI
When connecting to PostgreSQL, Power BI has an Advanced Options section in the connection dialog that includes a SQL statement field. You can write a query there and Power BI will load the result instead of the full table. This gives you all the control of SQL with all the visualisation power of Power BI, working together from the start.
The bottom line is this: Power BI tells the story. SQL decides which story is worth telling. Analysts who understand both are the ones who can build dashboards that people actually trust because they know what's in the data, not just what it looks like on a chart.
Conclusion
Connecting Power BI to PostgreSQL is one of those things that seems technical until you do it once and then it becomes second nature. The local connection is a few clicks: localhost, your database name, your pgAdmin credentials and you're in. The Aiven connection follows the same steps with a cloud host address instead.
The part that actually requires thought is what comes after the connection. Getting relationships right in the model, making sure your tables are linked the way the data actually works, understanding why a star schema is the right structure that's where the analysis either holds up or falls apart.
And underneath all of it is SQL. Not as a replacement for Power BI, but as the layer that makes everything Power BI receives worth working with. The best dashboards aren't built by people who are good at dragging visuals around. They're built by people who understood the data long before it reached the screen.
Something worked differently on your setup? Drop a comment below.
Top comments (0)