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
Super Userhi 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
It seems my original reply was lost, however we have identified the cause.
On one of the tables, the model requires additional columns which are calculated from summarising a number of other model tables. It seems these calculated columns were consuming all the remaining memory post ingestion of the data through Power Query.
Removing these columns allowed the refresh to complete. Originally taking over 40 minutes, it took only 3 minutes to refresh after removing those columns.
The problem was not in the Power Queries as we first thought, but in the post ingestion processing of the model.
Because we had pushed all the original table transformations from Power Query into databricks Materialised Views, we are now able to push the calculated columns down into the databricks engine as well, so we are currently refactoring that code.
Thank you both for your assistance. Your advice has been very helpful.