Connecting SQL Databases to Power BI

Introduction

In any organization, Data is one of the most important assets. With Power BI we are able to turn the raw information into useful and actionable insights. By connecting SQL databases to Power BI, businesses can seamlessly access, analyze, and visualize data stored in their relational databases without the need for manual exports or complex reporting processes. Thus this integration process is an important skill for a data analyst to have. In this article, we'll explore how to connect SQL databases to Power BI and the different connection options available.

a few prerequisites

• PostgreSQL installed and running
• An Aiven account with a running PostgreSQL stream.

• DBeaver - An open source, data management tool
• PowerBI Desktop

Local Database

This is a storage system that exists on your device or a local network as opposed to a remote cloud server

Step One

• Open DBeaver, Click on Database and establish a new database connection (PostgreSQL).

• Fill in the required information.

• Ensure you test the connection before finishing. Any errors can be detected and fixed at this point.
• Inside the new database you can create a new schema where the imported data will go.
• Select Input file(s) then Browse. Find the relevant data on your device and then click Proceed.

Step Two

• Launch Power BI
• Go to Home, then Get Data > More... > Database and select PostgreSQL database

• You will be prompted to fill in the Server and database details.
• Enter your Username and password when prompted and connect.

• Preview your data and load it into Power BI.

Cloud-Based SQL Database

Step One

• In this case you will repeat all the steps as you did with the local database but the details required when setting up a new connection will be sourced from Aiven.

• Follow the same steps from the Local database section to create and import the data.

Step Two

• Download the CA certificate provided in the Aiven account.
• Go to your search bar and find the manage user certificates.
• Follow these steps; Trusted Root Certification > Certificates (right click) > Import

• Browse your files and find the certificate.
• Press next on the wizard prompts till the end and Finish

Step Three

• Launch Power BI
• Go to Home, then Get Data > More... > Database and select PostgreSQL database

• Enter the connection details from Aiven

• Enter your Username and Password.
• Select the table you want and proceed with cleaning or analysis.

Conclusion

And its that simple! As data continues to grow in importance, leveraging SQL databases and Power BI together provides a powerful foundation for effective analytics and reduces instances of error that is common when manually handling data.