Power BI Usage Metrics Across All Workspaces

 

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 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

  1. Click App Registrations
  2. Click New Registration

Create 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

Register Azure AD Application

 

3. Add API Permissions

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

Add API Permissions

 

4. Request API Permissions

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

Request API Permissions

 

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

Gather Application ID and Directory

 

6. Create Client secret

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

Create Client Secret

 

7. Gather Client secret

  1. Copy the Client secret into notepad for use later

Gather Client Secret

Create Power BI Streaming Dataset

1. Create Streaming dataset

  1. Click +Create
  2. Click on Streaming dataset

Create Power BI Streaming Dataset

 

2. Select data source

  1. Select API
  2. Click Next

Select Data Source

 

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.

Enter Dataset and Field Names

 

4. Turn on historical data analysis and create the dataset

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

Turn on Historical Data Analysis

 

5. Gather PowerShell Code

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

Gather PowerShell Code

 

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.

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.

PowerBIUsage Script - Update 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.

RunScript-StartSubscription-1

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.

LoopScript-1

 

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

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)

Create Top Operations by Report Name

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.

 

Related: