Model metadata in Power BI is getting more and more accessable and there are a fair amount of tool that we can use to work with it. Report metadata on the otherhand is not easy to work with.
In my previous blog post, I showed how you can use Log Analytics to improve the performance of models and reports. The only missing piece was how you can find the visual that you need to work on.
This post will try to cover all the different ways we can extract and interact with report metadata.
Manual method
If you have access to the PBIX file for the report, you can extract the information for the file.
- Rename the file from .pbix to .zip
- Open the zip file and find the Layout file
- Open it in an editor like VS Code
- Format file to JSON
- Search for the VisualID (CTRL+F)
- Search for the Display name that you can look up in the Selection View in Power BI Desktop
- To make your life easier with larger report, you can also the find the Page name. You need to search for the ReportSection that encompasses the visual
Limited Python knowledge & GitHub Copilot method 🙂
I long wanted to find a meaningful task, where I could see how to use GitHub Copilot to enhance my limited python knowledge. It was quite an interesting learning experience that helped me understand how to interact with Copilot in a way that is productive.
Python Script:
This script is currently extracts the most essential information to help with the analysis in Log Analytics. Feel free to add to this script and update with additional information extracted. Happy for any code contributions in GitHub.
⚠️You might need to open the Layout file in VS Code first and format the file to JSON.⚠️
Output:
Script will create a csv file containing the Visual and Page details.
Adding the results to Log Analytics
When you add this extracted information to the Log Analytics template report, we discussed in my previous post, it greatly improve the insights and makes it really easy to find the visuals in a report.
Also enables you to do more generally analytics about the type of visualisations used and their performance.
Community tools available
Power BI Helper
“Power BI Helper is an external tool for Power BI Desktop. Using this tool, you can analyze your Power BI file, explore the data sources, the Power Query script, the data model, DAX expressions, and visualizations. This is a tool for Power BI developers and analysts and administrators, architects, and managers to create documentation of their work and have a better Power BI development experience.”
https://powerbihelper.org/
This tool give you an overview of the visuals and the ability to export this information.
Measure Killer
https://en.brunner.bi/measurekiller
- Analyzing the model and report to find unused measures and column
- Entreprise version allow tenant level analysis
- The UI doesn’t show the visual information but after exporting the data you can get to the visual details.
Microsoft Fabric APIs
With the appearance of Fabric APIs, we now have a Microsoft supported way to access Power BI Report metadata among a lot of other items available in Azure. As you can see in the image the API could be also used to manage these items.
Fabric Report Definition API: https://learn.microsoft.com/en-us/rest/api/fabric/articles/item-management/definitions/report-definition
Here are the information you can access through the API. The visual and page metadata is stored in the report.json.
Conclusion
Report metadata is useful for performance monitoring and integrating it with log analytics data can improve the usability.
