Reducing model size in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a Power BI developer at a retail company.

📋 Request: Your manager wants you to reduce the size of the current sales data model to improve report performance and loading speed.

📊 Data: You have a sales dataset with columns: OrderID, ProductID, ProductName, Category, SalesAmount, OrderDate, CustomerID, CustomerName, Region, and some calculated columns.

🎯 Deliverable: A Power BI report with a reduced data model size by removing unnecessary columns, optimizing data types, and using aggregations where possible.

Sample Data

OrderID	ProductID	ProductName	Category	SalesAmount	OrderDate	CustomerID	CustomerName	Region
1001	200	Chair	Furniture	150	2024-01-05	3001	John Doe	East
1002	201	Table	Furniture	300	2024-01-06	3002	Jane Smith	West
1003	202	Lamp	Lighting	80	2024-01-07	3003	Bob Brown	North
1004	203	Sofa	Furniture	500	2024-01-08	3004	Alice Green	South
1005	204	Desk	Furniture	250	2024-01-09	3005	Tom White	East
1006	205	Ceiling Light	Lighting	120	2024-01-10	3006	Emma Black	West
1007	206	Bookshelf	Furniture	200	2024-01-11	3007	Chris Blue	North
1008	207	Floor Lamp	Lighting	90	2024-01-12	3008	Pat Yellow	South

1

Step 1: Remove unnecessary columns from the data model that are not used in reports or calculations.

Remove columns: CustomerName, ProductName

Expected Result

Data model size decreases by removing text-heavy columns.

2

Step 2: Change data types to more efficient types where possible.

Change OrderDate column type to Date instead of DateTime; change SalesAmount to Decimal with 2 decimal places.

Expected Result

Data model size reduces by optimizing data types.

3

Step 3: Create a summary table to aggregate sales by Category and Region to reduce row count.

Use DAX: SalesSummary = SUMMARIZE('Sales', 'Sales'[Category], 'Sales'[Region], "TotalSales", SUM('Sales'[SalesAmount]))

Expected Result

Aggregated table with fewer rows and smaller size.

4

Step 4: Disable loading of the original detailed sales table to keep only the summary table in the model.

In Power Query, right-click original Sales table and select 'Disable Load'.

Expected Result

Model size reduces significantly by keeping only aggregated data.

5

Step 5: Create a measure to calculate total sales from the summary table for use in reports.

Total Sales = SUM(SalesSummary[TotalSales])

Expected Result

Reports show correct total sales using smaller model.

Final Result

-----------------------------------
| Category | Region | Total Sales |
|----------|--------|-------------|
|Furniture | East   | 400         |
|Furniture | West   | 300         |
|Furniture | North  | 200         |
|Furniture | South  | 500         |
|Lighting  | East   | 0           |
|Lighting  | West   | 120         |
|Lighting  | North  | 80          |
|Lighting  | South  | 90          |
-----------------------------------

✓Removing unused text columns reduced model size.

✓Changing data types to more efficient ones saved space.

✓Aggregating data into a summary table greatly reduced row count.

✓Disabling load of detailed data improved report performance.

✓Final report shows total sales by category and region correctly.

Bonus Challenge

Implement incremental data refresh to load only new sales data each day to keep the model size small over time.

Show Hint

Use Power BI incremental refresh settings with a date filter on the OrderDate column.