In a recent Sales BI project, I built a Power BI dashboard to track KPIs like Total Sales, Department performance, and Product trends. On paper, the data model looked perfect. It was designed using a clean Snowflake Schema.
But when the sales team started using the report, they all said the same thing: the pages were slow, slicers were lagging, and drill-downs were freezing. The dataset was not very large, so the problem was not the data size. After checking deeply, I understood that the issue was not the data — it was the complex relationships between tables and here how did I optimize?
The Hidden Cost of Filter Propagation Path
In the original model, when a user applied a filter, Power BI had to move through many tables:
Sales → Product → Sub-Category → Category → Department
So when someone filtered by “Department,” Power BI had to go through four different tables before reaching the Sales table.
Because of these long propagation path, Power BI had to do more work every time a slicer was used. This caused delays. I realized that while a Snowflake Schema is good for databases, it can slow down performance in Power BI reports. So how did I optimized it?
1.Reduced Table Joins
To fix the problem, I reduced the number of tables and joins. I combined Department, Category, and Sub-Category into one single Product Dimension table. Instead of letting Power BI join tables while the report was running, I created a SQL view to combine the data first. This way, Power BI imported one ready-made table.
Finally I kept the full hierarchy (Department > Category > Sub-Category > Product) inside one table. So when a filter was applied, Power BI did not need to jump across multiple tables. And I have noticed slicers became very fast, and KPI cards loaded quickly without delay.
2. Reduced Table Width (Memory Optimization)
I also found another issue. The model had extra columns that were not used in the report, such as:
- Internal system IDs
- Technical flags
- Long product descriptions
- Audit columns like Created Date and Modified By
Even if these columns are not shown in the report, Power BI still loads them into memory. Columns with many unique values (like IDs) take more memory and do not compress well. So how did I optimize this?
I checked every visual and measure and removed any unused columns in Power Query and then I have removed high-cardinality columns that were not needed for reporting. This reduced huge memory usage.
This helped the model size became smaller, memory usage reduced, and the report became faster and smoother.
Final thoughts
This project taught me an important lesson: A clean database design does not always mean a fast Power BI report. A Snowflake Schema is good for data accuracy and structure, but a Star Schema is better for performance.
By simplifying the model, flattening hierarchies, and removing unnecessary columns, I turned a slow dashboard into a fast and efficient one. Performance is not just about having clean data — it is about how easily and quickly that data can be used.
If you found this insightful, follow me Jayasri Balakrishnan
