Module 3 – Data Modelling & Relationships Between Entities

Overview

Data modelling is an important part of Power BI development. It determines how information from different tables is connected, how calculations work across datasets, and how business reports produce accurate results.

In this module, learners will understand how to design structured data models using fact tables, dimension tables, primary keys, foreign keys, and relationships. They will explore star and snowflake schemas, relationship cardinality, cross-filter directions, and different modelling approaches for operational, enterprise, portfolio, and group company reporting.

The module also introduces connecting to existing Power BI and Fabric semantic models, controlling report access through Row-Level Security (RLS), managing automated data refresh, and improving model performance.

This module primarily supports the Model the Data skill area of the Microsoft PL-300: Power BI Data Analyst certification examination.

What Will Be Covered

3.1 What is Data Modelling?

• Understanding data modelling and its importance in Power BI.

• Learning how data modelling organizes information from multiple sources.

• Understanding entities, attributes, records, and relationships.

• Exploring how a well-designed data model supports accurate reporting and analysis.

• Understanding the relationship between data preparation, modelling, and report development.

3.2 Connect with an Existing Power BI Fabric Semantic Model

• Understanding Power BI and Microsoft Fabric semantic models.

• Connecting Power BI reports to existing semantic models.

• Understanding the purpose of shared semantic models.

• Exploring how multiple reports can use a common semantic model.

• Understanding the benefits of centralized data modelling and consistent business definitions.

3.3 Fact vs Dimension Tables – Difference Between Primary Key and Foreign Key

• Understanding fact tables and dimension tables.

• Identifying transactional and descriptive data.

• Understanding the purpose of primary keys and foreign keys.

• Identifying common fields used to connect tables.

• Understanding how fact and dimension tables work together in analytical reporting.

• Exploring practical examples using sales, finance, customer, and operational datasets.

3.4 Naming and Sorting the Columns

• Applying consistent naming conventions to tables and columns.

• Organizing fields to improve model readability.

• Renaming columns based on business requirements.

• Configuring column sorting and sort-by-column settings.

• Understanding how column properties improve the usability of reports and semantic models.

3.5 Normalization vs Denormalization – Importance of Modelling

• Understanding database normalization and denormalization.

• Exploring the differences between normalized and denormalized data structures.

• Understanding the advantages and limitations of each approach.

• Identifying when to use different modelling structures.

• Understanding how data structure affects relationships, reporting, and model performance.

3.6 Difference Between Merge and Relate

• Understanding the difference between merging queries and creating relationships.

• Exploring Merge Queries in Power Query.

• Understanding relationships between tables in the Power BI model.

• Identifying when to combine tables and when to keep them separate.

• Understanding how these approaches affect data structure and reporting flexibility.

3.7 Understand Relationship Types, Cardinality and Cross-Filter Direction

• Understanding relationships between tables in Power BI.

• Exploring one-to-one, one-to-many, and many-to-many relationships.

• Understanding relationship cardinality.

• Configuring active and inactive relationships.

• Understanding single-direction and bidirectional cross-filtering.

• Identifying how filters flow between related tables.

• Resolving common relationship issues in data models.

3.8 Design a Consistent Star Schema in the Data Model

• Understanding the structure and purpose of a star schema.

• Identifying fact and dimension tables.

• Connecting fact tables with relevant dimension tables.

• Designing clear and consistent relationships.

• Understanding the benefits of star schema modelling for analytical reporting.

• Applying star schema principles to business reporting scenarios.

3.9 Scenarios for Using Snowflake Schema for Parent-Child Hierarchical Reporting

• Understanding the structure of a snowflake schema.

• Exploring the differences between star and snowflake schemas.

• Understanding parent-child relationships and hierarchical data.

• Identifying scenarios where snowflake modelling is appropriate.

• Structuring hierarchical information for organizational and business reporting.

• Exploring reporting scenarios involving multiple levels of related entities.

3.10 Monthly, Weekly, Daily and Yearly Trend Reporting of KPIs and Process Performance

• Understanding the importance of time-based data modelling.

• Organizing data for daily, weekly, monthly, quarterly, and yearly analysis.

• Understanding date dimensions and calendar tables.

• Structuring data to support time-based KPI reporting.

• Preparing models for period-based business and process performance analysis.

• Supporting comparisons across different reporting periods.

3.11 Learn How to Design Process Reporting, Enterprise Reporting, Portfolio Reporting and Group Companies Reporting

• Understanding the data modelling requirements for different reporting structures.

• Designing models for process-level reporting.

• Organizing enterprise-wide data for consolidated reporting.

• Structuring portfolio-level information for performance analysis.

• Understanding modelling requirements for group companies and subsidiaries.

• Managing relationships between multiple business entities.

• Exploring reporting structures that support consolidated and cross-functional analysis.

3.12 Create Hierarchy, Groups and Bins for Categorical Analysis

• Creating hierarchies for structured data navigation.

• Organizing fields into meaningful groups.

• Creating bins to categorize numerical values.

• Understanding how hierarchies support drill-down analysis.

• Using groups and bins to organize information for reporting.

• Applying categorical analysis to business datasets.

3.13 Import or DirectQuery the Tables from the Source

• Understanding Import and DirectQuery storage modes.

• Exploring how data is accessed and stored in Power BI.

• Understanding the differences between Import and DirectQuery.

• Identifying scenarios where each storage mode is appropriate.

• Understanding the impact of storage modes on data refresh and report performance.

• Exploring the considerations involved in selecting a data connectivity approach.

3.14 Control Row-Level Access to Reports and Data by Roles

• Understanding Row-Level Security (RLS) in Power BI.

• Identifying scenarios where access restrictions are required.

• Creating security roles for different user groups.

• Defining data access rules based on business requirements.

• Understanding how roles control the data users can view.

• Applying RLS to departmental, regional, and organizational reporting scenarios.

• Testing security roles to validate data access.

3.15 Publish and Manage Automated Data Refresh to Semantic Model in Fabric

• Understanding the publishing process for Power BI reports and semantic models.

• Publishing content to Power BI Service and Microsoft Fabric workspaces.

• Understanding semantic model refresh requirements.

• Configuring scheduled data refresh.

• Managing refresh settings and monitoring refresh status.

• Understanding the role of gateways where required.

• Identifying and troubleshooting common refresh failures.

• Understanding how automated refresh supports recurring business reporting.

3.16 Optimize Model Performance

• Understanding the importance of data model performance.

• Identifying unnecessary tables, columns, and data.

• Reducing model complexity and improving data structure.

• Understanding the impact of relationships on model performance.

• Reviewing model design for efficient reporting.

• Identifying common performance bottlenecks.

• Exploring techniques to improve the efficiency of Power BI semantic models.

Skills You Will Gain

By completing this module, learners will develop practical skills in the following areas:

1. Data Modelling Fundamentals

• Understand how data modelling works in Power BI.

• Identify fact tables, dimension tables, primary keys, and foreign keys.

• Understand normalization and denormalization.

• Connect to existing Power BI and Fabric semantic models.

• Apply consistent naming and sorting conventions.

2. Relationship Design and Model Architecture

• Create and manage relationships between tables.

• Understand cardinality and cross-filter direction.

• Differentiate between merging tables and creating relationships.

• Design star and snowflake schemas.

• Structure parent-child relationships and hierarchical data.

• Build models for operational, enterprise, portfolio, and group company reporting.

3. Time-Based and Categorical Modelling

• Organize datasets for daily, weekly, monthly, and yearly reporting.

• Understand the role of date dimensions.

• Create hierarchies, groups, and bins.

• Structure data for KPI and process performance analysis.

4. Data Connectivity and Security

• Understand Import and DirectQuery storage modes.

• Control data visibility using Row-Level Security.

• Create and manage security roles.

• Publish Power BI reports and semantic models.

• Configure and manage automated data refresh.

5. Model Performance and Optimization

• Identify unnecessary data and model complexity.

• Review relationships and model structures.

• Identify potential performance bottlenecks.

• Apply appropriate modelling practices to improve report efficiency.

Learning Outcomes

By the end of this module, learners should be able to:

1. Explain the importance of data modelling in Power BI and how it supports accurate business reporting.

2. Connect to existing Power BI and Fabric semantic models.

3. Identify fact tables, dimension tables, primary keys, and foreign keys.

4. Apply appropriate naming and sorting conventions to model tables and columns.

5. Differentiate between normalization and denormalization.

6. Understand when to use Merge Queries and when to create table relationships.

7. Configure relationships using appropriate cardinality and cross-filter directions.

8. Design consistent star schemas and understand when snowflake schemas are suitable.

9. Structure datasets for daily, weekly, monthly, and yearly KPI reporting.

10. Design data models for process, enterprise, portfolio, and group company reporting.

11. Create hierarchies, groups, and bins for categorical analysis.

12. Select appropriate Import or DirectQuery storage modes based on reporting requirements.

13. Configure Row-Level Security roles to control access to reports and data.

14. Publish Power BI reports and semantic models and manage automated data refresh.

15. Identify model performance issues and apply appropriate optimization techniques.

Microsoft PL-300 Exam Alignment

This module primarily supports the Model the Data skill area of the Microsoft PL-300: Power BI Data Analyst examination.

Model the Data – 25–30% of the examination

Microsoft's study guide identifies three major competency groups within this area.

1. Design and Implement a Data Model

Candidates should be able to:

• Configure table and column properties.

• Implement role-playing dimensions.

• Define relationship cardinality and cross-filter direction.

• Create a common date table.

• Identify use cases for calculated columns and calculated tables.

2. Create Model Calculations by Using DAX

Candidates should be able to:

• Create single aggregation measures.

• Use the CALCULATE function.

• Implement time intelligence measures.

• Use basic statistical functions.

• Create semi-additive measures.

• Create measures using Quick Measures.

• Create calculated tables and columns.

• Create calculation groups.

3. Optimize Model Performance

Candidates should be able to:

• Improve performance by identifying and removing unnecessary rows and columns.

• Identify poorly performing measures, relationships, and visuals using Performance Analyzer and DAX query view.

• Improve performance by reducing data granularity.

Additional PL-300 Skills: Manage and Secure Power BI

Although this module primarily focuses on data modelling, some topics also support the Manage and Secure Power BI skill area, which accounts for 15–20% of the examination.

Relevant examination competencies include:

• Assigning workspace roles.

• Configuring item-level access.

• Configuring access to semantic models.

• Implementing Row-Level Security roles.

• Configuring Row-Level Security group membership.

• Identifying when a gateway is required.

• Configuring scheduled semantic model refresh.

Recommended Microsoft Learn Resources

The following official Microsoft Learn resources complement this module:

1. Model Data with Power BI – Learning Path
   Covers semantic model configuration, relationships, DAX calculations, filter context, time intelligence, and visual calculations.

2. Configure a Semantic Model
   Learn how to configure relationships, tables, columns, hierarchies, measures, and parameters in a Power BI semantic model.

3. Choose a Power BI Model Framework
   Understand different model frameworks, their benefits and limitations, and when to use Import, DirectQuery, and composite models.

4. Microsoft PL-300 Exam Study Guide
   Review the complete examination objectives and identify the skills required under Model the Data, including related security and performance competencies.

Continue to Module 4: DAX Functions & KPIs

Build on your data modelling knowledge by learning to create DAX calculations, develop business KPIs, and analyse performance across different dimensions and reporting periods.