By Scott Sugar on February 04, 2021

Power BI Usage Metrics Across All Workspaces: Step by Step


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 Power BI 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:

admin portal power bi


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

  1. Click App Registrations
  2. Click New Registration

Azure AD Application


2. Register Azure AD Application

  1. Enter Application Name
  2. Select Supported Acccount Types (default is fine if just gather activity logs for one tenant)
  3. Click Register

how to register Azure AD Application


3. Add API Permissions

  1. Click API Permissions
  2. Click "Add a permission"
  3. Click "Office 365 Management APIs"

How to Add a permission in Azure


4. Request API Permissions

  1. Click "Application Permissions"
  2. Select ActivityFeed.Read
  3. Select ServiceHealth.Read
  4. Click "Add permissions"


4a. Grant Admin Consent for your org

  1. Click "Grant admin consent for ..."
    How to grant Consent in Azure


5. Gather Application (client) ID, and Directory (tenant) ID

  1. Click Overview
  2. Copy the Application ID and Directory ID into notepad for use later



6. Create Client secret

  1. Click Certificates and Secrets
  2. Click New client secret
  3. Enter a description
  4. Click "Add"



7. Gather Client secret

  1. Copy the Client secret into notepad for use later


Get rid of manual reporting processes & gain confidence in your data.

This demo-based video illustrates the capabilities of Power BI, compared to manual reporting processes via Excel.


Watch The Full Webinar

Create Power BI Streaming Dataset

1. Create Streaming dataset

  1. Click +Create
  2. Click on Streaming dataset

Tutorial Power BI Steaming Dataset


2. Select data source

  1. Select API
  2. Click Next

Tutorial Power BI Steaming Dataset


3. Enter dataset and field names

  1. Enter a dataset name
  2. Fill in the "Values from stream" section as per the screenshot below.
    Note: CreationTime is type DateTime, everything else is text.

Tutorial Power BI Steaming Dataset


4. Turn on historical data analysis and create the dataset

  1. Scroll down
  2. Enable "Historical data analysis"
  3. Click Create

Tutorial Power BI Steaming Dataset


5. Gather PowerShell Code

  1. Click on PowerShell
  2. Copy the endpoint URL to notepad for use later
  3. Click Done

Tutorial Power BI Steaming Dataset


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.

Update Script

Before trying to run the script, please update the variables at the top of the script with the values we copied into notepad.

how to update power bi usage script

Run Script

To start, you'll need to "create a subscription" so the Management API will start to save your tenant's activities for later retrieval.

.\Get-PowerBIUsage.ps1 -startSubscription

Loop Script

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

  1. Click on Datasets
  2. Click on Create report

Tutorial Create Power BI Report


2. Create Top Operations by Report Name visualization

  1. Click on the "Stacked bar chart" visualization
  2. Drag-and-drop Report Name into the Axis
  3. Drag-and-drop Operation into the Value (will become count of operation)

Tutorial Create Power BI Report

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.

HIROC using Power BI


Published by Scott Sugar February 4, 2021