Intro

For more information please watch the session PBI Monitoring 101 (slides here)

This project aims to provide a solution to collect activity & catalog data from your Power BI tenant using powershell scripts and a Power BI templates to analyse all this data.

You can deploy the powershell scripts in two ways:

Local on Windows
As an Azure Function

Requirements

Ensure you have the propper permissions

A Power BI Administrator account to change the Tenant Settings
Permissions to create an Azure Active Directory Service Principal
Permissions to create/use an Azure Active Directory Security Group

Create a Service Principal & Security Group

On Azure Active Directory:

Go to "App Registrations" select "New App" and leave the default options
Generate a new "Client Secret" on "Certificates & secrets" and save the Secret text
Save the App Id & Tenant Id on the overview page of the service principal
Create a new Security Group on Azure Active Directory and add the Service Principal above as member
Optionally add the following API's on "API Permissions" and Administrator grant to get the license & user info data:
- User.Read.All
- Organization.Read.All

Authorize the Service Principal on PowerBI Tenant

As a Power BI Administrator go to the Power BI Tenant Settings and authorize the Security Group on the following tenant settings:

"Allow service principals to use read-only Power BI admin APIs"
"Allow service principals to use Power BI APIs"
"Enhance admin APIs responses with detailed metadata"
"Enhance admin APIs responses with DAX and mashup expressions"

Setup - Local PowerShell

Install Required PowerShell Modules (as Administrator)

Install-Module -Name MicrosoftPowerBIMgmt -RequiredVersion 1.2.1026

Change the Config.json

Open the Config File and write the saved properties from the Service Principal:

AppId
AppSecret
Tenant Id

Run

The file Fetch - Run is the entry point to call the other scripts.

Ensure Fetch - Run is targeting the proper configuration file (parameter "configFilePath") and you can also control which scripts are executed on the parameter $scriptsToRun

Run Fetch - Run

Open the Power BI Report

Open the Power BI Template file and change the parameter "DataLocation" to the data folder.

Setup - As an Azure Function

On an Azure Subscription create a resource group:

All the resources should be created in the same region as the Power BI Tenant, to see the region of the Power BI tenant go to the About page on powerbi.com:

Inside the Resource Group start a Function App Creation Wizard

Basics

Runtime - "PowerShell Core"
Version 7.0

Hosting

Storage Account - Create a new storage account to hold the data collected from the Azure Function
Plan Type - Consumption, oon a large Power BI tenant a dedicated plan might be needed because on consumption the functions have a 10 minute timeout

Monitoring

Create a new AppInsights for logging & monitoring execution

In the end the resource group shall have the following resources:

To deploy the Azure Function code you need to run the script Tool - PublishAzureFunction. This script will create a zip file ready to deploy to the Azure Function:

Open the Azure Function page, go to "Advanced Tools" and click "Go ➔" This will take you to a page called "Kudu Services"

Go to "Tools" -> "Zip Push Deploy" and drag & drop the file AzureFunction.zip:

Confirm if the deploy was successful:

Go back to the Azure Function page and click on "Configuration", the following configuration settings must shall be created:

Setting	Value
PBIMONITOR_AppDataPath	C:\home\data\pbimonitor
PBIMONITOR_ScriptsPath	C:\home\site\wwwroot\Scripts
PBIMONITOR_ServicePrincipalId	[YOUR SERVICE PRINCIPAL ID]
PBIMONITOR_ServicePrincipalSecret	[YOUR SERVICE PRINCIPAL SECRET]
PBIMONITOR_ServicePrincipalTenantId	[YOUR TENANT ID]
PBIMONITOR_ServicePrincipalEnvironment	Public
PBIMONITOR_StorageContainerName	pbimonitor
PBIMONITOR_StorageRootPath	raw

The function should be ready to run, go to the function page and open the “AuditsTimer” and Run it:

A change to the Power BI file is required to work with the Blob Storage, open the PBIX and the Power Query window, go to the query "FilesProxy" and uncomment the queries "* from BlobStorage":