Forum Discussion

kemalfaisal's avatar
kemalfaisal
Frequent Visitor
1 year ago
Solved

Issue with Power BI Capacity Unit (CU) Overuse and Dataset Optimization

Hi Experts, I'm encountering an issue with our Capacity Unit (CU) frequently exceeding 100% of the allocated capacity. After reviewing the Fabric Capacity Metric, I discovered that background pr...
Need Help
Show and Tell
  • lbendlin's avatar
    lbendlin
    1 year ago

    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 processes

    The main difference is the smoothing period - 24 hrs for background, and (most of the time) 5 minutes for interactive.

     

    moving refreshes to off-peak hours

    That 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.

kemalfaisal's avatar
kemalfaisal
Frequent Visitor
1 year ago

Hi Ibendlin,

Thank you for your response.

I have multiple datasets that refresh frequently, every 5-10 minutes. This might be why the dataset is using most of the CU capacity according to the Fabric Capacity Metric. In cases like this, it might be better to use a direct connection to the database instead of frequent dataset refreshes, correct?

Are there best practices regarding the ideal percentage allocation between background and interactive usage?

Additionally, how can I identify if a dataset needs tuning to reduce CU usage with each refresh?

Regards,
Faisal

lbendlin's avatar
lbendlin
Super User
1 year ago
Are there best practices regarding the ideal percentage allocation between background and interactive usage?

The ideal percentage allocation is 0% background.  🙂

 

Only refresh when the data source has been updated, and if the users actually need to see the latest data right away.  (oftentimes weekly refreshes are just fine)

 

how can I identify if a dataset needs tuning to reduce CU usage with each refresh?

This is not about datasets. This is about developers. Whoever uses the most CUs needs to be locked in a windowless room and be given a full day training on Power BI best practices. Rinse and repeat.