DEV Community: Kigen Tarus

SQL ARTICLE

Kigen Tarus — Sun, 12 Apr 2026 12:30:22 +0000

Understanding DDL, DML, and SQL Operations in Practice

DDL vs. DML

In SQL, commands are broadly divided into two categories: Data Definition Language (DDL) and Data Manipulation Language (DML).

DDL defines and structures the database. It includes commands like CREATE, ALTER, DROP, and RENAME. These commands shape the schema, tables, and columns.
DML works with the actual data inside those structures. It includes INSERT, UPDATE, DELETE, and SELECT. These commands add, modify, remove, or query records.

The difference is clear: DDL sets up the “blueprint” of the database, while DML fills and manages the “content.”

Applying CREATE, INSERT, UPDATE, and DELETE

In the Nairobi Academy assignment:

CREATE was used to build the schema (CREATE SCHEMA nairobi_academy) and tables (students, subjects, exam_results).
INSERT populated the tables with 10 students, 10 subjects, and 10 exam results.
UPDATE modified specific records, such as changing Esther Akinyi’s city from Nakuru to Nairobi and adjusting exam marks.
DELETE removed unwanted data, like cancelling exam result ID 9.

These commands show the lifecycle of data: creation, insertion, adjustment, and removal.

Filtering with WHERE

The WHERE clause is the backbone of SQL queries. It filters results based on conditions:

= finds exact matches (e.g., students in Form 4).
> or < compares values (e.g., marks greater than 70).
BETWEEN checks ranges (e.g., marks between 50 and 80).
IN and NOT IN test membership (e.g., students from Nairobi, Mombasa, or Kisumu).
LIKE searches patterns (e.g., names starting with “A” or “E”).

This flexibility makes WHERE essential for precise data retrieval.

CASE WHEN for Transformation

The CASE WHEN statement adds logic to queries, transforming raw data into meaningful categories. For example:

Exam results were labeled as Distinction, Merit, Pass, or Fail based on marks.
Students were classified as Senior (Form 3 & 4) or Junior (Form 1 & 2).

This feature turns numbers and codes into human‑readable insights, making reports more useful.

Reflection

Working through this assignment highlighted how SQL is both technical and practical. The DDL tasks were straightforward—building tables felt like laying a foundation. The DML tasks required more attention, especially ensuring data consistency when updating or deleting records. The most interesting part was using CASE WHEN, because it showed how SQL can go beyond storage and retrieval to provide analysis and interpretation. The challenge was remembering the exact syntax for altering and renaming columns, but once mastered, it felt empowering to reshape the database structure mid‑stream.

Publishing and Embedding Power BI Reports

Kigen Tarus — Sun, 05 Apr 2026 13:43:48 +0000

By: Tarus Kigen

To publish a Power BI report and embed it into a website, you’ll use Power BI Service to upload your report, generate an embed code, and paste it into your site. This makes your interactive dashboards viewable directly in a browser.

# 🖥 Introduction to Power BI Publishing
Power BI is Microsoft’s business analytics tool that lets you create interactive reports and dashboards. Once you’ve built a report in Power BI Desktop, you can publish it to the Power BI Service (app.powerbi.com) and share it with others. One powerful feature is embedding reports into websites, allowing stakeholders to interact with live data without needing Power BI installed.

# 📌 Step 1: Create a Workspace
A workspace is a collaborative area in Power BI Service where reports and dashboards are stored.

Go to app.powerbi.com and sign in.
On the left panel, click Workspaces → Create a workspace.
Name your workspace and configure access permissions.
Save it.

# 📌 Step 2: Upload and Publish Your Report
Once your workspace is ready, upload your report from Power BI Desktop.

In Power BI Desktop, click File → Publish → Publish to Power BI.
Select your workspace.
The report will appear in your online workspace.

# 📌 Step 3: Generate the Embed Code
Now you’ll create the code snippet that allows embedding.

In Power BI Service, open your report.
Click File → Publish to web.
A dialog box will appear with an embed code (iframe HTML).
Copy the code.

Important: Publish to web makes your report public. Anyone with the link can view it, so avoid using confidential data.

# 📌 Step 4: Embed the Report on a Website

Open your website’s HTML editor.
Paste the embed code where you want the report to appear.
Save and refresh your site.
The interactive Power BI report will now be visible to visitors.

# 🔑 Key Insights

Workspaces organize reports and control access.
Publishing moves your report from Desktop to the cloud.
Embed codes allow integration into websites, but they make reports public.
For secure embedding, use Power BI Embedded or organizational sharing instead of “Publish to web.”

✅ With these steps, you can take your Power BI dashboards beyond the app and make them accessible directly on your website, enabling interactive data exploration for your audience.

Feel free to leave any questions in the comments. Thank you for your time.

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

Kigen Tarus — Sun, 29 Mar 2026 15:24:42 +0000

By Tarus Kigen
Student Analyst*

Hello there. My name is Tarus, I thought Power BI was just about dragging colorful charts onto a canvas. I was wrong.

After getting lost in a mess of incorrect totals and weird filters, I realized the most important thing isn't visuals—it's data modeling. If your model is wrong, nothing works. So, I spent my week breaking down joins, relationships, and schemas. Let me share what I learned in simple terms.

What is Data Modeling?

Imagine building a house. You don't throw bricks, wood, and glass into a pile and call it a home. You organize them into walls, floors, and a roof. Data modeling is the same: organizing your raw data into a logical structure that Power BI can understand quickly and correctly.

Good data modeling = Fast reports + Correct numbers + Happy boss.

SQL Joins vs. Power BI Relationships (Key Difference!)

This confused me the most. In SQL databases, joins physically combine two tables into one new table. In Power BI, relationships leave the tables separate but "connect" them temporarily when you make a visual.

Join (Power Query): Permanently merges columns from two tables.
Relationship (Model View): A flexible link that respects table independence.

You use joins when cleaning data in Power Query. You use relationships when building models in Model View.

All SQL Joins Explained (As I Learned in Power Query)

I practiced these using Merge Queries in Power Query Editor (Home → Combine → Merge Queries). Here is my cheat sheet using two imaginary tables:

Table A: Students (StudentID, Name)

Table B: Grades (StudentID, Score)

1. INNER JOIN

"Give me students who have grades."

Result: Only rows where StudentID exists in both tables.
Real life: Listing only active employees who already completed training.
Diagram: Two overlapping circles → only the middle.

2. LEFT OUTER JOIN

"Give me ALL students, plus grades if they exist."

Result: Every row from left table (Students). Grades = NULL if missing.
Real life: Customer list with order amounts (show customers even if they never ordered).
Diagram: Left circle completely + overlapping middle.

3. RIGHT OUTER JOIN

"Give me ALL grades, plus student names if they exist."

Result: Every row from right table (Grades). Name = NULL if missing.
Real life: Transaction list showing unknown user IDs. (Honestly? You can just swap tables and use LEFT JOIN).

4. FULL OUTER JOIN

"Everyone and everything, matched where possible."

Result: All rows from both tables. NULLs where no match.
Real life: Merging two customer lists from different acquisitions.
Diagram: Both circles fully merged.

5. LEFT ANTI JOIN

"Students who have NO grades."

Result: Only rows in left table with no match in right.
Real life: Finding products that were never sold.
Diagram: Left circle minus the middle overlap.

6. RIGHT ANTI JOIN

"Grades with no student record."

Result: Orphaned records in the right table.
Real life: Detecting database integrity issues.

Where to make joins in Power BI:

Power Query Editor → Select first table → Merge Queries → Select second table → Choose join kind.

Power BI Relationships (Model View)

After loading tables, go to Model View (left sidebar). Drag a field from one table to another to create a relationship. Here is what each setting means:

Cardinality (1:M, M:M, 1:1)

1:M (One to Many): One product has many sales. This is 90% of your relationships. One row in the "one" side filters many rows on the "many" side.
1:1: One user has one passport. Rare. Use if splitting a wide table into two thin ones.
M:M (Many to Many): Students have many classes, classes have many students. Warning: Use carefully, or totals will double.

Cross-Filter Direction

Single (Default): Filters flow from "one" side to "many" side. You pick a date table, it filters sales.
Both: Filters flow both ways. Only use when you know what you are doing (otherwise, ambiguous paths occur).

Active vs. Inactive Relationships

You can have multiple relationships between two tables (e.g., Sales table has OrderDate and ShipDate both linked to a Date table). But only one active at a time. Use USERELATIONSHIP in DAX to activate an inactive one.

Fact vs. Dimension Tables (The Golden Rule)

After my week, I finally understood this:

Fact Table	Dimension Table
Contains measurements (Sales Amount, Quantity)	Contains descriptions (Product Name, Customer City)
Changes often (every transaction)	Changes slowly (customer address)
Has foreign keys (ProductID, CustomerID)	Has primary keys (ProductID)
Example: `Sales`	Example: `Products`, `Customers`

Rule: Fact tables are at the "many" side of relationships. Dimensions are at the "one" side.

Schemas: Star, Snowflake, and Flat Table (DLAT)

1. Star Schema (The Winner)

One fact table in the middle, dimension tables directly connected like a star.

Why I love it: Simple, fast, easy for beginners.
Use case: Most business reports (sales, inventory, HR).

2. Snowflake Schema

Dimensions are further normalized into sub-dimensions (e.g., Product → Category → CategoryType).

Why avoid as a beginner: Slower, more complex.
Use case: Huge enterprise databases where storage is critical.

3. Flat Table (Denormalized, aka DLAT – "Don't Look At That")

One giant table with everything (fact + dimensions combined).

Why not: Huge file size, repeating data, difficult filtering.
Use case: Simple exports for small data (<100k rows). I started here and regretted it.

Role-Playing Dimensions

A date table that filters sales by OrderDate, then by ShipDate, then by DueDate. Same dimension table, used many ways.

How to create: Build one Date table, create inactive relationships for secondary dates. Use measures like:

Sales by Ship Date = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[ShipDate], Date[Date]))

Common Modeling Issues I Faced (And Fixed)

Bidirectional filters causing ambiguity → Set cross-filter to Single.
Many-to-many giving wrong totals → Add a bridge table (e.g., StudentClass).
Blank rows appearing in slicers → Fix foreign key mismatches (orphan rows).
Circular dependencies → Remove redundant relationships.

Step-by-Step: Where to Create These in Power BI

Task	Where in Power BI
Merge two tables (JOIN)	Power Query Editor → Merge Queries
Create a relationship	Model View → Drag field to field
Change cardinality	Model View → Manage Relationships → Edit
Activate/inactivate relationship	Model View → Click line → Uncheck "Active"
View schema	Model View (zoom out to see all tables)
Create a flat table	Power Query → Append Queries (stack) or Merge (widen)

Final Advice

Start with a star schema: one fact table, a few dimensions. Use 1:M relationships with single filter direction. Avoid many-to-many. Use joins only in Power Query to clean data (e.g., adding lookup values), then use relationships for reporting.

After one week, my reports no longer lie. Yours will too. Happy modeling.

*— Tarus Kigen, Student Analyst.