"A slow Power BI model is always a hidden cost — not only in infrastructure, but in delayed decisions and demotivated users."
Why optimizing Power BI models matters
The first reaction when facing slow reports is to add CPU or migrate to Premium. That addresses symptoms and sometimes provides temporary relief — but rarely tackles the root cause. A poorly designed model keeps queries heavy, consumes memory unnecessarily and blows refresh windows. In a business context, every second counts: internal studies we reviewed show that average response times between 8–12 seconds per visual reduce user exploration rates by more than 40%, leading to fewer questions about the data and decisions based on samples or static reports.
Optimization is not an obsession with micro‑tweaks; it is efficiency: doing more with fewer resources. It means reducing model size, decreasing refresh time and accelerating interaction latency. These improvements are not only technical — they translate into lower capacity costs, higher user satisfaction and more timely decisions. For example, if average time per visual drops from 8s to 1s in an organization with 200 active users, the cumulative hours saved monthly can equal a full‑time analyst’s work and reduce compute costs by 20–40% in a Premium environment.
Additionally, optimization has a direct impact on operational reliability: faster refreshes reduce the failure window, which is critical for reports that support daily operations, such as inventories or real‑time monitoring of marketing campaigns.
Import vs DirectQuery: informed choices for performance
The choice between Import and DirectQuery is one of the most critical architectural decisions with long‑term impact. Import tends to offer much lower latency thanks to the efficient compression of the VertiPaq engine and in‑memory processing, making it ideal for ad‑hoc analysis and historical models that fit in memory. DirectQuery keeps data at the source, delegating execution to the source system, and is essential when you need to guarantee real‑time consistency or when tables exceed available memory.
It is not always black and white. A pragmatic approach is to use composite models: import dimensions and aggregated facts, and use DirectQuery for large tables that are rarely queried at detail. In many scenarios, 80–95% of interactions are covered by aggregations and imports and benefit from reduced latencies, while only 5–20% of queries trigger DirectQuery for point‑detail.
Some practical rules: 1) If the imported table with compression occupies less than 5–10 GB and queries are frequent, prefer Import. 2) If you need absolutely up‑to‑the‑minute data on every query (for example, book‑to‑order), DirectQuery may be mandatory. 3) Combine: import historical and aggregated data, and connect DirectQuery to operational data that changes by the minute.
Efficient modeling: columns, measures and granularity
Schema design determines a substantial part of performance. Wherever possible, adopt a star schema: simple facts (transactions, events) and denormalized dimensions. Avoid excessive normalization that forces complex joins; VertiPaq handles denormalized dimensions well and reduces the cost of joins at runtime.
Avoid calculated columns with complex logic applied to millions of rows; prefer measures in DAX, which are evaluated at the visualization level and benefit from caching. For example, calculating a column with IFs and LOOKUPs in a fact table of 50M rows can increase refresh processing time by 2–5x, while the same logic as a measure is only evaluated when needed.
Review granularity: ask directly — do you really need minute‑by‑minute transaction detail? Reducing granularity or pre‑aggregating for the most frequent questions can cut data volume by 10x–100x. If 90% of reports are by day or hour, keep minute‑level detail only in low‑cost storage and expose aggregations in the main model.
Any column not used in reports should be removed: unused columns are trapped memory. In practice, auditing column usage often reduces model size by 15–40%. Concrete examples: removing long descriptions, technical logs and debug fields that only serve an ETL context can transform a 12 GB model into 7–8 GB.
Compression and data types: reduce memory without losing functionality
The VertiPaq engine compresses data with techniques like dictionary encoding, run‑length encoding and bit‑compression. Effectiveness depends on column type and cardinality. Replacing repeated strings with integer keys (surrogate keys), converting dates to date/integer formats and using low‑cardinality columns (booleans, smallints) dramatically improves compression.
Practical examples: a country column with 250k rows and 50 unique values stored as text can compress to 1/10th the size if converted to an integer with a dictionary. A timestamps column with thousands of unique values may benefit if transformed to date (day) when time is not required. An alphanumeric ID unique per transaction rarely compresses well and should be excluded if not used in reports — or kept only in a detail table in DirectQuery.
Practical steps: eliminate long text columns, transform categorical fields into integer codes, reduce decimal precision where possible and normalize data before load. Small changes (for example, switching product key from text to integer) can reduce the model by 30–70%. In real cases, we saw fact tables with 20M rows shrink from 14 GB to 2.2 GB solely by key encoding and removing unnecessary columns.
Aggregations and composite models: speeding up queries on fact tables
Aggregations allow answering 80–95% of queries from aggregated tables while keeping a much larger detail table in the backend. Configure aggregation tables by common analysis levels (day, week, month; region; category) and let the engine redirect queries when the aggregation covers the request. The practical result is a latency reduction of 5x–20x for many visuals and dashboards.
Composite models combine Import and DirectQuery. Use Import for aggregated tables and DirectQuery for detailed fact tables. Plan fallback policies: when a query requires detail beyond the aggregation, the engine falls back to DirectQuery — accept that this operation will be slower, but limit its occurrence. When implementing well‑thought aggregations, it is common for the fallback rate to be below 10%.
Also consider dynamic aggregation levels: create aggregations by day+category for daily reporting queries and by month for executive reports. Monitor how many queries are satisfied by aggregations and refine them where usage is highest. Service tools allow seeing cache hit and fallback percentages, essential to optimize the trade‑off between storage space and latency.
Mini case study: online store with 120M rows
In an online store with 80 employees, the BI team imported 120 million transaction rows (3 years), resulting in a 28 GB model and a 9‑hour refresh time. Users exploring dashboards faced average times of 8 seconds per visual and entire dashboards took minutes, preventing real‑time campaign analysis.
Interventions performed and the rationale behind each:
- Removal of unused columns: usage audit identified 18 technical columns and debug fields — removal reduced logical volume by 20%.
- Key encoding: replacing SKU and customer strings with integers; reduced effective cardinality and improved compression.
- Creation of aggregation tables: day/region/category aggregations in Import would cover 95% of daily queries, leaving detail in DirectQuery only for forensic analysis.
- History partitioning and incremental refresh: recent data (last 3 months) with daily partitions and monthly history with incremental refresh reduced refresh time to the minimum necessary window.
- Measure rewrite: measures that previously iterated over the entire table were rewritten with vectorized functions and simplified calculation complexity.
Results in numbers: the model fell from 28 GB to 1.8 GB (≈93% reduction), refresh time dropped from 9 hours to 45 minutes, and average time per visual decreased from 8s to 0.6s. Required capacity went from two dedicated nodes to one base node with fewer Premium hours — estimated annual savings of €35–45k. The team of 3 analysts spent 60% less time waiting for reports and 40% more time on proactive analysis. This change also enabled launching 6 new operational dashboards that were previously unfeasible due to latency.
The ideal model is not the smallest; it is the one that delivers the right answers, fast and at the right cost.
Monitoring and diagnostics: where to invest to maintain performance
Without metrics, optimizations are guesses. Tools like Performance Analyzer in Power BI Desktop and query logs in the service allow identifying heavy visuals and expensive measures. Start by measuring: which visuals have the highest DAX/Query time? Which measures are recalculated frequently? Which queries trigger DirectQuery and with what latency?
In a Premium environment, use usage metrics and telemetry to understand load patterns and size partitions/refresh accordingly. Define internal SLAs, for example: 95% of queries below 1s, 99% of refreshes completed within a 2‑hour window. Configure alerts for signs of degradation — growing cardinality, increased fallback queries to DirectQuery or refresh time rising above defined thresholds.
Best practices: keep a performance dashboard with KPIs (model size, refresh time, percentage of queries via DirectQuery, cache hit rate), run quarterly model reviews and automate reports of file growth and cardinalities. Small proactive interventions prevent costly emergency fixes.
In summary
- Prefer Import for fast analysis; use DirectQuery only when real‑time consistency is mandatory.
- Design a star schema, minimize columns and prefer measures instead of calculated columns in facts.
- Reduce cardinality and choose efficient data types to benefit VertiPaq compression.
- Implement aggregations and composite models to speed up 80–95% of the most common queries.
- Monitor with Performance Analyzer and service logs; adjust incremental refresh and partitions according to usage.
Optimizing Power BI models is an ongoing effort: it starts with modeling and continues with monitoring and adjustments. Small well‑placed changes yield exponential improvements and turn BI from an operational bottleneck into a decision engine.
If you want, we can map your current model in two hours and list five changes with immediate impact. Which part of your Power BI model is causing you the most pain today?
