Question

Why does a seemingly small Power BI query consume several gigabytes of memory?

I’m experiencing high memory usage in Power BI for a seemingly small query and need to understand why this happens and how to optimize queries for better performance.

Answer 1

Numerous performance-affecting factors can lead to high memory usage in Power BI, even for queries that appear to be small. The following are typical causes and tips for query optimization to improve performance:

Typical Reasons for Excessive Memory Use:
Big Data Model:

Power BI can load and process large amounts of data into memory, regardless of the size of the query, if it works with a large dataset or a complex data model (many columns, large tables).

Optimization

Eliminate any tables or columns that are not in use.

When feasible, make use of aggregated tables, also known as summary tables.

By removing unnecessary rows (such as time periods that are no longer required), you can potentially reduce the volume of data.

Complicated computations in queries:

Memory usage can be greatly increased by using Power Query transformations, calculated columns, or complex DAX measures. computations that are done row by row or at a high level of granularity can consume a lot of memory.


Optimization:

• Break down complex calculations into smaller, more efficient parts.
• Use calculated columns sparingly in favor of measures, as measures are calculated dynamically and only when needed.

• Query Folding and Data Transformation:

  • If you are working with large datasets, Power Query may not be able to perform query folding (where transformations are pushed to the source database). This results in more data being loaded into Power BI and then processed in memory.

  • Optimization:

    • Ensure that Power Query steps are foldable (e.g., filter data as early as possible).

    • Avoid doing heavy transformations in Power Query if they can be handled at the data source level.

• Relationships Between Tables:

  • Power BI might create relationships between large tables, and when processing these tables together, it can cause significant memory consumption.

  • Optimization:

    • Avoid unnecessary relationships between large tables.

    • If possible, use star schema design for your model to reduce the complexity of relationships.

• Large Granularity in Data:

  • High granularity, such as daily-level data for years or large time-series datasets, can cause Power BI to load many rows of data into memory.

  • Optimization:

    • Aggregate data to a coarser level (e.g., monthly or quarterly) if detailed granularity is not needed.

    • Consider creating indexed tables for faster querying and smaller data loads.

• Columnar Data Storage:

  • Power BI uses a columnar storage model. If there are highly cardinal columns (e.g., unique IDs or text fields with many different values), it can consume more memory, as Power BI stores values per column rather than row.

  • Optimization:

    • Remove high-cardinality columns if they are not essential to the analysis.

    • Use compression techniques like aggregating or reducing the cardinality of columns when possible.

• Filters and Contexts:

  • If filters are applied to large datasets or there are many slicers in the report, Power BI might need to load a large amount of data into memory to apply these filters.

  • Optimization:

    • Limit the number of slicers or filters applied in the report.

    • Use direct query mode if the data is extremely large and doesn't need to be stored in memory