Connecting power bi to SQL databases locally and through cloud service.

Power BI is a powerful tool designed for cleaning data ,creating reports and dashboards.It is preferred over other tools due to its ability to handle large data sets,create real-time dashboards and its beginner friendly.Power BI desktop is a free version provided by Microsoft.

Main uses of Power BI

• Connecting data from different sources- Power BI has the ability to connect data from many sources including ;web,excel,SQL.
• Cleaning and transforming data-power bi is used to clean messy data making it ready to use for analysis.
• Data modelling-Through power bi it is possible to combine different tables with data for effective analysis.
• Data analysis using DAX(Data Analysis Expressions)-DAX is used to perform calculations in power bi.
• Data visualisation -Power BI converts data into charts,filters,slicers,graphs and reports. Reports and dashboard creation.
• Data automation, sharing and collaboration using Power BI service.

Why companies connect Power BI to databases.

• Power bi's ability to handle large data sets -databases store large sets of data and power bi is very effective for handling large data.
• Real time data -using power bi service companies can their data processed and accessed easily.Any changes made to data reflect instantly.
• Data security -databases ensure data is protected and control its access.
• Data centralisation -companies store their data in central databases and by connecting the database to power bi enables the staff to work with similar data at a given time.

Importance of SQL databases for storing and managing data

• SQL databases have structured data storage - Data is stored in tables which have rows and columns and this makes it easy to retrieve and use the data.
• Data retrieval efficiency -It is easy to retrieve specific data from SQL databases using queries.
• Data integrity -SQL databases have rules and constraints including primary and foreign keys which ensure data remains accurate and consistent.
• Large data sets- Most organisations deal with large volumes of data and SQL databases are effective for storage and management of this data.
• SQL supports data relationship -Through SQL databases it is possible to connect data from different sources.

Connecting power bi to a local postgresql

• Open the power bi desktop .

• Click on Get datain the home ribbon ,this enables power bi to connect to many data sources.

• Many options are available including excel,csv,web services click on postgresql .
  

• Click connect-once you select postgresql click the button to connect.

• Enter the server name local host:5432-this means that Power bi will be hosted locally in your computer.

• Enter the database name postgres-this is the database that contains the tables we will be using.
• Enter the authentication detail ;username and password.
• Click connect.
• After connection is complete a navigator window will open which shows what your database contains.

• Select the tables you want to load from your database (Customers_2,sales,inventory and products).

  • You can load your data directly into power bi.
  • You can use power query to clean and transform your data before loading.

• Once your tables are loaded they will appear on the data tab as shown below.
  
  

Connecting Power bi to Aiven PostgreSQL
The process of connecting power bi to a cloud database involves the following steps.Each step is illustrated with an image for clear understanding .

• Log into your aiven account and open your Aiven PostgreSQL to get your connection details.

• You will find the following details:Host ,Database name,Port,username and password.
• Go the connection information and find the SSL certificate section.

• Download the SSL certificate and save it in your computer.
• Open power bi desktop and once you are in the home tab click Get data then select PostgreSQL database
• Enter the connection details provided by Aiven and click OK.
• Configure the SSL details -enter the certificate downloaded in your computer.
• The navigator window appears and click load or transform data.

The SSL licence provides secure connection between Power bi and the cloud service(Aiven).
It protects the data from any malicious acts.
It also ensures you are connected to the right server and your credentials are protected.

Loading data into power bi
Open power bi desktop and once you are in the home tab click Get data then select PostgreSQL database
Select the tables you want to load from your database (Customers_2,sales,inventory and products).

• You can load your data directly into power bi.
• You can use power query to clean and transform your data.

Data Modelling and relationships
This process involves structuring,organizing and connecting data tables for effective performance.Designing a good data model is important since it makes reports accurate and easy to understand.To set up a data model in Power BI is done by opening the report view and go to Data Model.It involves bringing many data tables together and connecting the relationship between them.
A schema is a structure that shows how data is organized and related in a data model

Star Schema

This data model contains one central fact table and many dimension tables.The dimension tables are connected directly to the Facts table.
The fact table is at the center surrounded by the dimension tables forming the star shape.

Relationships in Data models

This explains how tables are connected in a data model.

Types of relationships

• 1 to many(1:*)-One record in a dimension data relates to many records in a fact table.
• Many to 1(*:1)-Same as 1 to many but viewed in the opposite direction.
• Many to many(:)-multiple records in one table match multiple records in another table.

Facts table

This table stores the key values for your analysis.It stores measurable and quantitative values and the values are mostly numeric.
In our case ,the sales table is the fact table.

Dimension tables.

This tables act like a look up table for the values in the facts table.

In our case the inventory,products and customers table are the dimensional tables.

Why is good data modelling critical?

• Good data modelling ensures data accuracy by reducing data ambiguity and duplication.
• A good data model improves performance by reducing the number of joins and relationships.Like in the case of star schema which has one fact table and a few dimension tables.
• Simplified analysis -a well designed data model makes it easy to perform accurate DAX calculations.
• A good data model ensures data integrity by enhancing accurate storage of data hence maintaining its integrity over time.

CONCLUSION.
It is important for power bi analysts to have SQL skills since they enable them to retrieve the stored data,interpret it and come up with reports that will impact decision making.Most organizations store their data in SQL databases therefore the power bi analysts have to retrieve the data from the databases for analysis.
Using the WHERE clause analysts are able to work on specific data.
SQL performs aggregation using functions such as COUNT,SUM,AVERAGE which enable analysts to summarise large sets of data and draw conclusions.
Analysts clean and transform data using query before building visualisations to feature in the dashboard.
They also use joins,subqueries and common table expressions to combine data from multiple tables for analysis.