Data Modelling in Power BI

Data modelling is the make-or-break stage for any Power BI Report. A well-built data model ensures your report refreshes fast and scales with compounding data.

Jackie Tejwani

Jackie Tejwani

Director - Business Intelligence

September 15, 2024

Data Modelling in Power BI

Data modelling is the make-or-break stage for any Power BI Report. A well-built data model ensures your reports last for years to come and enables the following the 3 benefits:

Reliability: Accuracy of the measures and calculations

Scalability: The report can handle increase in size of the data

Flexibility: Easy to change and implement new requirements.

The Star-Schema Data Model is the gold standard technique when modelling data in Power BI. It is recognized by the star shape and has the following characteristics.

• One to many relationships between the fact table and the dim table

• All relationships must be single direction to the fact table


Step 1: Understand Business Requirements

Before designing the data model, start with the requirements. What are the key stories the report is trying to understand:

• Sales trends by date, month, year

• Sales by Channel

• Sales by Customer

• Sales by Product


Step 2: Identify the Fact Table

Fact tables also known as Transactional tables used to run calculations, report key metrics or analyse business process. Common Fact tables:

• Sales

• Website visits

• Impressions

• P&L statements


Step 3: Identify Dim Tables

Dim Tables can be used to slice or group metrics and calculations from the fact tables. Common Dim Tables

• Date

• Sales Channel

• Customer

• Product


Step 4: Create the Relationships

Once the dim and fact tables have been ingested into Power BI it’s time to create the relationships.

*Note: Power BI auto-detects the relationships between your tables. But they don’t generally get it right. Best to turn off the feature and delete the auto created relationships

To create relationships between your tables, use the manage relationship button and select create new


Use the configuration below to create the relationship


Useful Links

M query date tables

https://forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390

Why Power BI loves star schema

https://www.youtube.com/watch?v=vZndrBBPiQc

Our Services Power BI Services

https://simpleanalytics.co.nz/services/power-bi


Data Modelling in Power BI

Data modelling is the make-or-break stage for any Power BI Report. A well-built data model ensures your reports last for years to come and enables the following the 3 benefits:

Reliability: Accuracy of the measures and calculations

Scalability: The report can handle increase in size of the data

Flexibility: Easy to change and implement new requirements.

The Star-Schema Data Model is the gold standard technique when modelling data in Power BI. It is recognized by the star shape and has the following characteristics.

• One to many relationships between the fact table and the dim table

• All relationships must be single direction to the fact table


Step 1: Understand Business Requirements

Before designing the data model, start with the requirements. What are the key stories the report is trying to understand:

• Sales trends by date, month, year

• Sales by Channel

• Sales by Customer

• Sales by Product


Step 2: Identify the Fact Table

Fact tables also known as Transactional tables used to run calculations, report key metrics or analyse business process. Common Fact tables:

• Sales

• Website visits

• Impressions

• P&L statements


Step 3: Identify Dim Tables

Dim Tables can be used to slice or group metrics and calculations from the fact tables. Common Dim Tables

• Date

• Sales Channel

• Customer

• Product


Step 4: Create the Relationships

Once the dim and fact tables have been ingested into Power BI it’s time to create the relationships.

*Note: Power BI auto-detects the relationships between your tables. But they don’t generally get it right. Best to turn off the feature and delete the auto created relationships

To create relationships between your tables, use the manage relationship button and select create new


Use the configuration below to create the relationship


Useful Links

M query date tables

https://forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390

Why Power BI loves star schema

https://www.youtube.com/watch?v=vZndrBBPiQc

Our Services Power BI Services

https://simpleanalytics.co.nz/services/power-bi


Data Modelling in Power BI

Data modelling is the make-or-break stage for any Power BI Report. A well-built data model ensures your reports last for years to come and enables the following the 3 benefits:

Reliability: Accuracy of the measures and calculations

Scalability: The report can handle increase in size of the data

Flexibility: Easy to change and implement new requirements.

The Star-Schema Data Model is the gold standard technique when modelling data in Power BI. It is recognized by the star shape and has the following characteristics.

• One to many relationships between the fact table and the dim table

• All relationships must be single direction to the fact table


Step 1: Understand Business Requirements

Before designing the data model, start with the requirements. What are the key stories the report is trying to understand:

• Sales trends by date, month, year

• Sales by Channel

• Sales by Customer

• Sales by Product


Step 2: Identify the Fact Table

Fact tables also known as Transactional tables used to run calculations, report key metrics or analyse business process. Common Fact tables:

• Sales

• Website visits

• Impressions

• P&L statements


Step 3: Identify Dim Tables

Dim Tables can be used to slice or group metrics and calculations from the fact tables. Common Dim Tables

• Date

• Sales Channel

• Customer

• Product


Step 4: Create the Relationships

Once the dim and fact tables have been ingested into Power BI it’s time to create the relationships.

*Note: Power BI auto-detects the relationships between your tables. But they don’t generally get it right. Best to turn off the feature and delete the auto created relationships

To create relationships between your tables, use the manage relationship button and select create new


Use the configuration below to create the relationship


Useful Links

M query date tables

https://forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390

Why Power BI loves star schema

https://www.youtube.com/watch?v=vZndrBBPiQc

Our Services Power BI Services

https://simpleanalytics.co.nz/services/power-bi


SHARE