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