DEV Community: Gracemunyi

Understanding PostgreSQL The Practical Way: From Database Structure to Data Querying

Gracemunyi — Sun, 12 Apr 2026 15:35:51 +0000

Week 3 of my Data Engineering journey was all about hands-on PostgreSQL. Here’s a simple summary of what I learnt, from building database structures to managing and transforming data with SQL queries.

Database structure (Schema / structure design)

Creating tables using CREATE TABLE
Defining columns and data types

Data management (DML)

INSERT → adding data
UPDATE → modifying data
DELETE → removing data

Data transformation (querying logic)

CASE WHEN → converting raw values into categories
WHERE → filtering specific data sets
Operators like =, >, BETWEEN, IN, and LIKE

1. What are DDL and DML in SQL?

In SQL, commands are generally divided into Five main categories: Data Definition Language (DDL), Data Manipulation Language (DML), Data Query Language (DQL), Transaction Control Language (TCL), and Data Control Language (DCL)**.

Today, we cover the first two: DDL and DML

DDL (Data Definition Language): It is used to define, shape and manage the structure of a database. This includes creating, altering, and deleting schemas and tables.

DML (Data Manipulation Language): It deals with the data inside the database. It is used to manage the data within the tables e.g. adding, updating, or removing records.

2. DDL (Data Definition Language):

We create schema and tables structure here.
Commands mostly used here include:

CREATE : Used to build things inside an SQL database To create a table, we we will need column name, data type, constraints (rules like Not Null, unique)
ALTER: Used for changing or correcting the structure of a table. You can add or remove columns here from an already created table instead of deleting the whole table first to create a new updated one.
DROP: Used to delete the whole table or just one column
TRUNCATE: Used to removes all rows and data inside a table but retains the structure of the table.

Additional commands such as Int, varchar, date, decimal will also be used in defining the column types.

-Varchar: used to refer to texts, strings characters columns e.g Grace Munyi.
-Int : used to refer to integers, numbers e.g 1,2, 34,65,100
-Decimal : Used to refer to values that are not in whole number format e.g currency 3563.75, 56926.77 etc
-Date : used to refer to calendar date columns e.g 2026-04-12

How I created a Schema, Schema Path and Tables

1) Creating a Schema

-- Creating a schema if it doesn't exist

CREATE SCHEMA IF NOT EXISTS training;

2) Set a path to the created schema above

-This is done instead of having to add the schema name in the table names that you will create. E.g. instead of creating students table called training.students, your name will simply be students.

set search_path = trainingdb;

3) Create tables

-- Creating a table called students inside the Schema.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade VARCHAR(10),
    enrollment_date DATE
);

4) Alter a table

We are adding a column into the students table created

ALTER TABLE students 
ADD COLUMN email VARCHAR(100);

5) DROP Whole table or One Column

To drop the whole students table created.

DROP TABLE students;

-- To drop just one column in the students table created e.g enrollment_date column

ALTER TABLE students
DROP COLUMN enrollment_date;

6) RENAME a Column

-- If we want to rename one column e.g email column added earlier to student_email

ALTER TABLE students
RENAME COLUMN email to student_email;

7) TRUNCATE

-- If we wanted to clear the students table by deleting the data that was in it, we will use Truncate.

TRUNCATE TABLE students;

DML (Data Manipulation Language)

Used to manage the data within the tables i.e. adding, updating, or removing records.

1) INSERT data

To insert data in the column names created

INSERT INTO students (name, age, grade)
VALUES ('Alice', 20, 'A', 'Alice@gmail.com');

2) UPDATE data

To update a variable in the table e.g Change Alice's grade from an A to B.

UPDATE students
SET grade = 'B'
WHERE name = 'Alice';

3) DELETE data

To delete a student record from the table.

DELETE FROM students
WHERE name = 'Alice';

Please remember; When using UPDATE and DELETE always include the WHERE command too to specify the specific column/s you want affected. Otherwise, without it, every row gets affected.

These commands helped me fully manage both the structure and the data in my database.

3. How filtering with WHERE works

The WHERE clause is used to filter records based on specific conditions. It allowed to retrieve only the data i needed.
Commands used together with WHERE include; =, >, BETWEEN, IN, LIKE)

Here we use;

SELECT * From Table name
WHERE column name, filter condition

Examples of operators:

1) = (equals)

SELECT * FROM students
WHERE grade = 'A';

2) > (greater than)

SELECT * FROM students
WHERE age > 18;

3) BETWEEN

SELECT * FROM students
WHERE age BETWEEN 18 AND 22;

4) IN

SELECT * FROM students
WHERE grade IN ('A', 'B');

5) LIKE (pattern matching)

SELECT * FROM students
WHERE name LIKE 'A%';  -- names starting with A

WHERE clause is very important because it helps narrow down results efficiently.

4. How CASE WHEN helps in transforming data

The CASE WHEN statement allows us to create conditional logic inside SQL queries. It helps transform or categorize data thus making the data easier to understand and analyze.

Example 1:

SELECT name, marks, grade,
case
    WHEN marks >= 80 then 'Distinction'
    WHEN marks >= 60 then 'Merit'
    WHEN marks >= 40 then 'Pass'
    ELSE 'Fail'
End as Performance
from students;

In this example, students are grouped into categories based on their marks with a new column created called Performance.

Example 2:

SELECT name, age,
    CASE
        WHEN age < 18 THEN 'Minor'
        WHEN age BETWEEN 18 AND 25 THEN 'Young Adult'
        ELSE 'Adult'
    END AS age_group
FROM students;

In this example, students are grouped into categories based on their age with a new column created called Age_group.

5. Reflection

• Every SQL statement must end with a semicolon ( ; )
• Text values must always be in single quotes - 'Nairobi'
• Numbers do NOT need quotes - WHERE marks > 70
• Always use WHERE with UPDATE and DELETE - without it, every row gets affected
• Dates are written as 'YYYY-MM-DD' - e.g. '2024-03-15'
• BETWEEN is inclusive - BETWEEN 50 AND 80 includes 50 and 80 themselves
• IN uses brackets and commas - IN ('Nairobi', 'Mombasa')
• LIKE patterns - 'A%' starts with A, '%Studies%' contains Studies
• CASE WHEN always ends with END - don't forget it!
• Give your CASE WHEN result a name using AS

How to Publish and Embed Power BI Reports on a Website: For Beginners

Gracemunyi — Sat, 04 Apr 2026 11:52:22 +0000

My next update is a simplified guide to publishing a Power BI report on the Web. I will walk you through every step from creating your first workspace to embedding a live interactive report on any website using an iframe.

This guide is written for a complete beginner. I assume you have never published a Power BI report before and may not even know what a workspace is yet. Every term and step will be explained in detail with a screenshot.

By the end of this article you will know how to:

Understand what Power BI is and what you need before starting
Create a workspace in the Power BI Service
Upload and publish your report to the cloud
Generate an embed code from Power BI
Paste that embed code into any website using an HTML <iframe>

What is Power BI?
What You Need Before You Start
Step 1 — Sign In to the Power BI Service
Step 2 — Create a Workspace
Step 3 — Upload and Publish Your Report
Step 4 — Embed the Report on Your Website and Generate the Embed Code
Step 4 — Make it Responsive
Step 6 — Delete or Revoke an Embed Code
Key Takeaways

What is Power BI?

Power BI is a data visualization tool owned by Microsoft that allows you to connect and extract data from various data sources such as databases, excel, web etc., in order to clean and transform the data then analyze it using interactive charts, graphs, and dashboards.. This is to answer the necessary business questions/goals. People can click, filter, and explore the dashboard live.

The dashboards can then be:

Shared privately among organization employees
Published publicly on the internet
Embedded directly into a website: which is what this guide is covering.

The two parts of Power BI you must know

Power BI has two parts: Power Bi the desktop App and Power BI Service

Part	What it is	What you use it for
Power BI Desktop	A free Windows app you download and install in your laptop	Building and designing your report
Power BI Service	A website at `app.powerbi.com`	Publishing, sharing, and embedding your report

What is a .pbix file in Power BI?

You must always save the data work you have been working on in Power BI Desktop. The data file saved will be in a format called *.pbix". Just like a saved Excel file format is ** .xlsx"". This is the file you upload to the cloud or share. If a colleague sends you a Power BI report by email it will almost always be a .pbix file e.g sales-report.pbix.

Steps to build a simple practice .pbix file

Open Power BI Desktop → click Enter data → load some data e.g from excel → click Load → click the bar chart icon on the right > create some visualizations → go to File → Save as → save it as Your_name_practice_report.pbix.

That is enough as an upload file to get you started on the uploading process.

What You Need Before You Start

Before starting the upload process, make sure you have all of these ready:

Requirement	Why you need it	Where to get it
Power BI Desktop	To build and save your reports	Download free from Microsoft
Microsoft work or school account	Personal Gmail or Outlook accounts cannot publish reports	Provided by your employer or school
Power BI Pro license	Required to publish to workspaces and share reports	Included in many Microsoft 365 plans, or activate a free 60-day trial
A `.pbix` report file	The report you want to publish	Built in Power BI Desktop

Step 1 : Sign In to the Power BI Service

The Power BI Service is the cloud website where publishing and embedding happens. You cannot design the reports here. Its only used for managing and sharing them with a larger audience team without requiring them to also download the Power Bi Desktop App.

How to sign in

Open your web browser
Go to https://app.powerbi.com
Click Sign in
Enter your work or school Microsoft email address — for example yourname@yourcompany.com
Enter your password and complete any two-factor authentication your organization requires.

Getting an error on sign-in? Make sure you are using a work or school account, not a personal Microsoft account. If your company uses Single Sign-On (SSO), you may be redirected to your company login page first — that is normal.

Once signed in you will land on the Home screen. On the left side you will see a vertical sidebar with icons labelled Home, Create, Browse, and Workspaces.

Step 2: Create a Workspace

What is a workspace?

A workspace is a folder in the Power BI cloud where your reports, datasets, and dashboards live. It is where you organize and share your work.

By default Power BI gives every user a personal area called My workspace. However, Reports stored in "My workspace" cannot be embedded on a website. Thus, you must publish your report to a proper named workspaces for embedding to work.

	My workspace	A named workspace
Who can see it	Only you	You and anyone you invite
Can embed reports	No	Yes
Can share with others	No	Yes
Requires Pro license	No	Yes

Steps to create the Workspace

1. In the left sidebar, click the Workspaces icon. Workspaces appear right above my Workspace as shown in the screenshot below

A panel will slide out showing any existing workspaces.

2. At the bottom of the panel, click + New workspace

3. A settings panel will open on the right side of your screen. Fill in the following fields:

Workspace name eg. your name, or project description like Sales Reports 2026 or Marketing Dashboard
Leave all other settings at their defaults for now.

4. Click Save

Your new workspace now appears in the left sidebar under Workspaces. Click on it to open it — it will be empty, but the next step fixes that.

Step 3 : Upload and Publish Your Report

Publish from Power BI Desktop to the web service (Most Recommended) as it is thefastest approach

1. Open your report in Power BI Desktop
2. Sign in to Power Bi. Click Sign In in the top right corner and enter the same email and password you use for the Power BI Service.

2. Click the Home tab in the ribbon at the top of the screen. Make sure you are on the Home tab and not on another tab like Insert or View.

3. Click the Publish button on the far right side of the Home ribbon.

4. A dialog box will appear titled "Publish to Power BI" asking you to select a destination

5. Click on the workspace you just created : e.g Your name or Sales Reports 2026

Choose your named workspace from the list. The report will be published there and will be ready for embedding.

Please remember: Do not select My workspace. If you publish to My workspace the report cannot be embedded. Always choose your named workspace.

6. Click Select

7. Power BI Desktop will show a progress bar, then a green tick with the message "Success!"

8. You can click Open your-report-name in Power BI in the success dialog to jump straight to the report in your browser, or close the dialog and continue with this guide.

9. Confirm the report is there by refreshing Power Bi Service then Click my workspaces. The uploaded report will appear.

Step 4 : Embedding the report on a website.

Power BI can generate a piece of HTML code called an iframe that you paste into your website's code. This tells the browser to fetch and display the live Sales Reports 2026report, allowing anyone who visits your website to view and interact with the report.

Power BI offers several ways to embed a report. Here is what each one means:

Option	Who can view it	Best used for
Publish to web (public)	Anyone on the internet, no login required	Public websites, blogs, news articles
Embed for your organization	Only people signed in with your company account	Internal tools, company intranets
SharePoint Online	People with access to your SharePoint site	SharePoint intranet pages
Website or portal (secure)	Users authenticated via your app	Custom web applications

For this guide we will use Publish to web (public): the simplest and the right choice when learning.

Please Note: "Publish to web" makes your report visible to every person on the internet with no password and no login. Never use this with real business data, financial figures, personal information, or anything confidential.

Generate the Embed Code called an iframe

1. Make sure your report is open in the Power BI Service. If it is not, go to your workspace and click on the report name to open it.

2. Click File (top-left area of the report look for the menu bar).

3. In the dropdown, Hover over Embed report and a sub menu will appear to the right

4. Click Publish to web (public)

A dialogue box will appear with a warning, explaining that the report will be visible to anyone on the internet. Read it carefully.

5. Then click Create embed code

Step 5: Making the Embed Responsive

If a dialog pops up requesting you to contact the admin for access to enable publishing, close the dialog box then repeat 1,2 and 3.

4. Click Website or Portal

5. Copy the Iframe link that appears under HTML to paste on a website box

6. Go to your PC Desktop and Create a Folder. Name it Deploying or Publishing

7. Open your Visual Studio code App to the Welcome page

8 Click Open Folder and select the Publishing folder you created.

9 Once inside the folder, Navigate to the left side where the name Publishing appears, then Click New Open File

10 Name it ** Sample.html** or Index.html

11 Open the Index.html file

12 Go to W3Schools on your browser W3Schools HTML Tutorial and open it

13 Copy the whole code listed under Example

14 Paste it in the VS Code file you created named index.html and Save it by Clicking Ctrl + S

15 Go to the publishing folder that you created in your PC. An Explorer file called Index should be appear.
16 Double click it. It will open in your browser. Copy the URL that appears. Format is
file:///C:/User/User/Desktop/Publishing/Index.html

17 Open a new Tab on your browser and paste the URL. Then click Enter. . Our goal is to replace the This is a paragraph text with your actual Power BI Sales Report 2026*.

18 GO BACK to your Visual Studio Text editor you named Index.html. Then Delete line 9 of the W3School code that you paste there.ie <p>This is a paragraph.</p>

20 Under line 9, Paste the HTML iframe code you had copied in POINT 5

21 Save the Changes by clicking CTRL+S

22 Go back to your browser from point 17 and refresh. Your Power BI Report should be loading.

24 Click Sign In and log in using your Power BI Service log in details as you have not yet given public access to your Power BI report.

25 Report will then load shortly. Below should be the type of view that you get.

STEP 6 You can Delete or Revoke an Embed Code

You can take a report offline at any time. Once you delete an embed code the report immediately stops showing on every website that uses it — no need to edit any HTML.

Steps to revoke an embed code:

Open the report in the Power BI Service
Click File in the top-left menu bar
Hover over Embed report
Click Publish to web (public)
In the dialog that appears, click "Manage embed codes"
A list of all embed codes you have ever created will appear
Find the one you want to remove and click the delete icon (trash can) on the right
Confirm the deletion

Key Takeaways

#	Key point
1	Power BI Desktop is for building reports. Power BI Service at `app.powerbi.com` is for publishing and embedding them.
2	You can publish Power BI reports in the web for easier accessibility to a wider audience.
3	You must publish to a named workspace, not My workspace, for embedding to work.
4	Use Publish from Power BI Desktop, or Upload in the browser, to get your `.pbix` file into the cloud.
5	Generate your iframe code via File → Embed report → Publish to web (public) or Website & Publish.
6	Publishing to web (public) means anyone on the internet can access it without any login required
7	Never embed real or sensitive data/reports like company reports using Publish to web. Tt is visible to the entire internet with no password. Always publish as Embed for your organization, meaning only people signed in with your company account
8	You can revoke any embed code at any time from the Manage embed codes screen, which immediately takes the report offline everywhere it was embedded.

THE END

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

Gracemunyi — Wed, 01 Apr 2026 09:45:56 +0000

This is a complete beginner guide covering:

A) SQL Joins & Power BI Relationships

SQL Joins

Power BI Relationships

B) Fact vs Dimension Tables, Star Schema, Snowflake Schema, and Common Concepts

Fact Tables

Dimension Tables

Star Schema

Snowflake Schema

C) Modeling Issues

Common Modeling Issues

Diagrams

SQL Code

Step-by-step Instructions

Data Modeling Explained

Lets say, a retail company has three tables: Sales, Customers, and Products. We require to model the data by organizing data tables and defining how they connect to each other so that every sale is linked to the right customer and the right product thus be able answer business questions accurately.

1. SQL Joins

A join combines rows from two tables based on a shared column. Before creating relationships in Power BI, understanding joins helps you know what data you are working with.

We use two simple tables throughout all join examples below.

Example

Table A — customers

id	name	country
1	Maria	Germany
2	John	USA
3	Georg	USA
4	Martin	Germany
5	Peter	USA

Table B - orders

order_id	sales	customer_id
1001	$35	1
1002	$15	2
1003	$20	3
1004	$10	6

Note: Peter and Martin have no orders. Order 1004 has customer_id 6
which does not exist in the customers table.

INNER JOIN:Matching rows only

Returns only rows where a match exists in both tables. Peter, Martin,
and Order 1004 are all excluded.

Venn diagram

INNER JOIN = centre match only

SQL syntax

SELECT c.id, c.name, o.order_id, o.sales
FROM customers AS c
INNER JOIN orders AS o
  ON c.id = o.customer_id;

-- Only rows matching in BOTH tables returned

Result of merged tables after executing INNER JOIN

c.id	c.name	o.order_id	o.sales
1	Maria	1001	$35
2	John	1002	$15
3	Georg	1003	$20

Use INNER JOIN when you only want customers who have actually placed
an order.

LEFT JOIN: All from left, matched from right

Returns all rows from the left table. Where no match exists, NULL
is shown.

Venn diagram

LEFT JOIN = entire left circle

SQL syntax

SELECT c.id, c.name, o.order_id, o.sales
FROM customers AS c
LEFT JOIN orders AS o
  ON c.id = o.customer_id;

-- All customers returned, NULL where no order exists

Result of merged tables after executing LEFT JOIN

c.id	c.name	o.order_id	o.sales
1	Maria	1001	$35
2	John	1002	$15
3	Georg	1003	$20
4	Martin	NULL	NULL
5	Peter	NULL	NULL

Use LEFT JOIN when you want all customers including those who havenever placed an order.

RIGHT JOIN: All from right, matched from left

Returns all rows from the right table. Where no matching customer
exists, NULL is shown.

SQL syntax

SELECT c.id, c.name, o.order_id, o.sales
FROM customers AS c
RIGHT JOIN orders AS o
  ON c.id = o.customer_id;

-- All orders returned, NULL where no customer exists

Result of merged tables after executing RIGHT JOIN

c.id	c.name	o.order_id	o.sales
1	Maria	1001	$35
2	John	1002	$15
3	Georg	1003	$20
NULL	NULL	1004	$10

Most analysts prefer to swap tables and use LEFT JOIN.

FULL OUTER JOIN: Everything from both tables

Returns all rows from both tables. Unmatched rows get NULL on the
missing side.

Venn diagram

FULL OUTER JOIN = both full circles

SQL syntax

SELECT c.id, c.name, o.order_id, o.sales
FROM customers AS c
FULL OUTER JOIN orders AS o
  ON c.id = o.customer_id;

-- ALL rows from both tables, NULL on missing sides

Result of merged tables after executing FULL OUTER JOIN

c.id	c.name	o.order_id	o.sales
1	Maria	1001	$35
2	John	1002	$15
3	Georg	1003	$20
4	Martin	NULL	NULL
5	Peter	NULL	NULL
NULL	NULL	1004	$10

LEFT ANTI JOIN — unmatched from left only

Returns only rows from the left table that have no match in the
right table.

SQL syntax

SELECT c.id, c.name
FROM customers AS c
LEFT JOIN orders AS o
  ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

-- Only customers with NO matching order

Result of merged tables after executing LEFT ANTI JOIN

c.id	c.name
4	Martin
5	Peter

Use LEFT ANTI to answer: "Which customers have never placed an order?"

RIGHT ANTI JOIN : Unmatched from right only

Returns only rows from the right table that have no match in the left table.

SQL syntax

SELECT o.order_id, o.customer_id, o.sales
FROM customers AS c
RIGHT JOIN orders AS o
  ON c.id = o.customer_id
WHERE c.id IS NULL;

-- Only orders with NO matching customer

Result of merged tables after executing RIGHT ANTI JOIN

o.order_id	o.customer_id	o.sales
1004	6 (no match)	$10

Use RIGHT ANTI for data quality checks: "Which orders have an
invalid customer ID?"

Joins in Power BI

Feature	SQL Join / Power Query Merge	Power BI Relationship
What it does	Physically combines rows into one table	Keeps tables separate, filters flow between them
When it runs	At data load time	At query time when you click a slicer or visual
Best for	Flat tables, one-off analysis	Star schema models with slicers and visuals

Simple rule: Use a merge in Power Query when you need one combined
table. Use a relationship in Model View when you want tables to filter
each other in your report.

How to create a join in Power Query — step by step

Open Power BI Desktop and load your tables
Click Home → Transform Data to open Power Query Editor
Select the left table (e.g. Customers) in the left panel
Click Home → Merge Queries
Select the right table (Orders), click the matching column in each table, then choose your join type from the dropdown
Click OK — a merged column appears. Click the expand icon to choose which columns to bring in
Click Close and Apply to load the merged table back into Power BI

Available join types in the Power Query dialog:

Left Outer (all from left). It is the most common
Right Outer
Full Outer
Inner
Left Anti
Right Anti

2. Relationships in Power BI

Relationships in Power BI keep tables separate but allow filters to flow between them. You manage them in Model View or through the Manage Relationships dialog.

Types of relationships

One-to-Many (1:M): Most common-One customer has many orders

Many-to-Many (M:M): Many students take many courses

One-to-One (1:1): Each employee has one profile |

Cross-filter direction

Single direction: Filters flow one way, from dimension to fact table. Default and safest.
Both directions :Filters flow both ways. Useful but can cause circular dependency issues if overused.

How to create a relationship in Model View — step by step

Click the Model View icon on the left sidebar
You will see all your tables displayed as boxes with their columns
Drag the CustomerID column from the Customers table and drop it onto CustomerID in the Orders table
A relationship line appears — 1 on the Customers side, * on the Orders side
Double-click the line to edit cardinality, cross-filter direction, or active/inactive status ---

3. Data Warehouse Schemas

Fact Tables vs Dimension Tables

Feature	Fact Table	Dimension Table
Stores	Measurable events	Descriptive context
Row count	Many — can be millions	Few — hundreds to thousands
Contains	Amounts, quantities, foreign keys	Names, categories, dates, locations
Examples	Sales, Orders, Transactions	Customers, Products, Date, Location

Simple rule: If a table answers "how much or how many" it is
a Fact table. If it answers "who, what, when, or where" it is a
Dimension table.

Star Schema, Snowflake Schema, and Flat Table

Flat table

Everything in one single wide table. Easy to start but causes data duplication and incorrect totals when filtering.

Star schema: Recommended for Power BI

Its is one central fact table directly connected to dimension tables. Simple, fast, and best for DAX and Power BI slicers.

In a star schema, customer details live in one row only

Example of one in power bi

Steps to build a star schema in Power BI

Load your Fact table (Sales) and all Dimension tables into Power BI
Go to Model View on the left sidebar
Arrange the Fact table in the center and Dimension tables around it
Drag the foreign key in the Fact table to the primary key in each Dimension table to create a 1:M relationship
Verify all lines show 1 on the dimension side and * on the fact side

Snowflake schema

Similar to a star schema but dimension tables are further broken into sub-tables. More complex and slower in Power BI. Only use snowflake if your data warehouse already uses this structure.

Example of one in Powerbi

When to use each schema

Schema	Use when	Avoid when
Star	Building any Power BI report	Never avoid it. Always the default choice
Snowflake	Data warehouse already uses it	Building a new model from scratch
Flat table	Quick one-off analysis on small data	Data is updated regularly

6. Role-Playing Dimensions

A role-playing dimension is one dimension table used multiple times in a fact table for different purposes.

Example: Orders fact table with three date columns:

Column	Meaning
OrderDateKey	When the customer placed the order
ShipDateKey	When the order was dispatched
DeliveryDateKey	When the customer received it

All three foreign keys point to the same Date dimension table.

How to set this up in Power BI

Create one active relationship from the Date table to OrderDateKey
Create two inactive relationships to ShipDateKey and DeliveryDateKey
Use USERELATIONSHIP() in your DAX measures to activate the right one:

ShippedRevenue =
CALCULATE(
    SUM(Sales[Amount]),
    USERELATIONSHIP(Date[DateKey], Sales[ShipDateKey])
)

7. Common Modeling Issues to Avoid

Issue	Why it is a problem	How to fix it
Many-to-many relationships	Causes duplicated totals	Use a bridge table or restructure your model
Bi-directional filters everywhere	Creates circular dependencies	Use single direction by default
No dedicated Date table	Time intelligence DAX will not work	Always create a proper Date dimension table
Flat table models	Data duplication causes wrong aggregations	Switch to a star schema
Wrong cardinality direction	Breaks filter propagation	Put 1 on dimension side, * on fact side
Calculated columns on fact tables	Slows down large tables — stored in memory	Use DAX measures instead

8. Key Takeaways

Use star schema as your default one fact table, dimensions around it
SQL joins combine tables physically: relationships keep them separate and filter across them
1:M relationships are the most common and safest type in Power BI.
Always create a dedicated Date table for time intelligence
A LEFT JOIN keeps all rows from the left table including those with no match
Avoid flat tables and bi-directional filters unless you have a specific reason.
Use role-playing dimensions when the same dimension table is needed multiple times in one fact table

This was the second update on my learning journey to becoming a data engineer. If you found this helpful, feel comment on other areas of learning and improvement.

Scared of Linux as a Beginner Data Engineer? Here’s How to Get Started

Gracemunyi — Mon, 30 Mar 2026 09:26:38 +0000

If you're scared of Linux as a beginner data engineer, you're not alone. Almost everyone feels this way at the start. This year, I decided to transition from being a data analyst to a data engineer with zero Linux experience.

Over the past two weeks, I’ve been learning practical Linux skills and how they apply to solving real world data problems for businesses. Here’s a summary of what I’ve learned.

Firstly, Every stage of the data engineering pipeline runs on Linux servers, usually in the cloud.

As a data engineer, here’s what I’ll actually use Linux for:

Setting up and managing servers: Configuring the machines where your data tools run.
Scheduling jobs: Using CRON to trigger data pipelines automatically.
Debugging failures: Connecting via SSH to investigate logs when a pipeline breaks.
Moving and managing files: Handling raw data before it lands in storage like S3.
Installing tools: Setting up Python, Spark, Airflow, and other software on a server.
Monitoring resources: Checking server memory, disk usage, and overall health.

Secondly: In real life, businesses pull data from APIs, databases, or external files daily. One has to automatically pull the data from these APIs using a Linux Server.

To achieve this, one has learn how to:

Connect to a virtual Linux Server
Manage files on the server

Below are simplified steps to achieve this.

Step 1: Connect to the Server via SSH and Update It

SSH (Secure Shell) allowed me to open an encrypted terminal session to
a remote server. I needed two things:

The server's IP address
My username

On Windows, you can use PowerShell or Git Bash.

I was using PowerShell.

Step 1: Connect via SSH

SSH (Secure Shell) opens an encrypted terminal session. You need your
server's IP address and username.

On Windows, use PowerShell or Git Bash:

ssh root@118.173.249.268

Type yes to accept the server key.
Enter your password (it won't show).
Press Enter, and you're in!

Step 2: Update the Server

Always update your server first before doing anything else:

sudo apt update      # Check for updates
sudo apt upgrade     # Install updates
pwd                  # See your current directory
ls                   # List files and folders

Step 3: Create Your Own User

Avoid using root regularly by creating a personal user right after setup:

sudo useradd -m grace   # Create user with home folder
sudo passwd grace       # Set password
logout                       # Log out from root
ssh grace@118.173.249.268

Step 4: Create Folders and Files

Now that you are logged in as your own user, organize your workspace:

mkdir Project        # Create a folder
cd Project           # Enter the folder
touch main.py        # Create a Python file
mkdir data           # Create a sub-folder
ls                   # Verify folder and files

Step 5: Edit Files

Use nano to write or paste your code into the file:

nano main.py

Paste your text or code
Press Ctrl + O to save
Press Ctrl + X to exit

View file contents anytime with:

cat main.py
less main.py
more main.py

Step 6: Downloading file from webpage and Managing it

Now that the workspace is set up, you can bring in data files:

wget https://example.com/data.csv      # Download a file
tar -xzf archive.tgz                   # Extract compressed files

Step 7: Transfer Files Between Your Local PC and Server

Move files from your local machine to the server using SCP
(Secure Copy Protocol):

scp main.py grace@118.173.249.268:/home/grace/from_local/

On the server, navigate to the folder and run your script:

cd from_local
python3 main.py

Summary Takeaways as a beginner

Every tool in a data engineering pipeline runs on a Linux server to navigate, organize, and run tasks.
SSH is your bridge between your PC and the server.
Always update your server and create a personal user before anything else.
Start small: create folders, files, and scripts, then automate tasks.
Everything you do here mirrors real world data engineering work, like managing pipelines, logs, or datasets.

If you’re also learning Linux for data engineering, what’s been challenging for you so far?. Drop a comment. I’d love to learn from your experience.

Also, stay tuned for the next two weeks progress update.

DEV Community: Gracemunyi

Understanding PostgreSQL The Practical Way: From Database Structure to Data Querying

1. What are DDL and DML in SQL?

2. DDL (Data Definition Language):

How I created a Schema, Schema Path and Tables

1) Creating a Schema

2) Set a path to the created schema above

3) Create tables

4) Alter a table

5) DROP Whole table or One Column

6) RENAME a Column

7) TRUNCATE

DML (Data Manipulation Language)

1) INSERT data

2) UPDATE data

3) DELETE data

3. How filtering with WHERE works

1) = (equals)

2) > (greater than)

3) BETWEEN

4) IN

5) LIKE (pattern matching)

4. How CASE WHEN helps in transforming data

5. Reflection

How to Publish and Embed Power BI Reports on a Website: For Beginners

Table of Contents

What is Power BI?

The two parts of Power BI you must know

What is a .pbix file in Power BI?

Steps to build a simple practice .pbix file

What You Need Before You Start

Step 1 : Sign In to the Power BI Service

How to sign in

Step 2: Create a Workspace

What is a workspace?

Steps to create the Workspace

Step 3 : Upload and Publish Your Report

Step 4 : Embedding the report on a website.

Generate the Embed Code called an iframe

Step 5: Making the Embed Responsive

STEP 6 You can Delete or Revoke an Embed Code

Key Takeaways

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

A) SQL Joins & Power BI Relationships

B) Fact vs Dimension Tables, Star Schema, Snowflake Schema, and Common Concepts

C) Modeling Issues

Data Modeling Explained

1. SQL Joins

Example

INNER JOIN:Matching rows only

Venn diagram

SQL syntax

Result of merged tables after executing INNER JOIN

LEFT JOIN: All from left, matched from right

Venn diagram

SQL syntax

Result of merged tables after executing LEFT JOIN

RIGHT JOIN: All from right, matched from left

SQL syntax

Result of merged tables after executing RIGHT JOIN

FULL OUTER JOIN: Everything from both tables

Venn diagram

SQL syntax

Result of merged tables after executing FULL OUTER JOIN

LEFT ANTI JOIN — unmatched from left only

SQL syntax

Result of merged tables after executing LEFT ANTI JOIN

RIGHT ANTI JOIN : Unmatched from right only

SQL syntax

Result of merged tables after executing RIGHT ANTI JOIN

Joins in Power BI

How to create a join in Power Query — step by step

2. Relationships in Power BI

Types of relationships

Cross-filter direction

How to create a relationship in Model View — step by step

3. Data Warehouse Schemas

Fact Tables vs Dimension Tables

Star Schema, Snowflake Schema, and Flat Table

Flat table

Steps to build a simple practice `.pbix` file