A Step-by-Step Guide: Connecting Power BI to SQL Databases

Introduction

Many companies and organizations interact with large amount of data from collection to creating insights from it. Transforming these data will bring meaningful insights and there making informed decions for the end users. Power BI is a powerful tool that enables one to create interactive dashboards and reports that communicates what the data is all about. Connecting Power Bi to SQL databases now allows onre to analyze data in real time or through a scheduled time.
In this article demonstrates a step-by-step guide on how to connect Power BI to SQL databases both on local network and cloud-based, and start transforming your data into meaning visualizations.

Part 1: Connecting to a local PostgreSQL Database

Ensure that your PostgreSQL is downloaded and installed in your local machine. Launch Power BI desktop and select get data from the home tab.

1. Choose SQL Server From get data,select more,search for database then PostgreSQL database and connect which will require sever connection details.
2. Connection Details Enter the servername and database name. In this case I will user postgres as username and the server is *localhost:5432 *
3. Choose import data querry mode and enter your login credentials.
4. The window will then lists tables and you will select the one to work on with.For data cleaning click Transform data and the load for creating creating new measure and data models.

The navigator window will now show the tables available in the local PostgreSQL database.

Part 2: Connecting to a Cloud Based PostgreSQL on Aiven

1. Log in to your Aiven account, create a new service or the one existing and ensure it is running. The dashboard has Host Port Database name (usually defaultdb) Username (usually avnadmin) Password, for connection details

1. Download CA certificate, navigate to manage certificate manage,Trusted Root Certificate, right click certificates and import the certificate from your downloads.

1. Open Power BI Desktop, navigate to home tab, Get Data, More, select PostgreSQL, enter login credentials

After selecting what you want to transform like the local connection, performing data cleaning and load for modelling and all DAX irregardless of where the data is in whether local database or cloud.

Conclusion

Connecting Power BI to SQL databases whether hosted on local or cloud enables organizations to transform raw data into meaningful and interactive insights.
The steps of connecting Power BI to SQL databases are similar in that the cloud connection require SSL certificate for security access to be grated , otherwise every operation works the same.