Lab: Building the Hospital Data Model

Real-Life Hospital Example

The CFO wants a single report where clicking one doctor’s name instantly filters appointments, treatments, and billing together. That kind of one-click filtering only works if the underlying data model is built correctly.

Practical Exercise (Hands-On)

In Model view, connect patient_id (patients to appointments and billing), doctor_id (doctors to appointments), appointment_id (appointments to treatments), and treatment_id (treatments to billing). Confirm every relationship is one-to-many with a single, dimension-to-fact filter direction.

Step-by-Step

⚠️ Caution: This lab was developed using Microsoft Power BI Desktop v2.155.756.0 (64-bit). If you are using a different version, you may notice minor differences in the menus, icons, or interface. The overall functionality and steps should remain largely the same.

1. Click the Model view icon on the left sidebar of Power BI Desktop.
2. Drag each table’s title bar so all five tables (patients, doctors, appointments, treatments, billing) are visible with dimension tables on top and fact tables below.
3. Drag patient_id from patients onto patient_id in appointments to create a relationship; confirm Cardinality is One to Many and click OK.
4. Drag doctor_id from doctors onto doctor_id in appointments (One to Many).
5. Drag appointment_id from appointments onto appointment_id in treatments (One to Many).
6. Drag treatment_id from treatments onto treatment_id in billing (One to Many).
7. Drag patient_id from patients onto patient_id in billing (One to Many) so billing can also be filtered directly by patient.
8. Double-click each relationship line to confirm Cardinality = One to many and Cross filter direction = Single, flowing from the dimension table to the fact table.
9. Fix any relationship that Power BI auto-detected incorrectly (dotted line or wrong cardinality).
10. Switch to Report view, add a Table visual with doctors[first_name] and appointments[appointment_id], and confirm the filtered count changes correctly when you click a doctor’s row.
11. Save the file.

Expected Result

Model view shows five tables connected by five one-to-many relationship lines, all with single-direction filter arrows pointing from dimension tables to fact tables.

Mini Lab / Scenario Task

Create a standalone Date dimension table and connect it to appointment_date, bill_date, and treatment_date so a single date slicer can filter all three at once.