CONTENT
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
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
CONTENT
SHARE