Connecting Power BI to a SQL Database

Introduction

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

Power BI can connect to many data sources, including spreadsheets, cloud services, APIs and relational databases. In many organizations, data is stored in structured databases which Power BI accesses to analyze and visualize for insights.

One commonly used database for analytics is PostgreSQL, an SQL-based database system designed to efficiently store, manage and retrieve large volumes of structured data. Connecting Power BI to a PostgreSQL database allows analysts to access large datasets, run SQL queries, combine multiple tables and build real-time or scheduled dashboards for better business insights.

By the end of this article, we will have a step by step guide on how to connect to a local database and also to a cloud-based database.

Connecting Power BI to a Local PostgreSQL Database

This is the most common starting point for any data analyst where you are able to connect Power BI to a local PostgreSQL DB running locally on your machine.

Step 1: Open Power BI Desktop
Once you open, click Blank report. This navigates you to the main page (see below) of the application.

On the top ribbon, click Get data. This option allows Power BI to connect and get data from different data sources.

Step 2: Choose PostgreSQL database
From the list of the sources, look for PostgreSQL database and then click Connect.

Step 3: Enter Database connection details
After selecting PostgreSQL database and clicking Connect, a connection window will appear where you must enter the local database details.

For a database that runs locally on your machine, the server is usually localhost. For the Database name, we will navigate to my local database and trace the connection details from there.

Since I have pg4Admin 4, a graphical tool for managing and developing my PostgreSQL databases, I will open the tool and trace the details from my server properties.

For this connection, let's use the below connection details:

• Server hostname localhost
• Username postgres

Input the above details and click OK.

Step 4: Enter Authentication credentials
Power BI will then prompt you to enter your database credentials. Authentication details typically include: Username & Password. For this, I will copy the username from pg4Admin 4 and for the password I will use the same password I used when setting up my pg4Admin during installation then click Connect.

Step 5: Load tables into Power BI
After connecting successfully, Power BI opens the Navigator window, which lists all available tables in the database. Here you are able to preview each available table and select the tables you want to load then click Load.

Alternatively, you can select Transform Data to clean or modify the data in the Power Query Editor before loading it. Once the tables are loaded, begin analyzing and visualizing your data for insights.

Connecting Power BI to a Cloud PostgreSQL DB

Many companies host their databases in the cloud instead of locally. One example is Aiven which provides managed PostgreSQL databases. Connecting Power BI to a cloud PostgreSQL database follows a similar process but requires additional connection details.

Step 1: Create a PostgreSQL DB on Aiven
Creating a PostgreSQL database on Aiven is a straightforward process that allows you to host and manage a cloud database without worrying about infrastructure setup. First, create an account and log in to the Aiven console. Once logged in, click Create Service on the dashboard. From the list of available services, select PostgreSQL as the database engine.

Next, configure the service by choosing a cloud provider, selecting a region where the database will be hosted and picking a pricing plan that fits your needs. After entering a name for the service, click Create Service. Aiven will then automatically deploy the PostgreSQL database in the cloud.

Step 2: Download SSL certificate from Aiven
Cloud databases often require secure encrypted connections. From the Aiven console, navigate to Connection Information and download the CA Certificate (SSL certificate).

Step 3: Connecting to Aiven Using PostgreSQL ODBC
To connect applications such as Microsoft Power BI, Excel or other analytics tools to a cloud database hosted on Aiven, you can use the ODBC (Open Database Connectivity) driver for PostgreSQL. ODBC provides a standardized way for software applications to communicate with database systems.

• First, download the version that matches your system (32-bit or 64-bit) and install the PostgreSQL ODBC driver, commonly known as psqlODBC.

Download the driver from https://www.postgresql.org/ftp/odbc/releases/REL-17_00_0008-mimalloc/

• Open ODBC Data Source Administrator, navigate to System DSN then click Add. Another Create New Data Source will appear and give you an option to select a driver for which you want to set up a data source. Choose PostgreSQL Unicode(x64) and click Finish.

• Another psqlOBDC set up window will appear requesting for connection details. All these connection details can be traced from a running service on Aiven. Copy and paste on this set up window and test connection.

Note: Ensure the SSL Mode is set to require.

Step 4: Connect Power BI Using the ODBC Source
Once the ODBC data source is configured, navigate to Power BI Desktop. On the home page, click Get data, select ODBC and Connect. Another window will appear where you will be required to choose your DSN (Data Source Name). Choose PostgreSQL ODBC and click OK.

You will then be prompted to input the Username and Password available from Aiven then click Connect. Power BI will then retrieve the tables from the PostgreSQL database hosted on Aiven. Select the tables you need and click Load.

It is important to note that, SSL certificates are required because they encrypt the connection between Power BI and the database. When we attempt to get data from the cloud-based database hosted on Aiven, the PostgreSQL ODBC driver automatically establishes a secure and an encrypted connection using the SSL certificate we downloaded locally.

Creating relationships between tables

Power BI automatically detects relationships between tables based on matching columns based on the primary & foreign keys.

See below relationships we are working with from the data we just loaded on Power BI.

Table	Related Table	Relationship
customers	sales	customer_id
sales	products	product_id
products	inventory	product_id
sales	inventory	product_id

From these relationships, Power BI knows how tables relate to each other so that calculations and visualizations are accurate.

Why SQL skills are important for Power BI analysts

Although Power BI provides powerful visual tools, SQL remains an essential skill for data analysts. SQL helps analysts:

• Write queries that assist to retrieve data efficiently
• To filter data before importing it into Power BI which reduces the dataset size and improves performance.
• Perform aggregate operations that assist to prepare analytical datasets before visualization
• To join multiple tables, clean the dataset and build aggregated tables in preparation for the creation of dashboards in Power BI.

Conclusion

Power BI is a powerful business intelligence platform that enables organizations to transform raw data into interactive dashboards and insights. By connecting Power BI to local or cloud-based SQL databases such as PostgreSQL, data analysts can work with large datasets directly from the source rather than relying on manual data exports.

Feel free to leave comments!