Forem: Odhiambo

A Look into SQL Query Basics

Odhiambo — Mon, 13 Apr 2026 13:02:28 +0000

SQL is essential for the modern data professional. It is incorporated into most tools that drive data architectures. As such, it is important to have a solid understanding of the formative fundamentals.

Data definition language (DDL) VS Data Manipulation Language (DML)

SQL queries have commands and underlying syntax which can be grouped into several categories. Two of these are data definition language and data manipulation language.

Data definition language is used to modify the structure of the objects that hold data. They consist of commands such as CREATE, ALTER, DROP among others.

Data manipulation language commands are used to change the actual data. These include commands such as INSERT, UPDATE or even DELETE.

Other categories include:

Data query language: used to retrieve data eg. SELECT
Data control language: manage access and permissions to data eg. GRANT, REVOKE.

Let us look at brief commands examples that form the foundational structure of SQL. As convention, SQL specific commands are written in uppercase while user defined instructions are written in lowercase. SQL queries should always end with a semicolon (;).

-- 1. creating the schema
CREATE SCHEMA school_schema;

This creates a schema called school. Think of schema as the folder that will contain all related work in one place. In the case of SQL, these include tables, views, indexes etc. The '--' are comments which are not executed as part of the SQL command.

--setting the search path for the session
SET search_path = school_schema;

This is used in Postgresql to instruct that all the commands we will run should be executed in the schema given.

--creating a students table
CREATE TABLE 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)
);

--Add a phone number column
ALTER TABLE students
ADD COLUMN
phone_number VARCHAR(20);

Renaming a column

--Rename credits column
ALTER TABLE subjects
RENAME COLUMN credits TO credit_hours;

Deleting a column

--Remove phone number column
ALTER TABLE students
DROP COLUMN phone_number;

Inserting data into a table

INSERT INTO students
VALUES
(1,'Amina','Wanjiku','f','2008-03-12','Form 3','Nairobi'),
(2,'Brian','Ochieng','m','2007-07-25','Form 4','Mombasa');

Updating a column in a record

UPDATE students
SET city = 'Nairobi' WHERE student_id = 1
RETURNING*;

The returning* keyword shows us the result of our query after we execute it. IF we don't explicitly give the WHERE clause, all the records will have their city column updated to Nairobi. The WHERE clause acts as a guardrail to prevent this.

Deleting a record

--Delete student with id 2
DELETE FROM students 
WHERE student_id = 2
RETURNING*;

Intermediate commands

--students neither in form 3 nor form 2
SELECT CONCAT(first_name,' ',last_name) AS student,class
FROM students GROUP BY class,student
HAVING class NOT IN ('form 3','Form 2') ORDER BY class;

Case statement to check condition first before assigning a value

--students classification as 'Senior' or 'Junior'
SELECT first_name,last_name,class,
CASE
    WHEN class IN ('Form 3', 'Form 4') THEN 'Senior'
    ELSE 'Junior'
END AS student_level
FROM students;

A Comprehensive Guide to Publishing Power Bi Reports

Odhiambo — Mon, 13 Apr 2026 07:02:03 +0000

Once you have selected your data source in a new session on Power Bi desktop application, load data into power query to transform it.

In case of a mutli-sheet workbook in Excel, select sheet(s) you intend to work with. They will eventually be made into tables in Power Bi. Then click on transform data.

Ensure to check for inconsistent data from column filters. Standardize any inconsistent data as well as blanks and pseudo-blanks. Using the column filter we are able to see categories for each column at a glance. This would help identify blanks, inconsistencies and missing values.

Click close and apply to load data into Power Bi.

Select the sheet from data pane and right click to rename it into something meaningful. Remember to save progress in location of our choice to avoid data loss.

1. Total sales from all products.

Since we already have sales for each individual product, we simply use SUM function to get the total for the SalesAmount column. Click on New Measure, give it a descriptive name and enter the function:

2. Profit Margin

Profit margin is calculated by profit/SalesAmount * 100. Since we already have the total value for sales amount calculated above, we will use SUMX to calculate the profit. Our new expression will be as shown:

The SUMX above encloses the profit for each row. It calculates profit for each row and also a sum at the end. For each row, this value is used to calculate the profit margin.

This will allow us to filter by country, product in the report view.

3. Change currency to USD

Currency is changed to USD from CAD for all columns that have currency using the formula below.

Shipping cost to USD

Sales Amount to USD

Profit to USD

4. Missing or incorrect values

Missing data values can be standardized as pseudo blanks for text columns or to be null for numbers.

In addressing missing numeric values, median is the best option as it accounts for skewed data. Mode is recommended for text, categorical or nominal categorical data. Mean is advised for data with normal distributed data.

5. Model data

Modelling our data involves creating dimensions tables and a fact table. Click on Transform data in Power Bi to open Power Query. Duplicate the existing table to the number of total tables (dimension tables and fact tables) needed.

Except for the fact table, columns not needed in each dimension table are deleted. Columns in the fact table are kept as is to use in the subsequent steps of creating unique identifiers for all dimension tables.

Click on Close & Apply to effect the changes and return to Power Bi.

Tip: Always keep a copy of the original table/data.

Date Table

To create a date table, click on the New Table command in Power Bi.

Enter the formula below to create the table:

CALENDARAUTO() generate a new date column based on all the dates in our data. To limit the use of possible unnecessary date columns that might exist in our data, eg. Date of Birth, we create new variables and determine the range our date should fall in. In this example, the MinDate looks for the earliest date an order was placed and the MaxDate returns the most recent date for delivery.

The filter is then used to give CALENDARAUTO() the date range to filter from based on these two values.

The other Date-related columns are derived from the date column returned by CALENDARAUTO() function.

Here is a view of our tables after the steps.

Creating unique identifiers

All our dimension tables will need unique identifiers to be linked to the fact table.

1. Create Unique ID in the dimension table

Click CustomerEmail from the customers table and select Remove Duplicates from the drop down.

Unique email identifiers remain.

From the Add Column tab, click on Index Column and the initiator of choice, in this case, Custom.

Define the desired index format and increment and click OK.

I rename the column to CustomerID, this is what we will use to join it in the fact table. I also change the type to text. This just as a personal preference for columns that eventually may include a lot of records.

NOTE: REPEAT THIS PROCESS FOR THE OTHER DIMENSION TABLES

2. Merge the Fields from the tables

Still in the Power Query editor, click on the fact table and select Merge Queries from the ribbon.

Select Left Outer Join with the fact table as the first table. You see from the image below we get all records matching. Click ok.

Click on the expand tables icon.

Deselect all column names except the CustomerID. Our new fact table will have this column included.

Our CustomerID is now included in the fact table.

Repeat for the other dimension tables.

The Relationship and view should be as shown

6. Report and Dashboard

Go to the report view and select visualization build from the side panel as needed. Add your key overview metrics from the different values. This will from the dashboard as shown.

Save your work and click on publish. Log in to a Power Bi account from the Power Bi Service online and select the option to create a workspace.

Give the workspace a name. Log in to the Power Bi account from your desktop application and click on publish. Choose the same workspace created earlier to publish the report to. You have now published your report.

Create a new page on the Power Bi desktop application and give it a relevant name for specific metrics to be shown.

This will form the individual reports of your work.

The published work should reflect on the online Power Bi service.

Embedding

To embed the report in another online document such as a webpage, click on File then select the Embed report then Website or portal.

Copy the resulting iFrame for embedding inside a html document.

Your Power Bi Report page will appear on the html document.

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

Odhiambo — Mon, 30 Mar 2026 16:37:52 +0000

Many times while working with relational data, there is need to get context information from different data tables. Understanding how to perform modelling when working with such situations is essential. Let us look at some data modelling concepts when working with Power Bi.

Joins

In Power Bi, joins are called Merge queries. This is when two tables are joined together depending on matching values from columns. At least one column in the two tables must have matching values eg. an id appearing both in users table and a similar matching id value appearing in subscriptions table. The names of the two columns do not need to be same, but the underlying values must match.

In most cases, this involves a primary key from one table and a foreign key in the other table. To perform joins in Power Bi, we select Merge queries command from the home tab.

Image from learn.microsoft.com

Several types of joins are available in Power Bi:

inner join: returns only matching rows from both the left and right tables

Image from learn.microsoft.com
left outer join: returns all the rows from left table but only the rows from the right table that have a match

Image from learn.microsoft.com
right outer join: returns all the rows from right table but only the rows from the left table that have a match

Image from learn.microsoft.com
full outer join: returns all the rows from both the left and right tables

Image from learn.microsoft.com
left anti join: returns only records from left table that do not have matching rows in the right table

Image from learn.microsoft.com
right anti join: returns only records from right table that do not have matching rows in the left table

Image from learn.microsoft.com
fuzzy merge: this is only supported over text columns. It aims to provide a more standardadized way of representing text data in a columns eg. where distinct names are misspelled eg Vollvo instead of Volvo. It it preceded by another join type, typically, left outer join.

Sample goal of fuzzy join from learn.microsoft.com
cross join: results in a Cartesian-like result from the two tables
you first select a table of interest then click on custom column command from the Add column tab on the ribbon.

image from learn.microsoft.com

Enter any name for the new custom column in the dialogue window that appears. In the Custom column formula* enter the name of the other table or query. Here it is called Colors

Image from learn.microsoft.com

Select ok and also expand the new column and click ok again to view final result.

Image from learn.microsoft.com

Relationships

There are 4 main types of relationships called cardinality
1. Many to one(*:1): It is the default. A column can have many instances of a value and only have one instance of the same value in the other table, known as a lookup table.
2. One to one(1:1): A columns can have only one instance of a value and also only one instance of the value in the other table.
3. One to many(1:*): A columns has only one instance of a value but can have many instances in the other table
4. Many to many(:): There are no unique constraints on values in the tables.

Active relationships in Power Bi are shown by solid continuous lines while inactive relationships are shown by dotted lines. Only one active path exists between tables and this is used to filter data for visuals.

Joins vs relationships

From the foregoing, we can see that joins physically merge tables while relationships only show linkages between tables.

Schemas

In Power Bi, schemas describe how your data is organized and structured. A well designed data model improves the performance of your queries as well as usability of in DAX.

A few concepts are worth noting when dealing with schemas in Power Bi:

Dimension tables: These tables describe the things being modeled such as products or places.
Fact tables: They store observations or events and contain dimension key columns.

1. Star schema

Tables are classified either as dimension of fact tables.

star schema from radacad.com

Normalizing tables in star schema involves storing data in fact tables by avoiding repetition.

2. Snowflake

It is some sort of a normalized version of star schema. Dimension tables are broken down further into sub-dimensions and end up forming branch-like structure.

Image from mmsqltips.com

3. Flat Table (DLAT)

Data is entered and consolidated into one single table. The table is usually very wide and has no relationship with other tables.

Image by Firat on medium.com

It is called DLAT(denormalized large aggregation table)

Role playing dimensions

These occur when a single dimension table can be used to filter a fact table. A fact table of sales, for instance, can be filtered based on the dates, such as order_date, shipping_date to show these specific analysis for dates.

Common modelling issues in Power Bi

Some common issues you might run into when working with Power Bi include the following:

Overloading the model: Trying to load everything instead of just what is needed from the slow. This might result in slow query processes. Reduce the number of columns used and remove any unnecessary field to improve the model.
Overusing the calculation to create new columns instead of just creating a new measure can lead to excessive use of RAM. It is recommended to use measures for aggregation and only use calculated columns when necessary.
Ignoring data types: Data should always be in the correct types to improve on the model. It is advisable to first transform the data in Power Query before loading.
Misconfiguring the table relationships can lead to circular dependencies or broken relationships. Particularly, using many-to-many relationships inaccurately can lead to double counts and eventual inaccurate data.

How Linux is Used in Real-World Data Engineering (For Beginners)

Odhiambo — Fri, 27 Mar 2026 10:15:59 +0000

Prerequisites

1. A working public IP address to an Ubuntu server on the cloud
2. Access to admin user credentials (password or private key for key-based authentication)

Linux For The Cloud

Data engineering is increasingly seeing the uptake of cloud infrastructure as essential in how firms manage their data. From storage, automation, and analysis, many tools and resources are now deployed in the cloud.

Working on Linux environments is an unspoken necessary skill for data engineers. There are tools and processes you will come across in the data engineering lifecycle for which you need to have skills on Linux. Linux servers are almost an intrinsic choice for cloud servers. Their ubiquity on the cloud is because they are lightweight and highly optimized for the cloud.

As such, navigating and running commands from the Linux terminal is the focus of this article. It gives you a beginner friendly feel to working from the terminal interface.

Accessing the server

You need to be able to access the server remotely to do routine management and maintenance tasks. Open a terminal and type the following command:

ssh root@143.110.225.134

replace the IP address after the @ sign with the actual server public IP address. The first time you log in to the server, your computer will prompt you to add the host to your local machine addresses of known hosts. Click yes to accept this step.

root@143.110.225.134's password: 
Welcome to Ubuntu 24.04.4 LTS (GNU/Linux 6.8.0-71-generic x86_64)

 * Documentation:  https://help.ubuntu.com
 * Management:     https://landscape.canonical.com
 * Support:        https://ubuntu.com/pro

 System information as of Wed Mar 25 14:54:56 UTC 2026

  System load:  0.0               Processes:             137
  Usage of /:   4.8% of 47.39GB   Users logged in:       4
  Memory usage: 20%               IPv4 address for eth0:143.110.225.134
  Swap usage:   0%                IPv4 address for eth0: 10.48.0.5

Expanded Security Maintenance for Applications is not enabled.

0 updates can be applied immediately.

Enable ESM Apps to receive additional future security updates.
See https://ubuntu.com/esm or run: sudo pro status


*** System restart required ***
Last login: Wed Mar 25 14:35:08 2026 from 41.99.105.22
root@ubuntu:~#

_NOTE: The numbers are fictional for demo only.
The line root@ubuntu indicates that we are logged in as user root and the name of the host/server machine is ubuntu. If you run the pwd command, you should see that you are inside the root folder as shown below.

root@ubuntu:~# pwd
/root
root@ubuntu:~#

Creating a user

Now let us create a user called odhiambo on this system. We use the _adduser_ command as shown below.

root@ubuntu:~# sudo adduser odhiambo

You will get a prompt to set and confirm the password of the user. You will also get several optional prompts including to enter the full name and phone details. We skip these optional steps by just pressing Enter at each prompt.

Giving the user administrative privileges

The user odhiambo does not have permission to run administrative tasks on the system. We then grant these privileges by adding the user odhiambo to the sudo group with the following command:

root@ubuntu:~# sudo usermod -aG sudo odhiambo

The first sudo is to allow us to run the command without running into permission denied issues

The usermod command is for modifying system user settings

The -a instructs the system to append the user with the G specifying the append is to a group. So the command is appending to a group

The group the user is being appended to is called sudo, which is the superuser group. This will give the user full access to all the privileges. However, they should invoke the _sudo_ keyword before running these commands

The username being modified is odhiambo

Logging in with our user account

We are now able to log on to the server using our user.

ssh odhiambo@143.110.225.134

We should see that the terminal should now indicate that we are logged in as odhiambo.

odhiambo@ubuntu:~$

A glimpse into the Linux file structure

For new Linux users, one of the most important things to have a fundamental understanding on is the Linux file structure. The top-most level folder structure typically looks as shown below:

odhiambo@ubuntu:~$ ls /
bin                home               mnt   sbin.usr-is-merged  usr
bin.usr-is-merged  lib                opt   snap                var
boot               lib64              proc  srv
cdrom              lib.usr-is-merged  root  swap.img
dev                lost+found         run   sys
etc                media              sbin  tmp
odhiambo@ubuntu:~$

Here we have used the ls command that lists all the files in a folder. The / sign indicates that we want to view the files in theroot ie _top most level_folder.While this file structure is not the focus of this article, I want to talk about the home folder.

Notice that our path on the terminal when we logged in as odhiambo included a tilde ~ ie odhiambo@ubuntu:~$. This is a shorthand to show that we are in our user's home folder, in this case, odhiambo's home folder.

User's home folder is different from the home folder at the root level. All user accounts created on the system will have a folder with their name inside the root level home folder eg. /home/odhiambo. So in actuality, the path odhiambo@ubuntu:~$ corresponds to odhiambo@ubuntu:~$/home/odhiambo

Handling files

A data engineer works a lot with files on the terminal. This includes making edits to configuration files, or making creating script from the terminal. Knowing the basics of file handling in data engineering in Linux server environment is important.

1. Creating files

The most basic way to create a file is using the touch command with the file name.

touch orders.txt

This will create a file called orders.txt

touch orders.txt Manual.md suppliers.txt

This will create multiple files

2. Deleting files

use rm to remove a file

rm orders.txt

3. Listing files

We have already hinted at this before. The ls command lists files and folders

ls

This will list all files and folders on a path. File and folders are distinguished based on the colors. Files also may have an extension.

ls -a

This lists all files and folders, including hidden files and folders.

4. Moving files

Moving a file does not leave a copy behind. We use the mv command followed with the source file followed by the destination path.

mv orders.txt completed/

This moves the orders.txt to a folder called completed inside the current path. If the folder does not exist, the command will fail.

5. Copying files

The cp command is used to copy files.

cp orders.txt completed/

This will copy the orders.txt file into a folder called completed in the current path. If the folder does not exist, the command fails.

scp orders.txt odhiambo@143.110.225.134:/home/odhiambo/orders/

This will copy the orders.txt file from our local machine to the server inside the orders folder on the path shown. Note: You will be prompted to enter the server password. The scp allows to securely copy files over the internet.

File permissions

The last thing we will look at is a brief overview of file permissions in Linux. Use the ls -l command to view file permissions.

odhiambo@ubuntu:~/$ ls -l
total 0
-rw-rw-r-- 1 odhiambo odhiambo 0 Mar 27 13:04 orders.txt
odhiambo@ubuntu:~/$

Permissions in Linux are read, write and execute with their corresponding values as 4, 2 and 1. So a value of 7 means a user has all the three permissions (4+2+1).

In addition, the ls -l commands lists permissions in a 3 part string, user, group and other. r, w and x correspond to the read, write and execute permissions.

The image below shows a summary of this.

photo from bytebytego.com

cover photo by pressfoto on freepik

How To Install Microsoft Power Bi On VirtualBox In Ubuntu

Odhiambo — Wed, 11 Mar 2026 08:40:35 +0000

Installing Microsoft Power Bi on Ubuntu is a fairly beginner-friendly process. We first start by spinning a virtual machine on our Ubuntu. VirtualBox is chosen for this case for its prominent use in the linux community.

1. Download Virtualbox

Go to the download page of virtualbox linux download page here and download the deb package for your Ubuntu distribution. To know the version of Ubuntu you are running open a terminal and type:

lsb_release -a

First ensure to update your package manager by typing sudo apt update on your terminal.

Install the .deb file you have downloaded. To do this, open the .deb download location and install it using sudo dpkg -i your_virtualbox_download.deb eg

sudo dpkg -i virtualbox-7.2_7.2.6-172322~Ubuntu~noble_amd64.deb

2. Download the Windows .iso image

Next, we need to download an iso image which we will use to run the windows environment on Virtualbox. Go to this link and download an iso image for Windows.

3. Create a virtual machine

With the Windows .iso image downloaded, launch the Virtualbox app from the app launcher. Click on New from the home dashbord.

Follow the series of prompts filling out the necessary details such as the name of the virtual machine. Select the .iso file you downloaded from the ISO Image dropdown.

Follow the prompts and enter all the necessary details. Remember your username and password as we will need them to log in to our Windows virtual machine. Assign a minimum of 4GB Ram in the Base Memory section. 6GB Ram is assigned here.

Click on Finish and allow it some time to install all the necessary requirements for your Windows environment.

4. Open the Windows Virtual Machine

After the Windows installation, enter your username and password when prompted. You now have access to the Windows virtual machine.

5. Install Power BI

Launch the Microsoft Store, search for Power BI and proceed with guided steps on the installation.

Alternatively, launch the Edge browser and search for Power Bi installation.

Open the application after installation. Congratulations, you now have Power BI on your Ubuntu system.

Tips

Always assign sufficient RAM to allow the resources to be well served.
Remember to keep your username and password secure.
Shut down the virtual machine from the Virtualbox window after use. Click on File then Close and select the Power off the machine

Forem: Odhiambo

A Look into SQL Query Basics

Data definition language (DDL) VS Data Manipulation Language (DML)

Intermediate commands

A Comprehensive Guide to Publishing Power Bi Reports

1. Total sales from all products.

2. Profit Margin

3. Change currency to USD

Shipping cost to USD

Sales Amount to USD

Profit to USD

4. Missing or incorrect values

5. Model data

Date Table

Creating unique identifiers

1. Create Unique ID in the dimension table

2. Merge the Fields from the tables

6. Report and Dashboard

Embedding

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

Joins

Image from learn.microsoft.com

Image from learn.microsoft.com

Image from learn.microsoft.com

Image from learn.microsoft.com

Image from learn.microsoft.com

Image from learn.microsoft.com

Image from learn.microsoft.com

Sample goal of fuzzy join from learn.microsoft.com

image from learn.microsoft.com

Image from learn.microsoft.com

Image from learn.microsoft.com

Relationships

Joins vs relationships

Schemas

1. Star schema

star schema from radacad.com

2. Snowflake

Image from mmsqltips.com

3. Flat Table (DLAT)

Image by Firat on medium.com

Role playing dimensions

Common modelling issues in Power Bi

How Linux is Used in Real-World Data Engineering (For Beginners)

Prerequisites

Linux For The Cloud

Accessing the server

Creating a user

Giving the user administrative privileges

Logging in with our user account

A glimpse into the Linux file structure

Handling files

1. Creating files

2. Deleting files

3. Listing files

4. Moving files

5. Copying files

File permissions

photo from bytebytego.com

cover photo by pressfoto on freepik

How To Install Microsoft Power Bi On VirtualBox In Ubuntu

1. Download Virtualbox

2. Download the Windows .iso image

3. Create a virtual machine

4. Open the Windows Virtual Machine

5. Install Power BI

Tips