Connecting Power BI to Local and Cloud-based PostgreSQL

#postgres #beginners

Introduction

Power Bi is a data visualization tool that transforms raw data into meaningful and interactive insights, whereas PostgreSQL is a database management system that uses the SQL(structured query language) to store and interact with data from the databases.

Local PostgreSQL means that the database is hosted locally on my computer while a cloud-based stores the databases on cloud data platforms.

We therefore connect Power BI to PostgreSQL so that data from the databases can be analyzed, visualized and turned into meaningful reports and dashboards.
Instead of exporting data every day, Power BI retrieves the data directly from the database, saving time and reducing errors.

Connection Process

For a local host, download and install PostgreSQL.

For the cloud based;

create an account in Aiven - which is a Database-as-a-Service platform
download and install DBeaver - a database management tool

From Aiven you'll get the required connection information as below;

After successful connection, DBeaver is as below;

Data Importation

To import data to DBeaver, on the left side expand on databases > defaultdb > schemas > either create a new schema or use the public schema

Right click on your preffered schema and click import data. Brings you to the window below

Go to -> Input file(s), Browse and select your data

Click Proceed at the bottom, till its done. After importing is done you should see

Voila, you just imported a file to aiven database.

Connecting Power BI to the Database

Now we have the file in a database. In order to work on the data and visualize it, we connect our visualization tool to the database.

Open Power BI Desktop on your computer
Click Get Data in the Home ribbon and select More

In the dialog box, search for PostgreSQL database under the Database category and click Connect

Input server and database details gotten from the connection information on Aiven.
Server -> Host:Port
Database -> Database name

After a successful connection, check the data under navigator.
You'll get a preview and you can choose either to load or transform the data on Power BI.

To connect to the local postgreSQL, the only difference is the connection information used.
Server -> localhost or the IP address
Database -> Database name