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 processes consistently consume at least 50% of the capacity. I understand that this is due to throttling and smoothing mechanisms, which allow the system to "pay back" capacity that has already been borrowed. Additionally, the high volume of user interactions is contributing to the CU utilization, as there’s significant demand on Power BI from our users.

The Fabric Capacity Metric does provide insights into which datasets are consuming the most CU, but it doesn’t offer detailed process-level information to help fine-tune these datasets.

With this in mind, I'm trying to explore ways to optimize our Power BI datasets to reduce CU usage. Specifically, I’d like to know how we can monitor and decrease CU usage, as well as establish best practices (Do’s and Don’ts) for creating new datasets to prevent future overuse.

Any advice would be greatly appreciated.

Regards,
Kemal

 

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.

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago
    I understand that this is due to throttling and smoothing mechanisms, which allow the system to "pay back" capacity that has already been borrowed.

    Only the 24 hr smoothing contributes. Throttling is an unrelated activity.

     

    Think of it from the perspective of the end users.   Let's say your backend processes consume 50%.  Your interactive processes consume 10%.  Bluntly speaking, 80% of your refreshes are wasted. You have semantic models and dataflows that are refreshed, and then nobody is looking at them. Refresh them less frequently, and only when they will actually be used.

     

    This is massively oversimplifying the situation but I hope you get my point.  At a minimum background and interactive should balance out. Ideally interactive should be much bigger than background. (Most interactive is smoothing within 5 minutes rather than the 24 hrs for background)

     

     

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