Introduction
As a data analyst, you are probably interacting with Microsoft Power BI in your everyday operations, because it is one of the most powerful business intelligence and data visualisation tools ever developed. Power BI is known for its ability to transform raw data and generate meaningful insights, reports and analytics through creating interactive dashboards for consumption by the end users of the data. Most companies and businesses rely on it to analyse trends, monitor performance and support data-driven decisions.
SQL databases play a critical role in storing and managing analytical data in modern organisations. Structured Query Language (SQL) databases are designed to organise large volumes of structured data into tables consisting of rows and columns. This structure makes it easier to store, retrieve, and manipulate data efficiently, which is essential for data analysis and business decision-making.
SQL databases form the foundation of many modern data systems, including data warehouses and analytics platforms. Databases such as PostgreSQL, MySQL, and Microsoft SQL Server are widely used to store structured analytical data that can later be analysed using statistical tools, machine learning models, or reporting platforms. Power BI has the ability to connect directly to such databases to allow analysts to access real-time data for data visualisation and reporting.
Linking Power BI
1. To a Local PostgreSQL Database
- You start by opening your Power BI Desktop, as it is the main environment for dashboard and report creation.
2. Pulling your data from the database of your choice
Click 'Get Data', then in the drop-down, you will click "more", which will lead you to another interface where you will choose "database", then choose "PostGreSQL", then connect.
3. Connecting to PostgreSQL Database.
Here, you will be prompted to enter database connection details.
In the dialogue box, you add the server_name, which is localhost:5432 (default server name for locally connected PostgreSQL database) and the name of the database you would like to connect to. In my case, the database is walmart_db
4. Configuration with your database
You will be directed to another dialogue table that will be used for configuration purposes.
Since you are connecting from your local environment, the default connection details for PostgreSQL are
Host: localhost
Port: 5432
Username: postgres
Database: postgres
Password: your_password
5. Loading your data from your database to Power BI
After filling in the right credentials and connecting, once connected successfully, a navigator window will appear, displaying the tables available in PostgreSQL.
Click "Load" to import the selected tables into Power BI
In our case, we have loaded our table that has more than 9000 rows
2. To a Cloud PostgreSQL Database
Modern systems databases are hosted in the cloud environment as opposed to the traditional local environment for management, because it offers flexibility, scalability, and reliability compared to traditional systems.
For organizations working with large analytical datasets, cloud databases provide a flexible and powerful foundation for data-driven decision-making. It also improves scalability, accessibility, cost efficiency, reliability, and integration with advanced analytics tools.
One such cloud platform that provides fully managed open-source data infrastructure services is Aiven.
1. Obtain Connection Details
On your Aiven page after creating PostgreSQL service, you can retrieve connection details such as
- Host
- Port
- Database Name
- Username
- Password These details will be used in Power BI for the connection
2. Download SSL Certificate
An SSL certificate (Secure Sockets Layer certificate) is a digital certificate that enables encrypted and secure communication between your application and database, protecting credentials and data during transmission.
From the Aiven dashboard, locate the CA certificate and download it. Save it locally.
After down loading the CA certificate, you create a folder in your computer and store the CA certificate there and rename it from ca.pem to ca.crt. Power BI will automatically detect the ca.crt, enable SSL and verify server certificate.
3. Connect to Power BI
Click 'Get Data', then in the drop-down, you will click "more", which will lead you to another interface where you will choose "database", then choose "PostGreSQL", then connect.
3. Connecting to PostgreSQL Database.
Here, you will be prompted to enter database connection details.
In the dialogue box, you add the server_name, which is pg-2f14f89-shabsibrah-9c1c.c.aivencloud.com:10116 (server name from Aiven and the port number) and the name of the database you would like to connect to. In my case, the database is luxsales from DBeaver, click OK.
You will go back to your Aiven and copy the user_name and password, and fill in the dialogue box and click "Connect".
4. Loading your data from your database to Power BI
After filling in the right credentials and authenticating, once it connects successfully, a navigator window will appear, displaying all the tables available in that particular database. Select the tables that you need and load them into Power BI
5. Loaded Tables and Relationships between them
Once te tables have been loaded to your Power BI the next step is to carry out data modelling by detecting and establishing relationships that exist between the loaded tables.
This is first done by understanding what each table communicates
Customer table --------> Gives information about the customer
Products table --------> Gives information about the products
Invetory table --------> Gives details on the stock levels
Sales table -----------> This is the fact table tells more on sales tranactions.
Power BI automatically attempts to detect and establish the relationships that exist between the tables such that it connects table using primary key from dimension table to the corresponding foreign key in a facts table or foreign key of another dimension table.
For examples
- sales.customer_id -> customers.customer_id
- sales.product_id -> products.product_id
- inventory.product_id -> products.product_id
The result can be seen on the model view with a diagram displaying the final model created.
Data Modelling is a very powerful process since it helps in combining data correctly, proper filtering of data across the tables and finally ensuring that accurate reports are built at the end.
Power in combining SQL with Power BI
SQL has proven to be one of the most powerful analysis tools to date, it comes in very handy especially when you are dealing with large datasets. This is well completemented with Power BI which is one of the most powerful tools of data visualisation and transformation capabilities.
With use of proper queries SQL can effectively retrieve data effeciently from very large databases.
</>SQL
Select product_id, sum(amount) as sum_rev
from sales
group by product_id
SQL helps analysts filter datasets before loading them into Power BI. Instead of importing an entire database, analysts can use SQL WHERE clauses to limit the data to a specific period, region, or category
</>SQL
Select gender, count(customer_id) as count_client
from sales
group by gender
where gender = "Male"
SQL enables analysts to perform aggregations and calculations directly within the database. Using functions such as SUM, COUNT, AVG, and GROUP BY, analysts can compute metrics like total sales, average product prices, or the number of customers per region before the data reaches Power BI.
Additionally, SQL is extremely useful for preparing and transforming data. Analysts frequently need to join multiple tables, clean inconsistent values, or create intermediate datasets. SQL features such as JOIN, CASE, CTE (Common Table Expressions), and subqueries allow analysts to shape the data into a structure that works well with Power BI’s data model.
Finally, SQL complements Power BI by enabling analysts to build reliable and scalable data pipelines. When data is properly prepared using SQL, the dashboards built in Power BI become simpler, faster, and easier to maintain.
By combining the power of SQL for data extraction and transformation with visualization capabilities in Microsoft Power BI, analysts can build insightful dashboards that support better business decision-making.
Conclusion
While Power BI is a very powerful, most effective business intelligence platform for transforming database to interactive dashboards and reports, connecting it with SQL databases such as PostgreSQL analysts can access large volumes of data and analyse it effeciently.
Top comments (0)