A practical guide to integrating Power BI Desktop with local PostgreSQL and cloud-hosted Aiven databases - including data modelling and why SQL still matters.
Table of Contents
- Introduction: Power BI and SQL Databases
- Connecting to a Local PostgreSQL Database
- Connecting to Aiven Cloud PostgreSQL
- Loading Tables and Creating Relationships
- Why SQL Skills Matter for Power BI Analysts
1. Introduction: Power BI and SQL Databases
Microsoft Power BI is one of the leading business intelligence platforms in use today. It enables organisations of all sizes to transform raw data into interactive dashboards, reports, and visualisations that help decision-makers act on evidence rather than intuition. From tracking monthly sales performance to monitoring operational KPIs in real time, Power BI sits at the centre of how modern businesses consume their data.
Power BI is available in several forms. Power BI Desktop is the Windows application used to build reports and data models. Power BI Service is the cloud-based platform where those reports are published and shared across an organisation. Together, they cover the full lifecycle of analytical work - from raw data connection to executive-level dashboarding.
Why connect Power BI to a database?
While Power BI can import data from Excel files, CSV exports, and web APIs, these sources have significant limits. They are static, often out of date, and difficult to maintain at scale. A well-structured relational database, by contrast, is the authoritative source of truth for most business data. It stores transactions, customer records, inventory levels, and operational events with precision, consistency, and referential integrity.
When Power BI connects directly to a database, analysts can query the freshest available data, apply complex filters at the database level, and avoid the overhead of manually exporting and re-importing flat files. The database handles storage and retrieval efficiently; Power BI handles visualisation and exploration. Each tool does what it does best.
The role of SQL databases in analytical workflows
SQL (Structured Query Language) databases, including PostgreSQL, Microsoft SQL Server, and MySQL, are the backbone of most enterprise data architectures. They organise data into tables with clearly defined schemas, enforce relationships between entities, and support powerful querying through the SQL language.
PostgreSQL, in particular, is an open-source relational database widely used in both development and production environments. It supports advanced data types, complex joins, window functions, and JSON storage, making it a versatile choice for analytical workloads. Whether self-hosted on a local machine or managed in the cloud through platforms like Aiven, PostgreSQL integrates cleanly with Power BI.
2. Connecting to a Local PostgreSQL Database
What this guide covers: This guide walks through connecting Power BI to both a local PostgreSQL instance and a cloud-hosted Aiven database. It also covers data modelling with four linked tables:
customers,products,sales, andinventoryand concludes with a discussion of why SQL fluency is valuable for BI analysts.
A local PostgreSQL database runs on the same machine as Power BI Desktop, or on a machine within your local network. This is the standard setup for development, testing, or environments where the data does not leave the building. The connection process requires no SSL configuration and is straightforward once PostgreSQL is running and a database exists.
Prerequisites
Before beginning, confirm the following are in place:
- Power BI Desktop is installed (Windows only).
- PostgreSQL is installed, and the target database is created.
- The Npgsql PostgreSQL connector is installed. Power BI requires this driver to communicate with PostgreSQL. Download it from the official Npgsql releases page and install it before opening Power BI.
- You know the database name, a valid username, and its password.
Step-by-step connection process
Step 1 - Open Power BI Desktop
Launch the application. On the start screen, click Get Data. If you are already inside a report, navigate to Home → Get Data in the ribbon.
Step 2 - Search for PostgreSQL Database
In the Get Data dialogue, type PostgreSQL in the search box. Select PostgreSQL Database from the results and click Connect.
Step 3 - Enter the server and database details
In the connection dialogue, fill in two fields:
-
Server - enter
localhostfor a local instance, or a hostname/IP address for a network server. -
Database - enter the exact name of the PostgreSQL database you want to connect to (for example,
assignment).
Leave the Data Connectivity mode as Import unless you specifically require DirectQuery.
Step 4 - Provide credentials
Power BI will prompt for a username and password. Select Database under the credential type dropdown, then enter your PostgreSQL username (often postgres) and the corresponding password. Click Connect.
Step 5 - Select and load tables
The Navigator pane will display all schemas and tables in the database. Check the tables you want to load - for example, customers, products, sales, and inventory. Click Load to import them directly, or Transform Data to open the Power Query Editor first.
Connection flow:
[Power BI Desktop] - [Get Data / PostgreSQL] - [Server & Credentials] - [Navigator] - [Load]
Step 1 Step 2 Steps 3–4 Step 5 Final
Figure 3 - Local PostgreSQL connection flow
Tip: If the connection fails with a driver error, install the Npgsql connector and restart Power BI Desktop before trying again.
3. Connecting to Aiven Cloud PostgreSQL
Aiven is a managed cloud database platform that hosts PostgreSQL (and other databases) on your choice of cloud provider - AWS, Google Cloud, or Azure. Connecting Power BI to an Aiven PostgreSQL instance follows the same general steps as a local connection, with two important differences: the connection details are specific to your Aiven service, and SSL must be used to encrypt the connection.
Obtaining connection details from Aiven
Log in to the Aiven Console and open your PostgreSQL service. On the service overview page, you will find all the information needed to establish a connection:
| Parameter | Where to find it | Example value |
|---|---|---|
| Host | Service Overview - Connection Information | pg-abc123.aivencloud.com |
| Port | Next to the host, typically a custom port | 15432 |
| Database | Listed under the service name | defaultdb |
| Username | Connection Information section | avnadmin |
| Password | Click the eye icon or copy button | (hidden - copy directly) |
| SSL Certificate | Download button in Connection Information | ca.pem |
Download the CA certificate (ca.pem) and save it to a location you can reference easily, such as C:\certs\aiven-ca.pem on Windows.
Why SSL certificates are required
A cloud database is accessible over the public internet. Without encryption, data transmitted between Power BI and the Aiven server - including credentials and query results - would be visible to anyone monitoring the network. SSL (Secure Sockets Layer) / TLS (Transport Layer Security) encrypts the entire connection, preventing interception.
The CA certificate serves a second purpose: it allows Power BI to verify that it is connecting to the genuine Aiven server and not an impostor. This is known as certificate verification, and it protects against man-in-the-middle attacks. Aiven requires SSL on all connections; it cannot be disabled.
Step-by-step: connecting via Power BI Desktop
Step 1 - Open Get Data - PostgreSQL Database
Follow the same steps as the local connection: Home - Get Data - PostgreSQL Database - Connect.
Step 2 - Enter the Aiven host and port
In the Server field, enter the full Aiven hostname followed by a colon and the port number:
pg-abc123.aivencloud.com:15432
In the Database field, enter the database name (often defaultdb unless you created a named database).
Step 3 - Expand Advanced Options and add the SSL certificate
In the connection dialogue, click Advanced Options to reveal additional fields. In the Additional connection string parameters box, enter the SSL certificate path in the following format:
sslmode=verify-ca;sslrootcert=C:\certs\aiven-ca.pem
This tells Power BI to use SSL, verify the server's certificate, and trust only certificates signed by the CA you downloaded.
Step 4 - Enter credentials and connect
Click OK. When prompted, select Database authentication, enter the Aiven username (typically avnadmin) and password copied from the Aiven Console. Click Connect.
Step 5 - Select tables in the Navigator
The Navigator pane will display the available tables. Select the required tables and click Load or Transform Data.
Certificate path must use the correct syntax. On Windows, use double backslashes or forward slashes in the path. If the
ca.pemfile is not found, Power BI will fail to connect with an SSL handshake error. Verify the file exists at the exact path specified.
4. Loading Tables and Creating Relationships
Once the connection is established, Power BI loads the selected tables into its internal data model. For this guide, the PostgreSQL database contains four tables organised around a retail business scenario:
| Table | Primary key | Key columns | Description |
|---|---|---|---|
customers |
customer_id |
first_name, last_name, email, registration_date, membership_status | 50 customer records with registration and membership data |
products |
product_id |
product_name, category, price, supplier, stock_quantity | 15 products across categories with pricing and supplier info |
sales |
sale_id |
customer_id (FK), product_id (FK), quantity_sold, sale_date, total_amount | 15 transaction records from 2023–2024 |
inventory |
product_id (FK) |
stock_quantity | Current stock levels for each product |
How Power BI auto-detects relationships
After loading these tables, Power BI may automatically detect relationships based on matching column names and data types. In this schema, it will likely identify that sales.customer_id references customers.customer_id, and that sales.product_id references products.product_id. The inventory table shares product_id with products.
To inspect and manage relationships, navigate to the Model view in Power BI Desktop, the icon that looks like three connected boxes in the left sidebar. Here you can see a visual map of all tables and the lines connecting them.
Creating and editing relationships manually
If Power BI does not detect relationships automatically, or if a detected relationship is incorrect, you can manage them manually. In the Model view, drag from a foreign key column in one table to the primary key in another. Power BI will draw the relationship line and ask you to confirm the cardinality and cross-filter direction.
For this schema, the three key relationships are:
-
customers.customer_id-sales.customer_id(one-to-many) -
products.product_id-sales.product_id(one-to-many) -
products.product_id-inventory.product_id(one-to-one)
What data modelling enables
With relationships in place, Power BI treats the tables as a unified data model rather than isolated datasets. When you build a chart showing total sales revenue by product category, Power BI knows to join sales to products via product_id to retrieve the category name. When filtering by customer membership status, it traverses the relationship from customers to sales automatically.
This is the core principle of star schema data modelling: a central fact table (sales) linked to dimension tables (customers, products) that describe the who, what, and when of each transaction. The inventory table functions as a supplementary dimension providing current stock context alongside product data.
Note on cross-filter direction: By default, Power BI uses single-directional filtering; filters flow from the dimension table into the fact table. In most cases, this is correct. Avoid enabling bidirectional filtering unless you have a specific requirement, as it can produce unexpected aggregation results and slow report performance.
5. Why SQL Skills Matter for Power BI Analysts
Power BI's graphical interface makes it possible to build dashboards without writing a single line of SQL. But analysts who understand SQL bring a fundamentally different level of capability to their work. SQL is not a requirement for using Power BI, it is a requirement for using it well.
"An analyst who can write SQL is not just faster, they understand the data at a structural level that shapes every design decision they make in Power BI."
Four ways SQL strengthens Power BI work
1. Precise data retrieval
Rather than loading an entire table and filtering inside Power BI, an analyst with SQL knowledge writes a query that retrieves only the rows and columns needed. This reduces memory usage, speeds up refresh times, and keeps the data model lean.
2. Filtering at the source
SQL WHERE clauses filter data before it reaches Power BI. An analyst who understands this can avoid importing years of historical records when only the past 12 months are relevant to the dashboard, a significant difference at scale.
3. Pre-aggregation and joins
Complex calculations, such as monthly revenue per customer segment, or average order value by product category, can be computed in SQL before the data is loaded. This offloads processing to the database engine, which handles large aggregations far more efficiently than Power BI's in-memory model.
4. Data preparation and quality
SQL lets analysts clean and reshape data at the source, standardising date formats, handling nulls, concatenating name fields, or pivoting rows into columns, before the data ever reaches Power Query. This keeps the Power BI model simple and the transformation logic auditable.
SQL in the context of this schema
With the assignment database used throughout this guide, a Power BI analyst who understands SQL can write queries like the one below to pre-aggregate sales data before loading it, rather than importing all 15 raw transaction rows and computing totals inside DAX:
select
p.category,
c.membership_status,
SUM(s.total_amount) as revenue,
COUNT(s.sale_id) as transactions
from sales s
inner join products p on s.product_id = p.product_id
inner join customers c on s.customer_id = c.customer_id
group by p.category, c.membership_status
order by revenue desc;
This query produces a compact summary table; joined, grouped, and sorted, ready for Power BI to visualise. An analyst who cannot write this query must load three raw tables, build the join in Power Query, and compute the aggregations with DAX. The result is the same, but the path is longer, more error-prone, and harder to debug.
Summary: SQL and Power BI as complementary layers
| Layer | Tool | Responsibility |
|---|---|---|
| Storage & retrieval | PostgreSQL | Tables, indexes, relationships, data integrity |
| Transformation | SQL | Filtering, joining, aggregating, cleaning at source |
| Modeling | Power BI Desktop | Star schema, relationships, DAX measures |
| Visualisation | Power BI Desktop / Service | Dashboards, reports, interactive charts |
SQL and Power BI are not competing tools; they are complementary layers in the same analytical pipeline. SQL handles structured retrieval and transformation at the database level. Power BI handles interactive visualisation and self-service exploration at the consumer level. Fluency in both means the analyst decides, with full awareness, where each operation belongs.
Data Engineering & BI · Technical Guide · PostgreSQL · Aiven · Power BI Desktop
Top comments (0)