Power BI: Data Modelling, Relationships and Joins

Introduction

When initiating a healthcare dataset in Power BI, the focus is often on creating visually appealing dashboards rather than the underlying data structure. The objective is to generate visuals that answer practical inquiries, such as patient visit counts per facility, most utilized health services, trends in patient visits over time, and locations with the highest activity.

However, it quickly becomes apparent that merely producing a chart is only a part of the task. For a visual to accurately address a question, the data supporting it must be properly structured. If tables lack proper organization or relationships are inaccurate, a dashboard can appear polished yet still yield misleading results. This realization underscores the significance of data modelling in Power BI projects.

Data Modelling Fundamentals

Data modelling revolves around structuring tables and defining their connections to ensure Power BI interprets and analyzes the information accurately. A well-designed model facilitates precise calculations, simpler Data Analysis Expressions (DAX), efficient filtering, enhanced performance, and simplified maintenance. In the context of the healthcare dataset, the focus shifts from mere rows and columns to representation of real-world events and entities.

For instance, a patient visit is considered an event, while a patient, health facility, service, or date offers descriptive insights into that event.

The article outlines the process of transforming the healthcare data into a model capable of supporting reliable reporting. It delves into concepts such as fact and dimension tables, grain, flat, star, and snowflake schemas, keys, relationships, cardinality, filter direction, and referential integrity. Additionally, it explores Power Query Merge Queries and the six primary join types, highlighting the distinction between a join and a Power BI relationship.

The ultimate objective is to construct a data model that enables dashboard visuals to precisely and flexibly respond to health-related inquiries.

The Modelling Process

Prior to establishing relationships or selecting a schema, comprehending the data being modelled is essential. In this project, a simplified healthcare dataset centered around patient visits serves as the foundation. The dataset can be organized into tables like FactVisits, DimPatient, DimFacility, DimService, DimDate, and DimLocation.

This immediately raises the question of how each table should represent information and how they should interact. An improperly structured model may lead to duplicated data, convoluted relationships, inaccurate totals, and complex DAX calculations. Conversely, a well-structured model provides Power BI with a clear framework, making visualisation more adaptable.

For instance, selecting a facility from a slicer should automatically update relevant patient visits and services. A robust data model acts as the foundation for comprehensive analysis, positively influencing critical areas such as accurate reporting, DAX calculations, performance, scalability, and maintainability.

Transitioning from Flat Tables to Structured Models

Initially, the idea of storing all healthcare information in a single flat table, with each row detailing a patient visit, including key data such as patient, facility, service, date, location, and cost, is considered simple. However, duplicating information such as facility names and service descriptions for each visit proves inefficient.

To facilitate easier analysis and maintenance, the data transitions from a flat table to a structured data model. Instead of consolidating everything into one table, the information is segmented into a FactVisits table containing actual visit records and several dimension tables holding descriptive details about patients, facilities, services, dates, and locations.

These tables are then interconnected using keys and relationships. This approach minimizes unnecessary duplication and offers a clearer structure for Power BI to filter data, execute DAX calculations, and generate accurate, adaptable visualisations.