Data model best practices in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a business intelligence analyst at a retail company.

📋 Request: Your manager wants you to build a clean and efficient data model in Power BI that supports fast report creation and accurate analysis.

📊 Data: You have sales transactions data, product details, customer information, and date data in separate tables.

🎯 Deliverable: Create a Power BI data model that follows best practices: proper relationships, star schema design, and optimized for performance.

Sample Data

SalesID	ProductID	CustomerID	DateID	Quantity	SalesAmount
1	101	1001	20230101	2	40
2	102	1002	20230102	1	25
3	103	1003	20230103	3	75
4	101	1002	20230104	1	20
5	104	1001	20230105	5	100
6	102	1003	20230106	2	50
7	103	1001	20230107	1	25
8	104	1002	20230108	4	80

ProductID	ProductName	Category
101	Notebook	Stationery
102	Pen	Stationery
103	Chair	Furniture
104	Desk	Furniture

CustomerID	CustomerName	Region
1001	Alice	East
1002	Bob	West
1003	Charlie	North

DateID	Date	Month	Year
20230101	2023-01-01	January	2023
20230102	2023-01-02	January	2023
20230103	2023-01-03	January	2023
20230104	2023-01-04	January	2023
20230105	2023-01-05	January	2023
20230106	2023-01-06	January	2023
20230107	2023-01-07	January	2023
20230108	2023-01-08	January	2023

1

Step 1: Import all four tables (Sales, Product, Customer, Date) into Power BI.

Use Power BI 'Get Data' feature to load each table separately.

Expected Result

All tables appear in the Power BI data model.

2

Step 2: Create relationships between tables to form a star schema.

Connect Sales[ProductID] to Product[ProductID], Sales[CustomerID] to Customer[CustomerID], and Sales[DateID] to Date[DateID]. Set relationship cardinality to Many-to-One and cross filter direction to Single.

Expected Result

Relationships are active and correctly link fact table (Sales) to dimension tables.

3

Step 3: Set the Sales table as the fact table and Product, Customer, Date as dimension tables.

Verify that Sales contains transactional data and others contain descriptive data.

Expected Result

Data model follows star schema design with one fact table and multiple dimension tables.

4

Step 4: Remove any unnecessary columns from each table to optimize performance.

Keep only columns needed for analysis and relationships.

Expected Result

Tables contain only relevant columns, reducing model size.

5

Step 5: Create a calculated measure for Total Sales Amount.

Total Sales = SUM(Sales[SalesAmount])

Expected Result

Measure calculates total sales correctly when used in reports.

6

Step 6: Create a calculated measure for Total Quantity Sold.

Total Quantity = SUM(Sales[Quantity])

Expected Result

Measure calculates total quantity correctly when used in reports.

7

Step 7: Use the Date table to create a hierarchy for Year and Month.

In Date table, create hierarchy: Year > Month.

Expected Result

Hierarchy allows drill-down in reports by year and month.

8

Step 8: Validate the model by creating a simple report showing Total Sales by Region and Month.

Create a matrix visual with Rows=Customer[Region], Columns=Date[Month], Values=Total Sales measure.

Expected Result

Report shows correct sales totals by region and month.

Final Result

Product

↓

Sales Fact Table

✓The star schema model improves report speed and clarity.

✓Relationships correctly link sales data to product, customer, and date details.

✓Measures for total sales and quantity provide accurate aggregation.

✓Using a date hierarchy enables easy time-based analysis.

✓Removing unused columns reduces model size and improves performance.

Bonus Challenge

Add a calculated column in the Product table to classify products as 'Stationery' or 'Furniture' and create a slicer to filter reports by product category.

Show Hint

Use a simple IF statement in Power BI DAX: CategoryType = Product[Category]

Practice

1. What is the main reason to use a star schema in Power BI data modeling?

easy

A. It hides data from users to improve security.

B. It allows storing all data in one big table.

C. It automatically creates relationships without keys.

D. It simplifies analysis by separating facts and dimensions clearly.

Solution

1. Step 1: Understand star schema purpose

   A star schema organizes data into fact tables (measures) and dimension tables (descriptions) for clarity.

2. Step 2: Recognize benefits in Power BI

   This separation makes analysis easier and faster by simplifying relationships and queries.

3. Final Answer:

   It simplifies analysis by separating facts and dimensions clearly. -> Option D

4. Quick Check:

   Star schema = clear fact and dimension tables [OK]

Hint: Star schema means clear fact and dimension tables [OK]

Common Mistakes:

• Thinking all data should be in one table
• Believing relationships form without keys
• Confusing star schema with security features

2. Which of the following is the correct way to create a one-to-many relationship in Power BI?

easy

A. Link the primary key in the dimension table to the foreign key in the fact table.

B. Link the foreign key in the dimension table to the primary key in the fact table.

C. Create a many-to-many relationship between two fact tables.

D. Use a calculated column to join unrelated tables.

Solution

1. Step 1: Identify keys in tables

   The dimension table has a primary key; the fact table has a foreign key referencing it.

2. Step 2: Create relationship direction

   One-to-many means one dimension row links to many fact rows, so connect primary key (dimension) to foreign key (fact).

3. Final Answer:

   Link the primary key in the dimension table to the foreign key in the fact table. -> Option A

4. Quick Check:

   One-to-many = primary key to foreign key [OK]

Hint: Primary key (dimension) links to foreign key (fact) [OK]

Common Mistakes:

• Reversing keys in relationship
• Using many-to-many where one-to-many is needed
• Trying to join unrelated tables with calculated columns

3. Given a data model with a Sales fact table and a Product dimension table linked by ProductID, what happens if you add a relationship from Sales[ProductID] to Product[ProductID] but the Product table has duplicate ProductIDs?

medium

A. Power BI will create a one-to-many relationship and filter correctly.

B. Power BI will show an error and not allow the relationship.

C. Power BI will create a many-to-many relationship automatically.

D. Power BI will ignore duplicates and create the relationship anyway.

Solution

1. Step 1: Understand relationship requirements

   One-to-many relationships require the 'one' side (Product) to have unique keys.

2. Step 2: Check effect of duplicates

   Duplicates in Product[ProductID] break uniqueness, so Power BI blocks the relationship creation.

3. Final Answer:

   Power BI will show an error and not allow the relationship. -> Option B

4. Quick Check:

   Unique keys required on 'one' side [OK]

Hint: One side keys must be unique to create relationship [OK]

Common Mistakes:

• Assuming duplicates are ignored
• Expecting automatic many-to-many creation
• Thinking relationship will still filter correctly

4. You created a relationship between Customer[CustomerID] and Sales[CustomerID], but your report shows incorrect totals. Which of the following is the most likely cause?

medium

A. The Sales table is missing CustomerID values.

B. CustomerID has duplicates in the Customer table.

C. The relationship is inactive and needs to be activated.

D. The relationship is many-to-many instead of one-to-many.

Solution

1. Step 1: Check relationship status

   Inactive relationships do not filter data automatically, causing wrong totals.

2. Step 2: Confirm activation needed

   Activating the relationship or using DAX USERELATIONSHIP fixes filtering issues.

3. Final Answer:

   The relationship is inactive and needs to be activated. -> Option C

4. Quick Check:

   Inactive relationship causes wrong totals [OK]

Hint: Inactive relationships block filtering; activate them [OK]

Common Mistakes:

• Ignoring inactive relationship status
• Assuming duplicates always cause wrong totals
• Thinking missing values break relationships

5. You have a complex data model with multiple fact tables and dimension tables. To improve performance and clarity, which best practice should you apply?

hard

A. Use a star schema with clear one-to-many relationships and avoid circular dependencies.

B. Combine all tables into one large table to reduce relationships.

C. Create many-to-many relationships between all tables for flexibility.

D. Use calculated columns to join tables instead of relationships.

Solution

1. Step 1: Identify best modeling approach

   Star schema organizes data clearly with fact and dimension tables linked by one-to-many relationships.

2. Step 2: Avoid common pitfalls

   Combining tables or many-to-many relationships can cause confusion and slow performance; calculated columns for joins are inefficient.

3. Final Answer:

   Use a star schema with clear one-to-many relationships and avoid circular dependencies. -> Option A

4. Quick Check:

   Star schema + one-to-many + no circular dependencies [OK]

Hint: Star schema and one-to-many improve clarity and speed [OK]

Common Mistakes:

• Merging all tables into one
• Using many-to-many everywhere
• Relying on calculated columns for joins