Table of Contents

1. Introduction: Power BI and SQL Databases

Why connect Power BI to a database?

The role of SQL databases in analytical workflows

2. Connecting to a Local PostgreSQL Database

Prerequisites

Step-by-step connection process

3. Connecting to Aiven Cloud PostgreSQL

Obtaining connection details from Aiven

Why SSL certificates are required

Step-by-step: connecting via Power BI Desktop

4. Loading Tables and Creating Relationships

How Power BI auto-detects relationships

Creating and editing relationships manually

What data modelling enables

5. Why SQL Skills Matter for Power BI Analysts

Four ways SQL strengthens Power BI work

SQL in the context of this schema

Summary: SQL and Power BI as complementary layers A practical guide to integrating Power BI Desktop with local PostgreSQL and cloud-hosted Aiven databases - including data modelling and why SQL still matters. 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. 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. 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. 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, and inventory and 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. Before beginning, confirm the following are in place: 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: 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. 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. 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. 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: 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. 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 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: 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: 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.pem file is not found, Power BI will fail to connect with an SSL handshake error. Verify the file exists at the exact path specified. 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: 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. 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: 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. 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." 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. 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: 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. 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 Templates let you quickly answer FAQs or store snippets for re-use. Hide child comments as well For further actions, you may consider blocking this person and/or reporting abuse