DEV Community: richardmuzungu

HOW TO CONNECT POWERBI TO A LOCAL POSTGRESQL AND AIVEN CLOUD

richardmuzungu — Wed, 03 Jun 2026 16:07:49 +0000

WHAT IS POWERBI

Power BI is a business analytics created by Microsoft that transforms raw, disconnected data into interactive visuals and actionable insights. It allows users to data sources, clean and model the information, and build stunning, real-time dashboards to drive smarter business decisions.

How it works

1.Connect: Pulls in data from virtually anywhere, including local Excel files, cloud platforms (like Salesforce or Google Analytics) and major databases .
2.Transform & Model: Cleans up messy data and establishes relationships between different tables.
3.Visualize: Uses intuitive drag-and-drop actions to build charts, graphs, maps, and matrices so data tells a clear story.
4.Share: Publishes reports to the cloud so teams can interact with the data, filter it, and make informed decisions.
SQL AND IT'S IMPORTANCE
SQL in full means Structured Query Language. It is the foundational standard for storing and managing structured data across modern businesses.
Benefits of SQL

Databases Guaranteed Data Integrity: Uses ACID properties (Atomicity, Consistency, Isolation, Durability) to ensure every transaction completes flawlessly without corrupting your data.
Massive Scalability: Handles millions of data rows smoothly while maintaining fast search speed
Standardized Language: Uses SQL, a universal language, allowing developers to write queries that work across different systems like MySQL, PostgreSQL, and SQL Server.
Advanced Security: Controls access through precise user permissions, encryption, and secure login protocols to protect sensitive business assets.
Powerful Relationships: Links different tables together using keys, eliminating the need to duplicate data across your system.

Connecting PowerBi to a local Postgre database

Install powerbi : Incase one does not have it
*Open Powerbi * : Open Powerbi and select Get data on the top bar menu

_Scroll through the list _ Scroll through the list and head to Database whereby you will see Postgre SQL Database displayed on your right, click it and click connect

_Name the server and database _ Set your server name and database then click OK.

Username and password Set your username and password for authentication then click connect

NB: The name of the database must exists in the PostgreSQL

Connecting Power BI to a Cloud PostgreSQL Database (Aiven)

Many people and organizations store their data in cloud databases such as Aiven because of its flexibility and scalability.

Log in Aiven Log in to your aiven account and select the postgres database and activate it(power it on)
Click the service option To display details in the service option.

Scroll to CA Certificate Click the download option and save the certificate on your computer

Rename the certificateThe certificate will download as a file named: ca.pem
Windows expects to use the .crt extension. To make the certificate compatible,
rename the downloaded file ca.pem file to ca.crt.
Double click the file Double click ca.crt to install the certificate in your computer

Select **_Local Machine as the installation location_**
Select the second option Which is Place all certificates in the following store

Select the Trusted Root Certification Authorities

_Click finish _ A notification will appear to show that the import was successful

Part 2: Login to the Database

Gather the details from the Aiven Overview Page:

Host Name
Port
Database Name
Username
Password Open PowerBI Then click Get Data

_ In the list, select More and choose PostgreSQL Database_

Enter the Server and Database using the values copied from Aiven. Leave the data connectivity mode on the default Import option.

Note: The server name uses the format hostname:port_number.

Insert the server name as hostname and name of the database, then click OK.
Insert the username and password, then click Connect.

JOINS AND WINDOWS FUNCTIONS IN SQL

richardmuzungu — Tue, 03 Mar 2026 03:25:59 +0000

STRUCTURED QUERY LANGUAGE
Mostly referred to as SQL, it's the standard programming language designed for managing, manipulating, and retrieving data stored in relational databases.
In this article, we will focus on the joins and windows functions.

JOINS

Essential operations in SQL that merge rows from different tables and bring information together. you can also say that they combine columns from multiple tables based on a related key, enabling data retrieval from a normalized database structure.
TYPES OF JOINS

1. INNER JOIN
Returns only rows that have matching values in both tables.
Example student table(table 1)and subject table(table 2)
Student table

Student ID	Name
1	Maki
2	Mani
3	Mapi

Subject table

EnrollmentID	Student ID	Subject Name
111	1	History
112	2	Geography
113	4	Business

The INNER JOIN Query would be
SELECT Student.StudentName,Subject.SubjectName
FROM Student
INNER JOIN Subject
ON Student.Student ID = Subject.Student ID

Results

StudentName	Subject Name
Maki	History
Mani	Geography

INNER JOIN returns only rows where the join condition matches in both tables; For Maki and Mani, a match was found in the student ID (1,2) but for Mapi, student ID (3) no enrollment hence excluded and studentID 4 no matching student was found also.
2. LEFT JOIN
Returns all rows from the left table and matching rows from the right table. If a row in the left table does not have a match in the right table, the columns from the right table will contain NULL values in the result. Left join records all from the left table and matching records from the right table.
Example Teacher _and _Classes
Teacher Table(Left table)

TeacherID	Name	ClassID
1	Cece	01
2	Gordon	02
3	Forrest	03
4	Frank	04

ClassID	ClassName
01	Junior
02	Midlevel
03	Senior

LEFT JOIN QUERY
SELECT Teacher.Name, Class.ClassName
FROM Teachers
LEFT JOIN Classes
_ON _Teacher.ClassID = Class.ClassID;

Results

Name	ClassName
Cece	Junior
Gordon	Midlevel
Forrest	Senior
Frank	NULL

NB LEFT JOIN takes all rows from left table, even if no match

3. RIGHT JOIN
It is the opposite of LEFT JOIN-returns all rows from the right table and matched rows from the left, with NULL for non-matches.
4. FULL JOIN
Also known as FULL OUTER JOIN. It combines LEFT and RIGHT JOINS. It returns all rows from the right and left tables, regardless of whether there is a match or not.
example
Person Table

CitizenID	Name
1	Aaron
2	Sheryl
3	Bitu

Sport Table

CitizenID	Sport
1	Rugby
2	Basketball
4	Fencing

Full Join Query
SELECT Person.Name, Sport.Sport
From Person
FULL JOIN Sport
ON Person.CitizenID = Sport.CitizenID
Results

Name	Sport
Aaron	Rugby
Sheryl	Geography
Bitu	NULL
NULL	Fencing

5. CROSS JOIN
Produces the Cartesian product of two or more tables, combining every row from the first table with every row from the second table. It does not require a join condition.
Example
Assume you have two tables: Colors and Sizes.
Colors Table:
color_name
Red
Blue
Sizes Table:
size_name
Small
Medium
Large
The SQL query would look like this:
SELECT Colors.color_name, Sizes.size_name
FROM Colors
CROSS JOIN _Sizes;
The result of the query would be:
color_name size_name
Red Small
Red Medium
Red Large
Blue Small
Blue Medium
Blue Large
6. SELF JOIN
Where a table is joined with itself. Useful when a table contains related data within itself. You must use table _aliases to differentiate two copies of the same table.
Example
Find workers who drive the same car in the company
Workers Table

WorkersID	Name	Car
1	John	Hilux
2	Salome	Ipsum
3	Daud	Hilux
4	Yakov	Fit
5	Sarah	Ipsum

SELF JOIN QUERY
SELECT
W1.Name AS Worker1,
W2.Name AS Worker2,
W1. Car
FROM Workers W1
INNER JOIN Workers W2
ON W1.Car = W2.Car
AND W1.WorkersID < W2.WorkerID
Result

Workers1	Workers2	Car
John	Daud	Hilux
Salome	Sarah	Ipsum

Both John and David have a Hilux, Salome and Sarah have the same car.

WINDOWS FUNCTIONS

They maintain the original row count, adding computed values as new columns.

Components of window Function

Function:: Like ROW_NUMBER(), RANK(), SUM() OVER() Clause: Sorts rows within each partition.

Types of Window Functions

1.RANKING FUNCTIONS-Assign unique numbers or ranks based on specific ordering. It assigns ranks or numbers to rows.

ROW_NUMBER(): Unique sequential function.
RANK(): Rank with ties skipping numbers(example 3,4,4,6)
DENSE_RANK(): Ranks with ties without skipping(example 1,2,2,3)
AGGREGATE FUNCTIONS- Performs calculations like SUM,AVG,MIN, MAX
VALUE FUNCTIONS- Access other rows' values. -LAG(): Value from a previous row. -LEAD(): Value from a next row -FIRST_VALUE()/LAST_VALUE(): First or last Value in the window

Being knowledgeable about joins and windows functions transforms SQL from a simple query language to an analytical tool.

How analysts messy Data, Dax and dashboard into Action using Power bi

richardmuzungu — Sun, 08 Feb 2026 12:43:40 +0000

Data analysts transform raw data to a clean one using Power Bi tool. This process turns disorganized data like duplicates, missing values, replacing missing values and turning them into clear information that aid in decisions.

Workflow steps in Powerbi

Once you open powerbi, one is able to retrive the file from any format like .csv and transorm them to powerbi format. Click transform get data on the top left and select the appropriate data. After that click on transform so as to make changes in your data.
Cleaning the data
A very essential part in ensuring that the data is usable and can clean. Therefore one starts by;

1_._ Removing the duplicates__- Unique columns such as phone number, identification card are used. select the column>click home>remove rows>remove duplicates.

2.Identifying missing values- Missing values in a data set may affect your outcome you need to fill them with UNKNOWN/NOT APPLICABLE for the text columns. For numeric columns we use replace errors because until Power Bi regards it as an error.
Press CTRL+H to replace the missing values.

3.Fixing data types in columns to their appropriate match.

LOGICAL FUNCTIONS

These are functions that make decisions based on conditions.
To add a function use new measure

SUM FUNCTION
Calculates the total of all numbers in a specified column.
-AVERAGE FUNCTION
Calculates the arithmetic mean of numeric values in a specified column.
MEDIAN FUNCTION
Returns the middle number
MAX AND MIN FUNCTION
Max function helps you get the highest value while min function helps you get the lowest value.
DAX-refers to Data Analysis Expressions that translate cleaned numbers to business metix.
Creating a table- we have the star and snowflake but star is widely used because it is easy to understand.

For visualization and interactivity we use dashboards- they assist in decision making.
Power BI is a decision accelerator and can be used to discover hidden financial recoveries, reduce operation costs in organizations. used in the health and banking sector.

POWER BI

richardmuzungu — Sun, 01 Feb 2026 19:59:43 +0000

Power BI is a Microsoft business analytics tool that connects to, cleans, and transforms raw data from various sources into interactive, visually immersive reports and dashboards. It enables users to analyze data and create important decisions.
Data modelling is the procedure used by data scientist and engineers to structure data in tables based on relationship and logical framework
Platforms of Power BI
Power BI Desktop: A free, Windows-based application used by data analysts to connect to, model, and visualize data. It is the primary tool for creating reports.
Power BI Service: A cloud-based service (SaaS) used to publish reports, create dashboards, share insights, and collaborate within organizations.
Power BI Mobile Apps: Applications for iOS, Android, and Windows devices to view and interact with reports and dashboards on the go.
Power BI Report Server: An on-premises report server, ideal for companies that cannot store data in the cloud.
A schema defines how data is structured and related, while data modeling is the process of building that structure to ensure performance, accuracy, and usability. The most important schema for Power BI is the star schema.

Types of schemas in power BI

Star Schema: This is the industry standard and recommended approach for Power BI. Consists of a central fact table (containing quantitative measures like sales amounts) surrounded by multiple dimension tables (containing descriptive attributes like product names, dates, or regions).
Advantages: Excellent performance due to fewer joins, simplified DAX calculations, and an intuitive, easy-to-understand model for report authors.
Snowflake Schema: A variation of the star schema where dimension tables are further normalized into sub-dimension tables. For example, a Product dimension might link to a Category table, which then links to a Manufacturer table.
Advantages: Reduces data redundancy and improves data integrity and storage efficiency.
Disadvantages: More complex structure and potentially slower query performance due to more joins compared to a star schema.
Galaxy Schema (Fact Constellation): Involves multiple fact tables that share some common dimension tables, suitable for complex, enterprise-level analysis of different business process

Fact and Dimension Table Overview
Fact Tables-Store transaction data, metrics, and foreign keys such as sale; They are tall, narrow, and grow over time.
Dimension Tables: Store descriptive, categorical data such as Customers. They are short, wide, and used to filter/group data.
Data modelling is important because:

It creates an organized and reliable roadmap for how data is stored, accessed, and interpreted.
Effective data storage

JUMIA ASSIGNMENT

richardmuzungu — Sun, 01 Feb 2026 19:16:05 +0000

To find the missing in rating/review
Highlight the review column, click CTRL+H. Find what leave it blank and on the replace option write 0. For the rating column follow the same formula but on the replace option write Not Provided.
On the out of rating column, use find and replace ,CTRL+H out of 5 in the find what then leave blank in the replace option.

For the old and current price, change them to currency... highlight the columns, head to numbers in the home section turn them to currency.
##TO REMOVE THE NEGATIVE SIGN FROM REVIEW
Use absolute function ABS(cell range of review) or use the if function =IF(e2<0,-e2,e2)

TO REMOVE DUPLICATES FROM THE DATA

Click anywhere in the cell, go to data option on the ribbon, click remove duplicates, a box will appear to select the columns to find the duplicates and if its the whole data select all. tick with the header option in the dialogue box then click ok.

ADD THREE COLUMNS

DISCOUNT
Which is the subtraction of the old price and new price
RATING CATEGORY
Use the formula IF function, =IF(I2="Not provided", "N/A" IF(I2<3,"Poor", IF(I2<=4.4, "Average", "Excellent"))) I2 is the cell name
DISCOUNT CATEGORY
=IF(cell name>40%,"High", IF(cell name>20%, "Medium", "Low"))
Then find the average of current Price, old price , discount percentage, rating.
Then find max and min of the current price, old price, discount percentage, rating.
For the average rating and discount category, create a pivot table; discount on the row, count of rating change on the value beside the rows then change the field value to average
To find what products are used? form a pivot table of rating(row) then the count of product on the value

RELATIONSHIPS

Create a pivot table between Discount%(row) and number of review(values). select the first cell on the pivot table, right click, go to the group option the select by 0.1 which is 10%

MS EXCEL INTRODUCTION FOR DATA ANALYSIS

richardmuzungu — Sun, 25 Jan 2026 16:02:28 +0000

Excel is an important tool when working with data - whether it is for business, marketing, research, or data analysis purposes. It performs calculations, summarizes data, performs visualization.
Data analysis is the science of analyzing raw data, insights to support decision making that's why excel is an important tool.
A cell is named using column and row for example cell named E2, its column is E and 2 is the row
Basic Excel functions to be used include SUM, AVERAGE, COUNT, MAX and MIN .
SUM
Is used to get the totals

in the particular image, we are finding the total of A2 and B2 in cell and the total in cell C2. Since SUM is a function you use equal sign =. You write the function =SUM(excel displays automatically) in a cell C2(can be in any cell to get your results) then the name of the first cell which is A2 full colon name of the second cell which is B2, close the brackets then enter.
AVERAGE
Finds the totals then divides it with the count of numbers

here we were finding the average from cell A2 to cell A6 whereby you write the function =Average write the range (A2:A6) close the brackets. you will find the total as 37.6
the function average added all values from a2 to a6 then divided with the total count that is 5
COUNT
Displays the count entries

write the function count, cell range which is from cell A2 to A6 close the bracket then ENTER. it displays 5 as the total count of values.
MAX
Displays the highest or lowest value

From the image above the max value(highest value) from cell A2 to A6 is 53 that is displayed on cell A7.
It also applies to minimum only that you use the function =MIN

CONCLUSION

_MS Excel is a good tool that aids in decision making. Understanding the basics is the foundation for advanced EXCEL. _

GIT ARTICLE

richardmuzungu — Fri, 16 Jan 2026 16:53:44 +0000

Version control

Version control is a system tracking changes to files overtime, people work on the same project and can track previous versions if something breaks.
Version control using git means maintaining a complete history of changes made to the project by who, when and why by takung snapshots called commits.
Git is an example of a version control(keeps history, allows branching and merging, allows users develop a project.

Two ways to move codes in github, they include;

git pull
git push
GIT PULL
Helps one download changes from remote repository and integrate them into your local branch
GIT PUSH
Used to upload local commits to a remote repository like github

TRACKING CHANGES IN GIT

Is the process by which systems like git monitor files to detect changes and record history. Every file exists in two states namely; untracked and tracked files.

- untracked files

These are files that git knows they exist in a folder but they will not be included in your history or backups until you tell it.

- tracked files

These are files that were part of your last commit. Git also monitors any modification

STAGES OF TRACKING

They include;

1. Modified/working directory
User edits files but changes are not tracked. Git sees the changes but has not registered them.

2. Stage/staging area
Stores information about what will go into your next commit. Like gathering all changes before going to the next step.
3. Repository
It is the last stage and where git stores the data permanently.

Version control enables one to revert entire/individual files to previous stable state if anything is wrong(reversibility). Users create independent lines of development. allows experimentation without affecting the main project(branching) also people are able to work on project concurrently(collaboration)

DEV Community: richardmuzungu

HOW TO CONNECT POWERBI TO A LOCAL POSTGRESQL AND AIVEN CLOUD

WHAT IS POWERBI

*How it works *

Connecting PowerBi to a local Postgre database

Connecting Power BI to a Cloud PostgreSQL Database (Aiven)

Part 2: Login to the Database

JOINS AND WINDOWS FUNCTIONS IN SQL

JOINS

Results

Results

WINDOWS FUNCTIONS

Components of window Function

Types of Window Functions

How analysts messy Data, Dax and dashboard into Action using Power bi

Workflow steps in Powerbi

LOGICAL FUNCTIONS

POWER BI

Types of schemas in power BI

JUMIA ASSIGNMENT

TO REMOVE DUPLICATES FROM THE DATA

ADD THREE COLUMNS

RELATIONSHIPS

MS EXCEL INTRODUCTION FOR DATA ANALYSIS

CONCLUSION

GIT ARTICLE

Version control

Two ways to move codes in github, they include;

TRACKING CHANGES IN GIT

- untracked files

- tracked files

STAGES OF TRACKING

How it works