Hello Power BI Fans! Many of you probably already heard about Copilot in Microsoft Fabric, and, most specifically, for Power BI. It’s been hyped more than any other feature, and it’s important to spend some time with this topic.
This will be a new series of articles about Copilot for Power BI:
- Introduction – current article
- Copilot in Power BI Desktop:
- DAX Query View
- Copilot Pane
- Copilot in Power BI Service
I had a different article planned, but I changed my mind after seeing a recent video from Guy in a Cube channel. Feel free to watch the video here:
Copilot for Power BI: Your Ultimate Copilot Guide (youtube.com)
What about the video?
My personal opinion is, that Guy in a Cube channel is one of the reasons why Power BI succeeded in first place. Microsoft has many great presenters, some of them I had a pleasure to meet in person. However, there is something exceptional about Patrick and Adam, who create the content for the channel. They are doing amazing work, which is clearly visible, but there is one more reason for the success of the channel, which remains “in the shadow” – editing staff. They create exceptional pace of the videos, making sure that maximum content is delivered within shortest amount of time. If you didn’t subscribe to their channel yet, do it before you finish reading this article 🙂
Copilot video provides great overview of its capabilities. It’s a bit longer than usual, but chock-full of content. So, is there anything wrong about the video? Not really, it serves well the purpose – advertise Copilot. It aims to show great value of Copilot, and how easy and intuitive it is to use.
But is it really that easy? There are certain complexities and cost to consider here, that was not covered in the video. I will talk about them in the Copilot series.
Where can we use the Copilot
At time of this article, Copilot for Power BI can help us in following areas:
- Power BI Desktop
- Quick measure suggestions
- Generating measure descriptions
- Report design
- Generating DAX code in DAX Query View
- Power BI Service
- Create Power BI Report from Semantic Model
- Analyze/summarize existing Power BI Report
- Generating insights within Smart Narratives
Can you use the Copilot?
During the Power BI Service Demo, where Copilot answers the questions about Power BI Report, you probably noticed Copilot icon appearing at the top:
If you can’t see it, there might be couple of reasons:
- Your organization doesn’t use Premium or Fabric Capacities. For Fabric Capacity, you must be at least at F64 SKU.
- Feature is not enabled in Tenant Settings.
- Tenant Admin enabled Copilot, but only for specific Security Groups.
Contact your Admin to learn more, but keep in mind, that Copilot is still a Preview Feature. Enabling it for entire organization may not be the best option.
If you see the Copilot icon but you can’t use it, it means that Capacity behind your workspace, is not located in US or France. At least this is what still appears in Microsoft Learn Portal. It is due to availability of Azure Open AI Services powering Copilot, deployed in US and France datacenters. However, with one of the recent updates Microsoft informed, that any EU based Region is allowed to use Copilot, without enabling the setting, that allows the data to flow between regions, while using Copilot. Microsoft guarantees, that for EU Capacities, data will not leave EU boundaries.
If you plan to start Fabric Trial to test Copilot – it will not work. You must own a paid Capacity, which is either P1 or F64 (at least).
How much does it cost?
Technically, Copilot comes as additional capability of your Fabric Capacity, thus it is included in the price you pay for Capacity. Reality is a bit more complicated. Copilot consumes resources within purchased Capacity, like any other workload we run against Fabric Artifacts. It will impact how fast we reach 100% of our Capacity and will be forced to purchase a new one. So, again, we don’t have to pay specifically for Copilot, but it may bring Capacity Scaling topic earlier to the discussion. Scaling up or scaling out will be required when you hit the roof of 100%.
What is the impact on our Capacity? This is what I will try to cover during the series. So far, there are two resources that Microsoft provides to calculate the cost:
- Announcing Fabric Copilot pricing | Microsoft Fabric Blog | Microsoft Fabric
- Fabric Copilot Pricing: An End-to-End example | Microsoft Fabric-Blog | Microsoft Fabric
Second resource is closer to what I will try to do here. Instead of providing the cost of individual action, I will run through couple of exercises, similar to what we see in Guy in a Cube demo, and we will find out the Capacity Consumption.
Very briefly about Capacity Utilization
Microsoft provides an app that we can install in our Tenants – Fabric Capacity Metrics App. Capacity Utilization is triggered by Operation that we performed on our data. It could be a scheduled refresh of our Semantic Model, or simply consuming the Power BI Report. Microsoft divided these operations to two Categories: Interactive and Background. When sum of both types of operations is higher than 100% of Capacity compute power, there is a certain throttling policy applied. Throttling may cause a delay of interactive operations, block them completely in next stage (Power BI Reports not working), last stage of throttling blocks completely all background operations (Semantic Models won’t refresh). Microsoft helps us to avoid throttling by applying smoothing. It spreads the capacity consumption over larger period of time, to avoid sudden spikes during the peak times. Interactive operations are smoothed over 5 minutes time intervals, while background operations are smoothed over 24 hours. This is extremely important, as Copilot is considered as background operation when it comes to smoothing, even though logic would rather suggest that usage of Copilot is a certain form of interaction with the data.
Double-edged sword
On one side, it’s great that Copilot is considered a background operation, we will have less spikes than we would with interactive operations. But there is a certain risk. If it happens that we overconsume the Capacity with too many background requests, high consumption will be spread over 24 hours of period. Overconsumption for that long, will block the Capacity, meaning the last throttling policy is applied – no interactive and background operations are allowed.
Disturbed Balance
So far, it was relatively easy to maintain the balance between interactive and background operations. We simply had Semantic Models, refreshed on agreed schedule, and consumed later through Power BI Reports. Now, with introduction of Microsoft Fabric it’s complicated, as Microsoft introduced many new background operations (Run Notebook, Pipeline, etc.) What is even more interesting, querying the Warehouse is also considered a background operation, even though, like Copilot, it is also a form of interaction with the data. This means that we have now far more background operations to worry about, with interactive operations kept as it is. We will most probably end up with higher capacity consumption for background operation, and lower for interactive, which are almost completely unpredictable. Because of this, there is a risk, that our ingestion pipelines will simply stop working. As an admin getting ready for Fabric, I can only advise to monitor the consumption to spot some patterns. Maybe different infrastructure approach must be taken, separating the new and legacy workloads, so less predictable part doesn’t affect the critical workloads. A lot of unknown ahead of us, so, do we want to add the Copilot as additional risk factor?
Is Copilot easy to use?
In the demo video, everything looks so easy. But we must remember one thing, even though Copilot is advertised as a tool that helps to deliver insights without knowledge about the data model, the questions during demos are asked by people who know the model very well. They are capable of asking precise questions, leaving very little space for error. To make sure that interaction with Copilot is smooth, there is some substantial work to be done by Power BI Developer. There are best practices for the model to apply, in order to make usage of Copilot a better experience, Microsoft listed them here:
In the demo video, there is one more part that could be a bit confusing to people. The part about using Copilot within DAX Query View.
DAX query view complexity
Interestingly enough, the demo video from Guy in a Cube didn’t show one Power BI Desktop usage of Copilot, and the first one – quick measures suggestions with Copilot. I don’t know why. I’ve tested it when it was released, and it didn’t leave a good impression on me. Maybe this was the reason, who knows. What was presented instead is the DAX Query View. What is the difference between them? Quick measure suggestion provides the DAX code adjusted to the needs of measures. You can copy paste it and it works without any problem. DAX Query View on the other hand, evaluates the query in the context of Table. Copilot will generate a code for measure but will evaluate it as a table in the end. Here is the example that makes it a bit more clear:
If we want to create a measure from the code generate by Copilot, there is an option to Update model: Add new measure, appearing right above the measure definition. However, Copilot is advertised here as a help, especially for people new to DAX. If someone is new to DAX, they most likely will not be familiar with small nuances like this one. I believe that this could cause a bit of frustration, but it’s all a matter of getting to know the tool.
Of course, Copilot in Power BI Desktop will also consume our Capacity resources. This part was not presented as well, as we first must enter the settings, and connect Power BI Desktop to a Workspace, backed by Premium Capacity. Only then we can use Copilot in Power BI Desktop. We will talk about it in details in next article.
Conclusion
As we will see in next articles, Copilot provides more value than I initially expected. I must admit that it surprised me couple of times. We must also remember that Copilot will be better and better with every question asked. For now, let’s remember about what we covered today:
- Copilot is not available for all Capacity Regions
- Copilot consumes your Capacity resources, that may lead to scaling your resources.
- Background operations are very dangerous and may kill entire Capacity.
- There is some work to be done with your data model, to help Copilot answer your questions accurately.
- Asking good questions is a skill that everyone should master.
- DAX Query View is fairly advanced feature, that may be a bit confusing for DAX beginners.
- You must be fairly technical to evaluate the result of code generated by Copilot.
This is all for today, I hope you enjoyed this article, and you will enjoy the series. Thank you for reading, and see you in next article 🙂
