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]