Data Modelling, Relationships & Joins in Power BI

Introduction

Data modelling in Power BI is the process of organizing data into tables and defining how those tables are connected so that information can be analyzed correctly.

A good model makes it easier to build reports,manage filters and write DAX calculations while keeping performance solid as the data keeps growing.

In this article I work through three modelling approaches flat table, star schema and snowflake schema using the hospital visit records. It holds patient visits together with the doctor who saw them, the department they visited, the diagnosis given, the insurance provider on file and the resulting bill.

Data Modelling in Power BI

Flat Table

A flat table stores transactional and descriptive data in one table. It's simple to set up and works fine for small datasets. As the dataset grows, it becomes harder to maintain the raw hospital export (RawHospitalVisits) represents this structure: every visit row repeats the patient's name, the doctor's name, the department and the insurance provider.

Advantages

• Simple to understand.
• Easy to import and start analyzing.
• Requires few or no relationships.
• Appropriate for small datasets.
• Convenient for simple dashboards.

Disadvantages

• Creates data redundancy.
• Doctor, department, and diagnosis information may be repeated.
• The table can become very wide.
• Data maintenance becomes harder.
• The model can become unnecessarily large.

Star Schema

A star schema separates the data into a central fact table connected directly to dimension tables. This structure is cleaner, since it separates the business events (visits) from the descriptive information (who, where, when). It supports simple DAX, predictable filtering and good report performance.

Advantages

• Provides a clear and understandable model.
• Reduces unnecessary duplication.
• Makes DAX measures easier to create.
• Provides efficient filter propagation.
• Supports scalable reporting.
• Makes the model view easier to understand.
• Works well for analytical reporting.

Disadvantages

• Requires more planning than a flat table.
• May require Power Query transformations.
• Poorly designed keys can cause relationship problems.
• Users need to understand fact and dimension concepts.

Snowflake Schema

A snowflake schema is an advanced version of the star schema, splitting some dimension information into additional tables. In Hospital records, DimDoctor's Specialty was separated into its own DimSpecialty table, DimPatient's Region into DimRegion, and DimDate's Month into DimMonth. This reduces redundancy but adds relationships and complexity.

Advantages

• Reduces duplication within dimensions.
• Can represent complex hierarchical structures.
• Can be useful when dimensions contain multiple levels.
• May be appropriate when source data is already highly normalized.

Disadvantages

• Creates more tables and relationships.
• Makes the model more complicated.
• Can make report development less intuitive.
• Can create longer filter paths.
• May require more complex DAX and relationship management.

Comparison

Approach	Structure	Advantages	Disadvantages	Best Fit
Flat	One wide table	Simple; few relationships	Redundancy; wide table	Small/simple datasets
Star	Fact + dimensions	Readable; scalable; DAX-friendly	Requires planning	Most BI projects
Snowflake	Fact + normalized dimensions	Less duplication; complex hierarchies	More tables; longer filter paths	Complex structures

Keys, Cardinality & Referential Integrity

PatientID is unique in DimPatient, making it the primary key. The same key appears many times in FactVisits as a foreign key, since one patient can make multiple visits a one-to-many (1:*) relationship.

Referential integrity means every foreign key should have a matching record in the related table; when it doesn't, Power BI shows it as a blank, unresolved member usually a sign of a data-entry problem worth investigating.

Active & Inactive Relationships

Power BI can hold more than one relationship between the same two tables, but only one is active at a time. DimDate[Date] is actively related to FactVisits[DateOfVisit]. A second, inactive relationship could connect DimDate[Date] to FactVisits[DateOfDischarge], invoked deliberately in DAX with USERELATIONSHIP().

Filter Direction

Single-direction filtering-filters move from dimensions into FactVisits-selecting "Oncology" in DimDepartment filters the related visits. Bidirectional filtering moves both ways, but should be used carefully since it can make filter paths harder to reason about.

Building the Hospital records Model, Step by Step

1. Import and inspect the data Home → Get Data → Excel Workbook, select the hospital records file. Clean in Power Query: numeric Age/Bill Amount, proper Date of Visit, consistent text casing for Doctor Name and Diagnosis.

2. The flat table loaded as-is, the cleaned sheet is one row per visit with every patient/doctor/department/insurance detail repeated.

3. Building the star schema split into FactVisits, DimPatient, DimDoctor, DimDate, DimDepartment, then created these relationships in Model View:

DimPatient[PatientID]       1 -* FactVisits[PatientID]
DimDoctor[DoctorID]         1 -* FactVisits[DoctorID]
DimDepartment[DepartmentID] 1 -* FactVisits[DepartmentID]
DimDate[DateID]             1 -* FactVisits[DateOfVisit]

4. The snowflake schema -separated Specialty out of DimDoctor into DimSpecialty (FactVisits → DimDoctor → DimSpecialty).

5. Fact vs dimension tables -FactVisits holds the measure Bill Amount (KES) plus foreign keys to every dimension. Grain = one row per visit (VisitID).

Demonstrating Relationship Types

• One-to-Many (1:*) -DimPatient[PatientID] → FactVisits[PatientID]. Used for every dimension-to-fact link.
• One-to-One (1:1) -DimPatient ↔ PatientInsuranceProfile, each patient has exactly one insurance profile (NHIF Number, Coverage Tier).
• Many-to-Many (*:*) -DimDoctor ↔ DimDepartment (a doctor can consult across departments), resolved with a BridgeDoctorDept table instead of a direct *:* relationship.

Power Query Joins

Two reference queries Patients and Visits with matching and non-matching records:

PatientID	Patient Name
6892	Jane Mutua
6413	James Atieno
4073	John Kamau
9754	Anne Mutua
6382	Susan Odhiambo

VisitID	PatientID	Diagnosis	Bill Amount (KES)
V1	6892	Diabetes	28,108.15
V2	6413	Hypertension	27,742.06
V3	4073	Asthma	21,428.48
V4	9754	Diabetes	24,371.93
V5	9999	Malaria	15,000.00

(Susan Odhiambo has no visit; V5 references an unregistered walk-in, PatientID 9999.)

Join	Result
Left Outer	All 5 patients + matches (Susan blank)
Right Outer	All 5 visits + matches (V5 blank)
Full Outer	All 6 records -everyone and every visit
Inner	Only 4 matched rows
Left Anti	1 row -Susan Odhiambo (never visited)
Right Anti	1 row -V5 (unregistered PatientID 9999)

Power Query Joins vs Power BI Relationships

A merge physically combines data during data prep merging Patients into Visits copies Patient Name directly into the Visits query. A relationship keeps DimPatient and FactVisits separate; it's created afterwards in Model View and only defines how the tables interact.

Use a merge when data genuinely needs to become one dataset. For the main Hospital records model, relationships were the right choice excessive merging would rebuild a flat table like the original RawHospitalVisits export.

Recommended Power BI Model Design

Star schema, with FactVisits connected to DimPatient, DimDoctor, DimDate and DimDepartment via one-to-many, single-direction relationships. It gives the best balance of performance, simplicity and scalability the flat table creates repeated data as visits grow, and the snowflake schema adds complexity that isn't worth it for most reporting needs. Many-to-many relationships, bidirectional filtering, and merges are reserved for the specific cases like the doctor/department bridge table that genuinely need them.

Conclusion

Flat tables, star schemas and snowflake schemas each have their place, but for this hospital dataset and most typical BI projects a star schema with one-to-many relationships and mainly single-direction filtering keeps the model organised, reduces unnecessary complexity and makes reporting, filtering and future changes easier to manage.