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 IIIThank you v-hashadapu and nilendraFabric for your thoughts and suggestions.
While my focus had been on the Power Query processing (data extraction and transformation), I finally found the cause was outside of Power Query but in the model itself.
Typically, we would use calculated columns to calculate values which required queries covering more than one of the model's tables. In this instance, we had a number of required columns which were determined by summarising related rows in related tables in temporary table variables in DAX.
The processing time for these were not evident in any of the traces we ran, however when refreshing on the desktop, it accounts for the long delay between ingestion row count completing and when the refresh actually finished.
In a test in which I removed the calculated columns from the model, the refresh time dropped from 40 minutes when last it ran, to only 3 minutes!
I am now modifying the model to perform the calculations in the databricks engine. This is made possible by previous work to move the power query transformations back into databricks Materialised Views.
I would like to thank you both for your helpful insights, all of which contributed significantly to the final solution. However as I can only mark one reply as a solution, I will in this case refer to that of nilendraFabric as this reply suggested the issue might be the calculated columns.
Thank you both for your help. It is appreciated.