How to Download the Fabric Capacity Metrics Data

[taran42]

How can I download the Fabric Capacity Metrics data?

I need to report on usage and cost for my organization and the data in the Fabric Capacity Metrics app has the data I need, however I cannot seem to download the data. I would like to download and store the data, possibly in Snowflake. 

[azurepocmain]

I have developed a comprehensive blog post that demonstrates how to extract Fabric Capacity Metrics data from a semantic model and incrementally load it into Fabric DW. This solution employs a Spark notebook for orchestration, ensuring efficient and incremental data loading: Fabric Capacity Metrics Extraction | fabricpocmain.github.io

 

[kayl-669]

Hi! This solution looks a lot less complicated than the method involving a lot of DAX put together on the pbi-guy website. However the dataframe returned by 

fabric.read_table("Fabric Capacity Metrics", "MetricsByItemandDay")

 ... and any other Metrics* table is empty for us. My colleague has experimented with the pbi-guy DAX method and apparently that also returns zero results until you pass capacity ID and timepoint parameters. Is something similar going on here?

[azurepocmain]

Hi kayl-669,

I've updated the instructions to specifically include the workspace and dataset IDs rather than just passing variables. Additionally, I utilized the remote parameters, however, the Capacity Metrics workspace still requires capacity, or an exception will be thrown. I hope this resolves the issue. Please keep me posted.  Fabric Capacity Metrics Extraction | fabricpocmain.github.io

[AmiraBedh]

If you're familiar with DAX, you can extract the data by executing custom queries in DAX Studio. This method allows you to tailor the data extraction by querying specific metrics, such as CPU usage, resource consumption, and user activity, based on the relevant time points and capacity IDs. Once you've extracted the data, you can store it in a database like Snowflake for further reporting.

Here is a good tuto to follow : https://pbi-guy.com/2024/03/30/how-to-extract-data-from-the-fabric-metrics-app-part-1/

You can use the Power BI REST API to execute DAX queries and extract the data you need. This is useful for retrieving specific metrics, but keep in mind that it has a limitation of 1 million rows per query. For large datasets, you may need to implement pagination or use other approaches like paginated reports​.

Extracting data from fabric capacity metrics app - Microsoft Fabric Community

Fabric Capacity metrics data REST API or alternate... - Microsoft Fabric Community

For more automated, periodic data extraction, you can set up a flow using Power Automate or leverage Fabric pipelines or Python notebooks (if you're using Fabric or Databricks) to automate the process

How to extract data from the Fabric Metrics App – Part 1 – PBI Guy (pbi-guy.com)

[taran42]

Hi Amira. Thanks for the quick response. I did attempt to extract the data using the first link you provided, but could not get it to work properly.

I did try connecting directly to the semantic model in PBI desktop, which somewhat worked. It was fine until I brought in a second data source, which changed the connection from live to DirectQuery. Then it would only give me data from the default capacity and none of the other capacities.

Is there a more direct way to connect to and work with this data?

[AmiraBedh]

what are your requirements ? Can you clarify more what you want to achieve ?

[taran42]

I need to create a report on both cost and usage pertaining to PBI. I currently have a PBI report built off of the Azure cost metrics data. I want to add to that usage data (particularly around users and CUs) per capacity and workspace.

It would be great if I could also tie cost data to a workspace. That would help with chargeback costs for specific projects.

[AmiraBedh]

Use Azure cost data to track expenses by tagging resources or workspaces, and the Power BI Admin or Capacity Metrics APIs to pull user activity and CU usage per workspace. Then, correlate the cost with the workspace CU consumption using DAX in Power BI to allocate costs for chargeback reporting, providing insights into both financial and operational metrics for your organization.

[taran42]

Thanks, Amira. Do you have any quick references on tagging resources/workspaces in Azure and documentation on API pulls for users/CUs?

[AmiraBedh · Accepted solution]

I found these ressources :

Tags are key-value pairs that you can apply to resources, resource groups, and subscriptions. https://learn.microsoft.com/en-us/azure/azure-resource-manager/management/tag-resources?tabs=json  explains how to add and manage these tags.

For API pulls related to Power BI usage (including users and CUs), the Power BI REST APIs provide endpoints for retrieving detailed usage metrics:
- The Power BI Admin APIs https://learn.microsoft.com/en-us/rest/api/power-bi/admin

allow you to pull user activity reports and track capacity metrics, such as Capacity Unit consumption.
- For querying specific dataset usage or capacity, you can use the Execute Queries API https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries