The rapid growth of analytical products has made it increasingly challenging to manage and control their costs. As these costs rise, developing reliable, optimized solutions becomes essential. Ensuring high-quality, cost-effective Power BI reports should be a top priority for every Power BI developer.

If you followed the Introduction to Power Query series, you know that Power Query plays a crucial role in controlling overall costs. In the Capacity Metrics App, Semantic Model refreshes are classified as background activities, which is a double-edged sword. While this classification helps smooth performance by distributing consumption over a 24-hour period, high background usage can lead to increased capacity requirements from Microsoft.

With this in mind, let’s explore the strategies available to optimize our Semantic Models for maximum efficiency.

How are you charged for Capacity Consumption?

For many Power BI developers, capacity consumption costs are an abstract topic. Teams hiring BI experts often lack visibility into these costs, leading to a focus on producing more reports without ensuring efficiency or avoiding duplication of efforts. As a result, companies may face increasing costs with minimal added value.

Capacities are resource pools purchased from Microsoft, but you don’t pay for raw compute power. Instead, Microsoft provides the compute needed, and costs are based on consumption over time. Each capacity tier has a fixed cost, but overconsumption leads to throttling, delays, or even halted background activities. This requires careful management to delay reaching that threshold – and, potentially, the need to buy additional capacity.

Have you ever heard your manager asking, “what is your current capacity”? They are not asking if your capable of performing the task but if there is a chance of squeezing more work within your standard working time. That’s exactly how Microsoft Capacities work. You pay for being able to run a certain number of operations during given period of time. If there are no operations – you still pay for it the full price.

As Microsoft transitions to Fabric Capacities purchased through Azure, these costs are no longer part of Office 365 negotiations but are treated as cloud services, often with fewer discounts than typical Office 365 licenses. Fortunately, Microsoft permits short bursts of overconsumption, provided usage is balanced within the billing period.

Good thing is that Microsoft allows you to overconsume your Capacity for short period of time without penalizing you for it. The rule here is that you must “pay back” the overconsumption in next time intervals:

So, in theory you buy Capacity and pay for it fixed price. If you start to overconsume too much it will trigger throttling mechanism, introducing delays for your reporting, blocking report consumption and – in worst case scenario – blocking all background activities (blue operations in screenshot responsible for refreshes). When this happens, you must think about purchasing another Capacity from Microsoft. If reporting demand grows in your company, you may end up in this place sooner or later. That’s why Power BI Developer has a huge responsibility to build good-quality reports, that will help you to reach the boiling point much later.

I’ll explore capacity consumption in depth in future articles, but for now, this overview is for those who may not yet be focused on the costs associated with Power BI.

Refresh Optimization Strategies

Now you know more or less how Microsoft Capacities work, and how important it is to consume it in responsible manner. It was already established that we aim to build optimized reports. By doing it we want to achieve two primary results:

• Faster, reliable refreshes – usually business requires data to be ready at specific time of the day, therefore, we must make sure that everything works like a Swiss Clock. Reliable data ingestion process helps to improve End User Experience and builds trust between your team and your Customers.
• Reduce overall cost of Power BI – having less data to transform and load means lower Capacity cost as well. While the Capacity cost is fixed, it is important we utilize to its full potential before we consider buying more.

Let’s get to the point, what are the strategies? Each strategy will represent a set of actions that must be taken to secure our end goal:

You can argue that technically all the strategies could be grouped under “Follow best practice” 😀 But I hope it makes a bit more sense when I start to provide more context.

1. Follow Best Practice – There is a set of basic recommendations that you can follow while working with Power Query. Like for example what are the data types you should use, avoid heavy steps like “Sort”, what are the techniques to reduce columns cardinality, using variables, and so on. On top of that there is additional set of recommendations that will help to keep your code nice and clean. While it is more related to “cosmetics”, keeping your code organized forces you to re-think your design, and might highlight additional areas that require performance tuning.
2. Reduce Data Size – A lot of techniques covered in this strategy are covered also by Best Practice checklist. Steps like removing unnecessary columns, filter the data on early stage, reduce cardinality of columns, those are common techniques for both strategies. However, this particular strategy looks at the wider problem. Reducing the overall model size is one thing, but we could also focus on reducing the size of data that must be refresh. How? Power BI has a great feature called Incremental Refresh. It allows you to split your data into stale and dynamic partitions, significantly reducing the amount of data included in a single refresh. Another great benefit of Increment Refresh is that it allows your Semantic Models to grow beyond standard size limits. It will all be covered in dedicated article, so, stay tuned 🙂
3. Delegate the workload – Another great way to save some money of running transformations in Power Query is… not to run them in Power Query 🙂 We often connect to SQL based data sources, where there is a certain flexibility in how the data is fetched by Power BI. The best option is when at least majority of the transformations are available in the data source directly. If not, we can ask the owner of database to create new Table, or at least a View or Stored Procedure that we can use in Power BI to fetch the data. Result is that all the transformations are done by SQL Engine. If this is also not possible, Power BI has another great feature which is support for Query Folding. If you are forced to run your transformation in Power Query, Query Folding will translate the transformation steps in SQL Query and send it to the data source. Query Folding doesn’t support all transformations, but we will learn how to use it properly in future articles.
4. Consolidate Workloads – Finally, last strategy that is probably the least technical one, yet could be the most powerful. The idea here is to simply avoid the double effort. If there are certain business logic handled in individual Semantic Models, let’s not keep them isolated but make sure that they are done once, and re-distributed to your other reports, other colleagues or even other teams. This way you make sure that you have a central point of maintenance, especially in case when business logic must be updated. You make sure that resources are not wasted to run the same (sometimes really heavy) transformations in multiple reports, other people in the organization are not working on something that is already delivered, and there is a single source of truth for the data you own in the organization. This strategy is strictly related to Data Governance concept and while it’s quite difficult to do it right on the Organization level, it is extremely bad if it’s not even done on the Team level. Your role as Power BI Developer is extremely important here to raise your voice when you hear during the meetings about new development plans, to remind that maybe it was already done in the past. Then it requires maybe just a gap analysis vs requirement for new project, but for sure doesn’t make sense to do everything from scratch again.

Stay Calm and Drive the Change

You might face pressure to deliver results quickly, often without optimization, especially if cost visibility is limited. In such cases, incremental improvements – raising optimization suggestions, respectfully challenging requirements, and offering solutions – can gradually drive positive change. Regardless of your role, you can help steer your team toward smarter practices, even from a supportive position.

Conclusion

This article opens a new chapter for this blog, setting the stage for deeper dives into cost-effective Power BI development. There’s much more to explore, and I look forward to continuing this journey together.

As always, thank you for reading and see you in next article 🙂