Connecting Power BI to SQL database.

Introduction

Microsoft power BI is an effective data analysis and business intelligence tool. It assists users in converting raw data to valuable information by providing visual reports in the form of charts, graphical and dashboard.

A lot of firms save their information in databases. A SQL database like PostgreSQL is one of the common databases. Such databases contain data in a structured form in tables and thus it becomes simple to arrange and manipulate huge amounts of data such as customer details, product data, sales and inventory.

Linking power BI with a database enables analysts to handle actual data. It is easier to analyse information, monitor the performance of the business, and build dashboards, which automatically update whenever the database is changed.

Due to this reason, the SQL databases are significant in terms of storing and managing the data that companies utilize to make decisions.

Linking Power BI with Local PostgreSQL Database.

The initial method of linking Power BI with data is by connecting it to a local PostgreSQL database that is located on your computer.

Step 1: Open Power BI Desktop
To begin with, launch Power BI Desktop on the computer. It is the primary application that is used to develop reports and dashboards.

Step 2: Select “Get Data”
On the upper left hand side of the screen, there is Home, which then will lead to Get Data.
Power BI is a source of numerous data sources. You will find and then choose PostgreSQL Database on the list.

Step 3: Key in Database Connection Information.
Having chosen PostgreSQL, an opening to a connection window will open.
The following are the details of the database connection:

Server name: localhost
DB name: postgre (example)
Click OK to continue.

The next thing that power BI will ask is your logins.

Enter:

• Username
• Password

Step 4: Load the Tables
Once the connection is successful, Power BI will open the Navigator window.
This window shows all the tables within the database.

Example tables include:

• customers
• products
• orders Choose the tables desired and click Load.

Integrating Power BI with a Cloud Database (Aiven PostgreSQL).

Most of the organizations save their databases on the cloud rather than locally. Aiven PostgreSQL is one of the examples.
The first step to connect Power BI to this database is to get the connection details of the Aiven dashboard.

These include:

• Host
• Port
• Database name
• Username
• Password

You are also required to download the Aiven provided certificate of the SSL.

-if it works, it will open your avien cloud database as shown

-choose tables from database

-creating relationship between tables

-Data modelling

The reason why we need SSL Certificate.

The connection between Power BI and the cloud database is secured with the use of anSSL certificate. It ciphers the data transferred such that it is not intercepted by unauthorized users.

In configuring the connection in Power BI, the SSL certificate should be added so as to have a secure connection.

Table loading and Relationships.

After loading the tables into power BI, they can be seen in Model View.

In this perspective, you will be able to establish relationships between the tables to make Power BI know how they relate.

For example:

customers.customer id to sales.customer id

.

products.product-id sales.product-id

products.product_id <|human|>products.product_id inventory.product_id.

These associations generate what is referred to as data model. The data model assists the Power BI to analyze the data appropriately and generate accurate reports.
The Reason SQL Skills Matter to Power BI Analysts.

SQL This is a highly essential skill in Power BI analysts as it can be used to deal with databases as well.

With the help of SQL, data can be retrieved and prepared by the analysts prior to the construction of dashboards.

Example SQL queries:

Retrieve data

SELECT * FROM sales;

Filter data

SELECT * FROM sales
WHERE region = 'East';

Perform aggregations

SELECT product_id, SUM(amount)
FROM sales
GROUP BY product_id;

Note
By linking Power BI with SQL databases, the analysts gain access to and are able to analyze large datasets effectively. Regardless of whether the database is on-premises in PostgreSQL or in the cloud with providers such as Aiven, the database can easily be linked to and loaded with the necessary tables by Power BI.

After loading in the data, the analysts are able to draw dependable links between tables and develop dashboards that include vital business data.

The skills required by Power BI analysts are SQL skills since these enable the user to retrieve, filter, and prepare data, and then create reports, which is why data analysis is more efficient and quicker.