DEV Community: Feddy Mwanjumwa

From Dirty Data to Business Insights: Building the Tembo Hotel Suites Database

Feddy Mwanjumwa — Sun, 13 Sep 2026 11:52:58 +0000

From Dirty Data to Business Insights: Building the Tembo Hotel Suites Database

Introduction

As part of my data analytics journey, I worked on a practical SQL project based on a fictional hotel called Tembo Hotel Suites.

The goal was not simply to write SQL queries. I wanted to take a messy hotel booking dataset, clean it, transform it into a properly structured relational database, and finally use SQL to answer business questions.

This project helped me understand how SQL fits into a real-world data analytics workflow — from raw data all the way to business insights.

The Problem

The original dataset contained hotel booking information such as:

Guest details
Phone numbers
Room information
Booking dates
Staff information
Salaries
Payment methods
Booking status
Services used
Guest ratings
Revenue information

However, the data was so messy.

Some of the problems included:

Inconsistent capitalization
Extra spaces
Different phone number formats
Missing values
Inconsistent room type names
Different date formats
Inconsistent payment methods
Inconsistent booking statuses
Numbers stored as text
Inconsistent formatting of monetary values

This made the dataset unsuitable for reliable analysis without cleaning it first.

The original Tembo Hotel dataset contained inconsistent formats, missing values, and other data-quality issues.

My Approach

I divided the project into five main stages:

Database and staging setup
Data cleaning
Database design
Loading clean data
Business analysis

This structure also helped me keep my SQL project organized and easier to understand.

1. Database and Staging Setup

I first created a staging area where I could import the original dirty dataset without modifying the raw data directly.

The main staging table was:

The columns included information such as:

booking_id
guest_name
guest_phone
guest_city
guest_nationality
room_no
room_type
room_rate_per_night
check_in_date
check_out_date
nights_stayed
staff_name
staff_department
staff_salary
payment_method
booking_status
total_amount
service_used
service_price
guest_rating

Keeping the raw data in a staging table allowed me to inspect and clean the data before moving it into the final database structure.

2. Data Cleaning

This was one of the most important parts of the project.

I inspected each column to identify inconsistencies and potential problems.

For example, guest names could contain inconsistent spacing or capitalization.

Room types also contained inconsistent values, such as abbreviated versions that needed to be standardized.

Phone numbers appeared in different formats, including local and international formats.

Dates were another major challenge because the dataset contained different formats, including values such as:

01-12-2024
05/05/2024
06-09-24
2023-06-12

Before performing analysis, these values needed to be standardized.

I also cleaned fields such as:

Payment methods
Booking statuses
Salaries
Service prices
Total amounts
Guest ratings

The purpose of this stage was to make the data consistent enough for reliable analysis.

3. Database Design

After cleaning the data, I moved from one large staging table to a more structured relational database.

I created separate schemas for different areas of the business:

hotel
finance
staffs
services
staging

I then created tables such as:

Guest

Stores information about hotel guests.

Room

Stores information about rooms and room types.

Booking

Stores booking information and connects guests, rooms and staff.

Payment

Stores payment-related information.

Staff

Stores information about hotel employees.

Service

Stores additional services offered by the hotel.

This approach reduced unnecessary duplication and made the database easier to maintain and query.

Relational structure connecting guests, bookings, rooms, staff, payments, and services.

4. Loading the Clean Data

Once the database structure was ready, I inserted the cleaned information into the appropriate tables.

I also performed verification queries such as:

SELECT *
COUNT(*)
Checking relationships
Checking whether records were successfully inserted

This allowed me to confirm that the transformation from the staging table into the final database had worked correctly.

5. Business Analysis

After the database was structured, I used SQL to answer business questions.

Some of the analysis included:

Revenue by Month

I analyzed how hotel revenue changed across different months.

This can help management identify strong and weak periods.

Room Performance

I looked at room types and their performance, including average guest ratings and booking activity.

Guest Locations

I analyzed the cities where guests were coming from.

This could help the hotel understand its customer base and potentially target marketing campaigns.

Staff Performance

I investigated which staff members handled the most bookings.

This provides a starting point for understanding workload distribution.

Department Revenue

I also explored revenue associated with different areas of the hotel.

Cancellation Rate

Cancellation rates were analyzed across room types to identify whether certain room categories experienced more cancellations.

Month-over-Month Revenue

I used SQL window functions such as LAG() to compare revenue between months.

This was particularly useful because it moved the project beyond basic aggregation into more advanced SQL analysis.

Using SQL window functions to compare monthly hotel revenue.

What I Learned

This project taught me that SQL is much more than writing SELECT statements.

I learned how important data preparation is before analysis.

Some of the biggest lessons were:

Dirty data can produce misleading insights.

If dates, numbers or categories are inconsistent, the final analysis may be inaccurate.

Staging tables are useful.

Keeping the original data separate from the cleaned data makes the workflow safer and easier to troubleshoot.

Database design matters.

Breaking a large dataset into logical tables makes the database more organized and reduces unnecessary duplication.

SQL can answer real business questions.

Queries become much more useful when they are connected to actual decisions a business needs to make.

Data analytics is a process.

The workflow is not simply:

Data → Dashboard

It is more like:

Raw Data → Cleaning → Transformation → Database Design → Analysis → Insights

Tools I Used

For this project, I used:

PostgreSQL — database management
DBeaver — SQL development and database management
SQL— data cleaning, transformation and analysis
GitHub — project versioning and portfolio documentation
Power BI — visualization and business intelligence

Project Structure

I organized the SQL project into separate files:

-README.md
-tembo_hotel_dirty.csv

-01_database_and_staging_setup.sql
-02_data_cleaning.sql
-03_database_design.sql
-04_loading_clean_data.sql
-05_business_analysis.sql

This structure makes it easier to follow the project from the original data through to the final analysis.

Final Thoughts

The Tembo Hotel Suites project was a valuable part of my data analytics learning journey.

Instead of working with a perfectly clean dataset, I had to deal with the kinds of problems that can appear in real-world data.

The project helped me practice:

Data cleaning
SQL transformations
Relational database design
Primary and foreign keys
Data validation
Aggregations
Window functions
Business analysis
GitHub project documentation

Most importantly, I learned that good analysis starts with good data preparation.

Power BI dashboard used to turn the analyzed hotel data into interactive business insights.

There is still a lot more I want to learn, but projects like this are helping me move from simply learning SQL syntax to using SQL to solve practical problems.

Project Repository

The complete SQL project, including the original dirty dataset and all SQL scripts, is available on my GitHub.

GitHub: https://github.com/FEDDY218/TEMBO_HOTEL-SUITES-DATABASE.git

My next goal is to continue building more projects that combine SQL, Python, Power BI and data analytics to solve real-world problems.

One project at a time.

Understanding Schemas, Relationships, and Data Modeling in Power BI.

Feddy Mwanjumwa — Sun, 13 Sep 2026 10:56:40 +0000

Introduction

Importing data into Power BI is only the beginning of the analytics process. A dashboard is only as reliable as the data model behind it. Poorly organized tables and incorrect relationships can lead to inaccurate calculations, duplicate values, and misleading reports.

One of the biggest lessons I learned while building dashboards was that data modeling is just as important as creating visualizations. In this article, I'll explain schemas, relationships, and data modeling in Power BI, and why they are important for the foundation of every successful report.

What Is Data Modeling?

Data modeling is the process of organizing tables and explaining how they relate to one another.

Instead of treating each table as an independent dataset, Power BI uses relationships to combine information from multiple tables into a single model.

A good data model has:

Report accuracy
Dashboard performance
Easier filtering
Better DAX calculations
Simpler report formation.

Understanding Fact Tables.
A fact table stores measurable business data.
These are the values you want to analyze, such as:

Sales
Revenue
Quantity Sold
Profit
Exam Scores
Attendance Records Fact tables usually contain many rows because they record business transactions or events.

Understanding Dimension Tables
Dimension tables describe the facts.
Instead of storing measurements, they store descriptive information.
Examples include:

Students
Subjects
Teachers
Schools
Dates
Counties

Dimension tables allow reports to answer questions like:

Which county had the highest average marks?
Which teacher teaches the most students?
Which subject has the highest pass rate?

What Is a Star Schema?
One of the most important data models in Power BI is the Star Schema.
In a Star Schema:

a single fact table sits at the center.
Multiple dimension tables surround it.
Every dimension connects directly to the fact table.

The structure looks like a star, that is why its called a star schema

This model provides:

Faster queries
Better performance
Simpler relationships
Easier DAX calculations
Cleaner dashboards

Whenever possible, Microsoft recommends using a Star Schema for Power BI models.

Understanding Relationships
Relationships tell Power BI how tables are connected.
Without relationships, tables remain isolated, making it impossible to combine information correctly.

Relationships are usually created using:
Primary Keys
Foreign Keys

For example:
Student_ID
Product_ID
Customer_ID
Order_ID

When relationships are correctly made, Power BI can filter data across multiple tables automatically.

Types of Relationships

Power BI supports several relationship types.

One-to-Many (1:*)

This is the most common relationship.

Example:

One student can have many exam results.

One customer can place many orders.

One-to-One (1:1)

Each record in one table matches exactly one record in another table.

This relationship is less common.

Many-to-Many (:)

Both tables contain duplicate values.

Although Power BI supports many-to-many relationships, they should be used carefully because they can create ambiguity and unexpected calculations.

Common Data Modeling Mistakes

While learning Power BI, I encountered several common problems:

Importing tables without relationships.
Using incorrect data types.
Creating many-to-many relationships unnecessarily.
Leaving duplicate values in key columns.
Connecting dimension tables directly to each other instead of through the fact table.

Correcting these issues significantly improved the accuracy of my dashboards.

Best Practices

When building Power BI models, consider the following recommendations:

Use a Star Schema whenever possible.
Keep fact and dimension tables separate.
Ensure key columns contain matching data types.
Remove duplicate values from lookup tables.
Avoid unnecessary many-to-many relationships.
Give tables and columns meaningful names.
Hide technical key columns that report users don't need.

Following these practices results in cleaner reports and easier maintenance.

Many beginners focus only on charts and visualizations.
However, dashboards are built on data models.

A good model has:

Accurate calculations
Reliable KPIs
Better report performance
Easier report maintenance
Consistent business insights Without proper modeling, even attractive dashboards can produce misleading results.

Final Thoughts

Learning schemas, relationships, and data modeling completely changed the way I do Power BI projects. I now understand that creating a dashboard is much more than dragging charts onto a canvas. The real work is usually in organizing the data correctly.
By understanding fact tables, dimension tables, star schemas, and relationships, I can build dashboards that are accurate and easier to maintain. These skills have strengthened my Power BI foundation and prepared me for more advanced topics such as DAX optimization, snowflake schemas, and enterprise-scale data models.

If you're beginning your Power BI journey, spend time learning data modeling. A strong model is the foundation of every successful dashboard.

Mastering SQL DDL, DML, and Data Querying by Building a School Database.

Feddy Mwanjumwa — Tue, 28 Jul 2026 14:15:44 +0000

Introduction

Databases are the backbone of almost every modern application, from school management systems and banking platforms to e-commerce websites and healthcare systems. While learning SQL, one of the best ways to understand how databases work is by building one from scratch.

As part of my SQL learning journey, I completed a project for Greenwood Academy, a fictional secondary school in Nairobi. The objective was to design the school's database, populate it with data, and write SQL queries to retrieve useful information.

This project gave me practical experience with database design, data manipulation, and querying using PostgreSQL.

Project Objective

The objective of the project was to build a relational database capable of storing student, subject, and examination information while practicing the core SQL concepts used in real-world database management.

The project involved:

Creating a database schema
Creating multiple related tables
Modifying table structures
Inserting records
Updating existing data
Deleting unwanted records
Querying data using different SQL operators
Using aggregate functions
Applying conditional logic with CASE WHEN

Designing the Database Structure (DDL)

Populating the Database (DML)
After creating the tables, the next step was inserting data.

Sample records representing students, school subjects, and examination results were inserted into the database.

I also learned how to maintain data by performing common database operations such as:

Updating incorrect records
Correcting student information
Removing cancelled examination results These tasks strengthened my understanding of SQL's Data Manipulation Language (DML).

Retrieving Information with SQL Queries
Once the database was populated, I explored different ways of retrieving information.

Some of the queries included:

Finding all students in Form 4
Displaying only female students
Retrieving subjects from the Sciences department
Filtering examination results above a certain mark
Finding students from specific cities
Searching names using the LIKE operator

I also combined multiple conditions using:

AND
OR
BETWEEN
IN
NOT IN These operators make SQL extremely powerful for filtering and analyzing data.

Using Aggregate Function
Another important concept introduced during the project was aggregate functions.

I used COUNT() to answer questions such as:

How many students are currently in Form 3?
How many examination results scored 70 marks or higher?

Aggregate functions help summarize large amounts of data and are commonly used in reports and dashboards.

Applying Conditional Logic with CASE WHEN

One of the most interesting parts of the assignment was using the CASE WHEN statement.

Instead of displaying only numerical marks, SQL was used to classify student performance into meaningful categories:

Distinction
Merit
Pass
Fail

I also categorized students as either:

Senior
Junior

based on their class level.

This demonstrated how SQL can transform raw data into information that is much easier to understand.

Skills I Practiced

This project strengthened my understanding of several important SQL concepts, including:

Database Design
PostgreSQL
SQL DDL
SQL DML
Table Relationships
Data Filtering
Search Operators
Aggregate Functions
CASE WHEN Statements
Data Management
Query Writing

More importantly, I gained confidence in solving practical database problems instead of only studying SQL syntax.

Challenges I Faced
Like many beginners, I encountered several challenges while working on the project.

Some of these included:

Remembering the correct SQL syntax.
Knowing when to use different filtering operators.
Understanding how ALTER TABLE modifies existing structures.
Writing efficient queries that returned the correct results.

Although these challenges slowed me down initially, solving them helped reinforce my understanding of SQL.

Key Lessons Learned

This project taught me that building a database involves much more than storing data.

A well designed database should make it easy to:

Organize information efficiently.
Maintain data accuracy.
Retrieve information quickly.
Support reporting and decision-making.

I also learned that SQL is not just a programming language it is a tool that transforms raw data into meaningful information for businesses and organizations.

Conclusion

Building the Greenwood Academy database was an excellent exercise that reinforced the fundamentals of SQL and relational databases.

From designing tables and managing records to querying data and applying conditional logic, every stage of the project reflected tasks that database professionals perform in real world environments.

This experience has strengthened my confidence in SQL, and I look forward to applying these skills to more advanced projects involving database optimization, joins, stored procedures, and business intelligence tools.

If you're also learning SQL, I would love to hear about the projects you're working on and the concepts you're exploring.

Happy coding!

Connecting Power BI to PostgreSQL using Aiven Cloud to work on a project.

Feddy Mwanjumwa — Mon, 20 Jul 2026 13:41:46 +0000

Introduction.

Power BI is one of the most popular tools for transforming raw data into interactive dashboards and reports. Many beginners start by importing Excel files, big organizations often store their data in relational databases such as PostgreSQL.

Connecting Power BI directly to PostgreSQL allows you to analyze data from a centralized source, reduce manual data imports, and create reports that can be refreshed whenever the database changes.

In this article, I'll take you through the process of connecting Power BI Desktop to a local PostgreSQL database.

What is PostgreSQL?
PostgreSQL is a powerful, open-source relational database management system (RDBMS). It is widely used because it is:

Free and open-source
Reliable and secure
Highly scalable
Supported natively by Microsoft Power BI Because of these features, PostgreSQL is an excellent choice for storing and managing business data.

Prerequisites
Before connecting Power BI to PostgreSQL, I ensured I had the following installed:

Aiven Cloud
Deabever
PostgreSQL
Power BI Desktop
A PostgreSQL database containing the tables I wanted to analyze.

Data Import into Aiven (PostgreSQL)

The dataset was imported into an Aiven-hosted PostgreSQL database using DBeaver. The process involved:

I created a PostgreSQL service on Aiven Cloud and downloaded the SSL certificate from Aiven.I connected DBeaver to the Aiven PostgreSQL database using:

Host
Port
Database name
Username
Password
SSL certificate

I created a table to store the JCars Logistics dataset.
I Imported the CSV dataset into the PostgreSQL table using DBeaver's Import Data wizard.
Lastly I verified that all records were successfully imported by querying the table.

Step 1: Open Power BI Desktop
Launch Microsoft Power BI Desktop.

From the Home ribbon, select:

Home → Get Data

A window containing all supported data sources will appear.

Step 2: Choose PostgreSQL Database

In the Get Data window:

Search for PostgreSQL Database.
Select it.
Click Connect.

Power BI will open the PostgreSQL connection dialog.

Step 2: Choose PostgreSQL Database

In the Get Data window:

Search for PostgreSQL Database.
Select it.
Click Connect.

Power BI will open the PostgreSQL connection dialog.

Step 3: Enter Your Database Details

Provide your PostgreSQL connection information.

For a local PostgreSQL server, the details are typically:

An example
Server localhost:5432 or 127.0.0.1:5432
Database postgres (or your database name)

Step 4: Authenticate

Power BI will ask for your PostgreSQL credentials.

Enter :

Username
Password

Then click Connect.

If the credentials are correct, Power BI will establish a connection to your PostgreSQL database.

Step 5: Select the Tables

After connecting successfully, the Navigator window will display the available tables.

Select the tables you want to import into Power BI.

You can either:

Load the data directly into Power BI, or
Select Transform Data if you want to clean or modify the data first.

Step 6: Transform the Data (Optional)

If you choose Transform Data, Power Query Editor opens.

Power Query allows you to:

Change data types
Rename columns
Remove duplicates
Handle missing values
Filter rows
Split or merge columns

Preparing the data before building reports helps improve accuracy and keeps your model organized.

Step 7: Build Your Report

Once the data has been loaded, you're ready to create visualizations.

Power BI offers many built-in visuals, including:

Bar charts
Line charts
Pie charts
Tables
KPI cards
Maps
Slicers

These visuals help transform raw database records into meaningful insights that users can explore interactively.

This process may come with alot of common connection issues.
If Power BI cannot connect to PostgreSQL, check the following:

Is the PostgreSQL service running?
Is the server name correct?
Is the port number correct?
Is the database name correct?
Are the username and password correct?
Has the PostgreSQL driver been installed?
Is a firewall blocking the connection?

Most connection problems can be resolved by checking these settings.

Benefits of Connecting Power BI to PostgreSQL

Using PostgreSQL as a Power BI data source offers several advantages:

Centralized data storage
Improved data consistency
Faster reporting
Reduced manual imports
Easier collaboration
Support for scheduled refreshes

Instead of repeatedly importing spreadsheets, reports can be built directly from the database.

Conclusion
Connecting Power BI to PostgreSQL is a valuable skill for anyone working in data analytics. It allows you to access data directly from a relational database, prepare it for analysis, and create interactive dashboards that can support informed decision making.

Whether you're working on personal projects or organizational data, learning to connect Power BI to PostgreSQL is an important step toward building efficient and professional reports.

How Excel is used in Real World Data Analysis- How deep, intresting and useful Excel is in the Tech space.

Feddy Mwanjumwa — Sat, 06 Jun 2026 20:26:15 +0000

Introduction.

My first encounter with Microsoft Excel was about 8 years ago in primary school. At the time, I only used a few basic features such as entering data into cells. To me, Excel seemed like nothing more than a digital spreadsheet where one could write some data figures like exam results.

Fast forward to 2026, and my perspective has completely changed. As I began learning Excel more seriously as part of my data analytics journey, I quickly realized that it is far more powerful than I had imagined. Beyond storing data, Excel can perform calculations, automate repetitive tasks, analyze large datasets, and help users make critical decisions.

What once looked like a simple spreadsheet application has proved itself to be a very useful tool used by students, businesses, researchers, accountants, and data analysts around the world. In this article, I share some of the key lessons and discoveries from my first week of learning Excel and how they have changed the way I view this software.

Understanding Excel at a deeper level.

One of the first things I learned was how Excel organizes information using rows, columns, and cells. This might look simple, but understanding how data is structured is important because almost every task in Excel depends on it.

My Introduction to Data Cleaning.

One thing that i found intresting to me during my first week of learning Excel was data cleaning. Before this course, I assumed that data collected from surveys, businesses, or other sources was always ready for analysis. However, I learned that raw data often contains errors, duplicates, missing values, and inconsistencies.

Excel has several tools that help clean and prepare data before analysis. Tools such as Find and Replace, Remove Duplicates, Text to Columns, and filtering make it easier to identify and correct problems within a dataset.

I came to understand that data cleaning is one of the most important steps in the data analysis process. Even the most advanced analysis can produce misleading results if the data is inaccurate or poorly organized. Learning about data cleaning gave me a new appreciation for the amount of work that happens before meaningful conclusions can be made from data.

I also learned how to format cells by changing fonts, colors, borders, and number formats. Proper formatting makes spreadsheets easier to read and gives them a more professional appearance.

My First Experience with Excel Formulas.

The most exciting part of learning Excel was discovering formulas. Instead of performing calculations manually, Excel can calculate values automatically.

Some of the first formulas I learned include:

SUM() for adding values
AVERAGE() for finding the mean
MAX() for finding the largest value
MIN() for finding the smallest value

These functions demonstrated how Excel can save time and reduce human error when working with data.

Sorting and Filtering Data.

Another feature that has the ability to sort and filter data. Sorting allows data to be arranged in ascending or descending order, while filtering helps display only the information that meets specific order

For example, in a dataset containing employee records, filtering can be used to display only employees from a particular department or those with a specific marital status. This makes it much easier to work with large amounts of information.

Final thoughts.

After just one week of learning Excel, I can already see how much depth the tool has beyond what I initially thought. What once felt like a simple spreadsheet application is now revealing itself as a powerful platform for working with data in a structured and efficient way.

This early stage has shown me that small skills like organising data, using formulas, and cleaning datasets are the foundation of much larger concepts in data analysis. I now understand that mastering Excel is not about memorizing functions, but about learning how to think logically about data.

Overall, this first week has completely changed how I view Excel. What seemed basic at first now feels like an essential skill that I will keep building on throughout my learning journey.

Share your journey on how you viewd excel when learning it for the first time, i would love to hear from you too!!!

What i learnt about Excel after a week of data science classes- How deep, intresting and useful Excel is in the Tech space.

Feddy Mwanjumwa — Sat, 06 Jun 2026 20:26:14 +0000

Introduction.

Understanding Excel at a deeper level.

My Introduction to Data Cleaning.

I also learned how to format cells by changing fonts, colors, borders, and number formats. Proper formatting makes spreadsheets easier to read and gives them a more professional appearance.

My First Experience with Excel Formulas.

The most exciting part of learning Excel was discovering formulas. Instead of performing calculations manually, Excel can calculate values automatically.

Some of the first formulas I learned include:

SUM() for adding values
AVERAGE() for finding the mean
MAX() for finding the largest value
MIN() for finding the smallest value

These functions demonstrated how Excel can save time and reduce human error when working with data.

Sorting and Filtering Data.

Final thoughts.

Overall, this first week has completely changed how I view Excel. What seemed basic at first now feels like an essential skill that I will keep building on throughout my learning journey.

Share your journey on how you viewd excel when learning it for the first time, i would love to hear from you too!!!