In order to shrink the Power BI dataset size and avoid hitting any memory limits within the Power BI Service, keep the following optimization strategies in mind:
1. Optimize Data Model & Storage
Remove Unnecessary Columns & Tables – Only leave fields needed for reports. Use Power Query to filter unwanted data before loading it. Optimize Data Types – Where possible, switch large text fields to categorical data types or reduce some precision on numeric values by avoiding unnecessary decimal places.
Use Star Schema: Flattening the tables increases the model size, so having those separate dimension and fact tables allows for better Compression.
2. Reduce Cardinality & Improve Compression
Use Aggregations: Set up pre-aggregated tables at various levels to compress the row count further.
Reduce High-Cardinality Columns: Avoid storing overly large unique values such as GUIDs, transaction IDs, or full timestamps. Instead, use surrogate keys or convert timestamps into different date + time buckets.
3. Optimize Data Load & Refresh
Enable Incremental Refresh: Rather than reloading the entire dataset, only refresh new/modified data to enhance efficiency.
Use DirectQuery for Large Tables: Keep the data in DirectQuery mode rather than importing if it is rarely needed.
Remove Calculated Columns & Tables: Discontinue the use of unnecessary DAX-calculated columns and leverage Power Query transformations or calculations in the source database.
