/work
Enterprise Semantic Model Re-Architecture
[ overview ]
Summary
Re-architected enterprise Power BI semantic models to improve performance, reuse, and metric consistency across reports.
Context
Legacy datasets and upstream pipelines evolved organically, resulting in slow refreshes (up to 30 min), duplicated logic, and sensitivity to schema changes across 20+ downstream reports.
Solution
Redesigned semantic models and optimized upstream data extraction using usage-driven column reduction and controlled ingestion.
Outcome
Dataset refresh cut from 30 min to 5 min, reduced operational errors, and a stable semantic layer serving 20+ reports.
[ tools & formats ]
[ implementation details ]
End-to-end performance problem identification
Performance issues were not isolated to the semantic model itself, but spanned the entire data delivery chain.
- Azure Data Factory pipelines extracted full source tables from customers
- Pipelines were slow and sensitive to upstream schema changes
- Power BI datasets relied on Power Query transformations with query folding
- Dataset refresh times increased as table width and complexity grew
Usage-driven model reduction
Optimization started by identifying what was actually used in reports.
- Measure Killer was used to analyze column and measure usage across datasets and reports
- Unused columns and measures were identified and removed
- In some cases, up to 70% of table columns were eliminated
Controlled data extraction
Instead of extracting entire source tables, ingestion was redesigned to be column-aware.
- Required columns were explicitly defined in control tables
- Azure Data Factory pipelines were modified to extract only required columns
- Schema drift errors caused by new upstream columns were eliminated
- Manual pipeline fixes were no longer required
Dataset and refresh optimization
With upstream volume reduced, the semantic model could be optimized further.
- Leaner tables improved query folding behavior
- Power Query transformation complexity was reduced
- Dataset refresh times dropped from 30 minutes to under 5 minutes
Validation against full datasets
To ensure correctness, optimized datasets were validated against original full datasets.
- Aggregates and key metrics were compared between reduced and full datasets
- Differences were investigated and resolved before rollout
- Data correctness was confirmed prior to switching consumers
What this enables
By combining semantic modeling with upstream pipeline optimization, performance improvements were achieved holistically.
- Faster Azure Data Factory pipeline execution
- Refresh times reduced from 30 min to under 5 min
- Reduced impact of upstream schema changes
- Lean, maintainable semantic models
- End-to-end performance improvements instead of local optimizations