Forem: melody mulei

Mastering the SQL Analytical Toolkit

melody mulei — Mon, 20 Apr 2026 06:20:35 +0000

As a budding data professional, I recently completed an intensive SQL Week 2 Assignment that pushed me from basic queries into more powerful territory. I worked with two real-world databases — nairobi_academy (school management) and city_hospital (healthcare appointments).

In this article, I’ll walk you through the key concepts I practiced, share practical queries, and explain why these skills matter in real jobs.

Why This Matters

Modern data roles don’t just require SELECT * FROM table. You need to clean data, combine multiple tables, analyze trends over time, and create meaningful rankings. This assignment covered exactly those real-world skills:

String & Number Functions → Data cleaning & formatting
Date & Time Functions → Temporal analysis
JOINS → Connecting related data
Window Functions → Advanced analytics without losing row-level detail
SET Operators → Combining results from multiple queries

Part 1: String Functions – Making Data Look Professional

String functions help clean and present data nicely.

Example Query (Uppercase + Lowercase + CONCAT):

SELECT 
    UPPER(first_name || ' ' || last_name) AS upper_name,
    LOWER(city) AS lower_city,
    CONCAT(first_name, ' ', last_name, ' is in Form ', form, 
           ' and comes from ', city) AS student_summary
FROM students;

Key functions practiced:
UPPER(), LOWER()
LENGTH()
LEFT() / SUBSTRING()
CONCAT()

Part 2: Number Functions – Doing the Math

SELECT 
    marks,
    ROUND(marks, 1) AS rounded_one_decimal,
    CEIL(marks) AS rounded_up,
    FLOOR(marks) AS rounded_down,
    ROUND(marks * 1.10) AS boosted_mark
FROM exam_results;

I also calculated summary statistics using COUNT, AVG, MIN, MAX, and SUM in a single query, very useful for reports.

Part 3: Date & Time Functions (PostgreSQL)

    first_name,
    date_of_birth,
    EXTRACT(YEAR FROM date_of_birth) AS birth_year,
    EXTRACT(MONTH FROM date_of_birth) AS birth_month,
    AGE(date_of_birth) AS age_in_years,
    TO_CHAR(exam_date, 'Day, DDth Month YYYY') AS formatted_date
FROM students;

Part 4: JOINS – The Heart of Relational Databases

This was the most exciting part.
INNER JOIN (Only matching records)
LEFT JOIN (All patients, even those without appointments)
RIGHT JOIN (All doctors)
Three-table JOIN for appointments + patient + doctor + prescription
Finding patients who never had an appointment:

SELECT 
    p.full_name,
    p.city
FROM patients p
LEFT JOIN appointments a ON p.patient_id = a.patient_id
WHERE a.appointment_id IS NULL;

Part 5: Window Functions – The Game Changer

This section took my SQL to the next level.
Window functions let you perform calculations across rows while keeping every detail.
Examples I wrote:
ROW_NUMBER() – Unique ranking
RANK() vs DENSE_RANK()
NTILE(3) – Dividing students into performance bands
AVG() OVER(PARTITION BY student_id) – Student average alongside each result
LAG() – Comparing current exam with previous exam

SELECT 
    result_id,
    student_id,
    marks,
    AVG(marks) OVER(PARTITION BY student_id) AS student_avg,
    LAG(marks) OVER(PARTITION BY student_id ORDER BY exam_date) AS previous_marks,
    marks - LAG(marks) OVER(PARTITION BY student_id ORDER BY exam_date) AS improvement
FROM exam_results;

Part 6: SET Operators (UNION, INTERSECT, etc.)

Learned how to combine results from different tables/databases cleanly.
-- Cities that exist in both school and hospital databases

SELECT city FROM students
INTERSECT
SELECT city FROM patients;

Key Takeaways & Advice

Working through these exercises helped reinforce several important SQL skills:

Cleaning and formatting text data
Performing numerical analysis
Working with dates and time
Combining tables with joins
Performing advanced analysis with window functions
Merging results using set operators

These skills form the foundation of real-world SQL work in data analysis, backend development, and data engineering.

Final Thoughts

SQL is more than just a query language,it’s a tool for discovering insights from data.

By practicing with realistic datasets like school records and hospital systems, we begin to see how SQL powers real applications.

Whether you're analyzing exam results or managing hospital appointments, mastering SQL opens the door to data-driven decision making.

How to Publish and Embed Power BI Reports on the Web Using IFrames – A Complete Step-by-Step Guide

melody mulei — Fri, 10 Apr 2026 10:13:50 +0000

Introduction
In today’s data-driven world, insights are only valuable when they are accessible. Creating a dashboard is just the beginning, the real impact comes from sharing it effectively.
In this guide, I’ll walk you through the complete process of publishing a Power BI report to the cloud and embedding it into a website using iframes — so anyone can interact with your insights directly from a browser.
I’ll use my Electronics Sales Assignment (built with queries, DAX, star schema modeling, currency conversion, KPIs, charts, and multi-page navigation) as a practical example.

What is Power BI?
Power BI is a powerful business intelligence tool developed by Microsoft that allows users to:
• Transform raw data into meaningful insights
• Build interactive dashboards and reports
• Share data visually with stakeholders
From data cleaning using Power Query to advanced calculations with DAX, Power BI provides an end-to-end analytics solution.

Why Publish and Embed Power BI Reports?
Publishing moves your report from your local machine to Microsoft’s cloud (Power BI Service), enabling:
• Scheduled data refreshes
• Easy sharing and collaboration
• Secure or public embedding on websites, blogs, intranets, or dashboards
Embedding with iframes enables you to bring fully interactive reports into any web page, without requiring a Power BI license for viewers in public mode.

Overview of the Publishing Process
Publishing a Power BI report involves four key steps:

Creating a workspace
Uploading and publishing your report
Generating an embed code
Embedding the report on a website Let’s go step by step.

Step 1: Create a Workspace in Power BI Service
A workspace is a collaborative environment where reports, dashboards, and datasets are stored and managed.
Steps:

Go to Power BI Service (https://app.powerbi.com)
Sign in with your account
On the left sidebar, click Workspaces
Click + New Workspace
Fill in: o Workspace Name (e.g., John Doe) o Description (optional)
Click Save

Step 2: Upload and Publish Your Report
Now that your workspace is ready, it’s time to upload your .pbix file.
Steps:

Open Power BI Desktop
Load your Electronics Sales report
Click Publish (top ribbon)
Select your created workspace
Wait for the confirmation message Alternatively (via Power BI Service):
Open your workspace
Click Upload → Browse files
Upload your .pbix file

Step 3: Generate the Embed Code
Once your report is published, you can generate a link or embed code.
Steps:

Open your report in Power BI Service
Click File → Embed report → Publish to web (public)
Click Create embed code
Copy: o The iframe embed code, or o The direct link

Step 4: Embed the Report into a Website
Now, let’s bring your dashboard to life on a website.

Steps:

Open your website’s HTML file
Paste the iframe code
Replace with your embed link
Save and refresh your website

Now your fully interactive report (with slicers, filters, drill-downs, and page navigation) is live on the web!

Key Insights

Power BI is not just about building dashboards—it’s about sharing insights effectively
Workspaces help manage and organize reports
Embedding dashboards makes insights accessible beyond Power BI users
Always consider data privacy before using public embed options
Combining Power BI with platforms like GitHub and blogs strengthens your data portfolio

Final Thoughts
Publishing and embedding Power BI reports transforms your work from static analysis into interactive, shareable insights. Whether you're presenting to management or showcasing your skills, this is a critical step in your data journey.

With this Electronics Sales project, you’ve not only analyzed data, you’ve learned how to deliver it professionally.

Understanding Data Modeling in Power BI: Joins, Relationships, and Schemas Explained

melody mulei — Tue, 31 Mar 2026 08:15:57 +0000

What is Data Modeling?
SQL Joins: The Foundation
Power BI Relationships
Fact Tables vs Dimension Tables
Schema Designs: Star, Snowflake & Flat Table
Common Modeling Issues & How to Fix Them
Where to Do Everything in Power BI
Summary & Key Takeaways

Data is powerful, but only when properly structured. In Power BI, data modeling is the crucial step that turns raw tables into fast, accurate, and insightful reports. Whether you're analyzing sales trends, healthcare records, or farm productivity, a well-designed model makes the difference between slow, confusing dashboards and trustworthy business intelligence.
This article explains data modeling in Power BI, covering SQL joins, relationships, fact vs. dimension tables, star/snowflake/flat schemas, role-playing dimensions, and common modeling issues, with clear explanations, real-life examples, and step-by-step instructions for implementing them.

What Is Data Modeling?

Data modeling is the process of organizing data into structured formats that define how tables relate to one another.
Think of it like designing a blueprint:
• It defines how data is connected
• It ensures efficient querying
• It improves data accuracy and reporting performance
In Power BI, data modeling happens after data is loaded and involves:
• Creating relationships
• Defining table structures
• Optimizing how data flows between tables
Power BI gives you two powerful ways to connect data:
• Joins (in Power Query) - physically combine tables
• Relationships (in Model view) - logically link tables without duplicating data

SQL Joins in Power Query

Joins are used in Power Query Editor to merge tables. Here are all the important types with practical examples using a Customers and Orders dataset.

Venn diagram showing the different SQL joins

1. Inner Join
a. Returns only records that exist in both tables.
b. Example: Show only customers who have placed orders.
2. Left Join (Left Outer)
a. Returns all records from the left table and matching records from the right.
b. Example: List every customer, even those who haven’t ordered yet (useful for retention analysis).
3. Right Join (Right Outer)
a. Returns all rows from the right table and matching rows from the left.
4. Full Outer Join
a. Returns all rows from both tables (with NULLs where no match exists). Example: Reconcile customer data from two different systems.
5. Left Anti Join
Returns rows from the left table that have no match in the right table.
Example: “Customers who never placed an order” — perfect for marketing campaigns.
6. Right Anti Join
Returns rows from the right table with no match in the left.
Example: Orders that are missing customer records (great for data quality checks).

Where Joins Happen in Power BI
In Power BI, joins are created in Power Query Editor:

Go to Transform data (Power Query Editor)
Select a table → Home → Merge Queries
Choose the second table and matching columns
Select the join type (Inner, Left Outer, Left Anti, etc.)
Click OK and expand the columns you need.

Power BI Relationships

Unlike SQL joins, Power BI typically uses relationships instead of merging tables. Relationships are logical links created in the Model view. They keep your model lightweight and performant.

Types of Relationships
• 1:M (One-to-Many) – Most common (One customer → Many orders)
• M:M (Many-to-Many) – Multiple records relate to multiple records. Requires careful handling
• 1:1 (One-to-One) – Rare, usually for splitting tables

Key Concepts
i. Cardinality
Tells Power BI how tables relate:
• One-to-Many
• Many-to-Many
ii. Cross-Filter Direction
Controls how filters flow:
• Single direction (recommended)
• Both directions (used cautiously)
iii. Active vs Inactive relationships:
• Active: Used automatically in visuals and DAX
• Inactive: Requires USERELATIONSHIP () function in DAX to activate (very useful for role-playing dimensions)

Where to Create Relationships in Power BI

Go to Model View
Drag and drop fields between tables OR
Click Manage Relationships
Define: o Columns o Cardinality o Cross-filter direction

Power BI Model view showing active relationships with cardinality indicators

Fact Tables vs Dimension Tables

A good data model separates data into:
Fact Tables
• Contain measurable data (e.g.,, sales, revenue)
• Large and transactional Holds foreign keys.
Dimension Tables
• Provide descriptive context (e.g., Customer Name, Product Category, Date, Region)
• Smaller and structured Holds primary keys.
Golden Rule: Facts tell you “what happened”. Dimensions tell you “who, what, when, where”.

Visual explanation of Fact vs Dimension tables

Data Schemas

1. Star Schema (Recommended)
One central fact table connected to denormalized dimension tables. Best for: Most Power BI projects- delivers excellent performance and simple DAX.

Basics of Modeling in Power BI: Fact Tables

2. Snowflake Schema
Dimension tables are normalized (split into multiple related tables). Use case: Large enterprise environments or when strict normalization is required.

Snowflake Schema structure

3. Flat Table (DLAT)
All data in one single table. Use case: Very small datasets or quick prototypes (not scalable for larger projects).

Role-Playing Dimensions & Common Issues

Role-playing dimensions occur when one dimension serves multiple purposes. Example: One Dim_Date table used for Order Date, Ship Date, and Delivery Date. Solution: Create multiple relationships (most inactive) and activate the correct one using DAX with USERELATIONSHIP().

Common modeling problems:

• Circular dependencies
• Ambiguous relationships (multiple paths between tables)
• Many-to-many confusion
• Overly wide fact tables
Fixes: Prefer single-direction relationships, use bridge tables for M:M, and regularly review your model in the Model view.

Step-by-Step Guide in Power BI

Load Data • Import datasets into Power BI
Clean Data (Power Query) • Remove duplicates • Merge tables (joins if needed)
Create Relationships • Use Model View • Define cardinality and direction
Optimize Schema • Use star schema where possible
Validate Model • Test filters and visuals

Final Thoughts

Data modeling is the foundation of every powerful Power BI report. Understanding joins, relationships, and schemas allows you to build models that are: Accurate, Scalable, and High-performing
As you grow in data analytics, mastering data modeling will set you apart, not just as someone who builds dashboards, but as someone who truly understands data.
Mastering data modeling turns basic reports into powerful decision-making tools. Start with Star Schema, understand SQL joins for cleaning, use relationships wisely for analysis, and always keep your model simple and performant.

You now have a complete toolkit to build professional Power BI models.
Happy modeling!

Forem: melody mulei

Mastering the SQL Analytical Toolkit

Why This Matters

Part 1: String Functions – Making Data Look Professional

Part 2: Number Functions – Doing the Math

Part 3: Date & Time Functions (PostgreSQL)

Part 4: JOINS – The Heart of Relational Databases

Part 5: Window Functions – The Game Changer

Part 6: SET Operators (UNION, INTERSECT, etc.)

Key Takeaways & Advice

Final Thoughts

How to Publish and Embed Power BI Reports on the Web Using IFrames – A Complete Step-by-Step Guide

Understanding Data Modeling in Power BI: Joins, Relationships, and Schemas Explained

Table of Contents

What Is Data Modeling?

SQL Joins in Power Query

Power BI Relationships

Fact Tables vs Dimension Tables

Data Schemas

Role-Playing Dimensions & Common Issues

Common modeling problems:

Step-by-Step Guide in Power BI

Final Thoughts