Storage Modes and Performance Optimization

Introduction

A Power BI semantic model is not merely a collection of tables and measures. It is an analytical system whose storage choices determine how data is loaded, compressed, queried, refreshed, governed, and experienced by users. A model that is correct but slow can reduce trust, encourage spreadsheet workarounds, and increase operational cost. Performance optimization therefore begins with architectural decisions rather than with isolated formula changes.

This chapter compares Import, DirectQuery, composite, and Direct Lake models. It then develops practical techniques for simplifying models, writing efficient DAX, using aggregations, and diagnosing bottlenecks. The emphasis is on decisions suitable for professional Power BI development: selecting an appropriate storage mode, designing a star schema, reducing unnecessary data, interpreting performance evidence, and balancing speed, freshness, governance, and complexity.

Learning Objectives

By the end of this chapter, you should be able to:

1. Explain how Import, DirectQuery, composite, and Direct Lake storage modes operate.
2. Select a storage mode using criteria such as latency, data volume, freshness, source capability, and governance.
3. Simplify a semantic model through dimensional design, column reduction, and appropriate data types.
4. Write and evaluate efficient DAX measures.
5. Explain the purpose and limitations of aggregation tables.
6. Use Performance Analyzer, DAX Studio, and related tools to identify performance problems.
7. Recommend an optimization approach for a realistic business scenario.

1. The Performance Problem in Context

A report interaction passes through several stages. The visual sends a query to the semantic model; the formula engine evaluates DAX and filter context; the storage engine retrieves and aggregates data; and, for DirectQuery models, the source database may execute generated SQL. Rendering, network transfer, gateway activity, and source concurrency can also contribute to elapsed time.

A useful conceptual relationship is:

Here, T_total is the observed visual response time; T_model is semantic-model processing time; T_source is time spent by the source system; T_network is communication time; and T_render is visual rendering time. This is not a physical law and the components may overlap, but it is a useful diagnostic decomposition. Improving only DAX will not solve a slow source query, an overloaded gateway, or an overcrowded report page.

Performance also has multiple dimensions:

• Interactive performance: the time required to respond to slicers, filters, and visual interactions.
• Refresh performance: the time and resources required to load or update data.
• Scalability: the ability to support more users, data, and concurrent queries.
• Freshness: how closely report results reflect the underlying operational data.
• Reliability: whether queries and refreshes complete consistently.
• Maintainability: whether the solution can be understood and changed safely.

These dimensions can conflict. Import generally provides fast interactive performance but requires refresh processing. DirectQuery may provide fresher results but depends on source performance. A technically fast model may still be unsuitable if its refresh window, licensing, security, or governance requirements are unacceptable.

2. Storage Modes

2.1 Import mode

In Import mode, Power BI loads data into the VertiPaq in-memory columnar engine. Data is compressed and stored in the model. Report queries normally execute against this in-memory copy rather than querying the original source for every visual interaction.

Strengths

• Usually the fastest interactive experience.
• Strong compression, especially for numeric and repeated categorical columns.
• Broad support for DAX and modelling features.
• Reduced dependence on source-system availability during report use.
• Good support for complex calculations and extensive slicing.

Costs and constraints

• Data is not automatically current; it must be refreshed.
• Refresh consumes processing resources and may require gateway connectivity for on-premises sources.
• Model size, capacity memory, refresh duration, and licensing impose practical limits.
• Very large models may require incremental refresh, partitioning, or aggregation strategies.

Import is normally the default choice when data freshness can be measured in minutes or hours rather than seconds and when the model can be refreshed reliably.

2.2 DirectQuery mode

In DirectQuery mode, Power BI stores metadata and sends queries to the underlying source when users interact with the report. The source database performs much of the filtering and aggregation.

Strengths

• Can expose recent source data without importing the entire dataset.
• Model size in Power BI can be smaller.
• Useful when the source is too large, already governed centrally, or subject to data-residency constraints.
• May be appropriate for operational scenarios requiring near-real-time access.

Risks and constraints

• Performance depends on source indexing, query design, concurrency, network latency, gateway configuration, and capacity.
• Not every DAX expression translates efficiently to source queries.
• Complex transformations and high-cardinality filtering can generate expensive SQL.
• Source workloads may be affected by report activity.
• Some modelling and calculation features have restrictions or behave differently from Import models.

DirectQuery is not simply “Import without storage.” It changes the performance responsibility: the database, gateway, network, and semantic model must work together for every query.

2.3 Composite models

A composite model combines storage modes within one semantic model. For example, a large transaction table may use DirectQuery while product, date, and geography dimensions are imported. Alternatively, some tables may be Import and others DirectQuery, with aggregation tables serving detailed DirectQuery data.

Composite models support architectural compromises, but they add complexity. Relationships crossing storage modes can cause cross-source or limited relationship behaviour, and a query may require multiple engines or data movement. Developers must test filter propagation, security, refresh, and performance rather than assuming that combining modes automatically improves the solution.

A common pattern is:

• Import small, stable dimensions.
• Keep very large or frequently changing facts in DirectQuery.
• Import summary tables for common reporting grains.
• Use detail-level DirectQuery only when users drill through or investigate exceptions.

2.4 Direct Lake

Direct Lake is a Microsoft Fabric storage mode designed for semantic models over data stored in OneLake, typically in Delta tables. It reads data into the analytical engine without the traditional scheduled import process. It is intended to provide Import-like analytical performance while avoiding a full copy into the semantic model.

Direct Lake is distinct from DirectQuery: it reads lake data rather than translating every visual query into a query against an external relational source. It is also distinct from conventional Import because the model uses OneLake data with mechanisms for loading and refreshing model data efficiently.

Important considerations include:

• Availability depends on the relevant Microsoft Fabric architecture, capacity, and feature support.
• The underlying Delta tables, lakehouse or warehouse design, and OneLake security model matter greatly.
• Certain situations may invoke fallback behaviour, in which queries are handled through an alternative path with different performance characteristics.
• Direct Lake is not a universal replacement for Import; model compatibility, governance, capacity, data engineering maturity, and workload patterns must be assessed.

2.5 Comparative decision table

The correct question is not “Which mode is newest?” but “Which mode satisfies the business service level with the least unnecessary complexity?”

Figure: A conceptual comparison of storage modes. The positions are illustrative, not measured benchmarks; actual performance depends on design and workload.

3. Model Simplification: The Highest-Value Optimization

3.1 Start with a star schema

A star schema separates measurable business events into fact tables and descriptive entities into dimension tables. A sales fact may contain date key, product key, customer key, quantity, sales amount, and cost. Product, customer, and date dimensions provide filtering and grouping attributes.

This design reduces repeated text in the fact table, makes filter propagation predictable, and allows the storage engine to execute common aggregation patterns efficiently. Avoid using one wide, denormalized table merely because it is convenient in Power Query. Denormalization can increase memory use and create ambiguous relationships.

Recommended relationship characteristics are:

• One-to-many relationships from dimensions to facts.
• A single, intentional filter direction unless a documented requirement justifies another.
• A unique key on the one side.
• A dedicated date table marked as a date table where appropriate.
• No unnecessary bidirectional relationships.

3.2 Remove unnecessary data

Import only columns and rows required for analysis, security, or legitimate operational requirements. Remove technical fields that are never used, historical periods outside the reporting policy, and duplicate attributes. Filtering rows before loading reduces both storage and processing.

Data types matter. A whole-number key generally consumes less memory than a long text key. Currency and decimal columns should use an appropriate fixed or decimal type. Do not preserve a high-precision type when the business process does not require it. Correct data typing also improves query folding and calculation reliability.

High-cardinality columns—such as transaction identifiers, timestamps with millisecond precision, or unique text descriptions—often compress poorly. Keep them only when required for drill-through, detail analysis, or governance.

3.3 Manage calculated columns and tables

A calculated column is materialized for each row during refresh and increases model size. A measure is evaluated at query time in filter context. When a result is needed only for aggregation or display, a measure is often preferable to a calculated column.

For example, instead of storing a row-level margin percentage, store Sales Amount and Cost Amount and define:

Gross Margin = [Sales Amount] - [Cost Amount]

Gross Margin % =
DIVIDE([Gross Margin], [Sales Amount])

DIVIDE safely handles a zero or blank denominator. The correct business treatment of zero sales should still be documented; returning blank is not automatically the only acceptable policy.

Calculated columns remain appropriate when a value is needed for grouping, sorting, relationship keys, or row-level filtering and cannot be produced efficiently upstream.

4. Efficient DAX and Query Design

4.1 Understand filter context

DAX measures are evaluated in the filter context created by slicers, visual axes, filters, and relationships. A measure should generally express a business definition rather than reproduce row-by-row processing unnecessarily.

Prefer simple storage-engine aggregations when possible:

Sales Amount = SUM(FactSales[SalesAmount])

Units Sold = SUM(FactSales[Quantity])

These measures are usually efficient because VertiPaq can scan compressed columns directly.

4.2 Use variables for clarity and reuse

Variables can prevent repeated evaluation and make logic auditable:

Profit Margin % =
VAR Revenue = [Sales Amount]
VAR Profit = [Gross Margin]
RETURN
    DIVIDE(Profit, Revenue)

Variables improve readability and may reduce repeated work, although their use alone does not guarantee faster execution. A variable should represent a meaningful intermediate result, not be added mechanically to every expression.

4.3 Avoid unnecessary row-by-row iteration

Iterator functions such as SUMX, FILTER, and AVERAGEX are useful, but they can be expensive over a large fact table when a native aggregation or precomputed column would suffice. Compare:

Sales Amount = SUM(FactSales[SalesAmount])

with a more expensive pattern that recalculates a multiplication for every fact row:

Sales Amount =
SUMX(FactSales, FactSales[Quantity] * FactSales[UnitPrice])

The second expression may be necessary when sales amount is not stored and cannot be calculated upstream, but it should not be used when an authoritative amount column already exists.

Filter narrowly and preserve simple predicates where possible:

Online Sales =
CALCULATE(
    [Sales Amount],
    DimChannel[Channel] = "Online"
)

Complex virtual tables, nested context transitions, and repeated FILTER operations should be justified by the analytical requirement.

4.4 Time intelligence and date design

Time-intelligence measures depend on a valid date table, continuous dates where required, and an unambiguous relationship to the fact table. A properly designed date dimension is usually more efficient and reliable than deriving dates repeatedly from a transaction timestamp.

4.5 Aggregation tables

An aggregation table stores results at a coarser grain, such as month, product category, and region, while the detailed fact remains available for drill-through. When a visual can be answered from the aggregation, the engine can avoid scanning the detailed table.

A useful conceptual relation is:

The symbol ≪ means “much fewer than.” This is a design objective, not a guaranteed numerical ratio. Aggregations are effective only when their dimensions, relationships, summarization definitions, and query patterns align with user behaviour.

An aggregation strategy should specify:

1. The frequent queries it is intended to accelerate.
2. The grain and measures stored.
3. The mapping between aggregation columns and detail columns.
4. The queries that must fall back to detail.
5. Refresh and reconciliation procedures.

An aggregation table is not a substitute for a poor model. If every visual requires a column absent from the aggregate, users will still experience detail-level queries.

5. Diagnostic Tools and a Performance Workflow

Optimization should be evidence-based. A practical workflow is:

1. Define a target, such as a maximum acceptable response time for common pages.
2. Establish a baseline using representative filters and user actions.
3. Identify whether the delay is visual, DAX, source, capacity, gateway, or network related.
4. Change one important design element at a time.
5. Re-test using the same workload.
6. Record the change, evidence, and outcome.

Performance Analyzer

Power BI Performance Analyzer records the time associated with report visuals. It helps identify visuals that are slow, generate expensive queries, or perform excessive rendering. Its timings should be interpreted as evidence about a particular report state, not as a universal benchmark.

DAX Studio

DAX Studio can connect to a model and inspect queries, server timings, and query plans. Server timings help distinguish formula-engine work from storage-engine work. Query plans can reveal scans, callbacks, materialization, and inefficient evaluation patterns. DAX Studio is especially useful for testing a measure outside the visual layout.

VertiPaq Analyzer

VertiPaq Analyzer, often used through external tools, helps inspect table and column sizes, cardinality, dictionaries, and compression. It can reveal that a rarely used text column consumes more memory than an important numeric measure.

Query folding and source tools

For Power Query transformations, query folding determines whether work can be delegated to the source. A transformation that breaks folding may force Power BI to retrieve and process far more data. Source-side execution plans, indexes, partitioning, and workload monitoring are essential for DirectQuery solutions.

Capacity and operational monitoring

In the Power BI service, capacity metrics, refresh history, gateway monitoring, and activity logs provide evidence about memory pressure, throttling, refresh overlap, and concurrency. A report that is fast for one developer may be slow in production because many users execute it simultaneously.

6. Worked Application: Regional Retail Analytics

Background

This illustrative case concerns a retailer with 180 million sales rows, 12 years of history, a product dimension of 80,000 rows, and 400 report users. Executives require daily performance by 08:00. Operations managers request data updated every five minutes for current-day sales. Analysts need detailed transaction drill-through.

Problem

The existing Import model contains 120 columns in the sales table, including unused text descriptions and a high-precision timestamp. It takes 95 minutes to refresh and frequently approaches capacity memory limits. A proposed solution is to convert the entire model to DirectQuery.

Analysis

The proposal would improve freshness but could cause every executive visual to query 180 million source rows. The source system is an operational database with limited capacity during business hours. Performance Analyzer also shows that most executive visuals use only month, region, product category, sales, units, and margin.

Approach and solution

The team adopts a composite architecture:

• Import the date, product, region, and other small dimensions.
• Retain detailed sales in DirectQuery for current-day investigation and drill-through.
• Create an Import aggregation at month–region–product-category grain for historical executive analysis.
• Remove unused columns and retain only the transaction fields needed for detail analysis.
• Replace several calculated columns with measures or upstream transformations.
• Partition historical data through an incremental-refresh policy where supported by the chosen architecture.
• Use a separate operational page for current-day detail rather than forcing all pages to use DirectQuery.
• Test row-level security, relationship behaviour, and aggregation hits under realistic user concurrency.

Outcome

The common executive pages use the aggregation and return substantially faster than the original detail scans. Refresh processing is reduced because unnecessary columns and historical changes are controlled. Current-day operational users retain access to recent detail, while the source system is protected from unnecessary executive queries.

The outcome is not produced by storage mode alone. It depends on grain selection, model reduction, disciplined report design, source engineering, and measurement.

Learning points

1. Requirements differ within the same organization; one storage mode need not serve every workload.
2. Aggregations are most useful when designed from observed query patterns.
3. Importing fewer columns can be more valuable than changing the entire architecture.
4. Near-real-time detail and fast historical summary are separable requirements.
5. Every composite design requires explicit testing of fallback behaviour and security.

7. Governance, Security, and Responsible Optimization

Performance changes must not weaken governance. Removing columns is appropriate only after confirming that they are not required for legal retention, audit, security, or approved analysis. Sensitive data should not be copied into an additional storage layer merely to improve speed without assessing access controls, residency, retention, and lineage.

Row-level security can affect aggregation usage and query plans. Security filters must be tested with representative roles. A technically fast report that exposes another region’s data is a failed solution.

Data freshness should be stated honestly. Import models have a refresh timestamp; DirectQuery results depend on source transaction visibility and caching; Direct Lake results depend on lake updates, capacity, and model behaviour. Users should be told what “current” means.

AI-assisted DAX and modelling tools can accelerate development, but generated expressions require human review. Developers must validate filter context, source permissions, privacy, performance, and business definitions. AI should not be treated as a substitute for query plans or controlled testing.

8. Limitations and Best Practices

No optimization technique is universally beneficial. Removing columns can impair legitimate analysis. Aggregations can increase refresh complexity. DirectQuery can be appropriate for freshness but harmful to an operational source. Composite models can solve conflicting requirements but are harder to govern. Direct Lake can simplify lake-centric access but depends on Fabric capacity, supported features, and competent lake engineering.

A concise best-practice checklist is:

• Begin with business service levels for freshness, response time, scale, and security.
• Prefer a clean star schema.
• Import only necessary rows and columns.
• Use correct, compact data types.
• Keep dimensions imported when that simplifies filtering and improves performance.
• Prefer measures and native aggregations over unnecessary calculated columns and iterators.
• Design aggregations from real query patterns.
• Preserve query folding where possible.
• Index, partition, and monitor DirectQuery sources.
• Test under realistic concurrency, not only in Desktop.
• Use Performance Analyzer and DAX Studio before changing formulas.
• Document storage-mode assumptions, refresh policies, security, and fallback paths.
• Re-measure after every material change.

Chapter Summary

Storage mode is an architectural decision that governs where data resides and how report queries are executed. Import usually offers strong interactive performance through VertiPaq but requires refresh and memory management. DirectQuery can provide fresher access to very large or governed sources, but its success depends on source design, translation, network, gateway, and concurrency. Composite models combine modes to serve different workloads, while Direct Lake provides a Fabric-oriented approach to analysing OneLake data without a conventional full import process.

Performance optimization begins with model simplification: use a star schema, remove unnecessary data, choose compact data types, control cardinality, and avoid unnecessary calculated columns. Efficient DAX generally favours native storage-engine aggregations, clear filter context, appropriate variables, and restrained use of iterators. Aggregation tables can accelerate common summary queries when their grain and mappings match user behaviour.

Finally, optimization must be measured. Performance Analyzer, DAX Studio, VertiPaq Analyzer, query-folding indicators, source execution plans, and capacity monitoring reveal different parts of the system. The best design is not the one with the most advanced feature; it is the simplest governed architecture that meets freshness, performance, scalability, and security requirements.

Key Terms

Storage mode: The method by which a semantic model stores and retrieves data.

Import: A mode that loads data into the VertiPaq in-memory engine.

DirectQuery: A mode that sends queries to the underlying source during report use.

Composite model: A model that combines multiple storage modes or source behaviours.

Direct Lake: A Fabric storage mode for analysing OneLake data through a semantic model.

VertiPaq: Power BI’s compressed, columnar analytical storage engine.

Star schema: A design with fact tables surrounded by descriptive dimension tables.

Fact table: A table containing measurable business events at a defined grain.

Dimension table: A table containing descriptive attributes used to filter and group facts.

Cardinality: The number of distinct values in a column or relationship.

Calculated column: A column whose values are computed for rows during model processing.

Measure: A DAX expression evaluated dynamically in filter context.

Filter context: The set of filters under which a DAX expression is evaluated.

Aggregation table: A table storing summarized data at a coarser grain.

Query folding: The delegation of Power Query transformations to the source system.

Formula engine: The component that evaluates DAX logic and coordinates calculations.

Storage engine: The component that scans and aggregates stored data.

Performance Analyzer: A Power BI tool that records visual and query timings.

DAX Studio: An external tool for analysing DAX queries, timings, and plans.

Incremental refresh: A policy that refreshes only selected portions of a model rather than all history.

Review Questions

Short Answer Questions

1. What is the principal difference between Import and DirectQuery?
2. Why can high-cardinality columns increase model size?
3. What is the purpose of an aggregation table?
4. What does query folding mean?
5. Name two tools that can help diagnose Power BI performance.

Long Answer Questions

1. Compare Import, DirectQuery, composite, and Direct Lake models, including their principal strengths and risks.
2. Explain how a star schema can improve both model maintainability and performance.
3. Discuss why replacing every calculated column with a measure is not always correct.
4. Describe a systematic workflow for diagnosing a slow report page.
5. Explain the governance and security issues that should be considered when optimizing a semantic model.

Analytical / Application Questions

1. A company needs five-minute current-day visibility but fast monthly executive reporting over ten years of history. Recommend an architecture and justify the role of each storage mode or aggregation.
2. A DirectQuery report is slow even though its DAX measures are simple. Identify at least four non-DAX causes that should be investigated and explain how you would test them.
3. A model has a 150-million-row fact table, 90 unused columns, several bidirectional relationships, and a high-cardinality transaction timestamp. Propose a prioritized optimization plan and explain the evidence you would collect after each step.

Multiple Choice Questions with Answers

Question 1

Which storage mode normally provides the fastest interactive performance when the model fits within available memory and scheduled refresh is acceptable?

A. Import

B. DirectQuery

C. Direct Lake only

D. Composite with no imported tables

Correct Answer: A. Import

Question 2

What is a principal characteristic of DirectQuery?

A. All source data is permanently copied into VertiPaq

B. Queries are sent to the underlying source during report interaction

C. It cannot use relational databases

D. It always eliminates network latency

Correct Answer: B. Queries are sent to the underlying source during report interaction

Question 3

Which design most directly supports efficient Power BI modelling?

A. A single wide table containing every descriptive attribute

B. A star schema with fact and dimension tables

C. Bidirectional filtering on every relationship

D. A separate calculated column for every measure

Correct Answer: B. A star schema with fact and dimension tables

Question 4

Why can an unnecessary high-cardinality text column harm Import-model performance?

A. It always prevents relationships from working

B. It generally compresses poorly and consumes memory

C. It automatically converts the model to DirectQuery

D. It removes all date-table functionality

Correct Answer: B. It generally compresses poorly and consumes memory

Question 5

Which DAX expression is generally preferable for a simple total of a stored sales amount?

A. SUM(FactSales[SalesAmount])

B. SUMX(FactSales, FactSales[Quantity] * FactSales[UnitPrice]) when SalesAmount already exists

C. FILTER(FactSales, FactSales[SalesAmount] > 0)

D. AVERAGEX(FactSales, FactSales[SalesAmount])

Correct Answer: A. SUM(FactSales[SalesAmount])

Question 6

What is the main purpose of an aggregation table?

A. To store detailed rows at greater precision

B. To replace all dimensions with text columns

C. To answer suitable summary queries without scanning the detailed fact table

D. To disable query folding

Correct Answer: C. To answer suitable summary queries without scanning the detailed fact table

Question 7

Which tool records timings associated with visuals on a Power BI report page?

A. Performance Analyzer

B. WordPad

C. Windows Calculator

D. PowerPoint Designer

Correct Answer: A. Performance Analyzer

Question 8

What does DAX Studio help a developer inspect?

A. Only report-page colours

B. DAX queries, server timings, and query plans

C. Only source-system passwords

D. Only Excel chart themes

Correct Answer: B. DAX queries, server timings, and query plans

Question 9

What is a major risk of an entirely DirectQuery solution against an operational database?

A. It cannot display any filters

B. Every interaction may impose additional workload on the source

C. It always requires all data to be imported first

D. It prevents the use of dimensions

Correct Answer: B. Every interaction may impose additional workload on the source

Question 10

Direct Lake is primarily designed for semantic models working with data stored where?

A. OneLake in Microsoft Fabric

B. Only local CSV files

C. Only browser cookies

D. A printer queue

Correct Answer: A. OneLake in Microsoft Fabric