Leveraging Power BI to monitor your Power BI Reports and workspace health
In the dynamic world of data analytics, ensuring the health and performance of your Power BI reports and workspaces is paramount. To streamline this process, I’ll guide you through creating a Power BI report that monitors the vital statistics of your reports and workspaces. This meta-monitoring approach adds an extra layer of efficiency to your analytics strategy.
Step 1: Data Collection Setup
Start by setting up a data collection mechanism to gather relevant metrics from your Power BI service. Utilize the Power BI REST API or leverage Power Automate to fetch data at regular intervals. Key metrics may include report load times, dataset refresh status, and user engagement statistics.
To fetch the data at regular intervals, you will need an orchestrator to run these jobs, it can be an Azure Data Factory pipeline, CRON job, Logic Apps etc.
In this example, I will use this API to get a list of reports in a specific workspace. But I need to get the token to run this API.
To get the token I need to run this other API:
url = f'https://login.microsoftonline.com/{tenantId}//oauth2/v2.0/token'
payload = {
'client_id': clientId,
'client_secret': clientSecret,
'grant_type': 'client_credentials',
'scope': 'https://analysis.windows.net/powerbi/api/.default'
}
response = requests.post(url=url,data=payload)
accessToken = response.json()['access_token']where clientId, client_secret and tenantID are related to the Azure subscription you are working on. Ask support to your platform team to get these details or check this other article:
https://docs.lacework.net/onboarding/gather-the-required-azure-client-id-tenant-id-and-client-secret
Once we get the token, we should use this token in this way
url = f'https://api.powerbi.com/v1.0/myorg/groups/{workspaceId}/reports'
print(url)
headers = {
'Authorization': f'Bearer {accessToken}'
}
#restAPI is a function that I wrote to run API easily in python
response = rest_api(url, headers, params = {}, body = {}, verb = 'get')Explore a variety of Power BI REST APIs by referring to the following link, where a comprehensive collection of resources is available for obtaining diverse information:
https://learn.microsoft.com/en-us/rest/api/power-bi/datasets.
You can find the function rest_api in this article: https://medium.com/@emanueleorecchio90/simplifying-rest-api-requests-in-python-with-a-generic-function-97f1333a7d8e
Step 2: Building the Monitoring Dataset
Create a dedicated dataset within Power BI that stores the collected metrics. Define tables and relationships based on the types of data you’re collecting. Ensure the dataset is refreshed frequently to keep the information up to date.
You can have several tables helpful to monitor the health of your project. You can create custom tables in the data source, for instance, a delta table in Databricks or a table in SQL server, or log your daily ETL process and ingest it in Power BI. In case of custom tables or logs, remember to insert a DateTime and an ID to identify the element uniquely and it will help to create just 1 to many relationships in your Power BI model.
It is really important to maintain good performance and scalability. More details about this topic are in this official documentation: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand
Step 3: Designing the Monitoring Report
Design a visually appealing report that provides a comprehensive overview of your Power BI environment. Include key metrics as well as graphical representations for better insights. Utilize Power BI’s extensive visualization options to customize the report according to your preferences.
We can add for example another table with the Power BI refreshes for each report using this other API:
You will get in this way a relationship one to many between the list of reports and the list of report refreshes.
The API about the refreshes is related to just one report, so you need to create a loop where for each report, you will call this API and save the results in a table. You can use the datasetID as key to join the two tables. Remember, one dataset can have several reports associated.
Step 4: Workspace Health Metrics
To monitor the health of your workspaces, incorporate metrics such as the number of reports, datasets, and dashboards within each workspace. Assessing the workspace activity and collaboration trends can help identify areas that require attention.
Craft an insightful dashboard that swiftly communicates your application’s status. Integrate direct links within your reports, ensuring seamless navigation to pinpointed issues, be it within a specific report or in case of pipeline failures. This user-friendly design not only provides a holistic view but also expedites issue resolution through immediate access to relevant details.
If you are using for example Azure Data Factory remember to save the runID in your table so you can recreate the direct pipeline link in this way if it is a SQL statement:
select
'https://adf.azure.com/en/monitoring/pipelineruns/' + ADFRunID + '?factory=%2Fsubscriptions%{add here subscriptionID}%2FresourceGroups%2F{add here resource group name}%2Fproviders%2FMicrosoft.DataFactory%2Ffactories%2F{add here azure data factory name}' as ADFrunUrl
from …Step 5: Report Performance Metrics
Dig into individual report performance metrics, including load times, user interactions, and popular visualizations. Pinpoint reports that may need optimization or investigate anomalies that could affect user experience.
An important part is to integrate the User metrics usage and the report performances, Power BI offers all these data, and you can create your usage metrics reports using this official documentation:
https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-usage-metrics
and you can connect to this report in Direct query and add further data from other tables. Please follow this article for further details:
Integrating Power BI Usage Metrics with Additional Data Sources for Comprehensive Analysis
Step 6: Automation and Alerts
Enhance your monitoring solution by incorporating automation. Set up alerts for predefined thresholds, enabling proactive responses to potential issues. Power Automate can be a valuable tool for triggering notifications based on the monitoring data or you can set your alerts directly in the Power BI dashboard by pinning your visuals as explained here.
Conclusion
Creating a Power BI report to monitor your Power BI reports and workspaces provides a holistic view of your analytics ecosystem. By tracking key metrics, you empower yourself to make informed decisions, optimize performance, and ensure a seamless experience for end-users.
Embrace this meta-monitoring strategy to elevate your Power BI governance and enhance the overall health of your analytics environment. As the data landscape evolves, staying vigilant and proactive in monitoring becomes a strategic advantage for any organization harnessing the power of Power BI.
This article serves as a concise introduction, providing a brief overview of the topic. Numerous additional details, components, and functionalities can be explored by referring to external sources on the internet. Subsequent articles will delve into additional features, contributing to a comprehensive understanding of the broader functionality associated with this subject.
Any questions on your mind or something you’d like me to explore further? Just write in the comments, and I’ll make sure to include it in my next article. Let’s keep the conversation going!
