DEV Community: Ibrahim0695

Up down or Down up. Understanding Subqueries and CTEs in SQL: A Comprehensive Guide

Ibrahim0695 — Mon, 20 Apr 2026 13:26:05 +0000

Introduction

When working with databases, there comes a time when a simple SELECT statement is not enough to answer complex questions. Imagine you need to find customers who spent more than the average spent by all customers, or products that have never been sold, or employees who earn more than their department's average salary. These are the moments when subqueries and Common Table Expressions (CTEs) become essential tools in your SQL toolkit.

This article explores subqueries and CTEs in depth, explaining what they are, how they work, when to use each, and most importantly—a clear comparison to help you choose the right approach for your queries.

What is a Subquery?

A subquery is a query nested inside another SQL query. It acts as a "query within a query," where the inner query provides data or conditions that the outer query uses. Think of it as solving a problem in steps: first, you calculate something small, then you use that result in the main query.

Basic Syntax

SELECT column_name
FROM table_name
WHERE column_name OPERATOR (
    SELECT column_name
    FROM table_name
    WHERE condition
);

Simple Example

Suppose you want to find products that cost more than the average price:

SELECT product_name, price
FROM Products
WHERE price > (
    SELECT AVG(price)
    FROM Products
);

In this example:

The inner query calculates the average price
The outer query retrieves products with prices above that average

Different Types of Subqueries

Subqueries can be classified in two ways: by their position in the query and by their behavior.

1. By Position

a. Subquery in WHERE Clause (Scalar Subquery)

Returns a single value (one row, one column):

SELECT product_name, price
FROM Products
WHERE price > (
    SELECT AVG(price)
    FROM Products
);

b. Subquery in FROM Clause (Derived Table)

Returns a temporary table used by the outer query:

SELECT customer_name, total_spent
FROM (
    SELECT c.customer_name, SUM(s.quantity * p.price) AS total_spent
    FROM Customers c
    JOIN Sales s ON c.customer_id = s.customer_id
    JOIN Products p ON s.product_id = p.product_id
    GROUP BY c.customer_name
) AS customer_spending
WHERE total_spent > 1000;

c. Subquery in SELECT Clause (Scalar Again)

Adds a calculated column:

SELECT product_name, price,
       (SELECT AVG(price) FROM Products) AS average_price
FROM Products;

2. By Behavior

a. Correlated Subquery

References the outer query's table. It runs once for each row in the outer query:

SELECT p.product_id, p.product_name, p.category, p.price
FROM Products p
WHERE p.price > (
    SELECT AVG(p2.price)
    FROM Products p2
    WHERE p2.category = p.category
);

This finds products priced higher than their category's average.

b. Non-Correlated Subquery

Does not reference the outer query's table. Runs once and provides a static value:

SELECT product_name, price
FROM Products
WHERE price > (
    SELECT AVG(price)
    FROM Products
);

c. Nested Subquery

Multiple levels of subqueries:

SELECT customer_name
FROM Customers
WHERE customer_id IN (
    SELECT customer_id
    FROM Sales
    WHERE product_id IN (
        SELECT product_id
        FROM Products
        WHERE category = 'Electronics'
    )
);

When to Use Subqueries

Subqueries are used in the following scenarios:

1. Filtering with Aggregated Data

Find customers who spent more than average:

SELECT customer_name, total_spent
FROM (
    SELECT c.customer_name, SUM(p.price * s.quantity) AS total_spent
    FROM Customers c
    JOIN Sales s ON c.customer_id = s.customer_id
    JOIN Products p ON s.product_id = p.product_id
    GROUP BY c.customer_name
) AS spending
WHERE total_spent > (
    SELECT AVG(total_spent)
    FROM (
        SELECT SUM(p.price * s.quantity) AS total_spent
        FROM Sales s
        JOIN Products p ON s.product_id = p.product_id
        GROUP BY s.customer_id
    ) AS all_spending
);

2. Finding Records That Do Not Exist

Find products never sold:

SELECT p.product_id, p.product_name
FROM Products p
WHERE p.product_id NOT IN (
    SELECT product_id
    FROM Sales
);

3. Comparison Within Groups

Find products priced higher than their category average:

SELECT p.product_name, p.price, p.category
FROM Products p
WHERE p.price > (
    SELECT AVG(p2.price)
    FROM Products p2
    WHERE p2.category = p.category
);

4. Dynamic Filtering

Find customers who registered earlier than average:

SELECT customer_name, registration_date
FROM Customers
WHERE registration_date < (
    SELECT AVG(registration_date)
    FROM Customers
);

What is a CTE (Common Table Expression)?

A Common Table Expression (CTE) is a named temporary result set that exists only for the duration of a single SQL statement. It provides a way to write auxiliary statements that can be referenced within a larger query, making complex queries more readable and organized.

Basic Syntax

WITH cte_name AS (
    -- CTE query
    SELECT columns
    FROM table_name
    WHERE condition
)
-- Main query referencing CTE
SELECT *
FROM cte_name
WHERE condition;

Simple Example

Find products above average price using CTE:

WITH AveragePrice AS (
    SELECT AVG(price) AS avg_price
    FROM Products
)
SELECT product_name, price
FROM Products p
CROSS JOIN AveragePrice avg
WHERE p.price > avg.avg_price;

Different Types of CTEs

1. Non-Recursive CTE

The most common type—processes data without self-referencing:

WITH CustomerSpending AS (
    SELECT c.customer_id, 
           c.customer_name, 
           SUM(s.quantity * p.price) AS total_spent
    FROM Customers c
    JOIN Sales s ON c.customer_id = s.customer_id
    JOIN Products p ON s.product_id = p.product_id
    GROUP BY c.customer_id, c.customer_name
)
SELECT customer_name, total_spent
FROM CustomerSpending
WHERE total_spent > 500;

2. Recursive CTE

References itself to process hierarchical data. Useful for organizational charts or tree structures:

WITH RECURSIVE org_chart AS (
    -- Base case: top-level managers
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: employees reporting to someone
    SELECT e.employee_id, e.manager_id, e.employee_name, oc.level + 1
    FROM employees e
    JOIN org_chart ec ON e.manager_id = ec.employee_id
)
SELECT * FROM org_chart;

3. Multiple CTEs

Chain multiple CTEs in one statement:

WITH 
CustomerSpending AS (
    SELECT customer_id, SUM(quantity * price) AS total
    FROM Sales s
    JOIN Products p ON s.product_id = p.product_id
    GROUP BY customer_id
),
AvgSpending AS (
    SELECT AVG(total) AS avg_spent
    FROM CustomerSpending
)
SELECT cs.customer_id, cs.total
FROM CustomerSpending cs
CROSS JOIN AvgSpending av
WHERE cs.total > av.avg_spent;

Use Cases of CTEs

1. Simplifying Complex Queries

Instead of nesting multiple subqueries:

-- Using CTEs for readability
WITH 
MonthlySales AS (
    SELECT DATE_TRUNC('month', sale_date) AS month,
           SUM(quantity * price) AS total_sales
    FROM Sales s
    JOIN Products p ON s.product_id = p.product_id
    GROUP BY DATE_TRUNC('month', sale_date)
),
AverageMonthlySales AS (
    SELECT AVG(total_sales) AS avg_monthly
    FROM MonthlySales
)
SELECT month, total_sales
FROM MonthlySales ms
CROSS JOIN AverageMonthlySales ams
WHERE ms.total_sales > ams.avg_monthly;

2. Reusing the Same Calculation

Calculate once, reference multiple times:

WITH 
SalesSummary AS (
    SELECT product_id, 
           SUM(quantity) AS total_qty,
           SUM(quantity * price) AS total_revenue
    FROM Sales s
    JOIN Products p ON s.product_id = p.product_id
    GROUP BY product_id
)
-- Reference the CTE twice
SELECT 'Top Products' AS category, COUNT(*) AS count
FROM SalesSummary
WHERE total_revenue > 1000

UNION ALL

SELECT 'Low Performers', COUNT(*)
FROM SalesSummary
WHERE total_qty < 10;

3. Working with Hierarchical Data

Employee organizational structure:

WITH RECURSIVE OrgStructure AS (
    SELECT employee_id, employee_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.employee_name, e.manager_id, os.level + 1
    FROM employees e
    JOIN OrgStructure os ON e.manager_id = os.employee_id
)
SELECT * FROM OrgStructure;

4. Running Totals and Window Functions

WITH DailySales AS (
    SELECT sale_date, 
           SUM(quantity * price) AS daily_total
    FROM Sales s
    JOIN Products p ON s.product_id = p.product_id
    GROUP BY sale_date
)
SELECT sale_date, 
       daily_total,
       SUM(daily_total) OVER (ORDER BY sale_date) AS running_total
FROM DailySales;

Subqueries vs CTEs: A Clear Comparison

Aspect	Subquery	CTE
Definition	Nested query inside another query	Named temporary result set
Readability	Can become messy with nesting	More readable and organized
Performance	Similar execution time	Similar execution time
Reusability	Cannot be referenced multiple times	Can be referenced multiple times
Debugging	Harder to debug	Easier to debug step by step
Recursive	Not supported	Supported
Scope	Limited to single query	Available throughout the statement

Performance Comparison

In terms of performance, both subqueries and CTEs typically execute similarly because the database optimizer converts both to the same execution plan. However:

Factor	Subquery	CTE
Optimization	May run multiple times (correlated)	Runs once
Materialization	May be materialized by optimizer	May be materialized
Index Usage	Uses indexes effectively	Uses indexes effectively

Key Takeaway: Performance difference is negligible in most cases. Choose based on readability and maintainability.

When to Use Subqueries

Simple filtering with aggregate values
Quick, one-off queries
When the logic is straightforward
When working with limited data

Example:

-- Simple filter—use subquery
SELECT product_name, price
FROM Products
WHERE price > (
    SELECT AVG(price)
    FROM Products
);

When to Use CTEs

Complex, multi-step logic
When you need to reference the same calculation multiple times
Recursive operations (hierarchical data)
Improved readability for you and your team

Example:

-- Complex logic—use CTE
WITH 
CustomerSpending AS (
    SELECT customer_id, SUM(quantity * price) AS total
    FROM Sales s
    JOIN Products p ON s.product_id = p.product_id
    GROUP BY customer_id
),
AvgSpending AS (
    SELECT AVG(total) AS avg_spent
    FROM CustomerSpending
)
SELECT cs.*
FROM CustomerSpending cs
CROSS JOIN AvgSpending av
WHERE cs.total > av.avg_spent;

Summary

Subqueries

Type	Use Case
Scalar in WHERE	Filtering with aggregate values
Derived Table (FROM)	Complex calculations as temporary table
Correlated	Row-by-row comparisons
Nested	Multi-level filtering

CTEs

Type	Use Case
Non-Recursive	Simplifying complex queries
Recursive	Hierarchical data
Multiple	Chaining calculations

Key Takeaways

Subqueries are best for simple, one-time calculations where you filter or compare against a single aggregate value.
CTEs shine when your logic is complex, multi-step, or needs to be referenced more than once.
Choose readability: If your query is hard to read, use a CTE to break it into logical steps.
Performance is equal: In most databases, the optimizer treats both similarly.
CTEs are more maintainable: If someone else needs to understand your query, CTEs make it easier.

Final Recommendation

Scenario	Recommended
Products above average price	Subquery
Customers who spent more than average	Subquery or CTE
Products never sold	Subquery
Hierarchical employee data	CTE (Recursive)
Multiple referenced calculations	CTE
Complex multi-step analysis	CTE

Remember: there is no strict rule; choose the tool that makes your code clearer and easier to maintain.

Understanding DDL and DML in Database Management

Ibrahim0695 — Sun, 12 Apr 2026 08:54:09 +0000

When working with databases, understanding the difference between Data Definition Language (DDL) and Data Manipulation Language (DML) is fundamental. These two categories of SQL commands serve different purposes but work together to manage and utilize data effectively.

What are DDL and DML?

Data Definition Language (DDL) refers to SQL commands that define or modify database structure. These commands create, alter, and delete database objects like tables, indexes, and schemas. DDL changes are permanent and auto-commit in most database systems.

Data Manipulation Language (DML) refers to SQL commands that manipulate data within existing database objects. These commands allow you to insert, update, delete, and retrieve data from tables.

Key Difference

DDL	DML
Defines structure	Manipulates data
Auto-commits	Requires explicit COMMIT
Examples: CREATE, ALTER, DROP	Examples: INSERT, UPDATE, DELETE

CREATE – Building the Foundation

The CREATE statement establishes new database objects. In my assignment, I used CREATE to set up tables with proper structure:

create table students(
    student_id INT primary key,
    first_name VARCHAR(50) not null,
    last_name VARCHAR(50) not null,
    email VARCHAR(100) unique,
    gender VARCHAR(1),
    date_of_birth date,
    class varchar(10),
    city varchar(50)
 );

This creates a students table with defined columns and data types. The PRIMARY KEY constraint ensures each student has a unique identifier.

INSERT – Adding Data

INSERT populates tables with new records. I used INSERT to add student information:

For multiple records:

insert into students (student_id, first_name, last_name, gender , date_of_birth, class, city)
values 
    (1, 'Amina', 'Wanjiku','F','2008-03-12', 'Form 3','Nairobi'),
    (2, 'Brian', 'Ochieng','M','2007-07-25', 'Form 4','Mombasa'),
    (3, 'Cynthia', 'Mutua','F','2008-11-05', 'Form 3','Kisumu'),
    (4, 'David', 'Kamau','M','2007-02-18', 'Form 4','Nairobi'),
    (5, 'Esther', 'Akinyi','F','2009-06-30', 'Form 2','Nakuru'),
    (6, 'Felix', 'Otieno','M','2009-09-14', 'Form 2','Eldoret'),
    (7, 'Grace', 'Mwangi','F','2008-01-22', 'Form 3','Nairobi'),
    (8, 'Hassan', 'Abdi','M','2007-04-09', 'Form 4','Mombasa'),
    (9, 'Ivy', 'Chebet','F','2009-12-01', 'Form 2','Nakuru'),
    (10, 'James', 'Kariuki','M','2008-08-17', 'Form 3','Nairobi');

UPDATE – Modifying Existing Data

UPDATE changes existing records. I used UPDATE to correct or modify data:

update students
set city = 'Nairobi'
where student_id = 5;

This changes Akinyi's city from Nakuru to Nairobi. The WHERE clause ensures only specific records are updated.

DELETE – Removing Data

DELETE removes records from a table:

delete from exam_results
where result_id = 9;

This removes results of result_id 9 from exam_reults table. Without WHERE, DELETE removes ALL records, so WHERE is critical.

Filtering with WHERE

The WHERE clause filters records based on conditions. I used various operators:

Equality (=)

select * from students
where class= 'Form 4';

Greater Than or equal to(>=)

select * from exam_results
where marks >= 70;

BETWEEN (Range)

select * from exam_results
where marks between 50 and 80;

IN (Multiple Values)

select *  from students
where city in ('Nairobi','Mombasa', 'Kisumu');

LIKE (Pattern Matching)

select * from subjects
where subject_name like '%Studies';

This finds all students whose first name starts with 'J'.

CASE WHEN – Transforming Data

CASE WHEN creates conditional logic to transform data. I used it to categorize students:

select
    first_name,
    last_name,
    CASE 
        when class IN ('Form 3', 'Form 4') then 'Senior'
        when class IN ('Form 1', 'Form 2') then 'Junior'
    end as student_level
from students;

results

first_name	last_name	student_level
Amina	Wanjiku	Senior
Brian	Ochieng	Senior
Cynthia	Mutua	Senior
David	Kamau	Senior
Felix	Otieno	Junior
Grace	Mwangi	Senior
Hassan	Abdi	Senior
Ivy	Chebet	Junior
James	Kariuki	Senior
Esther	Akinyi	Junior

CASE WHEN is powerful for creating new columns based on conditions without altering original data.

My Reflection

This week I found WHERE clause operators particularly interesting. Understanding how LIKE, BETWEEN, and IN work together made filtering data much more efficient. The challenge was remembering that WHERE affects UPDATE and DELETE statements permanently, so I had to be careful with my queries.

Key takeaway: DDL builds the structure while DML populates and manipulates it. Both are essential for effective database management.

Publishing a Power BI report and Embedding it in a Website

Ibrahim0695 — Sun, 05 Apr 2026 18:54:29 +0000

Introduction to Power BI and Publishing

Power BI is a powerful business analytics tool by Microsoft that transforms raw data into interactive visualizations and reports. Once you've created a compelling report with charts, tables, and insights, the next step is sharing it with your audience. Power BI provides seamless publishing capabilities that allow you to publish your reports to the Power BI Service (the cloud platform), generate embed codes, and integrate those reports directly into websites for broader accessibility.

Publishing a Power BI report involves uploading your .pbix file from Power BI Desktop to the Power BI Service, organizing it within a workspace, and then using the embed functionality to display the report on any webpage. This guide walks you through the entire process with detailed steps and visual guidance.

What you need:

Before you begin, ensure you have:

Power BI Desktop installed
A Power BI Pro or Power BI Premium license
A Power BI account (sign up at app.powerbi.com)
A report saved as a .pbix file
A website where you can embed HTML/JavaScript code

Step 1: Creating a Workspace in Power BI Service

A workspace in Power BI is a holder of your reports, datasets, dashboards, and workbooks. Think of it as a project folder where related content lives together.

Screenshot: Power BI Service - Create Workspace

Step-by-step:

Sign in to Power BI Service
- Open your browser and navigate to app.powerbi.com
- Sign in with your Microsoft account

Create a New Workspace
- In the left navigation pane, click on "Workspaces"
- Click the "Create a workspace" button (a plus icon or "New" button)
- You will see the workspace creation panel slide out

Configure Your Workspace
- Name: Enter a descriptive name such as "Sales Analytics" or "Marketing Reports"
- Description: Add a brief description explaining the workspace's purpose
- Workspace settings: Under "Advanced," you can configure:
  - License mode: Choose between Pro or Premium Per User (PPU)
  - Storage: Set a capacity if using Premium
  - Contact list: Add team members who will receive updates
  - OneDrive: Optionally connect to a SharePoint/OneDrive for seamless file management

Save the Workspace
- Click "Save" to create the workspace
- Your new workspace will appear in the workspaces list

Step 2: Publishing Your Report to Power BI Service

With your workspace ready, the next step is to publish your .pbix file. This uploads both your data model and your report design to the cloud.

Step-by-step in Power BI Desktop:

Open Your Report
- Launch Power BI Desktop
- Open the .pbix file you want to publish

Initiate the Publish Process
- Click the "Publish" button on the Home ribbon tab
- Alternatively, use the keyboard shortcut: Alt + F → H → P
Select Your Destination
- The "Publish to Power BI" dialog box appears
- Under "Destination," expand the dropdown
- Select the workspace you created (e.g., "Sales Analytics")
- Note: You can also publish to "My Workspace" for personal use

Confirm and Publish
- Click the "Publish" button
- Power BI Desktop will validate your data, upload the dataset, and then publish the report
- A success message appears with a link: "Open 'Your Report' in Power BI"
Verify the Upload
- Click the success link or go to app.powerbi.com
- Navigate to your workspace
- Confirm that your report and dataset appear

Step 3: Generating the Embed Code

Once your report is published, you can generate an embed code to display it on any website. Power BI offers two embedding methods: Embed for your organization (requires viewer authentication) and Embed for your customers (using embed tokens for public access).

Step-by-step:

Navigate to Your Report
- In Power BI Service, go to your workspace
- Click on the report you published

Access the Embed Options
- In the report view, look for the "File" menu or the "Share" button
- Alternatively, click the "..." (More options) menu on the report tile
- Select "Embed" → "Website or portal"
Configure Embed Settings
- The embed dialog shows your report with configuration options:
  - Security: Choose "Organization" (requires sign-in) or "Public" (embed token)
  - Size: Select canvas size (e.g., 16:9, 4:3, or custom)
  - Filters: Enable/disable filter pane visibility
  - Navigation: Enable/disable page navigation
  - Title and Banner: Show or hide report title and action bar
  - Background: Set transparency level
Generate the Embed Code
- After configuring settings, click "Apply"
- Power BI generates an iframe embed code and optionally an HTML/JavaScript snippet

Copy the Embed Code
- Copy the iframe code or the embed link to your clipboard
- You can also download the embed script for advanced integration

Embed Code Example:

<iframe 
    title="Sales Report" 
    src="https://app.powerbi.com/reportEmbed?reportId=abc12345&groupId=xyz67890" 
    frameborder="0" 
    allowFullScreen="true">
</iframe>

Screenshot Location: Power BI Service → Report View → File → Embed → Website or portal

Step 4: Embedding the Report on a Website

Now that you have the embed code, you can paste it into your website's HTML. The exact method depends on your website platform (WordPress, custom HTML, SharePoint, etc.).

Method 1: Embedding in a Standard HTML Website

Step-by-step:

Open Your Website Editor
- Access the HTML or source code of the page where you want the report
- Use a code editor or the built-in HTML editor of your CMS
Paste the Embed Code
- Locate the section of the page where you want the report to appear
- Paste the iframe code you copied from Power BI
Adjust Size and Styling
- Modify the width and height attributes to fit your layout:

   <iframe 
       title="Sales Report" 
       width="100%" 
       height="600" 
       src="https://app.powerbi.com/reportEmbed?reportId=abc12345&groupId=xyz67890" 
       frameborder="0" 
       allowFullScreen="true">
   </iframe>

Save and Preview
- Save your changes and preview the page
- The Power BI report will load and be interactive within the iframe

Method 2: Embedding in WordPress

Switch to HTML/Code View
- In the WordPress editor, click the "Code editor" or "</>" button
Paste the Embed Code
- Paste the iframe code in the desired location within the page/post
Option: Use a Plugin
- Install a Power BI embed plugin for WordPress
- Use a shortcode provided by the plugin instead of raw iframe code

Method 3: Embedding in SharePoint Online

Edit Your SharePoint Page
- Navigate to the SharePoint site and click "Edit"
Add a Web Part
- Click the "+" icon to add a new web part
- Search for and select "Power BI" (if available) or use the "Embed" web part
Paste the Embed Link
- If using the Embed web part, paste the report URL (not the full iframe code):

   https://app.powerbi.com/reportEmbed?reportId=abc12345&groupId=xyz67890

Save the Page
- Click "Publish" or "Republish" to make the report visible

Method 4: Using Power BI Embedded (For Developers)

For advanced embedding with custom authentication and user-specific views, use the Power BI Embedded API:

Register an Application in Azure
- Go to the Azure Portal → Azure Active Directory → App registrations
- Create a new application registration
- Note down the Client ID and Client Secret
Obtain an Embed Token
- Use the Power BI REST API to generate an embed token for your report
- Example using PowerShell or a backend script:

   POST https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports/{reportId}/GenerateToken

Embed Using the JavaScript SDK
- Add the Power BI Client SDK to your webpage:

   <script src="https://npmcdn.com/powerbi-client@2.22.0/dist/powerbi.min.js"></script>

Initialize and embed the report:

   const embedConfiguration = {
       type: 'report',
       id: 'YOUR_REPORT_ID',
       embedUrl: 'YOUR_EMBED_URL',
       accessToken: 'YOUR_EMBED_TOKEN',
       settings: {
           filterPaneEnabled: true,
           navContentPaneEnabled: true
       }
   };

   const report = powerbi.embed(reportContainer, embedConfiguration);

Key Insights and Best Practices

1. Choose the Right Embedding Method

Publish to Web: Quick and easy for public reports, but no row-level security. Use only for non-sensitive data.
Embed for Organization: Leverages existing Power BI licenses. Users must sign in to view the report. Supports full security and row-level security.
Power BI Embedded (ISV): Best for embedding in applications you sell. Requires Azure registration and backend token generation.

2. Security Considerations

Never expose your .pbix file credentials or embedded tokens publicly
For sensitive data, always use row-level security (RLS) and embed with organizational authentication
Regularly rotate your application secrets in Azure

3. Performance Optimization

Use the Power BI Service to schedule dataset refreshes so your embedded report always shows current data
Avoid embedding too many reports on a single page — each iframe consumes resources
Consider using Power BI paginated reports for print-ready layouts instead of standard reports

4. Mobile Responsiveness

Design your reports with phone layouts in Power BI Desktop
Use responsive iframe sizing (width="100%") in your website to ensure the report adapts to different screen sizes

5. Collaboration and Governance

Use workspaces to organize reports by department or project
Leverage Power BI deployment pipelines for moving reports between Development, Test, and Production environments
Set up workspaces with Premium capacity if you need faster performance and larger dataset sizes

6. Tracking and Analytics

For public embeds, you can track usage via Power BI's usage metrics
For embedded applications, implement custom telemetry to monitor how users interact with your reports

Conclusion

Publishing and embedding a Power BI report is a straightforward process that transforms how you share data insights. By creating a workspace in the Power BI Service, publishing your .pbix file, configuring embed settings, and pasting the embed code into your website, you can deliver interactive, real-time dashboards directly to your audience — whether they are internal stakeholders or external customers.

Remember to choose the embedding method that aligns with your security requirements, optimize your reports for performance, and leverage Power BI's governance features to maintain control over your analytics assets. With Power BI's publishing and embedding capabilities, your data stories can reach anyone, anywhere, on any device.

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

Ibrahim0695 — Sun, 29 Mar 2026 20:24:56 +0000

INTRODUCTION
Following the first week of messing around with ‘backend’ data at LuxDevHQ, this week, we were given a chance to mingle with the data analytics group again. For what you might ask, well, Power BI. Honestly, I didn’t think I would enjoy ‘frontend’ data like this! A lot has been learnt, but I think the most intriguing part of Power BI is data modelling, because to me it has a sort of likeness to data engineering in that it improves performance, reduces errors, and makes analysis easier. Who wouldn’t want data that is organized in structures that allows for fast and accurate analysis and visualization... I mean who?

In this article I’m planning to dive into data modelling in BI, placing focus on joins, relationships and schemas.

So what is data modelling?
It is simply the process of connecting different data sources, defining how they relate to one another(tables), and organizing them into a structure that provides easy analysis. A good way to think of it is the way movie shops, categorizes and indexes its movies and series in a way that is easy to retrieve and view.

SQL Joins in Power BI simplified

SQL joins are implemented through the "Merge Queries" feature in the Power Query Editor, which combines rows from two tables based on a shared column.

Power Bi supports several join types, below are their description and a simple example to get you up to speed;

Inner join; Returns only rows where the key exists in both tables.

Targeted Marketing: A list of customers who have both an active account and a registered email address.

Left Outer(left join); Keeps all rows from the "Left" table and adds matching data from the "Right.

Sales Report: All products in your catalog, including those that haven't sold yet (which show as null).

Right Outer( right join); Keeps all rows from the "Right" table and adds matching data from the Left.

Audit: All transactions in a bank ledger, matching them to customer profiles (useful if some transactions lack profile data).

Full Outer JOIN; Returns all rows from both tables, matching them where possible.

Consolidated Inventory: Merging stock lists from two different warehouses to see every item available in the company.

Left Anti JOIN; Returns rows from the Left table that do not have a match in the Right.

Inventory Cleanup: Finding products in your warehouse that have never appeared in a sales record.

Right Anti JOIN; Returns rows from the Right table that do not have a match in the Left.

Error Tracking: Finding sales records that are missing a corresponding "Product ID" in your master product list.

How to Create Joins in Power BI:

Open Power BI;

- Click Transform Data on the Home ribbon to open the Power Query Editor.
- Select your primary table from the "Queries" pane on the left.
- In the Home ribbon, click Merge Queries.
- In the dialog box, select the second table from the dropdown.
- Crucial Step: Click the matching column in both table previews (e.g., ProductID).
- At the bottom, choose your Join Kind (e.g., Left Outer).
- Once merged, click the **Expand **icon (two arrows) in the new column header to choose which columns from the second table you want to pull in.

Relationships in BI

In Power BI, Relationships are the secret sauce that allows you to connect different tables so they can work together as one. Without them, you would just have a bunch of isolated lists that can’t speak to each other.

Imagine you have a list of Sales and a list of Products. A relationship tells Power BI that the "Product ID" in your Sales list is the same as the "Product ID" in your Product list.

The Core Concept: Fact vs. Dimension Tables
Before looking at the lines connecting tables, you need to know the two types of tables:

- Fact Tables(The "What happened"): These are long tables that store events or transactions (e.g., Sales, Temperatures, Stock levels). They usually have many numbers.
- Dimension Tables(The "Context"): These are shorter, unique lists that describe things (e.g., a list of every Product you sell, a list of every Store, or a Calendar).

TYPES OF RELATIONSHIPS

1. Cardinality (The "How Many")

When you drag a line between two tables in the Model View, Power BI looks at how many times a value appears on each side.

One-to-Many(1:M) The "Gold Standard"
This is the most common and healthiest relationship.

Example: One Product (in your Dimension table) can be sold Many times (in your Sales/Fact table).

One-to-One (1:1)
Every row in Table A matches exactly one row in Table B.
This is rare and usually means the to tables should probably just be merged into one.

Many-to-Many(M:M)
This happens when values repeat in both tables.

Example: A list of Students and a list of Classes (One student has many classes; one class has many students).

Warning: Beginners should avoid this if possible, as it can make your numbers "double-count" or act strangely.

2. Cross-Filter Direction (The "Flow")

The arrow in the middle of the relationship line tells Power BI which way the "filtering power" flows.

Single Direction(One-way arrow): The filter flows from the "One" side to the "Many" side. If you click a "Category" in a slicer, your "Sales" total updates. This is the safest and fastest setting.

Both Directions (Two-way arrow): The filter flows both ways. While it sounds helpful, it can cause performance lag and confusing results in large models.

3. Active vs. Inactive Relationships

Power BI only allows one active path between two tables.

Active (Solid Line): This is the "Main Highway." Power BI uses this for all your charts by default.

Inactive (Dotted Line): This is a "Backroad." It exists, but Power BI won't use it unless you write a specific piece of code (DAX) to tell it to take that path. This is common if you have both an Order Date and a Ship Date in the same table.

How to Create a Relationship (Step-by-Step)

Click the Model View icon on the far left of Power BI Desktop (it looks like three small boxes connected by lines).
Find the column that exists in both tables (e.g., CustomerID).
Click and drag the column name from one table and drop it directly onto the same column name in the other table.
A line will appear. You can double-click that line to open the "Edit Relationship" window if you need to change the Cardinality or Filter Direction.

** Managing Relationships**

Model View: Click the Model icon (the third one down on the left-hand pane).
Drag & Drop: Simply click a field in one table and drag it onto the corresponding field in another. Power BI will attempt to guess the cardinality.
The Dialog Box: Double-click any relationship line (or click Manage Relationships in the top ribbon) to manually toggle it between Active/Inactive or change the Cross-filter direction.

Schemas

A **Schema **is simply the way you arrange your tables and the relationships between them. Think of it like a map: it shows where the data lives and how a filter in one place travels to update a number in another.

To understand schemas like relationships, you first need to know the two types of tables that live inside them:

Fact Tables: The "What happened." These are long lists of numbers (Sales, Temperature readings, Stock levels).

Dimension Tables: The "Who, Where, and When." These provide context (Product names, Date calendars, Store locations).

Here are the three most common ways to organize these tables:

1. The Star Schema (The Gold Standard)
The Star Schema is the most recommended layout for Power BI. It features one Fact table in the center, directly connected to several Dimension tables. It looks like a star because the dimensions radiate out from the middle.

**Why use it? **It is the fastest for Power BI to calculate. Because every dimension is only "one jump" away from the fact table, your reports will be snappy and your DAX formulas will be simpler.

Tip: If you aren't sure which one to use,** always try to build a Star Schema.**

2. The Snowflake Schema
A Snowflake Schema is just a Star Schema where some of the "arms" (dimensions) are broken down into even smaller tables. For example, instead of having a "Product" table with the Category and Sub-category inside it, you have three separate tables: Product → Sub-Category → Category.

Why use it? It is very organized and saves space because it doesn't repeat words as often.

The Downside: It’s "colder" and slower. Power BI has to "jump" through multiple tables to get an answer (e.g., to find Sales by Category, it has to go from Category to Sub-Category to Product to Sales). This can slow down large reports.

3. The Flat Table (DLAT)
This isn't really a "schema" in the traditional sense; it’s just one giant table that contains everything—the sales, the product names, the dates, and the customer addresses all in one spreadsheet-style view.

Why use it? Beginners often start here because it’s how Excel works. It’s easy to understand at first glance.

The Downside: It is very inefficient. If you have 1 million sales of the same "Red Hammer," Power BI has to store the words "Red Hammer" 1 million times. This makes your file size huge and makes "Time Intelligence" (like comparing this month to last month) very difficult to calculate.

How to Build These in Power BI
You manage these schemas in the Model View (the icon on the far left that looks like three little boxes connected by lines).

Drag and Drop: Click a column (like ProductID) in one table and drag it onto the matching column in another table to create the "arm" of your schema.

Check the Direction: Look for the arrow on the line. In a good Star Schema, the arrow should point away from the Dimension and toward the Fact table. This means the Dimension is "filtering" the Fact.

Common Modeling Issues to Watch For

Role-Playing Dimensions: This happens when a dimension (like Date) needs to filter the Fact table multiple times. Solution: Use Inactive Relationships and the DAX function USERELATIONSHIP.
- Many-to-Many Hazards: Relating two tables on a non-unique key can lead to "Double Counting." Solution: Use a "Bridge Table" with unique values to sit between them.
- Circular Dependencies: When filters loop back on themselves. Power BI will block these to prevent infinite calculation loops

Conclusion

It is crucial that before you throw data into Power BI, model it first This is go along way in avoiding confusion and lag.