Forum Discussion
Helper IIISame PBIX with databricks source runs out memory in a PRO workspace, but runs in 15 minutes in PPU
hi Mixednuts
In a Pro workspace, datasets are limited to 1GB in size, but the actual memory required during refresh operations can be significantly higher than the final dataset size. When refreshing a dataset, Power BI needs to:
Unzip the existing dataset into memory
Upload and process new data in memory
Create the refreshed dataset
This process can require almost double the memory needed to store the dataset itself, which explains why your 223MB PBIX is hitting memory limits during refresh
look like Your data processing is memory-intensive, possibly due to complex transformations
try :
Remove unnecessary columns and rows to reduce the dataset size
Minimize calculated columns and set appropriate data types
Move transformations to the data source (Databricks) rather than performing them in Power BI
Optimize Your Databricks Source:
Use Delta Tables in Databricks which are optimized for performance
Run OPTIMIZE commands on your Delta Tables regularly
Consider using SQL Warehouses instead of All-Purpose clusters for better BI workload performance
Worth looking at
Implement Power BI's Automatic Aggregations feature with Azure Databricks to improve performance on large datasetshttps://techcommunity.microsoft.com/blog/analyticsonazure/boosting-power-bi-performance-with-azure-databricks-through-automatic-aggregatio/4278890
Helper IIIFurther to this, I thought to adjust the settings on my desktop to try and simulate the memory restrictions experienced in the shared service. My hope was to further isolate the particular query which was the culprit.
To this end I set the memory limit thus:
Desktop would not let me drop the number of simultaneous evaluations below 2. I also had the parallel processing both turned off and turned on in separate executions.
In all of these, the refresh completed successfully.
It is possible I have misunderstood the way these setting operate, however I find it strange that in none of these tests did it report a memory threshold breach (particularly one at 16gb)
Thanks,
- v-hashadapu
Community Support
Hi Mixednuts , Thank you for reaching out to the Microsoft Community Forum.
Your tests and the Vertipaq Analyzer’s 69.63MB dictionary size point to Databricks query inefficiencies and PRO constraints. Since PPU and incremental refresh aren’t options, Optimize the Databricks query. In Power BI Desktop, use Performance Analyzer (View > Performance Analyzer) to find slow queries. Ensure query folding isn’t broken (right-click a step > View Native Query; if disabled, rewrite in Databricks as a simple SELECT materialized view).
Reduce the query load by fetching smaller chunks: SELECT * FROM table WHERE Date >= '2024-01-01' AND Date < '2024-02-01' UNION SELECT * FROM table WHERE Date >= '2024-02-01' AND Date < '2024-03-01'. This keeps memory below PRO’s 15.77GB limit. Enhance Databricks with Delta Tables: OPTIMIZE table_name ZORDER BY (join_column). This reduces memory overhead. Test in another PRO workspace to rule out contention. If it fails, contact Microsoft support about recent PRO memory limit changes.
If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.