Forum Discussion
Issue with Power BI Capacity Unit (CU) Overuse and Dataset Optimization
Lots of things to unpack here
is there a way to understand how CUs are utilized during and after a dataset refresh?CUs are incurred by a combination of duration and computational complexity. This includes both Power Query transforms and calculated columns and tables. You can have a long running refresh that doesn't cost much, and a short refresh with ginormous computational cost.
how CUs are distributed or calculated between background and interactive processesThe main difference is the smoothing period - 24 hrs for background, and (most of the time) 5 minutes for interactive.
moving refreshes to off-peak hoursThat has very limited benefits, as the smoothing period is the same 24 hrs no matter when your run the refresh. It is also not meaningful in an enterprise environment operating 24x7.
To emphasize again: Reducing the schedule is important, but not the most important. The most important is to reduce both the runtime and the complexity of each refresh. Your primary KPI should be the cumulative CU consumption per developer per day. Optimization of interactive queries would be a secondary KPI.
Hi Ibendlin,
I couldn’t agree more with the importance of enhancing our developer skills and knowledge regarding Power BI best practices. We’re still in the process of analyzing the best development approach case by case, in line with Power BI best practices.
As for reducing CU usage, I followed your advice to review the datasets with frequent refresh schedules that consume the most CUs. In addition, I moved the workspace to another capacity to validate whether frequent refreshes were the cause of high CU consumption. The results confirmed this, as the background CU usage on the new capacity spiked from 20% to 50%.
Given this, if the dataset itself isn’t the main cause of high CU consumption, is there a way to understand how CUs are utilized during and after a dataset refresh? Additionally, is there any explaination how CUs are distributed or calculated between background and interactive processes?
Based on your information and my understanding, there are three key ways to manage CU usage:
- Optimize dataset refreshes by reviewing current schedules, moving refreshes to off-peak hours, and reducing the frequency of refreshes to avoid competition with interactive processes.
- Optimize reports by simplifying visuals, reducing the number of queries, and improving model design.
- Monitor and scale CU usage through the Power BI Admin Portal to ensure efficient resource allocation.
I’m trying to better understand how CUs are calculated to determine if we need to purchase additional capacity based on current users accessing Power BI, as well as the reports and datasets already developed.
Additionally, I’m working on creating user guidance for those with the ability to develop their own reports, ensuring we have standardized report development aligned with specific use cases.
Regards,
Kemal
Lots of things to unpack here
is there a way to understand how CUs are utilized during and after a dataset refresh? CUs are incurred by a combination of duration and computational complexity. This includes both Power Query transforms and calculated columns and tables. You can have a long running refresh that doesn't cost much, and a short refresh with ginormous computational cost.
how CUs are distributed or calculated between background and interactive processesThe main difference is the smoothing period - 24 hrs for background, and (most of the time) 5 minutes for interactive.
moving refreshes to off-peak hoursThat has very limited benefits, as the smoothing period is the same 24 hrs no matter when your run the refresh. It is also not meaningful in an enterprise environment operating 24x7.
To emphasize again: Reducing the schedule is important, but not the most important. The most important is to reduce both the runtime and the complexity of each refresh. Your primary KPI should be the cumulative CU consumption per developer per day. Optimization of interactive queries would be a secondary KPI.
Hi Ibendlin,
This is very insightful!
I’ll go back and review each report developed by our developers and users.
Thank you for sharing your perspective, I really appreciate it.
Regards,
Faisal