Introduction
Power BI is a business intelligence and data visualization tool developed by Microsoft that allows user to connect to multiple data sources, transform and model data, and create reports and dashboards.
Power BI is used to perform data analysis and provide business intelligence by allowing analysts to import data from various sources such as databases, clean and transform the data, create relationships using the data and perform calculations using DAX(data analysis expressions).
Why do organizations connect Power BI to databases?
Organizations connect Power BI directly to databases because of reasons such as:
- Databases store large volumes of data efficiently and connecting Power BI to these databases allows analysts to work with enterprise scale data.
- Connecting Power BI to a database provides data consistency ensuring that everyone in the organization works with the same data.
- Databases offer real-time or near real-time reporting as databases receive new data enabling analysis to also be update or real-time.
Why is it important to have SQL databases for storing and managing analytical data?
SQL databases store structured data in tables with rows and columns and allow efficient querying using Power BI which also uses SQL queries to perform actions such as filtering, aggregation and joins.
In this article, we will be covering the process of connection Power BI to a local PostgreSQL database (a database located inside your local pc) and also connecting it to a cloud database (a database in a remote server) such as Aiven PostgreSQL.
Connecting Power BI to a Local PostgreSQL database
Step 1: Open Power BI Desktop
Once you have opened Power BI desktop, click on black report which will take you to the main page
On the main page, click on the Get Data icon. A popup window titled Get Data will appear, from here on the search bar type 'postgreSQL' to search for PostgreSQL database. Choose PostgreSQL database and click the connect button.
Step 2: Enter Database connection Details
Another popup titled PostgreSQL database will appear prompting you to enter server, database and Data connectivity mode
To locate my local PostgreSQL details, I will use a tool called DBeaver that visualizes my local database to get my database details.
From this, I am able to get my server and database names
Fill the details, leave the database connectivity mode as is then click ok.
Step 3: Authenticate to your Database
Here you enter your database name and a password that you created during the database PostgreSQL installation.
Under 'select which level to apply these settings to', select 'localhost;postgres' then click connect.
Step 4: Load Tables to Power BI
Power BI authenticates to the database using the credentials you provided it with and upon a successful connection, a popup appears showing the existing tables in your database.
To load this tables to Power BI, mark the checkbox to the left of each of the tables and then click on the load button. Alternatively, you can select Transform Data to clean or modify the data in the Power Query Editor before loading it.
Once the tables finish loading, navigate to the table view section on the left of your screen, to be able to see the tables you have loaded.
To toggle through the different tables, you can use the pane on the right of your screen and click on the table you want to view
Now your data is successfully loaded to Power BI and you can begin performing analysis on it.
Connecting Power BI to a cloud PostgreSQL Database
Not all everyone hosts their databases locally, some prefer to have their database hosted on the cloud. In such an instance, Power BI is also able to connect to those databases.
I will be using a cloud provider known as Aiven, to illustrate this connection.
Step 1: Open Power BI
Relaunch Power BI and navigate to get data, choose PostgreSQL and click connect.
Step 2: Access Aiven
Login to your Aiven platform and navigate to your database instance.
Click on the database to access your connection details which include: The host, port, database and username
Step 3: Download the SSL CA certificate
From the details list provided, click the download icon next to the CA certificate to save it to a known location locally on your computer.
Why do we need the SSL certificate?
The SSL certificate will be used to establish secure communication between your cloud database and your Power BI application. It also encrypts the data being transferred between your database and your Power BI application such that, in an event where your data is intercepted by a third party, they would not be able to see the contents of your data
Step 4: Enter Database Details
After locating your database details from Aiven, fill them into the PostgreSQL database popup window.
Fill in your server name (Host) and then your database name leave the 'Data connectivity mode' as is.
When entering the server name of a cloud server, we enter the name and the port separated by a colon
your-service-project.aivencloud.com:port
After entering your details click 'OK' to be prompted to enter your username and password for authentication
Enter your details and password then click connect
Step 5: Error
When you click connect, you will get an error. This error occurs because Power BI cannot locate the CA certificate we downloaded. We have to add to a location where it can be accessed by Power BI.
To resolve the error, click windows plus R keys on your keyboard and type the following:
certmgr.msc
Press enter and this will take you to certmgr (manages certificates on your pc)
Select 'Trusted Root Certification Authorities' and on the left side single click on 'Certificates' to chose select it.
Right click on it and chose 'All task' then 'Import'
A popup window will appear and you will be asked to choose the file you want to import. Click on browse to navigate to where you stored the ca.pem file we downloaded. If you navigate to the location you stored it and it's not appearing, under file type, change to all file and it will appear. Select the file by double clicking on it
The file path will appear on the popup and then click on next to continue.
After the certificate has been verified, click finish and to import the certificate.
Then restart Power BI for the changes to take effect.
Now after restarting Power Bi and following the process we used, now our connection will be successful and we will see the tables in our databases ready for loading.
Select the tables you want to load and click the load button.
Creating Relationships between tables
What are Relationships?
Relationships between tables define how data stored in different tables is connected. The relationships are typically created using keys such as primary keys and foreign keys. For example, in our assignment customers table (_see on image below) we have the customer information, while an assignment sales table we have records of transactions. The assignment sales table can include a foreign key referencing the customer ID from the assignment customers table, linking each order to a specific customer._ This structure allows databases to avoid duplication of data while maintaining logical connections between datasets, making it easier for analytics tools such as Microsoft Power BI to combine and analyze data from multiple tables.
Power BI automatically Detects relationships between tables based on matching columns and establishes the primary and foreign keys.
To toggle the relationships view, click on the shown icon.
From our image, we can see that Power BI has already detected the primary and secondary keys in our tables and established relationships off those.
We can edit the relationships by double clicking on the line joining the tables.
After editing your the relationship between the tables, click on save to save the new relationship.
Why are SQL skills important for a Power BI analyst?
SQL skills are essential for Power BI analysts because most organizational data is stored in relational databases that use SQL. Although Power BI provides graphical tools for building reports, analysts often need SQL to directly access and manipulate data in the source database. This SQL skills enable analysts to explore database structures, understand relationships between tables and write efficient queries that retrieve only the data required for analysis. This improves performance and ensures that the datasets imported into Power BI are accurate, relevant and optimized for reporting.
How SQL helps analysts perform analysis on Data before building visualizations.
Data analysts perform analysis on data such as retrieving specific records, filtering datasets using conditions, performing aggregations such as sums, averages, and counts, and joining multiple tables to combine related data. After performing this analysis, they visualize their results using dashboards. Analysts use SQL queries to do this analysis. For example, an analyst might write an SQL query to calculate the total sales for products grouped by categories to get a view of the best performing product categories. By performing these transformations at the database level, analysts reduce the amount of processing required inside Power BI and create cleaner, well-structured datasets that are easier to visualize and analyze in dashboards.
Conclusion
In this article we have covered what Microsoft Power BI tool is and how it used in organizations. We've gone through how to connect Power BI to a local and cloud PostgreSQL, load the tables in the chosen database and how relationships between these tables are established using the tool. Finally we understood why SQL skills are important to any analyst using Power BI and what is the benefit of using SQL on Power BI.
With this we can now become better analysts, able to work with both local and on cloud PostgreSQL databases on Power BI. Additionally we are now able to work better and efficiently on Power BI by utilizing SQL queries.
Top comments (0)