Connecting Power BI to a SQL Database: A Practical Guide

Introduction

Microsoft Power BI is a powerful business intelligence and data visualization tool used by organizations to transform raw data into meaningful insights. It allows users to create interactive dashboards, reports, and visualizations that support data-driven decision-making.

Companies connect Power BI to databases because most business data is stored in structured systems such as SQL databases. By linking Power BI directly to these data sources, analysts can:

• Access real-time or near real-time data
• Build automated dashboards
• Eliminate manual data handling
• Ensure consistency and accuracy in reporting

SQL databases such as PostgreSQL, MySQL, and SQL Server, play a critical role in storing and managing analytical data. They are used for querying, filtering, and aggregating large datasets, making them ideal for business intelligence workflows.

Connecting Power BI to a Local PostgreSQL Database

Below is a step-by-step guide to connecting Power BI to a local PostgreSQL database.

Step 1: Launch Power Bi Desktop on your laptop.

Step 2: Click "Get Data"

• On the Home ribbon, click Get Data
• Select Database
• Choose PostgreSQL database

Step 3: Enter Connection Details

In the dialog box:

• Server: localhost (or your local server ip)
• Database: your database name (e.g. sales_db)

Step 4: Provide Credentials

Choose Database authentication
Enter:

• Username
• Password Click Connect. If your credentials are correct and the database is running, Power BI will establish the connection.

Step 5: Load Data

Power BI will display a Navigator window showing a list of all schemas and tables available in your
database. Expand the assignment schema and place a checkmark next to each of the four
tables:

• assignment.customers
• assignment.products
• assignment.sales
• assignment.inventory You can preview the contents of each table by clicking on it. Once all four tables are selected, click Load to import the data into Power BI, or click Transform Data if you want to apply any data cleaning steps before loading.

Connecting Power BI to a Cloud Database (Aiven PostgreSQL)

Many organizations host their databases in the cloud rather than on local machines. Aiven is a cloud database provider that allows easy deployment and maintenance of PostgreSQL in the cloud without managing server infrastructure.

Connecting Power BI to an Aiven PostgreSQL database follows a similar process to the local connection, but with a few differences in connection details and SSL security.

Step 1: Get Connection Details from Aiven

From your Aiven dashboard, retrieve:

• Host
• Port
• Database name
• Username
• Password

Step 2: Download SSL Certificate

Aiven requires SSL for secure connections.

• Download the CA certificate (usually .pem file)
• Save it locally on your machine
• Double-click the file.
• Click Install Certificate and choose "Local Machine"
• Select: 'Place all certificates in the following store'
• Choose: 'Trusted Root Certification Authorities'
• Finish the installation.

Why SSL Matters

SSL encrypts the connection between your machine and the cloud database server. Aiven enforces SSL on all connections, which means Power BI must be able to verify the database server's identity using a certificate. If this verification fails, Power BI will refuse to
connect and display a certificate validation error.

Step 3: Connect from Power BI

In Power BI, Click 'Get Data', then 'PostgreSQL
Enter:

• Server: host:port (e.g., your-db.aivencloud.com:12345)
• Database name

Step 4: Loading Tables and Creating Relationships

Once Power BI has connected to your database and loaded the four tables, the next step is to
ensure that the relationships between those tables are correctly defined. This process is called Data Modeling.

Creating Relationships in Power BI

To set up relationships, switch to the Model view in Power BI by clicking the diagram icon on the
left sidebar. Power BI may automatically detect some relationships based on matching column
names.

The sales table sits at the center of this model. It references both customers (through
customer_id) and products (through product_id), and the inventory table extends product
information with stock data. This structure is known as a star schema, where a central fact table
(sales) is surrounded by dimension tables (customers, products, inventory).

To create a relationship manually, simply drag the column name from one table and drop it onto
the corresponding column in the other table. A line will appear connecting them, with an arrow
indicating the filter direction.

Why Relationships matter

Without correctly defined relationships, Power BI cannot associate data across tables. Creating relationships defines how tables relate to each other.

Why SQL Skills Matter for Power BI Analysts

1. Data Retrieval. SQL allows analysts to extract only the data they need.
2. Filtering Data: SQL allows you to reduce dataset size before loading into Power BI.
3. Aggregations: SQL helps compute summaries
4. Data Preparation: Analysts often clean and transform data using SQL before visualization.
5. Performance Optimization: Using SQL reduces:
6. Data load time
7. Memory usage in Power BI
8. Complexity of transformations inside Power BI

Conclusion

Connecting Power BI to SQL databases, whether local PostgreSQL or cloud platforms like Aiven, enables powerful, real-time analytics. By properly configuring connections, securing them with SSL, and building well-structured data models, analysts can unlock meaningful insights from raw data.

SQL plays a crucial role in this process, allowing analysts to efficiently retrieve, filter, and prepare data before visualization. Both Power BI and SQL form a powerful combination for modern data analysis and business intelligence.