DEV Community: Lucy Anyango

Window Functions, Joins, CTEs and Subqueries Using a Hospital Database

Lucy Anyango — Sun, 18 Oct 2026 11:05:50 +0000

Introduction

SQL allows us to store, retrieve, organize and analyze data in relational databases. In a hospital, SQL can help management understand patient visits, identify doctors handling the most cases, compare hospital bills, track admissions and investigate patterns in diagnoses and this article uses the PostgreSQL hospital database, which contains tables such as patients, doctors, department, diagnosis, procedures, wards and visits. The visits table is especially important because it records hospital visits and connects other tables through IDs. For example, visits.patient_id references patients.patient_id, and visits.doctor_id references doctors.doctor_id.
Before running the examples:

SQL Window Functions

What are window functions?
A window function performs a calculation across a set of related rows while keeping the individual rows in the result. For example, a hospital manager can see every visit alongside the total bill for that visit’s admission type. A GROUP BY query would combine visits into groups, but a window function can calculate a group total while still displaying each visit.
Window functions are useful for ranking doctors or visits, comparing bills across rows, calculating running totals, and finding group averages, counts, minimums and maximums.

OVER(), PARTITION BY, ORDER BY and window frames
Over() Defines the windows over which a function operates
Partition by Divides rows into independent groups without collapsing them.
Order by Defines the order in which rows are ranked.
Window frame Defines the particular rows in a partition used for a calculation relative to the current row.

Example Calculate a total for each group while retaining individual rows.
Compare billing across outpatient, inpatient and emergency visit

Select
Visit_id,
Visit_date,
Admission_type,
Total_bill_kes,
Sum(total_bill_kes) over |(partition by admission_type) as total_bill_by_admission_type
From visits
Order by visit_id;

Each visit remains in the output. All outpatient visits show the same outpatient total; inpatient and emergency visits show their own group totals. PARTITION BY separates the groups, while the final ORDER BY sorts the output.

A window frame determines exactly which rows inside the ordered window are included. For a running total, use a frame from the first row through the current row.

Example Calculate a cumulative sum in a deterministic order.
Hospital scenario: Track how recorded billing accumulates from one visit to the next.
SELECT
visit_id,
visit_date,
total_bill_kes,
SUM(total_bill_kes) OVER (
ORDER BY visit_date, visit_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total_kes
FROM visits;

The total begins at KES 3,500 for visit 1001, reaches KES 6,000 after visit 1002, and KES 34,000 after visit 1003. The frame includes all rows from the beginning through the current row.

Ranking window functions
ROW_NUMBER()
A unique sequential number to every row in the window.
Hospital scenario: List each doctor’s visits from the highest bill to the lowest.

SELECT
visit_id,
doctor_id,
total_bill_kes,
ROW_NUMBER() OVER (
PARTITION BY doctor_id
ORDER BY total_bill_kes DESC, visit_id
) AS bill_position
FROM visits;

Each doctor has a sequence beginning at 1. Doctor 101’s visit 1017, costing KES 82,000, ranks first among that doctor’s visits. The visit_id tie-breaker makes ordering predictable.

RANK()
Assign the same rank to tied values and leave gaps after ties.
Rank visits by total bill.

SELECT
visit_id,
total_bill_kes,
RANK() OVER (
ORDER BY total_bill_kes DESC
) AS bill_rank
FROM visits;

Visit 1017, costing KES 82,000, receives rank 1; visit 1010, costing KES 76,000, receives rank 2. If two visits tie for first, both get rank 1 and the next rank is 3.

DENSE_RANK()
Assign the same rank to ties without leaving gaps.
Rank distinct bill amounts without skipping rank numbers after ties.

SELECT
visit_id,
total_bill_kes,
DENSE_RANK() OVER (
ORDER BY total_bill_kes DESC
) AS bill_rank
FROM visits;

The highest bill receives rank 1. The next distinct bill receives rank 2, even if several visits share rank 1.

NTILE()
Divide ordered rows into a specified number of approximately equal-sized groups.
Divide the 30 visits into four groups by bill amount.

SELECT
visit_id,
total_bill_kes,
NTILE(4) OVER (
ORDER BY total_bill_kes DESC, visit_id
) AS bill_quartile
FROM visits;

Every visit is assigned to group 1, 2, 3 or 4. Group 1 contains the highest-billed visits and group 4 the lowest. Because 30 is not divisible by four, the groups will not all have the same size. NTILE creates groups, not statistical quartile boundaries.

Navigation window functions
LAG()
Retrieve a value from a preceding row without joining the table to itself.
Compare each visit’s bill with the previous visit’s bill in the ordered data.

SELECT
visit_id,
visit_date,
total_bill_kes,
LAG(total_bill_kes) OVER (
ORDER BY visit_date, visit_id
) AS previous_visit_bill
FROM visits;

Visit 1001 has NULL because it is first. Visit 1002 shows KES 3,500, the bill for visit 1001. LAG returns NULL when no preceding row exists unless a default is supplied.

LEAD()
Retrieve a value from a subsequent row in the ordered result.
Display the next visit’s bill beside the current visit.

SELECT
visit_id,
visit_date,
total_bill_kes,
LEAD(total_bill_kes) OVER (
ORDER BY visit_date, visit_id
) AS next_visit_bill
FROM visits;

Visit 1001 shows KES 2,500 as the next bill because visit 1002 follows it. The final row has NULL because there is no subsequent row. LAG looks backward; LEAD looks forward.

FIRST_VALUE()
Return the first value in the specified window frame.
Show each visit alongside the highest bill recorded for that visit’s doctor.

SELECT
visit_id,
doctor_id,
total_bill_kes,
FIRST_VALUE(total_bill_kes) OVER (
PARTITION BY doctor_id
ORDER BY total_bill_kes DESC, visit_id
) AS highest_bill_for_doctor
FROM visits;

Every visit handled by doctor 101 displays KES 82,000, the highest bill for that doctor. Other doctors display their own highest-bill values.

LAST_VALUE()
Return the last value in the specified window frame. With ORDER BY, the default frame commonly ends at the current row’s last peer rather than the whole partition.
Show each doctor’s lowest bill alongside every visit handled by that doctor.

SELECT
visit_id,
doctor_id,
total_bill_kes,
LAST_VALUE(total_bill_kes) OVER (
PARTITION BY doctor_id
ORDER BY total_bill_kes, visit_id
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS lowest_bill_for_doctor
FROM visits;

All visits handled by doctor 101 show KES 3,500 as that doctor’s lowest bill. The explicit frame makes PostgreSQL examine the whole partition.

Aggregate window functions
SUM()
Add values across a window.
Display each visit alongside total recorded hospital billing.

SELECT
visit_id,
total_bill_kes,
SUM(total_bill_kes) OVER () AS total_hospital_billing
FROM visits;

Every visit shows its own bill and the same overall total of KES 577,000 across all 30 visits. The window calculation retains individual rows.

AVG()
Calculate the average value across a window.
Compare each bill with the average bill for the same admission type.

SELECT
visit_id,
admission_type,
total_bill_kes,
AVG(total_bill_kes) OVER (
PARTITION BY admission_type
) AS average_bill_by_admission
FROM visits;

Each visit displays its bill and the average for its admission type. All inpatient visits share one inpatient average, for example.

COUNT()
Count rows or non-NULL values within a window.
Show how many visit records each doctor handled.

SELECT
visit_id,
doctor_id,
COUNT(*) OVER (
PARTITION BY doctor_id
) AS visits_handled_by_doctor
FROM visits;

Each visit for doctors 101 and 102 displays 10; visits for doctors 103 and 104 display 5. This counts visit records, not distinct patients.

MIN()
Find the minimum value within a window.
Show the smallest bill recorded for each admission type.

SELECT
visit_id,
admission_type,
total_bill_kes,
MIN(total_bill_kes) OVER (
PARTITION BY admission_type
) AS minimum_bill_for_admission
FROM visits;

Every visit displays the minimum bill for its admission type. The minimum outpatient bill is KES 1,800, for visit 1014.

MAX()
Find the maximum value within a window.
Show the largest bill recorded for each admission type.

SELECT
visit_id,
admission_type,
total_bill_kes,
MAX(total_bill_kes) OVER (
PARTITION BY admission_type
) AS maximum_bill_for_admission
FROM visits;

Every visit displays the maximum bill for its admission type. The maximum inpatient bill is KES 82,000 for visit 1017; the emergency visit is KES 22,000.

SQL Joins

*What are SQL joins?
*
A join combines data from two or more tables using related columns or a join condition. In the hospital database, visits stores IDs rather than repeating every patient, doctor and department name. Joins retrieve those details together.

INNER JOIN
Return only rows that have matching records in both tables.
Hospital scenario: Create a detailed report of each visit, its patient and the doctor responsible.

SELECT
v.visit_id,
p.patient_name,
d.doctor_name,
v.total_bill_kes
FROM visits v
INNER JOIN patients p
ON v.patient_id = p.patient_id
INNER JOIN doctors d
ON v.doctor_id = d.doctor_id;

The report contains 30 rows because every visit references an existing patient and doctor. A patient with repeat visits appears multiple times. Use INNER JOIN when matching records are required.

LEFT JOIN
Preserve every row from the left table and add matching right-table values; unmatched right-side values are NULL.
Hospital scenario: List every doctor, including any who have no visits.

SELECT
d.doctor_id,
d.doctor_name,
v.visit_id
FROM doctors d
LEFT JOIN visits v
ON d.doctor_id = v.doctor_id
ORDER BY d.doctor_id, v.visit_id;

All four doctors appear. In the current dataset each doctor has visits; a doctor without visits would still appear with a NULL visit_id.

RIGHT JOIN
Purpose: Preserve every row from the right table and add matching left-table values; unmatched left-side values are NULL.
Hospital scenario: List every registered patient, including any who have never visited.

SELECT
v.visit_id,
v.total_bill_kes,
p.patient_id,
p.patient_name
FROM visits v
RIGHT JOIN patients p
ON v.patient_id = p.patient_id
ORDER BY p.patient_id, v.visit_id;

All 25 patients appear. Patients with several visits may appear on several rows; patients without visits would have NULL visit details. All supplied patients have at least one visit.

FULL OUTER JOIN
Purpose: Return matches plus unmatched rows from both tables.
Hospital scenario: Reconcile doctor records against visit records.

SELECT
d.doctor_id,
d.doctor_name,
v.visit_id
FROM doctors d
FULL OUTER JOIN visits v
ON d.doctor_id = v.doctor_id;

Matching doctor-visit combinations appear. Unmatched doctors would have NULL visit details, and visits without matching doctors would have NULL doctor details. The supplied foreign-key data has no such unmatched records.

CROSS JOIN
Produce every possible combination of rows from two tables.
Create a planning matrix of all doctors and all departments.

SELECT
d.doctor_name,
dep.department_name
FROM doctors d
CROSS JOIN department dep;

Four doctors multiplied by four departments produces 16 rows. Every doctor is paired with every department, whether or not that doctor actually works there. Use CROSS JOIN only when all combinations are intended.

SELF JOIN
Join a table to itself using aliases to compare different rows in that same table.
Identify pairs of patients registered in the same county.

SELECT
p1.patient_name AS patient_one,
p2.patient_name AS patient_two,
p1.county
FROM patients p1
JOIN patients p2
ON p1.county = p2.county
AND p1.patient_id < p2.patient_id
ORDER BY p1.county;

Patient pairs from the same county are listed. For example, Amina Otieno and Isaac Kamau are both registered in Nairobi. The ID condition avoids pairing a patient with themselves and avoids reversed duplicate pairs.

Join comparison
Join What it preserves Common use
INNER JOIN -Matching rows only- Visits with patient and doctor details
LEFT JOIN -Every left-side row All doctors, including those without visits
RIGHT JOIN -Every right-side row -All patients, including those without visits
FULL OUTER JOIN -All rows from both sides -Reconciliation and mismatch detection
CROSS JOIN -Every possible combination -Planning matrices and combinations
SELF JOIN -Related rows within the same table- Comparing patients in the same county

A join does not automatically aggregate data. If a patient has five visits, joining that patient to visits produces five matching rows. Use aggregation when you need one summary row per patient or doctor.

Common Table Expressions (CTEs)

** What is a CTE?**
A Common Table Expression (CTE) is a named result set defined using WITH. It can be referenced by the main SQL statement that follows it. CTEs break complicated SQL into smaller, more understandable steps.

Basic CTE
Define one temporary named result set for use within a single SQL statement.
Compare total recorded billing associated with each doctor’s visits.

WITH doctor_billing AS (
SELECT
doctor_id,
SUM(total_bill_kes) AS total_billing_kes
FROM visits
GROUP BY doctor_id
)
SELECT
d.doctor_name,
db.total_billing_kes
FROM doctor_billing db
JOIN doctors d
ON db.doctor_id = d.doctor_id
ORDER BY db.total_billing_kes DESC;

One row per doctor shows the doctor’s name and the sum of bills for their visits, ordered from highest to lowest. The CTE aggregates visits; the main query joins in doctor names. The CTE does not create a permanent table.

Multiple CTEs
Define two or more named result sets that can feed into the main query.
Compare doctors by total billing and number of visits.
_
WITH doctor_billing AS (
SELECT
doctor_id,
SUM(total_bill_kes) AS total_billing_kes
FROM visits
GROUP BY doctor_id
),
doctor_visit_counts AS (
SELECT
doctor_id,
COUNT(*) AS total_visits
FROM visits
GROUP BY doctor_id
)
SELECT
d.doctor_name,
db.total_billing_kes,
vc.total_visits
FROM doctors d
JOIN doctor_billing db
ON d.doctor_id = db.doctor_id
JOIN doctor_visit_counts vc
ON d.doctor_id = vc.doctor_id
ORDER BY db.total_billing_kes DESC;_

One row per doctor displays name, total billing and visit count. One CTE calculates billing, another calculates visit counts, and the main query combines them.

Recursive CTE
Repeatedly reference a CTE’s own result to process sequential or hierarchical data.
Generate all dates in a five-day hospital reporting period, including dates without visits.

WITH RECURSIVE reporting_dates AS (
SELECT DATE '2026-06-01' AS report_date
UNION ALL
SELECT report_date + 1
FROM reporting_dates
WHERE report_date < DATE '2026-06-05'
)
SELECT report_date
FROM reporting_dates
ORDER BY report_date;

Five dates are returned: 1–5 June 2026. The anchor query starts at 1 June; the recursive query adds one day each time; the WHERE condition stops after 5 June. Recursive CTEs are also useful for parent-child hierarchies.

CTE with joins and aggregations
Aggregate data in a CTE, then join it to a descriptive table.
Compare departments by visit volume, total billing and average bill.

WITH department_summary AS (
SELECT
department_id,
COUNT(*) AS total_visits,
SUM(total_bill_kes) AS total_billing_kes,
AVG(total_bill_kes) AS average_bill_kes
FROM visits
GROUP BY department_id
)
SELECT
dep.department_name,
ds.total_visits,
ds.total_billing_kes,
ROUND(ds.average_bill_kes, 2) AS average_bill_kes
FROM department_summary ds
JOIN department dep
ON ds.department_id = dep.department_id
ORDER BY ds.total_billing_kes DESC;

Each department appears with visit count, total billing and average bill. The CTE aggregates the visits, and the join adds department names.

CTEs versus subqueries
Feature CTE Subquery
Definition Uses WITH Written inside another query
Readability Often clearer for multi-step logic Convenient for short calculations
Reuse in one statement Can be referenced multiple times, depending on structure May need to be repeated
Recursion Supports WITH RECURSIVE Does not directly provide the same recursive CTE structure
Performance Depends on query and PostgreSQL plan Depends on query and PostgreSQL plan

A CTE is not automatically faster than a subquery. PostgreSQL may inline or materialize a CTE depending on the query, version and optimization choices. Use CTEs primarily for clarity and inspect the execution plan when performance matters.

SQL Subqueries

What is a subquery?
A subquery is a SQL query placed inside another query. It can return a single value, a set of values, a derived table, or a Boolean result used to filter data.

Scalar subquery
Return a single value (one row and one column) for use in an expression or comparison.
Find visits with bills above the average bill across all visits.

SELECT
visit_id,
total_bill_kes
FROM visits
WHERE total_bill_kes > (
SELECT AVG(total_bill_kes)
FROM visits
);

Only visits with bills greater than the overall average are returned. The inner query calculates the average; the outer query compares every visit against it. A scalar subquery must return no more than one row.

Non-correlated subquery
Run independently of the current outer row because it does not reference outer-query columns.
Hospital scenario: Find visits involving patients registered in Kisumu County.

SELECT
visit_id,
patient_id,
total_bill_kes
FROM visits
WHERE patient_id IN (
SELECT patient_id
FROM patients
WHERE county = 'Kisumu'
);

Visits for patients whose county is exactly Kisumu are returned, including visits for Esther Achieng and Brian Omondi. The inner query identifies patient IDs and does not depend on the outer row.

Correlated subquery
Reference a column from the outer query, making the subquery depend logically on each outer row.
Identify doctors who have handled more than five visits.

SELECT
d.doctor_id,
d.doctor_name
FROM doctors d
WHERE (
SELECT COUNT(*)
FROM visits v
WHERE v.doctor_id = d.doctor_id
) > 5;

Doctors 101 and 102 qualify because each handled 10 visits. Doctors 103 and 104 handled five each and do not qualify. The inner query refers to d.doctor_id from the outer query. PostgreSQL may optimize execution rather than literally rerunning it for every row.

Nested subqueries
Place a subquery inside another subquery to answer a question in multiple stages.
Find patients who have at least one visit with a bill above the overall average.

SELECT
patient_id,
patient_name,
county
FROM patients
WHERE patient_id IN (
SELECT patient_id
FROM visits
WHERE total_bill_kes > (
SELECT AVG(total_bill_kes)
FROM visits
)
);

The innermost query calculates the average bill; the middle query identifies patient IDs with an above-average visit; the outer query retrieves those patients. Each patient is returned once even if they have multiple qualifying visits.

IN operator
Check whether a value matches any value in a list or subquery result.
Retrieve visits associated with diagnoses categorized as chronic diseases.

SELECT
visit_id,
patient_id,
diagnosis_id,
total_bill_kes
FROM visits
WHERE diagnosis_id IN (
SELECT diagnosis_id
FROM diagnosis
WHERE diagnosis_category = 'Chronic Disease'
);

The result includes visits linked to diagnoses such as Hypertension, Diabetes and Heart Failure. The inner query returns diagnosis IDs in the exact category Chronic Disease.

EXISTS operator
Test whether a subquery returns at least one row.
Find patients who have had an emergency visit.

SELECT
p.patient_id,
p.patient_name,
p.county
FROM patients p
WHERE EXISTS (
SELECT 1
FROM visits v
WHERE v.patient_id = p.patient_id
AND v.admission_type = 'Emergency'
);

The query returns patients with at least one emergency visit. In the supplied data, visit 1030 is an emergency visit involving patient 10, Peter Ouma. SELECT 1 is used because only existence matters.

ANY operator
Return true when a comparison succeeds for at least one value returned by a subquery.
Find visits whose bills exceed at least one emergency-visit bill.

SELECT
visit_id,
total_bill_kes
FROM visits
WHERE total_bill_kes > ANY (
SELECT total_bill_kes
FROM visits
WHERE admission_type = 'Emergency'
);

There is one emergency visit costing KES 22,000, so the query returns visits costing more than KES 22,000. With multiple non-NULL emergency bills, > ANY means greater than at least one of them, effectively greater than the minimum.

ALL operator
Return true only when a comparison succeeds for every value returned by a subquery.
Find visits whose bills exceed every emergency-visit bill.

SELECT
visit_id,
total_bill_kes
FROM visits
WHERE total_bill_kes > ALL (
SELECT total_bill_kes
FROM visits
WHERE admission_type = 'Emergency'
);

With one emergency visit costing KES 22,000, the query returns visits costing more than KES 22,000. With multiple non-NULL emergency bills, > ALL means greater than the maximum.
ANY versus ALL: > ANY means greater than at least one value; > ALL means greater than every value. Empty subqueries and NULL values have special SQL comparison semantics. In particular, NULL values can affect comparisons, and NOT IN requires care when the subquery can return NULL.

Subqueries in SELECT, FROM and WHERE
Subquery in SELECT
Add a calculated scalar value to every output row.
Hospital scenario: Display every visit’s bill alongside the hospital-wide average.

SELECT
visit_id,
total_bill_kes,
(
SELECT AVG(total_bill_kes)
FROM visits
) AS hospital_average_bill
FROM visits;

Every visit appears with its own bill and the same hospital-wide average. The subquery calculates the average independently of each visit.

Subquery in FROM
Create a derived table that the outer query can treat like a table.
Find doctors whose total recorded billing exceeds KES 100,000.

SELECT
doctor_id,
total_billing_kes
FROM (
SELECT
doctor_id,
SUM(total_bill_kes) AS total_billing_kes
FROM visits
GROUP BY doctor_id
) AS doctor_summary
WHERE total_billing_kes > 100000
ORDER BY total_billing_kes DESC;

The inner query calculates each doctor’s total bills. The outer query filters totals above KES 100,000 and sorts them from highest to lowest. The derived table needs an alias in PostgreSQL.

Subquery in WHERE
Filter records according to a calculated value or set of values.
Find visits whose bills exceed the overall average.

SELECT
visit_id,
patient_id,
total_bill_kes
FROM visits
WHERE total_bill_kes > (
SELECT AVG(total_bill_kes)
FROM visits
);

Only visits with bills above the average are returned. Use this form when the records you need depend on a separate calculation or list of values.

Clause Purpose Hospital application
SELECT Adds a calculated value to the output Show each bill beside the hospital average
FROM Creates a derived table for further analysis Calculate and filter total billing by doctor
WHERE Filters rows using a subquery result Find visits above average billing.

Conclusion
Window functions, joins, CTEs and subqueries are fundamental SQL techniques for analyzing relational data.

Window functions calculate rankings, comparisons and summaries while preserving individual rows.
Joins combine related information stored across tables.
CTEs organize complex queries into understandable steps, while recursive CTEs support sequential and hierarchical analysis.
Subqueries allow one query to depend on another query’s results.

Data Relationships, Data Modelling Schemas, and Joins in Power BI.

Lucy Anyango — Sat, 12 Sep 2026 22:14:18 +0000

Data Modelling in Power BI

What Data Modelling Means in Power BI

Data modelling is the process of defining relationships between tables and organising data structures to support analysis. It determines how data is stored, related, and queried.

The core responsibilities of a data model include:

Establishing relationships between tables: Connecting tables through one-to-many, many-to-many, or one-to-one relationships to enable cross-table filtering and aggregation
Enabling effective analysis: Allowing users to slice data by different attributes (date, geography, category)
Improving performance and efficiency: Reducing data redundancy, minimising memory footprint, and accelerating report loading
Supporting reusability and maintainability: Building scalable structures so measures and calculations can be reused across reports

Why a Well-Designed Data Model Matters

Impact on DAX calculations: In a star schema, DAX measures are simpler and more reliable because dimension tables and fact tables have clearly separated responsibilities — dimensions filter, facts aggregate.

Impact on performance: Power BI's storage engine and query engine are optimised for star schemas. The simpler the relationships (typically one-to-many from dimension to fact), the more efficient filter propagation and query optimization become.

Impact on scalability: As data volumes grow and multiple business subjects or reusable dimensions are needed, flat tables quickly become a bottleneck, while star schemas maintain stable performance.

Impact on maintainability: A clear model structure reduces the risk of modelling errors, enables non-technical users to understand the model, and allows them to build their own reports.

Comparison of Three Data Modelling Approaches

Flat Table

Definition: A flat table is a fully denormalized structure where all data is stored in a single table. Each row contains both fact measures and all related descriptive attributes.

Table structure: One wide table containing both transactional data (e.g., sales amount, quantity) and descriptive data (e.g., product name, category, customer city). No relationships, no separate dimension or fact tables.

Advantages:

Simplest structure, easy to understand
Fast to build for small, simple datasets
No relationships to manage

Disadvantages:

Severe data redundancy: Descriptive information such as product category is repeated in every row
Performance problems: As data volume grows, storage bloats and queries slow down
Difficult maintenance: Updating a product category requires modifying all related rows
Complicated DAX: Cannot clearly separate filter context from aggregation logic
Not scalable: Cannot be reused across multiple business subjects

When appropriate: Extremely small and simple datasets, limited dimensions, no need to reuse dimensions across multiple fact tables, and low performance/scalability requirements.

Implications for Power BI performance and complexity: Not recommended for any scaled analytics. A fully denormalised structure cannot fully leverage Power BI's relationship engine, resulting in inefficient queries. Model complexity appears low on the surface (no relationships), but the performance cost of data bloat far exceeds the overhead of managing relationships.

Star Schema

Definition: A star schema is a mature dimensional modelling approach that requires the modeller to classify tables into two categories: dimension tables and fact tables. The fact table sits at the centre, with dimension tables connected directly to it, forming a star pattern.

Table structure:

Dimension tables: Describe business entities (product, customer, date, geography) and contain unique key columns plus descriptive columns. Relatively few rows.
Fact tables: Store measurable business events (sales orders, inventory balances) and contain dimension key columns plus numeric measure columns. Can be very large and continuously growing.

Advantages:

Optimal performance: Simple one-to-many relationships make filter propagation efficient, and the query engine optimises best
Simplified DAX: Dimension and fact responsibilities are separated, making measure logic clear
Easy to understand: Intuitive structure that both business users and AI tools can grasp easily
Highly scalable: Supports adding new dimensions without affecting existing structure
High compression efficiency: VertiPaq's columnar compression works best with star schemas

Disadvantages:

Dimension tables may have some redundancy (e.g., category names repeated across product rows)
Highly complex hierarchies may require additional management

When appropriate: Almost all Power BI production models. When supporting multiple business subjects, reusable dimensions, large data volumes, or a good DAX experience is needed, the star schema is the first choice.

Implications for Power BI performance and complexity: The star schema is Power BI's recommended design pattern. Relationships are simple (typically single-direction one-to-many), filter paths are clear, and both the VertiPaq storage and query engines are optimised for this structure.

_Diagram**:

Snowflake Schema

Definition: A snowflake schema is an extension of the star schema in which dimension tables are further normalised and split into multiple related tables. Hierarchies (e.g., product → subcategory → category) are broken into separate tables.

Table structure: The fact table remains at the centre, but dimensions no longer connect directly. For example, the product dimension points to a subcategory table, which points to a category table. Queries must traverse multiple relationship layers to reach the top-level descriptive attributes.

Advantages:

Reduced data redundancy: Category names are stored once rather than repeated in every product row
Suited to already-normalised source systems: If the data warehouse is already snowflaked, it can be used directly
More flexible hierarchy management: Simpler updates when hierarchy attributes change independently

Disadvantages:

Increased number of relationships: Filtering must traverse multiple relationship chains, increasing query complexity
Degraded performance: More joins mean the engine processes more tables, and queries are typically slower than with star schemas
Complicated DAX: Requires understanding filter propagation across multiple relationship layers
Harder to understand: Business users struggle to intuitively find the attributes they need

When appropriate: When source data is already normalised, when dimensions are extremely large with complex hierarchies, or when attributes change frequently and need independent management.

Implications for Power BI performance and complexity: Snowflake schemas are generally not recommended in Power BI. Although Power BI supports snowflake structures, the extra joins degrade query performance and increase model complexity. Best practice is to denormalise (flatten) snowflake dimensions in Power Query, converting them into single dimension tables within a star schema.

Diagram references:

Fact Tables and Dimension Tables

The Difference Between a Fact Table and a Dimension Table

A fact table stores the measurable, numeric events of a business — the transactions, orders, sales, or activities that occur. A dimension table stores the descriptive context that gives those events meaning — who, what, where, when, and how.

In short:

Fact table = what happened, how much, how many
Dimension table = who, what, where, when, why

What Information Is Normally Stored in a Fact Table

A fact table normally stores:

Foreign keys that link to dimension tables (e.g., CustomerID, ProductID, DateID)
Numeric measures that represent business events (e.g., SalesAmount, Quantity, Discount, Cost)
Degenerate dimensions in some cases (e.g., OrderNumber stored directly in the fact table)
Transaction-level or aggregated-level records depending on the grain

Fact tables are typically narrow in columns but very tall in rows. They grow continuously as new transactions occur.

What Information Is Normally Stored in a Dimension Table

A dimension table normally stores:

A primary key that uniquely identifies each row (e.g., CustomerID, ProductID, DateID)
Descriptive attributes used for filtering, grouping, and labelling (e.g., CustomerName, City, Country, ProductName, Category, Year, Month)
Hierarchical attributes that support drill-down (e.g., Country → Region → City, Category → SubCategory → Product)
Static or slowly changing data that describes business entities

Dimension tables are typically wide in columns but short in rows. They change infrequently compared to fact tables.

Measures/Numeric Business Events Versus Descriptive Attributes

Measures (fact table): Numeric values that can be aggregated — summed, averaged, counted, or compared. Examples: SalesAmount, Quantity, Revenue, Profit, Discount, UnitPrice.

Descriptive attributes (dimension table): Text or categorical values that provide context. Examples: CustomerName, ProductCategory, OrderDate, ShipCity, EmployeeName.

A measure answers "how much?" A descriptive attribute answers "which one?" or "what kind?"

The Concept of the Grain/Granularity of a Fact Table

The grain of a fact table defines the level of detail at which each row is recorded. It answers the question: "What does one row in this fact table represent?"

Examples of grain:

One row per sales transaction line (finest grain)
One row per order (coarser grain)
One row per day per product per store (aggregated grain)

Choosing the correct grain is critical because:

It determines what questions the model can answer
It affects storage size and query performance
It must be consistent across all rows in the fact table
Mixing grains in one table causes incorrect aggregations and double-counting

Examples of Fact Tables

FactSales: One row per sales transaction line, containing SalesAmount, Quantity, Discount, and foreign keys to DimCustomer, DimProduct, DimDate, DimLocation
FactOrders: One row per order, containing OrderQuantity, OrderAmount, and keys to DimCustomer, DimDate, DimProduct
FactTransactions: One row per financial transaction, containing Debit, Credit, Balance, and keys to DimAccount, DimDate, DimCustomer
FactInventory: One row per product per store per day, containing QuantityOnHand, InventoryValue
FactShipments: One row per shipment, containing FreightCost, QuantityShipped, and keys to DimCarrier, DimDate, DimLocation

Examples of Dimension Tables

DimCustomer: CustomerID (PK), CustomerName, City, Country, Segment, JoinDate
DimProduct: ProductID (PK), ProductName, Category, SubCategory, UnitPrice, Color
DimDate: DateID (PK), FullDate, Year, Quarter, Month, Week, DayOfWeek, IsWeekend
DimLocation: LocationID (PK), City, State, Country, Region, PostalCode
DimEmployee: EmployeeID (PK), EmployeeName, Department, Role, HireDate

Relationships and Joins Between Tables

Relationship Types

In a Power BI data model, relationships are the mechanism that connects tables and enables filter propagation. Relationships define table types through cardinality:

One-to-many: The most common relationship type. A unique key in the dimension table (the "one" side) corresponds to multiple records in the fact table (the "many" side). Filters flow from dimension to fact.
Many-to-many: Used when neither table has unique values, requiring a bridge table to resolve. The bridge table contains unique combinations of keys from both sides and establishes one-to-many relationships with each.
One-to-one: Less common; both tables have unique keys.

One-to-Many (1:*)

How it works: A unique value in the "one" side (dimension table) relates to multiple occurrences of that value in the "many" side (fact table). The filter flows from the one side to the many side.

Practical example: DimCustomer has one row per customer (CustomerID is unique). FactSales has many rows per customer (CustomerID repeats for every sale). One customer → many sales.

When it should be used: This is the default and most common relationship type. It should be used whenever a dimension table connects to a fact table. It is the backbone of the star schema.

One-to-One (1:1)

How it works: A single row in one table relates to exactly one row in another table. Both columns contain unique values. Filter propagation can flow in either direction, but only one direction is active at a time.

Practical example: DimEmployee and DimEmployeeDetails — each employee has exactly one detail record (e.g., social security number, bank account). EmployeeID is unique in both tables.

When it should be used: Rarely. It is used when two tables contain different attributes of the same entity and must be kept separate for security or organisational reasons. In most cases, the two tables should be merged into one dimension table.

Many-to-Many (:)

How it works: Multiple rows in one table relate to multiple rows in another table. Neither side contains unique values. A bridge table (also called a junction table) is required to resolve the relationship into two one-to-many relationships.

Practical example: Students and Courses. One student can enroll in many courses. One course can have many students. A bridge table called Enrollment contains StudentID and CourseID combinations.

When it should be used: When there is a genuine many-to-many business relationship that cannot be resolved by adding attributes to either side. It is also used when connecting a fact table to another fact table at a different grain.

The Importance of Primary Keys, Foreign Keys, Unique Values, Cardinality, Referential Integrity, and Active/Inactive Relationships

Primary Keys: A primary key is a column (or combination of columns) that uniquely identifies each row in a table. In DimCustomer, CustomerID is the primary key — no two customers share the same ID. Primary keys must contain unique values and cannot be null.

Foreign Keys: A foreign key is a column in one table that refers to the primary key in another table. In FactSales, CustomerID is a foreign key — it points to the CustomerID in DimCustomer. Foreign keys can contain duplicate values because many sales can belong to the same customer.

Unique Values: The "one" side of a one-to-many relationship must contain unique values. If CustomerID appears twice in DimCustomer, the relationship cannot be created or will produce incorrect results. The "many" side does not require unique values.

Cardinality: Cardinality defines how many rows in one table relate to rows in another. The three types are one-to-many, one-to-one, and many-to-many. Choosing the correct cardinality is essential for accurate filter propagation and DAX results.

Referential Integrity: Referential integrity means that every foreign key value in the fact table has a matching primary key value in the dimension table. If FactSales contains CustomerID 999 but DimCustomer has no CustomerID 999, the sale is an orphan record. Power BI handles this by creating a blank row in the dimension for unmatched keys, but this can hide data quality problems. Referential integrity should be enforced at the source whenever possible.

Active and Inactive Relationships: A table can have multiple relationships to another table, but only one can be active at a time. The active relationship is used by default in DAX calculations. Inactive relationships must be explicitly activated using the USERELATIONSHIP function in DAX. This is common with role-playing dimensions — for example, a DimDate table connected to FactSales by OrderDate (active), ShipDate (inactive), and DeliveryDate (inactive).

Example: CustomerID contains unique values in DimCustomer because each customer appears once. CustomerID appears multiple times in FactSales because one customer can place many orders. The primary key enforces uniqueness in the dimension. The foreign key allows repetition in the fact. The one-to-many relationship between them enables filtering: selecting a customer in DimCustomer filters all their sales in FactSales.

Filter Direction

What Filter Direction Means

Filter direction determines the path along which a filter travels between two related tables. In Power BI, filter direction can be:

Single: The filter flows from the "one" side to the "many" side only.

Both: The filter flows in both directions between the two tables.
The default and recommended direction is single-direction: from dimension table to fact table. This ensures predictable filter propagation and avoids ambiguous results.

Bidirectional filtering should be used cautiously and only when there is a clear requirement, as it can lead to unexpected filter propagation and circular relationships.

Example Dimensions

A role-playing dimension is a dimension table that relates to the same fact table multiple times in different roles. The classic example is a date dimension: the same date table can filter a sales fact by order date, ship date, and delivery date separately.

What Bidirectional Filtering Is

Bidirectional filtering is a relationship setting in Power BI where filters propagate in both directions between two tables. Instead of the default single-direction flow (from the "one" side to the "many" side), a bidirectional relationship allows filters to travel from the "many" side back to the "one" side as well.

In Power BI Model View, this is configured by setting the Cross filter direction to Both on a relationship.

Why It Should Be Used Carefully

Bidirectional filtering is powerful but dangerous. It should be used carefully because it:

Introduces ambiguity into filter propagation
Can produce unexpected and incorrect results
Creates unnecessary model complexity
Degrades query performance
Makes the model harder to debug and maintain
Can create circular relationships that Power BI cannot resolve

Problem 1: Ambiguous Filter Paths

An ambiguous filter path occurs when there is more than one route for a filter to travel between two tables. When multiple paths exist, Power BI cannot determine which path to use, and the result becomes unpredictable or incorrect.

Problem 2: Unnecessary Model Complexity

Every bidirectional relationship adds a layer of complexity. The more bidirectional relationships there are, the harder it becomes to predict filter propagation, understand measure results, identify bugs, and document the model.

Problem 3: Performance Degradation

Bidirectional filtering increases the number of filter operations the engine must perform. Each bidirectional relationship forces the query engine to evaluate filter propagation in both directions, which increases query execution time, consumes more memory, slows down report rendering, and reduces the effectiveness of VertiPaq compression optimisations.

Problem 4: Circular Relationships

Bidirectional filtering can create circular relationships. A circular relationship occurs when a filter can travel from Table A to Table B and back to Table A through a different path. Power BI cannot resolve circular relationships and will either disable one of the relationships, return an error, or produce incorrect results silently.

Joins in Power Query

What a Join Is

A join is an operation that combines two tables into one by matching rows based on a common column (a key). In Power Query, this is done using Merge Queries, which lets you select two tables, choose the matching columns, and pick a join type.

The result is a single table containing columns from both source tables.

Sample Tables Used in All Examples

Customers
CustomerID CustomerName
1 Alice
2 Brian

Orders

OrderID CustomerID Amount
101 1 500
102 1 300

Note: CustomerID 3 and 4 have no orders. CustomerID 5 has an order but no customer.

1. Left Outer Join

How it works: Returns all rows from the left table and matching rows from the right table. Unmatched rows from the left keep their data; right-side columns become null.

Records retained: All left table rows + matching right table rows.

Practical example: Keep all customers, attach their orders where available.

Expected output:

CustomerID CustomerName OrderID Amount
1 Alice 101 500
1 Alice 102 300
2 Brian 103 700
3 Carol null null
4 David null null

2. Right Outer Join

How it works: Returns all rows from the right table and matching rows from the left table. Unmatched rows from the right keep their data; left-side columns become null.

Records retained: All right table rows + matching left table rows.

Practical example: Keep all orders, attach customer details where available.

Expected output

CustomerID CustomerName OrderID Amount
1 Alice 101 500
1 Alice 102 300
2 Brian 103 700
null null 104 200

3. Full Outer Join

How it works: Returns all rows from both tables. Matches are combined; unmatched rows from either side get nulls in the opposite columns.

Records retained: All rows from both tables.

Practical example: See every customer and every order, whether or not they match.

Expected output:
CustomerID CustomerName OrderID Amount
1 Alice 101 500
1 Alice 102 300
2 Brian 100 700
3 Carol null null
4 David null null
null null 104 200

4. Inner Join

How it works: Returns only rows where the key exists in both tables. Unmatched rows from either side are discarded.

Records retained: Only matching rows from both tables.

Practical example: Show only customers who have placed orders, and only orders that belong to a known customer.

Expected output:

CustomerID CustomerName OrderID Amount
1 Alice 101 500
1 Alice 102 300
2 Brian 103 700

5. Left Anti Join

How it works: Returns only rows from the left table that have no match in the right table.

Records retained: Left table rows with no match in the right table.

Practical example: Find customers who have never placed an order.

Expected output:
CustomerID CustomerName
3 Carol
4 David

6. Right Anti Join

How it works: Returns only rows from the right table that have no match in the left table.

Records retained: Right table rows with no match in the left table.

Practical example: Find orders that do not belong to any known customer.

Expected output:

OrderID CustomerID Amount
104 5 200

Power Query Joins vs Power BI Relationships

Overview

Power Query joins and Power BI relationships are two different operations that happen at two different stages of the Power BI workflow. They are often confused because both "connect" tables — but they do so in fundamentally different ways.

Aspect | Power Query Merge (Join) | Power BI Relationship |
Stage | Data preparation (before loading) | Data modelling (after loading) |
Effect | Physically combines columns into one table | Logically connects separate tables |
Storage | One wide table stored in the model | Separate tables stored in the model |
Filtering | Not applicable — data is already combined | Enables filter propagation between tables |
Reversible | Yes, by editing the query steps | Yes, by deleting the relationship |
Performance | Increases table width and size | Keeps model compact and optimised |

1. Does a Power Query Merge Physically Combine Columns/Data from Tables?

Yes. A merge in Power Query physically combines columns from two tables into a single table. The result is one wider table that contains columns from both source tables.

Example:
Customers
Customers CustomerName
1 Alice
2 Brian

Orders
OrderID CustomerID Amount
101 1 500
103 2 700

After a Left Outer Merge on CustomerID:
CustomerID CustomerName OrderID Amount
1 Alice 101 500
2 Brian 102 700

The result is a single physical table with all columns. CustomerName is now repeated for every order. The original two tables no longer exist separately in the model — only the merged result is loaded.

2. Does Creating a Relationship Combine the Tables?

No. A relationship does not combine tables. It keeps them separate and creates a logical link between them based on a common key.

Example: Using the same Customers and Orders tables, if you load them separately and create a relationship on CustomerID:

Customers table stays as:
CustomerID CustomerName

1 Alice

2 Brian

Orders table stays as:
OrderID CustomerID Amount
101 1 500
102 2 700

They remain two separate tables. The relationship allows filtering across them, but no columns are physically merged.

3. At What Stage of the Power BI Workflow Does Each Operation Occur?

Power Query Merge: Occurs during the data preparation stage, before data is loaded into the Power BI data model. It is part of the ETL (Extract, Transform, Load) process.

Power BI Relationship: Occurs during the data modelling stage, after data is loaded into the model. It is created in Model View or automatically detected by Power BI.

Workflow:
Step 1: Get Data
↓
Step 2: Power Query (Transform + Merge) ← Power Query joins happen here
↓
Step 3: Load to Data Model
↓
Step 4: Create Relationships in Model View ← Relationships happen here
↓
Step 5: Build Reports and Visuals

4. When Would You Choose a Merge Instead of a Relationship?

Use a merge when:

You need columns from both tables in a single flat output, such as for export or a calculated column.
You need to filter rows based on existence in another table (e.g., anti joins to find unmatched records).
You need to replace missing values with values from another table (e.g., lookup).
You are preparing data for a system that does not support relationships.
You need to combine tables at the same grain and the result is intended to be one table.

Example: Finding customers who have never placed an order using a Left Anti Join. This is a Power Query operation because it filters rows, not a modelling operation.

Use a relationship when:

You are building a star schema for reporting and analytics.
You need filter propagation from dimensions to facts.
You want to keep the model compact and performant.
You need to write DAX measures that aggregate across tables.
You want to avoid data redundancy.

Example: Connecting DimCustomer to FactSales for slicing sales by customer attributes. This is a relationship because the tables serve different roles (dimension vs fact) and should remain separate.

5. How Can Excessive Merging Affect the Structure of a Data Model?

Excessive merging produces a wide, flat table with many repeated values. This leads to:

Data redundancy: Descriptive attributes repeat in every row.
Increased storage size: More columns and more rows consume more memory.
Poor compression: VertiPaq compresses poorly when the same text repeats.
Slower queries: Larger tables take longer to scan and aggregate.
Loss of relationships: Once merged, you cannot create relationships between the original tables.
Difficult maintenance: Updating a dimension attribute requires refreshing the entire merged table.
No reusability: The merged table serves one purpose only; it cannot be reused for other analyses.

6. Why Might Keeping Fact and Dimension Tables Separate Be Preferable in a BI Model?

Keeping fact and dimension tables separate is the foundation of the star schema, which is Power BI's recommended design pattern. Benefits include:

Performance: Simple one-to-many relationships enable efficient filter propagation and query optimisation.
Compression: Dimension tables store unique values once, improving VertiPaq compression.
Reusability: The same dimension can serve multiple fact tables.
Scalability: New dimensions can be added without restructuring the model.
Maintainability: Updating a dimension attribute requires refreshing only one small table.
Clarity: Business users can easily understand the model structure.
DAX simplicity: Measures are simpler when dimensions filter and facts aggregate.
Flexibility: Users can slice and dice data by any dimension attribute without pre-defining combinations.

Conclusion and Recommendation

For a typical business intelligence project, I would use a star schema with one-to-many, single-direction relationships.

Justification

Query and report performance: One-to-many relationships between dimensions and facts give the fastest filter propagation and query execution. VertiPaq is optimised for this structure.
DAX simplicity: Measures are simple — dimensions filter, facts aggregate. No complex filter traversal is needed.
Model readability: A fact table surrounded by dimensions is intuitive for both developers and business users.
Scalability: New dimensions can be added without restructuring the model. Handles growing data volumes well.
Data redundancy: Dimension attributes are stored once, not repeated in every fact row, keeping the model compact.
Maintainability: Updating a dimension attribute requires refreshing one small table, not the entire model.
Ease of creating reports: Users can slice facts by any dimension attribute without pre-defined combinations.
Filter propagation: Single-direction filtering from dimension to fact is predictable, avoids ambiguity, and prevents circular relationships.
Model complexity: Star schemas are simple — no multi-layer chains, no ambiguous paths, no unnecessary bidirectional filtering.

Why Not the Others

Flat table: Rejected — severe redundancy, poor performance, no relationship support, not scalable.
Snowflake schema: Rejected — extra joins slow queries, complicate DAX, increase model complexity, and are harder for users to understand. Dimensions should be flattened in Power Query.

Relationship Design I Would Implement

One-to-many (1:*) between every dimension and the fact table.
Single-direction filtering from dimension to fact in all standard cases.
Bidirectional filtering only for bridge tables in genuine many-to-many scenarios, documented and tested.
One-to-one avoided — merge tables instead.
Many-to-many resolved through a bridge table with two one-to-many relationships.
Role-playing dimensions handled with multiple relationships and "User relation" in DAX.
Star schema as the target model, with all snowflake dimensions flattened in Power Query before loading.

This design delivers the best balance of performance, simplicity, scalability, and maintainability for a typical BI project.

GIT WORKFLOW

Lucy Anyango — Mon, 24 Aug 2026 20:31:46 +0000

## What is Git?

Git is a version control system used to track changes in files and maintain a history of a project.

It allows you to:

Track changes
Create versions of your work
Work with branches
Collaborate with others
Return to previous versions
Git vs GitHub

Git runs on your computer and tracks your project locally.

GitHub is an online platform where Git repositories can be stored, shared, and collaborated on.

## The Basic Git Workflow
Important workflow to understand is:

Working Directory
↓
git add
↓
Staging Area
↓
git commit
↓
Local Repository
↓
git push
↓

## GitHub

Check your changes git status

Shows which files have been added, modified, or are waiting to be staged.

Stage changes git add .

Moves your changes to the staging area.

Commit changes git commit -m "Update project files"

Creates a saved version of the staged changes.

Push to GitHub git push

Uploads your committed changes to the GitHub repository.

## Creating a Repository

Create a project folder and initialize Git:

mkdir my-project
cd my-project
git init

git init turns the folder into a Git repo.

## Connecting to GitHub

Connect your local repository to GitHub:

git remote add origin git@github.com:username/repository.git

Check the connection:

git remote -v

Then push the project:

git branch -M main
git push -u origin main

## Git Commands to Remember
Command Purpose

git status Check changes
git add . Stage changes
git commit -m "message" Save changes
git push Upload to GitHub

Remember this workflow:

EDIT
↓
git status
↓
git add
↓
git commit
↓
git push
↓
GITHUB

Once you understand this cycle, you have the best foundation for using Git and GitHub effectively.

DEV Community: Lucy Anyango

Window Functions, Joins, CTEs and Subqueries Using a Hospital Database

Introduction

SQL Window Functions

SQL Joins

Common Table Expressions (CTEs)

SQL Subqueries

Data Relationships, Data Modelling Schemas, and Joins in Power BI.

Data Modelling in Power BI

What Data Modelling Means in Power BI

Why a Well-Designed Data Model Matters

Comparison of Three Data Modelling Approaches

Flat Table

Star Schema

Snowflake Schema

Fact Tables and Dimension Tables

The Difference Between a Fact Table and a Dimension Table

What Information Is Normally Stored in a Fact Table

What Information Is Normally Stored in a Dimension Table

Measures/Numeric Business Events Versus Descriptive Attributes

The Concept of the Grain/Granularity of a Fact Table

Examples of Fact Tables

Examples of Dimension Tables

Relationships and Joins Between Tables

Relationship Types

One-to-Many (1:*)

One-to-One (1:1)

Many-to-Many (:)

The Importance of Primary Keys, Foreign Keys, Unique Values, Cardinality, Referential Integrity, and Active/Inactive Relationships

Filter Direction

What Filter Direction Means

Example Dimensions

What Bidirectional Filtering Is

Why It Should Be Used Carefully

Problem 1: Ambiguous Filter Paths

Problem 2: Unnecessary Model Complexity

Problem 3: Performance Degradation

Problem 4: Circular Relationships

Joins in Power Query

What a Join Is

Sample Tables Used in All Examples

1. Left Outer Join

2. Right Outer Join

3. Full Outer Join

4. Inner Join

5. Left Anti Join

6. Right Anti Join

Power Query Joins vs Power BI Relationships

Overview

1. Does a Power Query Merge Physically Combine Columns/Data from Tables?

2. Does Creating a Relationship Combine the Tables?

3. At What Stage of the Power BI Workflow Does Each Operation Occur?

4. When Would You Choose a Merge Instead of a Relationship?

5. How Can Excessive Merging Affect the Structure of a Data Model?

6. Why Might Keeping Fact and Dimension Tables Separate Be Preferable in a BI Model?

Conclusion and Recommendation

Justification

Why Not the Others

Relationship Design I Would Implement

GIT WORKFLOW