In Nov 2023, Microsoft announced Microsoft Fabric’s general availability and Public Preview of Copilot in Microsoft Fabric. In a previous post, I explained what Copilot means to Power BI developers, which is valid for other Fabric developers such as data engineers and data scientists as Copilot for Fabric helps with those experiences as well. But the main focus of this blog post is to discuss the requirements, how to enable Copilot, and how to use it from a Power BI development point of view. So, this blog will not discuss other aspects of Copilot in Microsoft Fabric. With that, let’s begin.
Right off the bat, Copilot is only available on Power BI Premium capacities or their equivalent Fabric capacities. So, NO it is NOT available on Power BI Pro or Premium Per User or Power BI Embedded Analytics. So the Power BI items you want to use Copilot on must be in a Workspace assigned to a Power BI Premium P1 or Microsoft Fabric F64 capacities or higher.
You also need to have a Contributor role on the premium workspace.
To use Copilot, your Microsoft Fabric Administrator must enable it from the Fabric Admin Portal. This setting is not available in all regions yet, but Microsoft is gradually rolling it out to more regions.
- This page explains Azure AI services‘ regional availability.
- This page describes how to find your Fabric home region.
Enabling Copilot on Fabric Admin Portal
As mentioned before, your Fabric Administrator must enable Copilot features within the Admin Portal. Follow these steps to enable Copilot on your tenant after logging into Microsoft Fabric:
- Click Settings (the gear icon on the top right of the page)
- Click Admin portal
- Ensure that the Tenant setting tab is selected
- Scroll all the way down to the Copilot and Azure OpenAI Service (preview) section
You can also use the search box and search for OpenAI to find the Copilot and Azure OpenAI Service (preview) section.
- Enable the Users can use a preview of Copilot and other features powered by Azure OpenAI
- Click the Apply button
- Enable the Data sent to Azure OpenAI can be processed outside your tenant’s geographic region, compliance boundary, or national cloud instance
- Click the Apply button again
That is it. You enabled the Copilot capabilities on your tenant.
The following image shows the preceding steps:
Here are some important notes to pay attention to:
- These configurations apply tenant-wide. We currently cannot restrict it to specific security groups.
- Microsoft has done a great job in highlighting a note when enabling the Users can use a preview of Copilot and other features powered by Azure OpenAI setting that says:
If Azure OpenAI is not available in your region, your data may need to be processed outside your tenant’s geographic region, compliance boundary, or national cloud instance. To allow data to be processed in a region where Azure OpenAI is available, turn on the related setting, “Data sent to Azure OpenAI can be processed outside your tenant’s geographic region, compliance boundary, or national cloud instance”.
- Pay attention to another note highlighted under the Data sent to Azure OpenAI can be processed outside your tenant’s geographic region, compliance boundary, or national cloud instance section saying:
Even if this setting is on, you will also need to turn on the related setting “Users can use a preview of Copilot and other features powered by Azure OpenAI” for these features to work.
- I encourage you to read Microsoft’s documentation around Responsible use for Copilot in Power BI.
With that, now let us use Copilot on Fabric service and see how it generates reports.
Generating Reports with Copilot for Power BI on Fabric Service
Generating reports with Copilot is super easy. Follow these steps to generate your first report with Copilot:
- Navigate to the desired premium workspace
- Hover over the desired semantic model and click the More options ellipsis button
- Click Create Report
- On the new report, click the Copilot button
- Click the Create a page that shows… button
- Type in to explain how you need the Copilot to generate the report
- Submit your request by pressing Enter on your keyboard or clicking the submit button
There you go! You have it!
It looks pretty cool, doesn’t it? But wait, there is something wrong with the report. Have you noticed the line chart in the bottom right of the report shows a flat line for Internet Sales and Internet Profit by Product Category? It cannot be correct. This takes us to the next section of this blog post, where we discuss some tips and tricks.
Tips and Tricks for Generating Reports with Copilot for Power BI in Microsoft Fabric
As Copilot looks at the structure of the semantic model to generate reports for us, it is crucial to make the semantic model as optimised as possible. For example, in the previous image, we can quickly spot an issue reflected in the Internet Sales and Internet Profit by Product Category line chart on the bottom right of the report page where the chart shows a constant line for all product categories. This indicates a potential missing relationship between the Product Category and the Internet Sales tables contributing to the relationship. Let’s look into this.
The following image shows the data model where I put the required tabled as a new layout:
As you can see, the missing relationship is indeed between the Product Subcategory and Product tables, which led to incorrect results in the reporting layer. Creating the relationship between the two tables fixes that issue as shown in the following images:
As you see, semantic model issues can lead to reporting issues generated by Copilot. Well, let’s face it, this is exactly what we expect to happen even if we manually create the report, isn’t it?
The preceding example leads us to the following tips and tricks to get the best Copilot experience:
- Get the relationships right: as we saw in the above example, missing or incorrect relationships will lead to inaccurate data visualisation.
- Naming convention: Use more user-friendly names in the data model. For example, Total Sales as a measure name would be more understandable than TotalSales. Fun fact: No one likes Col1 or Tble1 names for any objects, especially Copilot.
- Create explicit measures: It is better to have explicit measures in the data model instead of implicit measures. Just a quick note for those who do not know the difference between explicit and implicit measures:
- Implicit measures: Implicit measures are columns shown with a Sigma icon () in the Data pane in Power BI. These columns are automatically detected as measures when used in a visual on the reporting canvas. In other words, we do not create implicit measures.
- Explicit measures: On the other hand, the explicit measures, are those ones we create within the data model using DAX. The explicit measures also appear in the Data pane in Power BI. The icon for explicit measures looks like a calculator ().
- Follow star schema model design: Create fact and dimension tables following the star schema design. For example, it is best to keep additive, measurable and quantitative data, plus foreign keys of the dimension tables. In contrast, keep the descriptive data in the dimension tables.
- Create hierarchies: Creating hierarchies in dimensions helps Copilot understand the data grouping better. This is helpful, in particular with identifying drill-down activities.
- Pay attention to data types: Defining correct data types for measures and columns in the data model helps Copilot to generate better reports. For example, using Date data type instead of Text helps Copilot to understand it is dealing with date values instead of text.
- Use simpler prompts: In my experience with the report generation capability of Copilot for Power BI, it performs best when using simpler prompts with minimal conditions, such as Create a page that shows Sales by Product instead of Create a page that shows Sales by Product where Procust Category is “Accessories” and Calendar Date is 2012. While it still generates the report, its accuracy decreases by elevating the complexity of our prompt.
The Copilot for Power BI does a good job of generating reports, especially in its early stages. We can use this feature to make reports, but we should be aware that there might be problems, especially if we haven’t made our data models better. We need to check and fix the reports.
As always, please share your thoughts and opinions with us.