DEV Community: OSCAR MAINJE

I Built a Digital Notebook for a Kiosk Owner Who's Tired of Paper Records — Here is What I Learned

OSCAR MAINJE — Sun, 20 Sep 2026 11:22:54 +0000

The problem I set out to solve

Picture a small kiosk in a town — the kind that sells bread, milk, sugar, eggs, tea leaves. The owner tracks everything in a notebook: what is in stock, what sold today, how much cash should be in the till. It works, until it doesn't, a page gets torn, a calculation gets missed, or nobody notices sugar is almost out until a customer asks for it.

I built Nuru Shop Sales Tracker, a simple command-line tool in Python, to replace that notebook. No app download, no internet connection needed, no technical skill required beyond typing numbers into a menu.

What it actually does

When you run it, it asks for the kiosk's name and the owner's name, then enters a menu:

1. View Current Inventory Stock
2. Add or Restock a Product
3. Sell a Product
4. View Sales Report
5. Search Products
6. Save & Exit

From there, the owner can check what's in stock, add new products or top up existing ones, record a sale (with the system automatically calculating change to hand back in notes and coins), search for a product without needing to remember its exact name, and see a summary of the day's performance.

The part I'm most proud of: it thinks ahead for the owner

The most useful part of this project isn't the menu — it's what happens quietly in the background. Every time the owner exits the program, two things happen automatically:

A restocking list is generated for any product running low (fewer than 10 units), so the owner has a ready-made shopping list to hand their supplier, no manual stock-checking required.
The full inventory and the day's sales are saved to files, so nothing is lost between sessions. The next time the program runs, it picks up exactly where it left off.

At the end of a shift, instead of doing the math, the owner instantly sees their total revenue and which product sold the most, the kind of insight that used to take flipping back through notebook pages to figure out.

How I approached building it

Rather than writing one giant block of code, I broke the problem into small, single-purpose functions. One to view stock, one to sell a product, one to generate the sales report, and so on and all are called from one main loop. This meant I could test each piece (does selling more than what's in stock actually get blocked? does a typo in the menu crash the program?) independently before trusting them to work together.

I also spent some time on input validation. A shop owner using this tool might mistype a price, leave a field blank, or enter letters where numbers are expected, the program needed to handle all of that politely instead of crashing, since a crashed program is worse than a paper notebook.

What I'd build next

A configurable low-stock threshold per product. 10 units is a sensible cutoff for something like bread, but not for a slower-moving item like sugar.
Trend reporting across sessions, not just a single shift. Right now, sales history is saved but only ever summarized one day at a time.
A CSV export, so an owner could hand their sales data to an accountant without needing to open a text file.

What this project taught me

The technical skills — dictionaries, file handling, input validation — were the easy part to learn. The harder, more useful skill was thinking about the end user the whole way through: someone who isn't a programmer, might make typos, and just wants their day to run smoother than it did with a notebook. Every design decision, from the menu wording to the automatic restock alerts, came back to that person.

From a Messy Spreadsheet to Business Insights: Analyzing a Hotel's Booking Data

OSCAR MAINJE — Sat, 19 Sep 2026 12:21:16 +0000

How I cleaned 285 rows of real-world-style hotel data in PostgreSQL and turned it into revenue, occupancy, and guest-satisfaction insights in Power BI.

The brief

Tembo Hotel & Suites is a mid-range business hotel in Nairobi that has been running since 2023. Like a lot of small hospitality businesses, its booking records are kept in a spreadsheet and the spreadsheet was a mess.

The ask from the Hotel Director was simple to state and harder to deliver:

"I need you to clean this data, load it into our new database, analyse it, and present your findings to the management team in two days. I want to know: which rooms make us the most money, which months are busiest, how our staff are performing, and whether our guests are happy. Make it look professional — use Power BI for the visuals."

This is the story of that pipeline: from a dirty CSV export to a 3-page Power BI report, and the business decisions the numbers actually point to.

Step 1: Staging everything as text

The raw export had 285 rows and the kind of problems anyone who has cleaned real operational data will recognize: inconsistent capitalization, dates in two different formats mixed in the same column, currency values stored as strings like KES 1,000, phone numbers with stray country codes and dashes, ratings outside the valid 1–5 range, and duplicate bookings.

Loading data like this straight into a typed table is a losing game — a single erroneous row anywhere in the file kills the whole import. So the first step was a staging table where every column is TEXT:

CREATE TABLE tembo_staging(
    booking_id          TEXT,
    guest_name          TEXT,
    guest_phone         TEXT,
    ...
);

This guarantees the load succeeds, and pushes all validation and type-casting downstream, where it can be handled using SQL.

Step 2: Audit before you touch anything

Before writing a single UPDATE, I ran a set of diagnostic SELECT DISTINCT and pattern-matching queries to catalog exactly what was wrong:

Casing inconsistencies in names and cities
Non-standard category labels (Dlx instead of Deluxe, MPESA and M-PESA instead of one consistent label)
Two date formats in the same column, distinguishable only by checking whether the day or month value exceeded 12
Currency symbols and commas embedded in numeric fields
Guest ratings outside the valid 1–5 range
Exact duplicate booking_ids
Negative values for nights_stayed

This audit-first approach matters for a simple reason: cleaning decisions should be based on what's actually in the data, not assumptions about what might be wrong. It also means every subsequent UPDATE targets a specific, confirmed problem — verified with a SELECT first, per the golden rule of data cleaning:

Always run a SELECT before an UPDATE or DELETE, so you know exactly which rows will change.

Step 3: Cleaning, one problem at a time

With the audit complete, the fixes were mostly a mix of TRIM, INITCAP, REGEXP_REPLACE, and targeted CASE statements:

Text standardization:

UPDATE tembo_hotel.tembo_staging
SET guest_name = INITCAP(TRIM(guest_name))
WHERE guest_name <> INITCAP(TRIM(guest_name));

Currency-as-text:

UPDATE tembo_hotel.tembo_staging
SET total_amount = NULLIF(REGEXP_REPLACE(total_amount, '[^0-9]', '', 'g'), '')::NUMERIC(10,2)
WHERE total_amount IS NOT NULL AND TRIM(total_amount) <> '';

Mixed date formats, resolved by checking which part of the date exceeds 12 (and can therefore only be a day, not a month):

UPDATE tembo_hotel.tembo_staging
SET check_in_date =
    CASE
        WHEN split_part(check_in_date, '/', 2)::int > 12
            THEN TO_DATE(check_in_date, 'MM/DD/YYYY')
        ELSE TO_DATE(check_in_date, 'DD/MM/YYYY')
    END;

Phone numbers with country codes normalized back to local format, and genuinely missing values set to NULL rather than left as empty strings — an important distinction, since empty strings and nulls behave differently in aggregations and joins.

Duplicates, removed while keeping the first physical row for each booking_id:

DELETE FROM tembo_hotel.tembo_staging
WHERE ctid NOT IN (
    SELECT MIN(ctid)
    FROM tembo_hotel.tembo_staging
    GROUP BY booking_id
);

After cleaning, 278 valid bookings remained from the original 285-row export.

Step 4: Building the production layer

Cleaned staging data moved into a clean tembo_bookings table, and on top of that, a view — vw_clean_bookings — that adds the derived fields Power BI needs for time intelligence and segmentation without recomputing them in DAX:

CREATE OR REPLACE VIEW tembo_hotel.vw_clean_bookings AS
SELECT
    ...,
    EXTRACT(YEAR FROM check_in_date) AS check_in_year,
    TO_CHAR(check_in_date, 'Month') AS check_in_month_name,
    CASE
        WHEN guest_rating BETWEEN 4 AND 5 THEN 'Satisfied'
        WHEN guest_rating = 3 THEN 'Neutral'
        WHEN guest_rating BETWEEN 1 AND 2 THEN 'Unsatisfied'
        ELSE 'No rating'
    END AS satisfaction
FROM tembo_hotel.tembo_bookings;

This view is the single source that the Power BI report connects to instead of raw tables scattered across the model.

Indexes were added to match the query patterns the analysis would actually use: a composite index on (check_in_date, booking_status) for time-based revenue filtering, (room_type, booking_status) for occupancy and cancellation queries, (staff_name, staff_department) for staff analysis, and a single-column index on guest_city for location queries.

Step 5: What the data actually says

With the pipeline in place, here's what the numbers show.

Revenue is concentrated in the Suite, not the most-booked room

Total confirmed revenue across 246 checked-out stays is KES 7,584,900, averaging KES 30,833 per stay. But the breakdown by room type tells an interesting story:

Room Type	Bookings	Revenue (KES)	Avg. Nights
Standard	94	1,595,500	2.9
Deluxe	80	2,165,700	3.0
Suite	53	2,596,100	3.2
Penthouse	19	1,227,600	2.5

Standard is booked most often — it's the volume driver — but the Suite generates the most revenue despite being booked far less frequently, thanks to its rate and the longest average stay of any room type. If the hotel wants to grow revenue rather than just occupancy, Suite is the room type worth protecting and promoting.

The Penthouse has a serious cancellation problem

This is the sharpest signal in the dataset. Cancellation and no-show rates by room type:

Room Type	Cancellation Rate	Revenue Lost (KES)
Suite	3.6%	120,000
Deluxe	8.1%	170,000
Standard	15.3%	320,300
Penthouse	24.0%	565,000

The Penthouse — the hotel's second-highest-priced room — is cancelled or no-showed almost a quarter of the time. Those lost bookings alone account for nearly half of all lost revenue (KES 1,175,300 total), from a room type that makes up just 9% of total bookings. This is the single highest-leverage fix available: a deposit or full prepayment policy plus a pre-arrival confirmation call for Penthouse bookings specifically would likely recover a meaningful chunk of that KES 565,000.

Standard's 15.3% rate is lower per-booking, but because it's the highest-volume room type, the absolute loss (KES 320,300) is still worth addressing with the same approach.

The most profitable room has the least satisfied guests

Average guest rating by room type:

Room Type	Avg. Rating
Penthouse	3.12
Standard	3.11
Deluxe	2.93
Suite	2.83

Suite guests are the least satisfied of any room type — worse than even the budget Standard rooms. Out of Suite guests who left a rating, 23 were unsatisfied against only 17 satisfied. This matters because Suite is also the top revenue earner: a guest paying the most is having the worst relative experience. Left unaddressed, this is a retention and reputation risk sitting directly on top of the hotel's most valuable segment. Overall, guest sentiment across the hotel is close to a coin flip — 41% satisfied vs. 38% unsatisfied — so this isn't a Suite-only issue, but it's most acute there.

Guests barely use paid extras — and one service massively outperforms the rest

66% of all bookings (185 of 278) included no extra service at all. Of the services that were used, Conference Room bookings stand out sharply:

Service	Times Used	Total Revenue (KES)
Conference Room	13	195,000
Spa Treatment	13	45,500
Airport Pickup	13	32,500
Breakfast Buffet	14	16,800
Laundry	13	10,400
Room Service	14	7,000
Swimming Pool	13	6,500

Conference Room earns roughly 4x the next best-performing service from a similar number of bookings — unsurprising for a hotel positioned as a business hotel, but the low attach rate (13 bookings out of 278) suggests it's undersold rather than unwanted. Bundling it into corporate packages, rather than leaving it as an opt-in extra, is a low-effort revenue lever.

Revenue is growing, but volatile and heavily dependent on one city - Nairobi.

Monthly revenue roughly doubled between late 2023 and 2024, though the dataset only starts in June 2023, so that first partial year isn't a fair year-over-year baseline — it likely reflects the hotel ramping up rather than a seasonal dip. Within 2024 alone, revenue swings 30–40% month to month with no single clean seasonal driver, though December and June are consistently the strongest months.

On the guest side, 45% of all bookings come from guests based in Nairobi, with the next largest cities — Eldoret and Kisumu — each contributing roughly a fifth as much. This concentration is a natural byproduct of being a Nairobi-based business hotel, but it also means the guest base has limited geographic diversification.

Recommendations for management

Require a deposit or prepayment for Penthouse bookings and a 48-hour pre-arrival confirmation call. This single change targets the largest revenue leak in the business.
Investigate the Suite guest experience — review comments and staff feedback tied to Suite stays. A premium room underperforming on satisfaction is a compounding risk: it affects both repeat bookings and referrals.
Package and actively market the Conference Room to corporate guests, bundled with catering — it's already the highest-earning add-on per booking, just underused.
Introduce a simple post-stay follow-up (SMS or email) that does two things at once: offers an add-on service and captures a guest rating. This addresses both the 66% no-extras rate and the 5% of stays with no rating recorded.
Extend the deposit/confirmation policy to Standard rooms — with a lower cancellation rate than Penthouse, but high volume means the loss is still significant.
Make guest_city a required field at intake — 14 bookings (5%) currently have no recorded city, which limits the accuracy of any geographic analysis going forward.
Explore targeted outreach in Eldoret and Kisumu before spreading marketing spend across many small feeder cities — they're already the next-largest segments after Nairobi.

What this project demonstrates

Beyond the specific findings, this project is a fairly complete walkthrough of a real analytics workflow: staging dirty data safely, auditing before cleaning, applying targeted and verifiable fixes, building a clean semantic layer for BI tools to consume, and translating query results into decisions a non-technical audience — in this case, a Hotel Director.

If you're working through a similar hotel/hospitality dataset or have questions about the cleaning approach, I'd love to hear from you in the comments.

Building and Querying a Database: A Practical SQL Guide

OSCAR MAINJE — Sat, 18 Jul 2026 14:35:20 +0000

As a data analyst, mastering database design and querying is essential. In this article, I will walk you through how I built and queried a database schema for Greenwood Academy using PostgreSQL.

The article covers everything from defining or changing the structure of database objects using the Data Definition Language (DDL), modifying data within tables using the Data Manipulation Language (DML), and fetching and retrieving data from the database using the Data Query Language (DQL). The article also explores filtering the results, utilizing specialized operators, and writing conditional logic with CASE WHEN.

Building the Database (DDL)

Data Definition Language (DDL) is used to define the database structure. For this task, I created a dedicated schema called greenwood_academy to keep everything organized. Inside the schema, I created three tables: students, subjects, and exam_results. Before creating the tables it is important to make sure that they are created in the intended schema. The SET search_path TO [schema_name] is necessary for this. A better and safer practice is to always initialize your table names with the schema name, for example to create a table students in the schema greenwood_academy you can use the command CREATE TABLE greenwood_academy.students. This will ensure that the table is created in the correct schema.

-- Creating a schema greenwood_academy and making sure SQL is using it.

CREATE SCHEMA greenwood_academy;
SET search_path TO greenwood_academy;

-- Creating the students table.

CREATE TABLE greenwood_academy.students (
    student_id    INT PRIMARY KEY,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL,
    gender        VARCHAR(1),
    date_of_birth DATE, 
    class         VARCHAR(10),
    city          VARCHAR(50)
);

Table Modifications

In practice, database structural requirements change from time to time. In this case, I altered the existing table structures by adding a phone_number column to the students table, renamed a column in the subjects table for better clarity i.e. credits to credit_hours, and then dropped the phone_number column when it was no longer needed:

-- Adding a column 'phone_number'to the students table.

ALTER TABLE greenwood_academy.students
ADD COLUMN  phone_number VARCHAR(20);

-- Renaming column 'credits' to 'credit_hours'

ALTER TABLE   greenwood_academy.subjects 
RENAME COLUMN credits TO credit_hours;

-- Removing the phone_number column in the students table.

ALTER TABLE greenwood_academy.students 
DROP COLUMN phone_number;

Filling in the tables & Modifying the Database (DML)

With the structures ready, Data Manipulation Language (DML) was used to modify the data within the tables. Data for 10 students, 10 subjects, and their respective exam records was inserted into the three tables.

-- ===========================================================
-- Filling the Database(DML: INSERT, UPDATE, DELETE)
-- ===========================================================
-- Example of inserting student data
INSERT INTO greenwood_academy.students(student_id, first_name, last_name, gender, date_of_birth, class, city)
VALUES 
    (1, 'Amina', 'Wanjiku', 'F', '2008-03-12', 'Form 3', 'Nairobi'),
    (2, 'Brian', 'Mutua', 'M', '2007-07-25', 'Form 4', 'Mombasa'),
    (3, 'Cynthia', 'Mutua', 'F', '2008-11-05', 'Form 3', 'Kisumu'),
    (4, 'David', 'Kamau', 'M', '2007-02-18', 'Form 4', 'Nairobi'),
    (5, 'Esther', 'Akinyi', 'F', '2009-06-30', 'Form 2', 'Nakuru');

-- Note: Truncated for readability, full file includes 10 records per table

After inserting the data into the tables, it is important to confirm that it is entered correctly before proceeding.

-- SELECT query to confirm all 10 rows exist in each of the three tables

SELECT * FROM greenwood_academy.students;
SELECT * FROM greenwood_academy.subjects;
SELECT * FROM greenwood_academy.exam_results;

Data Maintenance

Databases are dynamic and get updates and deletions time and again. In this scenario, I updated a student's city, corrected a typing error in an exam mark, and deleted a specific exam result row entirely:

-- UPDATE statement to change Esther Akinyi's city to Nairobi

UPDATE greenwood_academy.students 
SET    city = 'Nairobi' 
WHERE  student_id = 5;

-- UPDATE to fix the marks for result_id 5 to 59

UPDATE greenwood_academy.exam_results
SET    marks = 59
WHERE  result_id = 5;

-- DELETE statement to remove result_id 9 from the exam_results table

DELETE 
FROM  greenwood_academy.exam_results 
WHERE result_id = 9;

Quick Reminder: Forgetting to specify the condition for the update or deletion in the WHERE clause in each of the queries above is catastrophic, as the command will affect the entire table.

Querying Data (Filtering with WHERE)

Filtering data effectively reduces server load and isolates relevant insights. Below are a couple of specific queries using basic WHERE, AND, and OR logical operators to look through the records:

Find all students in Form 3 AND from Nairobi:

SELECT student_id, first_name, last_name
FROM   greenwood_academy.students
WHERE  class = 'Form 3' AND city = 'Nairobi'
ORDER BY student_id;

Find all students who are in Form 2 OR Form 4:

SELECT student_id, first_name, last_name
FROM   greenwood_academy.students
WHERE  class = 'Form 2' OR class = 'Form 4'
ORDER BY student_id;

Range, Membership & Search Operators

To handle more versatile matching constraints, built-in SQL operators like BETWEEN, IN, and LIKE are usually used.

Range Filtering (`BETWEEN`)

Useful for narrowing down continuous datasets like dates or numeric boundaries:

-- Finding all exams that took place between 15th March 2024 and 18th March 2024.

SELECT result_id, student_id, subject_id, exam_date  
FROM   greenwood_academy.exam_results
WHERE  exam_date BETWEEN '2024-03-15' AND '2024-03-18'
ORDER BY exam_date;

Set Membership (`IN` / `NOT IN`)

Replaces repetitive multi-line OR statements:

-- Finding all students who live in Nairobi, Mombasa, or Kisumu using IN

SELECT first_name, last_name
FROM   greenwood_academy.students
WHERE  city IN ('Nairobi', 'Mombasa', 'Kisumu')
ORDER BY city;

-- Finding all students who are NOT in Form 2 or Form 3 using NOT IN.

SELECT student_id, first_name, last_name
FROM   greenwood_academy.students
WHERE  class NOT IN ('Form 2', 'Form 3')
ORDER BY student_id;

Pattern Matching (`LIKE` with Wildcards)

The % wildcard allows flexible string evaluation to search for prefixes or embedded substrings:

-- Finding all students whose first name starts with the letter 'A' or 'E' using LIKE.

SELECT student_id, first_name
FROM   greenwood_academy.students
WHERE  first_name LIKE 'A%' OR first_name LIKE 'E%'
ORDER BY student_id;

-- Finding all subjects whose subject name contains the word 'Studies'.

SELECT subject_name
FROM   greenwood_academy.subjects
WHERE  subject_name LIKE '%Studies%'
ORDER BY subject_name;

Aggregating Data with COUNT

Aggregation helps make sense of the larger scale of the data. Using the COUNT() function, we can calculate quick statistical summaries, such as performance thresholds:

-- Students currently in Form 3

SELECT count(*) AS form_3_students
FROM   greenwood_academy.students
WHERE  class = 'Form 3'; 

-- Exam results having a mark of 70 or above

SELECT count(*) AS scores_70_and_above
FROM   greenwood_academy.exam_results
WHERE  marks >= 70;

Conditional Logic with CASE WHEN

The CASE WHEN expression acts as an if-else statement directly inside the SQL query. This is useful for dynamically creating computed category labels on without changing the underlying stored data.

Generating Performance Descriptions

-- Exam results with a grade description

SELECT result_id, marks,
    CASE
        WHEN marks >= 80 THEN 'Distinction'
        WHEN marks >= 60 THEN 'Merit'
        WHEN marks >= 40 THEN 'Pass'
        ELSE 'Fail'
    END AS perfomance
FROM greenwood_academy.exam_results
ORDER BY marks;

Grouping Students by Seniority Level

-- Using CASE WHEN to label each student's level

SELECT student_id, first_name, last_name,
    CASE
        WHEN class IN ('Form 3', 'Form 4') THEN 'Senior'
        ELSE 'Junior'
    END AS student_level
FROM greenwood_academy.students
ORDER BY student_level;

Conclusion

This exercise simulates a realistic scenario for setting up and querying databases. It covers schema design, table modifications, and relevant classification using CASE WHEN. The task gives foundational knowledge to manipulating enterprise-level data.

The entire source code is structured cleanly into structured query files and tracked on my GitHub repository.

Thanks for reading! If you have any questions or alternate methods for querying this data, feel free to drop a comment below! Happy querying!

The Ultimate Step-by-Step Guide: Connecting Power BI to Cloud-Based & Local PostgreSQL

OSCAR MAINJE — Sun, 05 Jul 2026 21:38:57 +0000

Introduction

Whether your data is securely stored in a file on your desktop or is being streamed from a cloud-based platform on Aiven, establishing a connection to Power BI is a fundamental skill for the modern data analyst. Connecting Power BI to a database shouldn't feel like guesswork. This article provides a comprehensive, step-by-step guide for linking Power BI Desktop to two distinct environments: a local PostgreSQL instance localhost:5432 and a cloud-hosted Aiven PostgreSQL deployment secured by an SSL ca.pem Certificate.

Connecting Power BI Desktop to a Locally Hosted PostgreSQL Database

Before opening Power BI, it's important to ensure that the local database is running and note the credentials.
The credentials used should be as follows;

Host: localhost or 127.0.0.1
Port: 5432
Database Name: The specific name of your database for example postgres or your custom project database.
Username: Usually postgres
Password: The password you created when installing PostgreSQL on your laptop

Establishing the Connection in Power BI Desktop.

Launch Power BI Desktop.
On the Home ribbon tab, click Get Data > select More
Select Database from the left categories > click on PostgreSQL database > click Connect. As shown in the image below.

In the connection window, fill out the database credentials as follows:
Server: Type Host:Port, e.g., 127.0.0.1:5432
Database: Type the name of your local database e.g., postgres
Data Connectivity Mode: Select Import
Click OK as shown below.

Input your local database credentials

A credential window will pop up asking how you want to access the database.

Select the Database tab on the left margin.

Enter your local credentials:

User name: Type postgres (or your specific local username)
Password: Type your local database password.
Click Connect Because local connections do not typically use an SSL certificate, Power BI might show a warning message saying: The data source doesn't support an encrypted connection.
Simply click OK or Run Unencrypted to bypass this.

Since the data is residing on your own laptop, it does not travel across the internet, making an unencrypted local stream safe.

A Navigator window will open showing all the schemas and tables inside your local database. You can now pick the dataset you wish and proceed to transform in Power Query.

Connecting Power BI Desktop to a Cloud Hosted PostgreSQL Database on Aiven.

Power BI Desktop utilizes the underlying Windows Certificate Store to validate encrypted database links. Because Aiven uses its own to secure cloud data, Windows will block the connection as "Untrusted" unless we add it to the laptop's root directory.

To do this, we must first download the CA certificate from the Aiven console.
Open your web browser, navigate to your Aiven Console, and click on your running PostgreSQL service.
Stay on the Overview page and scroll down to the Connection information section.
Locate the row labeled CA Certificate and click the Download button next to it.

This will save a file named ca.pem directly to the laptop's Downloads folder.

Install it into the Windows Trusted Root Authorities.

To make this certificate trusted by Power BI for all connections, you must install it at the local machine.

In the search box, search for "Manage user certificates.
In the left-hand folders console, expand Certificates.
Right-click on the Trusted Root Certification Authorities folder > hover over All Tasks > click Import

The Certificate Import Wizard will open.

Click Next → Browse to find your file.
Crucial Step: In the file browser pop-up, change the file extension filter dropdown in the bottom right to All Files. Otherwise, your ca.pem file will stay hidden.
Navigate to your Downloads folder, select ca.pem, and click Open.
Click Next.
Ensure the option Place all certificates in the following store is selected and if it explicitly targets the Trusted Root Certification Authorities store.
Click Next.

Review the summary page and click Finish.

A pop-up confirming "The import was successful" will appear.
Close the console.

Establishing the Connection in Power BI Desktop

Now that your laptop recognizes Aiven as a trusted authority, you can complete the link securely.
Launch Power BI Desktop.

On the top Home ribbon, click Get Data → select More → choose Database → select PostgreSQL database.
Click Connect.

Input your parameters copied from your Aiven Console:

Server: Enter your Host name followed by a colon and the port number (e.g.,pg-123a4b5c-my-project.aivencloud.com:12345)
Database:Type defaultdb

Data Connectivity Mode

Select Import.
Click OK.

When the database authentication window pops up, click the Database tab on the left-side margin.
Input your database user details.

User name: avnadmin
Password: Paste your specific Aiven service password.
Click Connect

A Navigator canvas window will appear displaying your database contents.

Simply check the box next to your loaded table (jcars in my case) and click Transform Data to start transforming it within Power Query

In Conclusion ...

At the end of the day, a data analyst's value isn't just in making pretty charts but in building robust, dependable data infrastructure. Moving your pipelines onto PostgreSQL and securing them via trusted CA root certificates proves you understand high-level data security and database operations.
And that’s a wrap! You now have a fully functional, end-to-end data pipeline running seamlessly from a local environment or an encrypted cloud database straight into Power BI. No more connection timeouts, no more hidden SSL certificate rejections, and no more headaches.

Power BI Data Modelling Demystified: Models, Schemas, Relationships, and Joins

OSCAR MAINJE — Wed, 01 Jul 2026 12:31:04 +0000

Data Modelling in Power BI

Assume you are running a business that requires you to order and supply different products from different suppliers and sell them to different customers every day. To keep records of your business, you are likely to use tools such as Excel to store data for your suppliers, products, customers, and sales made on a daily basis in a table.

Having so much data in a table soon becomes problematic as the transactions grow into tens and hundreds of thousands. If this data is summarized in different tables, each storing data relating to one entity, then there's a need to establish the relationships between the different tables. This is where data modelling comes in.

The purpose of data modeling in Power BI is to create a structured, optimized, and meaningful representation of data to support effective analysis, reporting, and visualization. Data modeling is a critical step in the Power BI workflow that transforms raw data into actionable insights for decision-making and business intelligence.

A data model in Power BI is a logical representation of how data is structured and connected within the tool. It consists of:

Tables: Represent the data from one or more sources like Excel files, CSVs, or cloud services.
Relationships: Define how these tables are connected to each other.
DAX (Data Analysis Expressions): The formula language used to create custom calculations, i.e., measures and calculated columns

A well-designed data model is the foundation for creating reports and visualizations fast and should have the following features.

Scalability: Well-designed data models should handle more complex analysis as business needs grow without major changes.
Reduced Maintenance: Once the model is set up, it should be easier to maintain if it’s well organized and streamlined.
Proper Documentation: Its design should ensure that anyone working with it can easily understand how it works.

Why is Data Modeling Important in Power BI?

Efficient Data Exploration

Data Modeling helps create hierarchies and drill-down paths, allowing users to easily explore data, uncover insights and identify trends.

For example, hierarchy looks like this;
In a Sales Data set:
Country →County →City →Store →Product
or
In a Time Data set:
Year →Quarter →Month →Day.

Improved Performance

A well-designed data model makes data retrieval fast and efficient, whereas a poorly structured data model with redundant data or complex relationships can slow down queries and delay reports.

Accurate Reporting

Having a good data model ensures accuracy, consistency, and reliability. This helps in generating reliable reports, provides better insights, and leads to better decision-making. Proper data modeling helps eliminate errors and ensures that the data used for analysis is correct and dependable.

Types of Data Modeling

Relational Model: It prioritizes writing data efficiently without duplication. For example, a customer's address is only stored in one table, and linked to orders via IDs. This makes it great for applications but complex for analysts to run a simple report.

Dimensional Model: Uses Star or Snowflake schemas. It trades storage efficiency for query speed by duplicating and centralizing context (denormalization). For example, instead of querying multiple interconnected tables, an analyst looks at one central Sales Fact table linked to a Customer Dimension table to get sales by customer in seconds

The table below shows comparison between the relational and dimensional data models.

Fact Tables and Dimension Tables

What is a Fact Table?

A Fact table stores quantitative and numerical data which can be aggregated and analyzed. Fact table is a centerpiece of a data model and has information and has information about Transactions, Numerical measures like Sales amount, Quantity sold, Product cost and Foreign keys that links to dimension tables.

In a Fact table, each row represents a single transaction or event, and each column represents a measurable attribute. For example, a sales fact table may have columns for the product sold, quantity and the total sales amount.

Key Characteristics:

Measures or Metrics: Fact tables contain facts (quantitative data) such as sales amount, quantity sold, etc.
Foreign Keys: They have foreign keys that link to dimension tables, providing context for the facts.
Granularity: The level of detail or granularity of the fact table is defined during the design process (e.g., sales per day, per transaction).

Example:
Consider a retail business analyzing its sales data. The fact table might look like this:

ProductID, and StoreID are foreign keys linking to dimension tables.

What is a Dimension Table?

A dimension table in Power BI contains descriptive data. It provides context and additional details to the numerical data in a fact table. For Example: A product dimension table may have columns such as product name, category, description and supplier name. Dimension tables usually contain fewer rows, whereas Fact tables can hold large amounts of data that grows over time.

In a data model, a dimension table is connected to a fact table through a relationship. This allows Power BI users to analyze the fact table data by filtering, grouping and aggregating it based on the attributes in the dimension table.

Key Characteristics:

Descriptive Data: Contains descriptive data such as product names, customer names, dates, etc.
Primary Keys: Each dimension table has a primary key that corresponds to a foreign key in the fact table.
Attributes: Dimension tables include various attributes that describe the dimension.

Example:
Continuing with the retail business, dimension tables might look like this:
Product Dimension Table

Store Dimension Table

In these examples:
The Product Dimension Table provides details about each product.
The Store Dimension Table provides details about each store.

By linking these dimension tables to the fact table, you can generate insightful reports and analyses that provide a complete view of your business data.

Schemas

What is a Schema?

A schema is a collection of database objects, including tables, views, and indexes. It defines the structure of the data and the relationships between the different objects. In Power BI, a schema represents a logical grouping of tables that are related to each other.

Types of Schemas

There are two types of schemas: Star schema and Snowflake schema. Let's explore each of these in detail.

Star schema

Star Schema is a database design that separates data into fact tables(for summarizations) and dimension tables(for filtering and grouping).Its highly effective for Power BI as it optimizes the performance and usability by structuring data for easy reporting and visualization.

Explicit Measures are custom calculations created with DAX Formulas, whereas Implicit Measures are automatically generated by Power BI from columns that can be summarized such as sum or average without needing custom formulas.

A star schema may be preferable in some instances because it offers the following;

Simplified Querying: Star Schemas are easy to understand and implement. Their denormalized structure reduces the number of joins and required to retrieve data. This simplifies and leads to faster data aggregation and reporting.
Denormalization: It is the process of combining tables to reduce complexity and improve performance often by adding redundant data.
Flexibility: Star schemas are adaptable and easy to update when new data sources are added to a Power BI report.
Intuitive Analysis: Star schemas provide more intuitive and user-friendly experience for end-users of a Power BI report. The data is organized in a logical and easy to understand manner, making it easier for end-users to identify trends and patterns in the data.
Easier Maintenance: Having fewer tables and simple relationships between them, star schemas are easier to maintain and update over time.

An example of a model with a star schema design is shown below.

Snowflake schema

The Snowflake Schema is like the Star schema in terms of having a central fact table that everything passes through, but the main difference is that the Snowflake schema has normalized dimension tables as opposed to denormalized dimension tables for the Star schema.

The Star schema typically goes down two levels of data hierarchy while the Snowflake schema goes to a third level. The purpose of a Snowflake schema is to normalize the denormalized data in a Star schema.

In a snowflake schema, the relationships between tables form a pattern that resembles a snowflake, with the central fact table connected to multiple dimension tables and each dimension table potentially connected to additional sub-dimension tables.

An example of a model with a snowflake schema design is shown below.

Relationships in Power BI

In Power BI, relationships between tables are created to enable data to be combined across them. This is done using the following:

Primary and Foreign Keys: Ensures unique identifiers in fact tables match corresponding keys in dimension tables.
Cardinality: Defines the type of relationship between two tables, defining how data and filters flow between them.

Relationships Concepts

Primary keys
Primary keys are unique columns in a table that helps creates a relationship between two tables by sharing common values. The Primary key uniquely identifies each record.

Foreign keys
Foreign key is a column in one table whose values match the values of the primary key in another table.

In the above image, In the product dimension table, primary key is the product Id column, which is a unique identifier of a product or row in the products table. The product dimension table is connected to the fact Orders Fact table based on Product ID, so here the product Id column within the orders table acts as a foreign key.

Cardinality

Each relationship in a model is defined by a cardinality type. It refers to the number of unique values in one table related to the number of unique values in another table.

One to One (1:1)

This relationship occurs when one record in the first table is related to one and only one record in the second table. This is relatively uncommon in Power BI.

One to Many(1:*)

This occurs when a single record in one table relates to multiple records in another table, commonly used to link dimension tables(one side)to fact tables(many side).

Many to One(*:1)

This occurs when multiple records in a fact table relate to a single record in a dimensional table, which is commonly used in Power BI because it simplifies data aggregation such as sum or average and enhances filtering.

Many to Many (* : *)

This occurs when many records in the first table can be related to many records in the second table. This type of relationship is not directly supported in Power BI because it creates ambiguity in data aggregation and filtering, leading to performance issues and invalid results. Instead, such relationships require a bridge table to resolve the Many to Many connections.

Ambiguity: Unclear data aggregation due to multiple relationship paths.
Bridge Table: A table that links Many-to-Many relationships.

Cross Filter Direction

In Power BI, the cross-filter direction controls how filters propagate between visuals. Each relationship in a model defined with a cross-filter direction. It can be a single (Single directional) or both (Bi-directional).

Single

A single arrowhead represents a single-direction filter in the direction of the arrowhead. Here, only one of the tables in the relationship can filter data across to the other table. This means filtering data in one table affects the data shown in the other table, but filtering in the other table does not affect the data shown in the first table.

Bi-Directional

Bi-Directional cross-filtering enables them to apply filters on both sides of a table relationship(data can be filtered in either direction).This means filtering data in one table affects the data shown in the other table and vice versa. This type of filtering is less common and can degrade performance while potentially creating ambiguous filter paths. Cross-Filter options are dependent on the cardinality.

Active and Inactive Relationships

Active Relationships

Active Relationships are created in Power Bi Desktop’s Model View. To create one, drag and drop a common filed from one table to another table. Active relationships are visible in solid lines. These are default relationships used for filtering and calculations between tables ensuring data is linked for analysis.

Inactive Relationships

In Power Bi, inactive relationships are used when multiple relationships exist between tables, letting us specify which one should be used for certain calculations. Marking a relationship as inactive prevents it from being used by default, resolving issues like ambiguous paths and conflicting filters.

Joins

What are Joins?

Table joins can be used to compare or combine rows between tables. They compare tables based on one or more related columns. When the compared row values between two tables are identical, they are considered a match. The behavior that follows depends on the join type. Mastering joins is a valuable skill for the data cleaning process.

How to Merge Queries

So, how do you perform a join in Power Query? The easiest way to join tables in Power Query is by using merge queries. To start, go to the Home tab in the ribbon and navigate to the Combine section. There you will find the Merge Queries button. You have two options there:

Merge Queries performs a join on your current table and adds the result of this merge in the same query. This means you can easily see the impact of the merge in a single place.
Merge Queries as New performs the same join on your table but puts the result of the merge in a separate query. The benefit of this approach is that your starting query remains the same. This means that you can still reference that starting query for other purposes.

After choosing your desired way to merge queries, the below pop-up appears. This menu allows you to choose the Merge type you want to perform.

Types of Joins

Left Outer Join

One of the most common joins is the Left Outer Join or simply the Left Join. The left outer join returns all rows from the Left Table (table1). Then, by matching one or more column values with rows from table1 with similar column(s) from rows in table2, the join returns the matching values of the Right Table (table2).

Right Outer Join

The Right Outer Join returns all values from the Right Table (Table2) while only returning the matching values from the Left Table (Table1).
Its behavior is very similar to the Left Outer join, except that its base table is the Right table instead of the Left.

Full Outer Join

There are cases where you want to return all rows from both Table1 and Table2. In those cases, the Full Outer Join is your friend.

The Full Outer Join tries to match column values from Table1 with Table2 and if successful, puts them next to each other. The values of Table1 that don’t exist in Table2 get their own row. Any rows without a match in Table 2 return null values for these rows.

Similarly, the values of Table2 that don’t exist in Table1 also get their own row. For these rows, the missing columns from Table1 get null values.

Left Anti Join

The Left Anti Join only returns rows from the Left Table (Table1). Based on the key columns, the join searches for which values in Table1 do not exist in Table2. It then only returns the unique values from Table1.

Right Anti Join

The Right Anti-Join is the twin sister of the Left Anti-Join. It returns the rows from the Right Table (Table2) which are not present in the Left Table (Table1).

Inner Join

The Inner Join is a useful join that returns only those rows that have matching values in both the Left Table and the Right Table. This can be useful when creating tables for your data model. Imagine filtering down a transaction table to contain only those transactions you want to analyze, but your product table still contains all products from your database.

Self Join

A self-join is a join like all the others, but the table is joined with itself. In the Merge Queries interface, you can select the table to merge. When you want to do a self-join, you can simply fill in the same table twice.

The following images show the common types of joins in Power BI.

Conclusion
This article has provided an overview of data modelling in Power BI by exploring the types of modelling, different types of schemas, the relationships that exist within a schema and how to join tables in Power Query, covering standard joins like Inner, Left, Right, and Full Outer. The article also explores special joins such as the Self Join, along with ways to union tables.

It’s important to remember that each join type has its use case and that you should choose the one that best fits your needs. With these tools, you can now efficiently model data in Power BI.

Enjoy Power BI!

How Excel is used in Real-World Data Analysis

OSCAR MAINJE — Sat, 06 Jun 2026 12:03:10 +0000

What is Excel and who needs to know it?

Excel is a popular software that allows users to organize, format, and perform various calculations on data in a spreadsheet. Data in Excel is usually stored in rows or records that run horizontally from left to right and columns or fields that run vertically from top to bottom. The intersection of a row and column forms a cell wherein data is entered.

Excel is widely used in almost all industries to store, process, analyze, and visualize data. In finance, sales, and project management, for instance, it’s used to track invoices and forecast revenue. It’s even more important for those in careers that work daily with data, such as data analysts and scientists, marketing managers and business owners, in their day-to-day work.

What makes Excel even more popular are its collaboration tools like shared workbooks, which are valuable in business settings as they transform a single spreadsheet into dynamic and shared workspaces and improve operational efficiency.

Excel for Practical Data Analysis.

Having known what Excel is, we can now explore how it is used in real-world data analysis in 3 different industries and the most common features in Excel that facilitate that process.

Supply Chain and Logistics Management.

Managers in this industry can rely on Excel for inventory control to prevent product stockouts and track the shipping of products. In this case, conditional formatting can be used to automatically highlight a product in a certain color if the current stock falls below the preset threshold. Functions such as NETWORKDAYS() can be used to calculate the arrival dates for products on order.

Project Management.

Project managers can use Excel to make sure that complex projects are staffed adequately, executed within the budget, and delivered on time. Excel can be used to create a visual project timeline chart with fixed start and end dates. The function SUMIF() can be used, for instance, to calculate the total time an employee has been assigned across multiple projects and prevent overburdening one individual.

Human Resources Management.

HR managers can use Excel to manage employee data, analyze organizational costs, track overtime work, and use features such as PivotTables to ensure employee salaries across various departments are correct.

Formulas and Functions in Excel.

Formulas and functions are used in Excel to analyze data and manipulate text. Although technically different, the two terms are often used interchangeably.
Function: A built-in tool that is pre-programmed, e.g., SUM() or COUNT().
Formula: An equation written by the user to calculate a value in Excel. It always begins with an equal sign (=), e.g., =C4+B2

The following are the most common functions in Excel:

Math and Trigonometry functions.

Some functions in this category include the following;

SUM(): Adds its arguments.
SQRT(): Returns a positive square root.
ROUND(): Rounds a number to a specified number of digits.

The following image illustrates the use of the ROUND() function.

Text functions

These are the functions used to extract, clean, format, and manipulate text data.
Some functions in this category include the following:

TRIM(): Removes spaces from text.
PROPER(): Capitalizes the first letter in each word of a text value
UPPER(): Converts text to uppercase

The following image shows the use of the UPPER() function

Statistical functions

These functions are used to summarize, analyze and interpret data.
Some functions in this category include the following:

MIN()Returns the minimum value in a list of arguments
COUNT() Counts how many numbers are in the list of arguments
AVERAGEIF() Returns the average of all the cells in a range that meet a given criteria
AVERAGE() Returns the average of its arguments

The following image shows the use of the AVERAGE() function to calculate the average current price of 10 products

Conclusion

Having embarked on a journey of learning Excel as a tool for data analysis, I no longer see data just as a collection of numbers and texts with little meaning in real life. If analyzed well using tools such as Excel, data proves to have power to answer important questions and solve many operational challenges that businesses face from day to day. As I continue to develop my skills in Data Science, Analytics and AI, I now appreciate that even though there are more advanced tools like Python and Power BI, Excel will remain to be a tool that is widely used in the data field.

DEV Community: OSCAR MAINJE

I Built a Digital Notebook for a Kiosk Owner Who's Tired of Paper Records — Here is What I Learned

The problem I set out to solve

What it actually does

The part I'm most proud of: it thinks ahead for the owner

How I approached building it

What I'd build next

What this project taught me

From a Messy Spreadsheet to Business Insights: Analyzing a Hotel's Booking Data

The brief

Step 1: Staging everything as text

Step 2: Audit before you touch anything

Step 3: Cleaning, one problem at a time

Step 4: Building the production layer

Step 5: What the data actually says

Revenue is concentrated in the Suite, not the most-booked room

The Penthouse has a serious cancellation problem

The most profitable room has the least satisfied guests

Guests barely use paid extras — and one service massively outperforms the rest

Revenue is growing, but volatile and heavily dependent on one city - Nairobi.

Recommendations for management

What this project demonstrates

Building and Querying a Database: A Practical SQL Guide

Building the Database (DDL)

Table Modifications

Filling in the tables & Modifying the Database (DML)

Data Maintenance

Querying Data (Filtering with WHERE)

Range, Membership & Search Operators

Range Filtering (BETWEEN)

Set Membership (IN / NOT IN)

Pattern Matching (LIKE with Wildcards)

Aggregating Data with COUNT

Conditional Logic with CASE WHEN

Generating Performance Descriptions

Grouping Students by Seniority Level

Conclusion

The Ultimate Step-by-Step Guide: Connecting Power BI to Cloud-Based & Local PostgreSQL

Introduction

Connecting Power BI Desktop to a Locally Hosted PostgreSQL Database

Establishing the Connection in Power BI Desktop.

Input your local database credentials

Connecting Power BI Desktop to a Cloud Hosted PostgreSQL Database on Aiven.

Install it into the Windows Trusted Root Authorities.

Establishing the Connection in Power BI Desktop

In Conclusion ...

Power BI Data Modelling Demystified: Models, Schemas, Relationships, and Joins

Data Modelling in Power BI

Why is Data Modeling Important in Power BI?

Types of Data Modeling

Fact Tables and Dimension Tables

What is a Fact Table?

What is a Dimension Table?

Schemas

What is a Schema?

Types of Schemas

Star schema

Snowflake schema

Relationships in Power BI

Relationships Concepts

Active and Inactive Relationships

Joins

What are Joins?

How to Merge Queries

Types of Joins

How Excel is used in Real-World Data Analysis

What is Excel and who needs to know it?

Excel for Practical Data Analysis.

Formulas and Functions in Excel.

Conclusion

Range Filtering (`BETWEEN`)

Set Membership (`IN` / `NOT IN`)

Pattern Matching (`LIKE` with Wildcards)