Connecting Power BI to an SQL Database for Data Analysis: Local & Cloud-Based Connections

Introduction

In the modern world, organizations rely heavily on tools that can transform raw data into meaningful insights. Power BI is one of the most popular self-service business intelligence and data visualization tools developed by Microsoft. It allows users to connect to multiple data sources, analyze datasets, and create interactive dashboards and reports that support better decision-making.

Power BI is widely used by businesses, analysts, and data professionals to monitor performance, identify trends, and gain insights from large volumes of collected data. Through visualizations such as charts, tables, and dashboards, Power BI makes it easier for stakeholders to understand complex data.

Many organizations store their operational and analytical data in SQL databases. SQL databases are structured systems designed to store, manage, and retrieve data efficiently. They allow organizations to maintain large datasets while ensuring data integrity and consistency. By connecting Power BI to a SQL database, analysts can access real-time or frequently updated information directly from the source, making it possible to build reports and dashboards based on accurate data.

In this article, we’ll see how Power BI connects to SQL databases, including both local PostgreSQL databases and cloud-hosted databases such as Aiven PostgreSQL, and how these connections enable effective data analysis.

Let’s get to it!

Connecting Power BI to a Local PostgreSQL Database

Power BI Desktop allows users to connect to many different types of databases. One of the commonly used open-source relational databases is PostgreSQL. When connecting to a local PostgreSQL database, Power BI retrieves tables directly from the database and imports them into the Power BI environment for analysis.

Step 1: Open Power BI Desktop

Launch Power BI Desktop on your computer. After the application opens, the main interface will appear with options to create reports or connect to data sources.

Click Blank Report to navigate to the home page.

Step 2: Select "Get Data"

On the Home ribbon at the top of Power BI Desktop, click Get Data. This option allows you to connect Power BI to various data sources such as Excel, SQL Server, PostgreSQL, web services, and more.

Step 3: Choose PostgreSQL Database

After clicking Get Data, a window listing available data sources will appear. From this list, select PostgreSQL Database, then click Connect.

Step 4: Enter the Server and Database Details

Next, Power BI will prompt you to enter connection details. For a local PostgreSQL database, the server name is usually:

localhost

You will also enter the database name that contains the tables you want to analyze.

For example, let’s do a mock connection using PgAdmin 4 and Power BI.

i. Open PgAdmin 4

ii. Access your server

To access your server, PgAdmin 4 will request your password. Use the password you created when you first installed and set up PgAdmin 4.

Once entered, you will have access to your server (for example, PostgreSQL 18).

iii. Locate your server connection details

Next, we need to find the connection details for this server.

Place your cursor on the server, right-click, then select Properties.

A window will pop up. Click on Connection.

Here, you’ll find information such as the Host name/address, which is needed to connect Power BI to your PostgreSQL server.

For this Power BI connection, we will use the following information:

Username: localhost
Database: company (use the name of your database)

Click OK.

NOTE: Ensure that the details match in both Power BI and PgAdmin 4.

Step 5: Provide Login Credentials

Power BI will request authentication details to access the database. Enter the following credentials:

• Username
• Password

From PgAdmin 4, copy your username and paste it into Power BI.

For the password, use the exact same password you used when setting up your PgAdmin user account.

Example:

• Username: postgres
• Password: xxxxxxxx

Then click Connect.

These credentials must match the PostgreSQL database user account.

Step 6: Select and Load Tables

After successful authentication, Power BI displays a Navigator window listing the available tables in the database.

For example:

• assignment.customers
• assignment.products
• assignment.sales
• assignment.inventory

You can preview each table before importing it. Select the tables you want, then click Load to import them into Power BI.

Once the data is loaded, you can begin analyzing and visualizing it in Power BI.

Connecting Power BI to a Cloud Database (Aiven PostgreSQL)

Most modern systems host databases in the cloud rather than on local machines. Cloud database providers such as Aiven allow organizations to run managed PostgreSQL databases that are accessible from anywhere.

To connect Power BI to an Aiven PostgreSQL database, you need specific connection details.

Step 1: Obtain Connection Details from Aiven

In your browser, search for Aiven, then select the official website.

Log in to your Aiven dashboard, then select Create Service.

Do this if you do not already have a service created.

After clicking Create Service, a pop-up will appear displaying several service types you can connect to. For this example, we will use the PostgreSQL service.

Select the free service plan, then create the service.

The dashboard will provide the following information:

• Host name
• Port number
• Database name
• Username
• Password

These details are required to establish the connection from Power BI.

Step 2: Download the SSL Certificate

Cloud databases often require SSL encryption to secure connections between applications and the database server.

From the Aiven dashboard, download the SSL certificate file provided for your PostgreSQL service.

Step 3: Using the PostgreSQL ODBC Driver

i. Download and Install PostgreSQL ODBC Driver

The PostgreSQL ODBC driver is a software component that implements the Open Database Connectivity (ODBC) standard. It allows ODBC-compliant applications such as Microsoft Excel, Tableau, and Power BI to connect and interact with a PostgreSQL database using standard SQL without needing to understand the underlying database system.

You can download it from the psqlODBC website.

ii. Configure ODBC with Aiven

On your Windows computer, press:

Win + R

to open the Run dialog box.

Then type:

odbcad32

and click OK.

Navigate to System DSN, then click Add.

A window will appear. Scroll down and select PostgreSQL Unicode(x64), then click Finish.

Another window pop-up will appear requesting connection details.

Enter the following:

• Description - Any name you prefer (e.g., Local Postgres)

Copy the following information from Aiven:

• Database name - defaultdb
• Server - Host from Aiven pg-96fb47b-kelvinwarui457-01c1.f.aivencloud.com
• Port - 16552
• User Name - avnadmin
• Password - xxxxxxxxxxxx

NOTE: Ensure that the SSL Mode is set to Require.

Test the connection. If you receive a pop-up saying Connection Successful, click OK, then Save, and OK again.

Now navigate back to Power BI.

iii. Connect ODBC to Power BI to Access Your PostgreSQL Server in Aiven

Open Power BI and navigate to Home, then click Get Data.

Select ODBC, then click Connect.

A pop-up will appear.

Click the dropdown arrow and select PostgreSQL35W, then click OK.

Secure Connection Using SSL

When connecting Power BI to a cloud-hosted PostgreSQL database such as Aiven, SSL encryption is required to secure the connection.

The SSL certificate provided by Aiven helps verify the identity of the database server and ensures that communication between Power BI and the database is encrypted.

In Power BI Desktop, the PostgreSQL driver automatically establishes an SSL-secured connection when connecting to a server that requires SSL. Users only need to provide the server address, database name, and authentication credentials, while the SSL encryption is handled by the underlying database driver.

SSL encryption ensures that:

• Data transferred between Power BI and the database is encrypted.
• The database server’s identity is verified.
• Sensitive information cannot be intercepted during transmission.

Loading Tables into Power BI

Once the connection is established, Power BI displays the database tables in the Navigator window.

In this example, the dataset contains four main tables:

• Customers - stores customer information
• Products - contains product details
• Sales - records sales transactions
• Inventory - tracks product stock levels

Select these tables and click Load to import them into Power BI.

After loading, the tables appear in the Fields pane, where they can be used to create visualizations and reports.

NOTE: Always ensure that your service is running in Aiven for a successful connection.

Creating Relationships Between Tables

After importing the tables, the next step is data modeling. Data modeling defines how different tables relate to each other so that Power BI can correctly analyze the data.

For example:

Customers --> Sales
Connected through customer_id

Products --> Sales
Connected through product_id

Products --> Inventory
Connected through product_id

These relationships are created in the Model View in Power BI.

By defining these relationships, Power BI understands how data from multiple tables should interact. For instance, when analyzing sales by product or by customer, Power BI uses these relationships to combine data from different tables correctly.

Proper data modeling improves:

• Accuracy of reports
• Performance of queries
• Consistency of analysis

Without correct relationships, reports may display incorrect totals or duplicate values.

Why SQL Skills Are Important for Power BI Analysts

Although Power BI provides powerful visual tools for analysis, SQL skills remain essential for data analysts. SQL (Structured Query Language) allows analysts to interact directly with databases and retrieve the exact data they need.

SQL helps analysts:

1. Retrieve Data - Analysts can write queries to select specific columns or rows from large datasets.
2. Filter Data - SQL allows filtering using conditions such as dates, categories, or customer segments.
3. Perform Aggregations - Functions like SUM, COUNT, AVG, and GROUP BY allow analysts to summarize large datasets efficiently.
4. Prepare Data for Analysis - SQL can clean, transform, and structure data before it is imported into Power BI, making reporting faster and more efficient.

By combining SQL querying skills with Power BI visualization capabilities, analysts can build more accurate dashboards and extract deeper insights from data.

Conclusion

Power BI is a powerful business intelligence tool that enables organizations to analyze data and create meaningful visual reports. By connecting Power BI to SQL databases such as PostgreSQL, analysts can directly access structured data stored in relational databases.

This article explained how to connect Power BI to both a local PostgreSQL database and a cloud-hosted Aiven PostgreSQL database. It also described how to load tables, create relationships between them, and understand the basics of data modeling.

Finally, the article highlighted the importance of SQL skills for Power BI analysts, as SQL enables efficient data retrieval, filtering, aggregation, and preparation. When SQL and Power BI are used together, analysts can build powerful dashboards that support informed business decisions.