DEV Community: ASHLEY KALONDU

Synergy between PostgreSQL and PowerBi

ASHLEY KALONDU — Sat, 11 Jul 2026 10:33:19 +0000

PostgreSQL

PostgreSQL is a database used to store , manage and retrieve structured data. It can be hosted in 2 places

Locally hosted postgres
This database runs on your own computer and can only be accessed through your laptop.

Cloud hosted
In this case the database lives in a server owned by a cloud provider and they offer this access as a service. Examples are like

1.Azure
2.AWS
3.Google Cloud
4.Aiven
5.Digital Ocean

To enable you connect remotely, the cloud provider gives you
1.Host
2.Port
3.Database name
4.Username
5.Password

Using a DBMS

A DBMS is a software that enables one interact with their database either its stored locally or in the cloud.

We usually don't directly interact with PostgreSQL , we need a DBMS to provide an interface to interact with the database and perform DDL, DML, DQL, DCL, DCL, TCL.

Example is DBeaver and you fist choose the database to connect with and in our case is postgreSQL

Then we connect using the details host, port, password of your database.

For local hosted connection details are usually:
Host: 127.0.0.1 /localhost
Port: 5432 and for cloud hosted the provider share the details.

Creating and Importing Data

Once connected to a PostgreSQL database through DBeaver, there are two common ways to populate the database:

Creating tables and inserting data using SQL

Create a schema
Open an SQL script and write SQL commands to create tables.
Define primary keys and foreign keys to establish relationships between the tables.
Insert data into the tables using INSERT statements.

Importing existing data
Create a schema
If the data already exists in files such as CSV or Excel import it into the database.
During the importing the tables are loaded into to the schema.

Connecting to Power Bi

After our database contains the data , we use Power Bi as the reporting tool. PowerBi connect to many tools to get the data e.g
Excel
CSV
Database, in our case PostgreSQL

Local Connection

The server is the host: port , which is 127.0.0.1:5432

SSL CERTIFICATE

Local database usually connect directly because Power BI and PostgreSQL are on the same computer, however for Cloud they don't.
For security purposes we need download an SSL certificate which the cloud provider should give and upload it in our laptops in our certificate manager under Trusted Root Certification Authorities

In this case, Power BI has successfully connected to the Aiven PostgreSQL database. We can transform and clean it using Power Query, create visualizations and develop interactive reports and dashboards.

# POWER BI OVER SPREADSHEETS

ASHLEY KALONDU — Fri, 10 Jul 2026 11:28:33 +0000

Flat Tables

Our interactions with spreadsheet expose us to flat tables which is one large table storing all the data. Every row store all the information of one record and it’s easier to read because everything is one place.

However, many values are repeated across rows, making the table inefficient. This increases storage requirements and makes updating and maintaining the data more difficult.

Imagine in a instance where a doctor record changes , and he had treated 1000 patients, how would we update his information across 1000 rows?

Power BI introduces schemas

Unlike spread sheets, Power Bi is designed to work with relational tables other than one flat table. It can store data across several tables while ensuring the relationship is maintained.

Data modelling/Normalization

In modelling, Power Bi enables us to move from one flat table to several related tables.

The table below represents part of a hospital's records. Each row stores information about a patient visit, including details about the attending doctor. Since the same doctor may attend to many patients, the doctor's information such as gender, specialty, and years of experience is repeated across multiple rows.

We can create a separate table to store the doctors' information and establish a relationship with the main table using the Doctor ID. Whenever we need additional information about a doctor, such as their specialty, we use the Doctor ID to reference the Doctors table and retrieve the required details.

As you can see, the hospital has only four doctors, so there is no need to repeat their information throughout the hospital records.

We can do this and create several tables while maintaining a relationship with the fact table using an ID, which in this case is the primary key.

Example: In the below table the doctors id is captured in the facts table to maintain the relationship.

Database normalization is organizing data so that the same information is not repeated unnecessarily.

The main table is called the fact table. It stores the primary records and contains the IDs used to link to the related dimension tables.

The separate tables created from the repeated information are called dimension tables. They store description information, such as doctor details, procedure information, or hospital departments. These dimension tables surround the fact table, forming a star known as a star schema.

Relationship Cardinality

This represents the relationship between different tables. How many columns in one table match in the other tables

One-to-One: Each record in Table A relates to exactly one record in Table B, there is often little benefit in keeping them separate

One-to-Many: One record in Table A is associated with multiple records in Table B. example 1 record from the doctors table represents many records of the doctor treating various patients

Many-to-Many: Records in both tables can relate to multiple records in the other.

By using data normalization and relationships with the right cardinality, Power BI enables data to be organized more efficiently than a flat spreadsheet. This reduces data redundancy, improves maintainability, and makes analysis and reporting more effective.

How Excel is Used in Real-World Data Analysis .

ASHLEY KALONDU — Sun, 07 Jun 2026 12:00:31 +0000

I have used excel for years but in the basic way possible, just the usual enter data and do basic aggregations. What I know is there is a very big world out there on how excel is used, seemed far-fetched to me because I did not get to work on large data sets.
This week, I started getting deeper into excel and for the first team I received a disorganized data and knew how to locate duplicates.
A brief introduction to what Excel is
• In this week I have learnt the comprehensiveness of excel a software for storing, cleaning, analyzing visualizing data. It’s an all-comprehensive tool for a data professional.
3 ways excel can be used in real world data analysis

• Sales Performance: Excel can be used to map out when a business attained either highest or lowest sales using a pivot table. A visualization can be created of the current sales vis a vis the set targets. We can map out the highest and lowest sold products/service and aid in decision making
• Monitoring and Evaluation: Monitoring and evaluation professionals interact with excel when storing data for all their field activities, they go through the phase of cleaning and mapping out the success of their programs against their donor given targets. This enables them be aware if they are making an impact with their programs in the society.
• Budgeting and Forecasting: Excel is used to map out our budgets usage, double down to which areas have high expenses and also used in creating a forecast of future expenditure.
Mention at least 3 Excel features or formulas you've learned so far and explain how they can be applied
• Sum if and Sum ifs: This function is used when doing conditional aggregates. Data reveals more information when we calculate aggregates and more so conditional ones, this information can be used to drive decisions. For example, in an HR dataset, we can get the sum salary of Female in an IT department in comparison to the Male sum of salary in the same IT department.
• Pivot Table: I came across a pivot table a very strong analytical tool. By using the 3 elements row, column and values and its capabilities sort and order data we are able to analyze and understand the different parameters of our data.
• Central of tendency (Mean, Medium and Mode): The central tendency shows us where the majority of our data lie. If the mean and the median are in a close range with each other the data cluster around a certain value but it there is a big disparity between the 2 it shows our data has extreme outliers.
Your personal reflection:
• Excel is making interaction with data seamless and fun .