Combine Usage Metrics Across Workspaces?

[ncullen]

Hello all,

My organization is wanting to analyze the usage of a variety of tools across our enterprise. Since the default Usage Metrics Report exists on a workspace-level, is there an easy way to combine the semantic models of the various metrics reports into a single model for report creation in Power BI? Furthermore, it appears that the default metrics only capture a 30-day history of workspace usage. I would love a way to build history to encompass a much longer time frame (3-6 months at least). Does anyone have any ideas? I am a fairly novice Power BI user, but have attempted some Power Automate flows as a barebones solution with decent results, but nothing too promising. Thank you, all, in advance!

[mariussve1 · Accepted solution]

Hi,

I have a lot of experiance with the API's provided by Microsoft for Power BI.

If you have a data warehouse I recommend to store all your history there. You can also store history in Excel on a sharepoint site. Or Even better if you Are using Fabric.

I have created a smooth admin app with a lot of apis so I have full control of all of my workspaces, who has access to what and so on.

For your use case the ActivityEvent api is what you want to use.

Im using Fabric pipelines to fetch data and stores it on Fabric warehouse. You Can also use power Automate or Powershell.

Here is documentation on api:

https://learn.microsoft.com/en-us/rest/api/power-bi/admin/get-activity-events

There is a lot of blogs with details on how you Can do it in diffrent ways.

[rohit1991 · Accepted solution]

Hi @ncullen ,

Hi,

You're absolutely on the right track with wanting to centralize usage metrics across multiple workspaces. While the default Usage Metrics Reports are indeed limited to individual workspaces and only retain 30 days of history, there are ways to build a consolidated and extended view. One approach is to use the Power BI Activity Log, which provides detailed audit data across your entire tenant.

This data can be accessed via the Microsoft 365 Compliance Center or through the Office 365 Management API, and then ingested into Power BI for reporting. To automate this, you could set up a Power Automate flow or an Azure Logic App to pull log data on a scheduled basis and store it in a central location (e.g., SharePoint, Azure Blob Storage, or a SQL database). From there, you can build a semantic model in Power BI that refreshes regularly and retains historical data beyond the 30-day window. This approach gives you much more flexibility and control for tracking long-term usage across your organization.

[Anonymous]

Hi @ncullen 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[Anonymous]

Hi @ncullen 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Anonymous]

Hi @ncullen 
I wanted to check if you had the opportunity to review the information provide @mariussve1and @Akash_Varuna . Please feel free to reach us if you have any further questions. If their response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[mariussve1 · Accepted solution]

Hi,

I have a lot of experiance with the API's provided by Microsoft for Power BI.

If you have a data warehouse I recommend to store all your history there. You can also store history in Excel on a sharepoint site. Or Even better if you Are using Fabric.

I have created a smooth admin app with a lot of apis so I have full control of all of my workspaces, who has access to what and so on.

For your use case the ActivityEvent api is what you want to use.

Im using Fabric pipelines to fetch data and stores it on Fabric warehouse. You Can also use power Automate or Powershell.

Here is documentation on api:

https://learn.microsoft.com/en-us/rest/api/power-bi/admin/get-activity-events

There is a lot of blogs with details on how you Can do it in diffrent ways.

[Akash_Varuna]

Hi @ncullen Could you try these please 

• Export Metrics: Export usage metrics from each workspace (CSV/Excel) using Power Automate or manually.
• Central Storage: Store files in SharePoint or Azure Blob Storage.
• Combine Data: Use Power BI Dataflows or Power Query to append data into a single table.
• Build History: Add a "Load Date" column and enable incremental refresh for historical tracking.
• Automate: Automate exports and refreshes using Power Automate and Power BI schedules.
  If this post helped please do give a kudos and accept this as a solution
  Thanks In Advance