By Scott Sugar
In this blog, I want to talk about Power BI Usage Metrics across all Workspaces.
You’ve heard the adage, "You can't manage what you can't measure". So, when it comes to managing your tenant's online Power BI service, how do you measure the usage of your reports, dashboards, and datasets? Which reports are getting the most attention? By whom? Are there unused reports or workspaces that could be cleaned up?
There are Usage Metrics at the report/dashboard level that can be saved, edited, and unfiltered to show usage across an entire workspace, but for organizations with more than a few workspaces, monitoring usage at the workspace level is not scalable and doesn't allow for a unified view of usage across the tenant.
The online PowerBI service does offer Usage Metrics in the Admin portal, but it's not customizable or interactive, and seems to be more focused on "who has how many reports/dashboards" rather than the question I set out to answer "who's using the service, and what are they looking at?"
So, let’s look at Power BI Built-In Usage Metrics in the Admin portal:
Power BI Usage Metrics: Solution Walkthrough
Set up Pre-Requisites
We have a few things we need to set up in order to get this solution working:
- Azure AD Global Admin account
- Azure AD Application - to help us authenticate to the O365 Management APIs
- Power BI Streaming Dataset - to hold the Power BI activity logs
Create an Azure AD Application
1. Login to Azure AD Admin Portal and Create a new Azure AD Application
- Click App Registrations
- Click New Registration
2. Register Azure AD Application
- Enter Application Name
- Select Supported Acccount Types (default is fine if just gather activity logs for one tenant)
- Click Register
3. Add API Permissions
- Click API Permissions
- Click "Add a permission"
- Click "Office 365 Management APIs"
4. Request API Permissions
- Click "Application Permissions"
- Select ActivityFeed.Read
- Select ServiceHealth.Read
- Click "Add permissions"
4a. Grant Admin Consent for your org
Click "Grant admin consent for ..."
5. Gather Application (client) ID, and Directory (tenant) ID
- Click Overview
- Copy the Application ID and Directory ID into notepad for use later
6. Create Client secret
- Click Certificates and Secrets
- Click New client secret
- Enter a description
- Click "Add"
7. Gather Client secret
- Copy the Client secret into notepad for use later
Create Power BI Streaming Dataset
1. Create Streaming dataset
- Click +Create
- Click on Streaming dataset
2. Select data source
- Select API
- Click Next
3. Enter dataset and field names
- Enter a dataset name
- Fill in the "Values from stream" section as per the screenshot below.
Note: CreationTime is type DateTime, everything else is text.
4. Turn on historical data analysis and create the dataset
- Scroll down
- Enable "Historical data analysis"
- Click Create
5. Gather PowerShell Code
- Click on PowerShell
- Copy the endpoint URL to notepad for use later
- Click Done
Get-PowerBI Usage Script
The PowerShell script we'll use to grab the activity logs from the O365 Management API and load the results into a Power BI Streaming Dataset can be found here. Please save the script to a folder on your computer, open up PowerShell and change to the same directory where you saved the script.
Before trying to run the script, please update the variables at the top of the script with the values we copied into notepad.
To start, you'll need to "create a subscription" so the Management API will start to save your tenant's activities for later retrieval.
The code below will continue to look for available activity logs every 15 minutes and push the log entries into to the streaming dataset.
Start-Sleep -Seconds 900
Create Power BI Report
At this point, you can start creating your Power BI report and answering some of the questions we had before. As a start, let's look at what the most popular reports are in our tenant:
1. Create report
- Click on Datasets
- Click on Create report
2. Create Top Operations by Report Name visualization
- Click on the "Stacked bar chart" visualization
- Drag-and-drop Report Name into the Axis
- Drag-and-drop Operation into the Value (will become count of operation)
With the above in place, we can see that our "Revenue by Sales Rep v3" report is our most used (i.e. popular) report. From there, we can add slicers and other visualizations or tables to continue to delve into our tenant's Power BI usage and find out "who's using the service, and what are they looking at?".
Thoughts and Next Steps
This was a quick introduction to Azure AD Application authentication to the O365 Management Activity API, and to loading Power BI activity data into a Power BI streaming dataset.
With some work, this could be expanded to capture other O365 activity data (Exchange, Sharepoint, Teams, DLP, etc.), and could be used to save activity log information to long term storage (instead of a streaming dataset).
Let us know what you're interested in, and that will help us tailor our future technical blog postings. As always, if you have any issues, or would like to ask us any questions, please reach out to us.