Connecting power Bi to sql databases

what is power Bi

Power BI is a business analytics platform. It allows one to analyse raw data and transform it into interactive visualizations and actionable insights.

Power BI allows a user to connect to various data sources, such as cloud services, Excel, and various databases. Once connected, a user can clean and model the data, then create interactive and dynamic dashboards and reports that enable the final user to get an overall picture of the business and make insightful decisions.
Connecting to a live database provides the following advantages:

1. Centralized data management
2. Real-time analysis, especially for data that changes quickly.
3. Managing complex relationships in the data. We shall focus on connecting Poer BI to SQL databases.

What are SQL databases

SQL databases are a relational database management system. They store data in structured tables made up of rows and columns. They allow a user to query and manipulate the data.
They provide:

1. Scalability to allow growth of datasets
2. Data integrity and reliability.
3. Efficient querying for quick retrieval
4. Structured data that maintains its consistency.

Connecting Power BI to a local database.

A local database is a database within your local machine, meaning the data is stored in your pc.

1. Open Power BI and click the 'Get Data' icon on the ribbon

1. It provides a list of sources, so choose the PostgreSQL option

1. You will now be required to enter the database details, which will allow Power BI to access the database as illustrated below.

The details you provide are login/database credentials that allow you access. They include username and password.
Once connected, the navigator will list tables present in the connected database.

1. Loading data to Power BI: The tables provided in the navigator section contain select tickboxes, where you will click to add the table to the list, which will be loaded to Power BI. If the data does not need transformation, click the load button to complete the loading process.

Connecting Power BI to a cloud PostgreSQL database.

1. Create a cloud service that provides a database hosting service. We will use Aiven as our preferred service provider. On your browser, go to aiven.com, create an account, then start a PostgreSQL service.

1. Note the following details of the PostgreSQL service:

Note the CA certificate, click the download option

3.Link the service to Power BI
Start Power BI application, on the home ribbon, click 'Get Data' click more and look for the PostgrSQL option

4.Proceed to enter the aiven service details you took note of as prompted by Power BI.

1. On your left, the navigator shows tables available on the database you just connected to. to load the data, click the checkbox next to the tables for the tables you wish to load. If the data needs cleaning, click the transform data option on the bottom right corner to clean the data. Once done, click the load option to load data and proceed.

Now that the data is loaded and calculations can be done, lets explore the relationship between the data. Data in each table has a primary key. This key is a unique identifier for each specific set of data which annot be replicated.
Since this key is used once, we can use it in different tables(it now becomes a foreign key) to create relationships. For example, you make gave a customer table which contains customers information. a customer id, would be the primary key, as its the only unique identifier of the customer in a table with many customers.
In a different table like orders table, we will refer to this primary key to identify the individual customer. When we reference this key, we refer to it as a foreign key.
We now have a relationship between different tables as illustrated in the table below.

We can also create relatioships manually when Power BI does not automatically create them.
Click modelling on the home ribbon and select manage relatioships.Select new relationship then proceed to select the type of relationship.

Select the 'from table' you wish to link and select it's primary key.
Select the 'to table' and then select they foreign key and save.

You have successfully created a relationship.

importance of relatioships.

They allow filtering of related data. If you select one specific set of data in a chart, other linked data charts and tables will automatically filter and show selected data.

importance of SQL

1.Data retrieval.
SQL queries allow filtered and selected data only, avoiding execessive
data that may corrupt final results.

1. Filtering data. This is handles by the database thus moving the heavy load lifting from your personal pc.
2. Transformation of data. SQL helps transform large data sets, which would slow down Power BI.