M2-C1 - CDD - Structuration, optimisation and modelisation

Structuring, optimization, and modeling

Start

Summary

Chapter 1

Will be discussed during this chapter :

• Facts and dimensions tables

• Star & Snowflake Schema

• Relationships between tables

• Primary and foreign keys

• Quiz

OVERVIEW

By the end of this module, you will be able to :

GOAL 2

GOAL 3

GOAL 1

Understand how it works in Power BI depending on the semantic model you have.

Know the possibilities in terms of semantic model.

Optimize your semantic model and your tables.

DIMENSIOns & factstables

dimensions & facts - introduction

Fact and dimension tables are key concepts in modern Power BI modeling. Respecting these concepts is essential because it optimizes report performance, query execution speed, and maintainability.

Facts Tables

Dimensions Tables

• Contain analysis axes (e.g., product, customer, date).

• Act as filters for the fact table.

• Allow segmentation, grouping, and filtering of data.

• Help avoid complex measures (e.g., instead of writing a measure with conditions, use a dimension to filter).

• Few rows but many descriptive columns.

• Provide different levels of granularity for analysis.

• Contain numeric values (sales, quantities, amounts).

• Are at the finest level of granularity (e.g., each transaction).

• Are linked to dimensions through keys (e.g., Product ID, Customer ID).

• Are often very large in volume.

dimensions & facts - introduction

Key benefits of using dimensions & facts tables

Performance impact

Lisibility

Flexibility

Clear, understandable and logical model.

Less complexity when doing calculs.

Filtering is way easier.

Without this modeling and the definition of dimension and fact tables, you risk:

• A confusing model
• Complex and unreliable DAX measures
• Slow reports

star & snowflake schema

One Big Table

Star Schema

Snowflake Schema

Poor modeling = Slow reports, ambiguous relationships, incorrect calculations. Good modeling = Improves performance, simplifies maintenance, reduces DAX errors.

quiz modelisation

relationships & cardinality

Product Description

Product Brand

Customers

Orders

Machines

Materials

one-way relationship

The use of bi-directional relationships can lead to :

• Performance impact -> Evaluates filters in both directions
• Risk of ambiguity and wrong results -> Power BI may struggle to determine the correct filter path.
• Increased Maintenance Complexity -> Harder to maintain and debug. Risk of circular dependencies

primary & foreign keys

To ensure optimal modeling, it is necessary to use star schemas, dimension and fact tables, and reliable relationships ; But none of this works without clearly defining primary and foreign keys.Keys are the essential elements on which all modeling is based. There are two types: primary keys and foreign keys. Hover the keys to deep dive with us into the details.

Primary

Foreign

Why is this important?

• Allows tables to be linked in Power BI
• Guarantees data integrity
• Without well-defined keys → ambiguous relationships, calculation errors
• Mandatory for a proper star schema design

Keys identification

The end

Now that you understand the entire modeling process, including dimension and fact tables, the star schema, and the use of keys, it’s time to put your knowledge into practice. Go ahead and create your first report by applying all the best practices we’ve covered: from connecting to data sources to building an efficient semantic model. Remember: strong data modeling and well‑defined relationships are essential to ensuring an accurate and optimized report.

Click here to exit the genially

Star Schema

Single dimension table, can have duplicates, faster querying, easy-to-understand

Definition : A central fact table directly connected to multiple dimension tables. Based on the principle of normalization.

Advantages :

• Simplicity → easy to understand
• Performance → fast queries
• Ideal for Power BI (optimized for this type of model)
• Suitable for OLAP models

When to use it : Always recommanded in Power BI.

One Big Table Schema

Multi-level dimension table, storage optimisations, slow queries, plus difficile à maintenir

Definition : A single table that combines all facts and dimensions into one dataset, often created to simplify development.

Avantages :

• Quick to build initially.
• No need to manage relationships between tables.

Disadvantages :

• Large table size slows refresh and queries.
• DAX becomes harder.
• Filtering and grouping are inefficient.
• High risk of duplicates and inconsistencies.

When to avoid : AlwaysWhen to use : Never

Snowflake Schema

Multi-level dimension table, storage optimisations, slow queries, more difficult to maintain.

Definition : Dimensions are normalized into sub-tables (e.g., a “Product” dimension linked to a “Category” sub-dimension).

Advantages :

• Reduces redundancy
• Optimizes storage

Disadvantages :

• Increased complexity
• Slower queries
• Harder to maintain

When to use : Prefer Star Schema except in very specific cases (e.g., storage constraints).