Copilot Reports with Microsoft Fabric – Part 2

Copilot Reports have been a challenge for a while, but Microsoft Fabric has made life so much easier! In last week’s article, we went through the process to start harvesting data from our endpoints. This week is about getting the model in place so we can deploy an easy-to-use report to track utilization trends.

While this process is a little tedious, this should make your life much easier. With a report that is already designed with the key metrics and outcomes built for you, it should only take minutes for you to find some key insights. Just make sure you pay attention to the details and everything should work out smoothly!

Add Tables to your Semantic Model

The first step we need to complete is the process of adding tables to your default semantic model. This is a critical step to ensure you have the right data available to you. We will start by going to your workspace and opening your SQL analytics endpoint tied to your Lakehouse:

Once, opened, we can go to the Modeling tab and manage your default semantic model. Once the new window appears, select the tables holding your data within the Lakehouse and click confirm:

Now that we have your tables in place, it is time to setup some relationships to enable our reports.

Building Your Relationships

Now that we have our tables selected, we need to connect them together. If you have been using Power BI already, you know how this works. In our situation, we will need to drag our tables to the model layout so we can use it for our Copilot Reports:

You will note that I added all tables except one. The collaboration_levels table was intentionally omitted because it will not have any relationships. This table is purely for labeling purposes only and will not directly interact with the tables displayed in this layout.

Connecting the Date Dimension for Copilot Reports

To get started, we will connect our date_table to our different activity tables. To make this simple, all of our relationships will use the same column name. We just need to connect the dots. We will simply connect date_table[date] to the following columns with a one-to-many relationship:

• onedrive_user_activity[Report_Refresh_Date]
• teams_user_activity[Report_Refresh_Date]
• sharepoint_user_activity[Report_Refresh_Date]
• viva_engage_user_activity[Report_Refresh_Date]
• copilot_last_activity[Report_Refresh_Date]

You will note that I left one table out of the mix. We will need to create a relationship with the copilot_data table, but this one will be a little different. For this one, we need to make sure the relationship is inactive:

This might not make sense at the moment, but it will work out later. I promise! With these relationships in place, your model layout should look something like this:

With our date dimension in place, it is time to build our user data.

Connecting the User Dimension for Copilot Reports

Our graph_users table holds a lot of valuable information for deeper analysis. For example, we can provide dimensional analysis based on a user’s department, office, company, manager, or even job title! This will obviously only be as effective as the data associated with your user within Entra ID. However, many organizations find this as a great place to start.

The process of building these relationships is far easier. Both sides of the relationship use the [User_Principal_Name] column, so we just need to build out our connection. To make this happen, we will create a one-to-many connection between graph_users[User_Principal_Name] with the following columns:

• onedrive_user_activity[User_Principal_Name]
• teams_user_activity[User_Principal_Name]
• sharepoint_user_activity[User_Principal_Name]
• viva_engage_user_activity[User_Principal_Name]

Wait a minute! What about the copilot activity data tables?! Yes, I did exclude them for the moment. The reason is we need to create a bi-directional relationship our user dimension and these tables as it will aid in some of our reporting:

We are getting close, but we have one last relationship to create.

Connecting the License Dimension for Copilot Reports

The last dimension we are going to provide is the ability to filter users based upon what licenses they have assigned to them. For some organizations, there is a split in users between enterprise and frontline licenses. For others, they want to be hyper focused on only Copilot for Microsoft 365 users.

Regardless of your use case, this could provide some additional insight. To make things easier for us, we will simply create a relationship between the licensing_data and graph_users table with the [User_Principal_Name] column on both sides. This will also be a bi-directional relationship.

With our relationships in place, our model should look something like this:

At this point, please double and triple check your relationships to make sure nothing is missing or incorrectly mapped. If you do not have this in place, next week’s article is going to be painful as you will be coming back to this article.

Modeling Choices for our Copilot Reports

So why did I make some of the choices I did with relationships? Well, I can tell you that part of this is because of the challenges I have experienced already with the stock labels. For example, I have experienced some volatility with the labeling of copilots in the copilot_data table prior to the release of the API endpoints. To avoid having to make these changes in the future, I have taken a few steps to ensure this is not an issue for the future.

I also fully expect this solution to be obsolete at some point in the future. When the announcement of the Copilot Reports endpoint was made, I was hoping this would provide some more granular results. For whatever reason, this has not happened yet. However, I fully expect this to eventually fall in line with the other reporting endpoints in the future. No clue on the timeline and I won’t speculate on it, but I have to imagine that this will happen eventually. Therefore, I hope to be writing an article that says this series is obsolete and you should be using the new version of the endpoint.

Fun Fact About This Article

I think one of the biggest aspects of leveraging Microsoft Fabric is the power to build everything within a web browser. My favorite part of writing this article is that I did it completely at 37,000 feet in the air! If you are a fan of Chick Corea, I just heard 500 Miles High on repeat in my head as I wrote this article.

While the soundtrack is fun, I just wanted to highlight the power and ability to manage this project all while in the air. While working on airplane might not be your thing, it is still amazing to see what we can get done with a limited internet connection. Naturally, your milage may vary by airline, but still pretty cool in my book!

Next Steps

At this point, we are harvesting our data and we have our model set. By next week, we should have three weeks of data that we can trend and start to provide better Copilot for Microsoft 365 data points to review. I am so excited to share this and look forward to publishing that article soon!