Connecting Power BI to a SQL Database

Introduction: Power BI and the Role of SQL Databases

Power BI is used across industries retail companies use it to track product performance, healthcare organizations use it to monitor patient outcomes, financial firms use it for risk reporting, and logistics companies use it to optimize supply chains. It is one of the most widely deployed analytics tools in the world, and proficiency with it is one of the most sought after skills for data analysts and business intelligence professionals.

Connecting Power BI to a Local PostgreSQL Database

Before working with cloud databases, it is valuable to understand how to connect Power BI to a local PostgreSQL instance running on your own machine.
The process begins with opening Power BI Desktop. On the Home ribbon, you will see the Get Data button this is the entry point for connecting to any data source.

Step 1: Open Power BI Desktop and Click Get Data

When you launch Power BI Desktop, you are presented with the welcome screen. To begin connecting to your database, click the Get Data button in the Home ribbon at the op of the screen.

Step 2: Select PostgreSQL Database from the Get Data Dialog

After clicking Get Data, a dialog window appears listing all available data connectors. Power BI supports over 100 data sources from Excel files and CSVs to enterprise databases and cloud APIs. To connect to PostgreSQL, click on the Database category in the left panel, then scroll through the list and select PostgreSQL Database. Click Connect to proceed.

.

Step 3: Enter the Server Name and Database Details

Once you select PostgreSQL Database and click Connect, a connection dialog appears. This dialog requires two key pieces of information: the server name and the database name

Install the PostgreSQL ODBC Driver (Required for Both)

1. Go to: https://www.postgresql.org/ftp/odbc/versions/msi/
2. Download psqlODBC — get the x64 (64-bit) version
3. Install it like a normal Windows program Without this driver, Power BI won't even show PostgreSQL as a data source option.

Connecting to Localhost PostgreSQL

The database is connected and runs locally on my machine, the server is usually localhost.The database name will be postgres and you will see when the connection starts.The graphical tool, pg4Admin 4 is used in managing and development of the PostgreSQL databases.

The server name host will be – localhost
username – postgres

Connecting to Aiven PostgreSQL

While a local database is ideal for development, real world analytics almost always involves cloud databases. Cloud hosted databases offer critical advantages: they are accessible from anywhere, they scale automatically as data grows, they include built in backup and recovery, and they support multiple simultaneous connections from different tools and team members.

Connecting Power BI to Aiven PostgreSQL follows a similar process to a local connection, with one important addition: SSL certificate authentication.

Step 1: Get Your Aiven Connection Details
Log in to console.aiven.io, open your PostgreSQL service, and note:
• Host — pg-3ae2ac09-susanjeri4717-1f9e.c.aivencloud.com
• Port — 25837
• Database — defaultdb
• Username — avnadmin
• Password — from the Aiven console
• SSL Certificate — download the ca.pem file from the service page

Step 2: In Power BI Desktop

1. Home → Get Data → More… → PostgreSQL → Connect
2. Fill in the server field as:
3. Enter your Database name
4. Click OK
5. Choose Database authentication → enter Aiven username & password

Step 3: Handle SSL
Power BI's PostgreSQL connector supports SSL but may not ask you for the certificate directly in the GUI. If the connection fails with an SSL error, you have two options: using an ODBC DSN connection or Setting SSL mode via connection. We will use option A which is;

Use an ODBC DSN connection :

1. Open ODBC Data Source Administrator

1. Add a new System DSN → choose PostgreSQL Unicode

1. Fill in host, port, database, credentials — and point it to your ca.pem file under SSL settings

1. Back in Power BI → Get Data → ODBC → select your DSN

You will also see the Data Connectivity mode options: Import and DirectQuery. Import copies the data into Power BI's in memory storage, while DirectQuery sends live queries to the database every time a visual refreshes. For learning and development, Import mode is the best choice.

Step 4: Enter Your Database Credentials

After clicking OK, Power BI will prompt you to enter your credentials. Select the Database authentication tab, then enter your PostgreSQL username, commonly postgres, and your password. Power BI will store these credentials securely so you do not need to re enter them each time you connect.

Step 5: Select Tables in the Navigator

After successful authentication, the Navigator window opens. This window displays a tree structure of your database, showing all available schemas and tables. Expand the assignment schema to see the four tables from your database: customers, products, sales, and inventory.

Check the box next to each table you want to load into Power BI. The right panel shows a preview of the data in the selected table, allowing you to verify that you are connecting to the correct data before loading it. Once you have selected all four tables, click Load to import the data into Power BI.

After clicking Load, Power BI imports all selected tables into its in memory data model. You will see the data loading progress in the status bar at the bottom of the screen. Once complete, the tables are available in the Fields panel on the right side of the report canvas.

Loading Tables and Creating Relationships in Power BI

Once the four tables from the assignment schema are loaded customers, products, sales, and inventory they appear in Power BI's Fields panel.

Understanding the Four Tables

Before creating relationships, it helps to understand what each table contains and how they are logically connected:
• customers; Contains one row per customer, with details like customer id, first name, last name, email, and membership status. The customer id is the unique identifier (primary key).
• products; Contains one row per product, with product id, product name, category, price, supplier, and stock quantity. The product id is the primary key.
• sales; The central transactional table. Each row is a sale, with sale id as the primary key. Crucially, it also contains customer id and product id as foreign keys these are the columns that link each sale to a specific customer and product.
• inventory; A supporting table that tracks stock levels per product, linked to the products table via product id.

Creating Relationships in the Model View

To define relationships, switch to the Model View by clicking the relationship icon in the left sidebar . Power BI may automatically detect some relationships based on matching column names.

Conclusion

Power BI has become one of the most essential tools in a data analyst's toolkit, transforming raw database records into interactive dashboards and visual reports that drive business decisions. Its ability to connect directly to local databases like PostgreSQL is what makes it particularly powerful for analysts rather than exporting data manually to spreadsheets and risking outdated figures, a live local connection means your reports are always pulling from the actual source of truth.
For developers and students especially, working against a localhost database allows you to build, test, and refine reports in a fast, controlled environment without incurring cloud costs or depending on internet connectivity. This direct line between your database and your visualisation layer is what bridges the gap between raw SQL data and the polished, decision-ready insights that stakeholders actually consume.