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:
- As an Azure Function - Recommended
- As a Local PowerShell in Server
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
[!NOTE]
Azure Active Directory is now call Entra ID.
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 Application level permissions on "Microsoft Graph" API with Administrator grant to get the license & user info data:
- User.Read.All
-
Directory.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"
API's Used
| Scope | Resource | API |
|---|---|---|
| Activity | Power BI Activity Logs | Admin API - Activity Events |
| Power BI Metadata | Workspaces,DataSets,Reports,Dashboards,Permissions,Schema & Lineage | Admin Scan API – GetModifiedWorkspaces; Admin Scan API – PostWorkspaceInfo; Admin Scan API – GetScanStatus (loop); Admin Scan API – GetScanResult |
| RefreshHistory | Dataset Refresh History | Admin API - GetGroupsAsAdmin + Expand DataSets; Dataset API - Get Refresh History |
| Users & Licenses | Users & Licenses; Licenses Details | Graph API – Users;Graph API – SubscribedSKUs |
| Tenant Settings | Current Fabric Tenant Settings | Fabric REST APIs / Tenants / Tenants - Get Tenant Settings |
Setup - As an Azure Function
*Fabric API used for Tenant settings but does not require any other permissions for your Service Principal
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
[!NOTE]
On a large Power BI tenant a dedicated plan might be needed because on consumption the functions have a 10 minute timeout. Learn more about timeouts here and how to extend the timeout configuration host.json here.
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 deploy the AzureFunction.zip package:
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:
Azure Function Configuration
Go back to the Azure Function page and click on "Configuration", and manually add the following settings:
| Setting | Value | Description |
|---|---|---|
| PBIMONITOR_StorageConnStr | Optional, only if you want to store data in a different storage from the Storage Account (setting 'AzureWebJobsStorage') | |
| PBIMONITOR_AppDataPath | C:\home\data\pbimonitor | Path to AppData in Azure Function Disk, its where the state file is stored |
| PBIMONITOR_ScriptsPath | C:\home\site\wwwroot\Scripts | Path to scripts on Azure Function Disk |
| PBIMONITOR_ServicePrincipalId | [YOUR SERVICE PRINCIPAL ID] | |
| PBIMONITOR_ServicePrincipalSecret | [YOUR SERVICE PRINCIPAL SECRET] | |
| PBIMONITOR_ServicePrincipalTenantId | [YOUR TENANT ID] | |
| PBIMONITOR_ServicePrincipalEnvironment | Public | Power BI Cloud Environment |
| PBIMONITOR_StorageContainerName | pbimonitor | Name of the blob storage container |
| PBIMONITOR_StorageRootPath | raw | Path on the storage container |
| PBIMONITOR_FullScanAfterDays | 30 | Number of Days to repeat a full scan - Optimization to avoid reading too many scanner files on the Power BI Dataset |
| PBIMONITOR_CatalogGetModifiedParameters | Optional, default: 'excludePersonalWorkspaces=false&excludeInActiveWorkspaces=true' | |
| PBIMONITOR_CatalogGetInfoParameters | Optional, default: 'lineage=true&datasourceDetails=true&getArtifactUsers=true&datasetSchema=false&datasetExpressions=false' | |
| PBIMONITOR_GraphExtractGroups | false | Optional, if 'true' it will extract the members of the security groups to expand artifact permissions. |
Enable Azure Azure Key Vault (Optional)
Its possible to store the Service Principal secret in Azure Key Vault, see the documentation for more detail:
Create a system assigned managed identity for your Azure function:
Create your secrets in Azure Key Vault:
Add access policy for you system assigned managed identity created in your Azure function:
Grant "Get" under "Secret Permissions":
Reference your KeyVault on the Application Settings of Azure Function:
| Setting | Value |
|---|---|
| PBIMONITOR_ServicePrincipalId | @Microsoft.KeyVault(VaultName=myvault;SecretName=appid) |
| PBIMONITOR_ServicePrincipalSecret | @Microsoft.KeyVault(VaultName=myvault;SecretName=pbilog) |
| PBIMONITOR_ServicePrincipalTenantId | @Microsoft.KeyVault(VaultName=myvault;SecretName=tenantid) |
Azure Function Time Triggers
The Azure Function has 4 time trigger functions enabled by default:
| Function | Default Execution | Description |
|---|---|---|
| AuditsTimer | Everyday at 2AM | Fetches activity data from the Actitivy API |
| CatalogTimer | Everyday at 1AM | Fetches metadata from the tenant: workspaces, datasets, reports,data sources |
| DatasetRefreshTimer | Everyday at 5AM | Fetches the refresh history of all datasets in workspaces where the service principal is a Member |
| GraphTimer | Everyday at 4AM | Fetches the User & License information from Graph API |
| TenantSettingsTimer | Everyday at 4am | Fetches Tenant Setting data from Fabric API |
The function should be ready to run, go to the function page and open the “AuditsTimer” and Run it:
Change Azure Function Time Trigger
Its possible to change the time of the trigger by changing the 'function.json' file for each timer using App Service Editor:
Or editing the timer integration:
Force a Full Scan
On large tenants you may run into memory issues reading all the data from a Power BI Dataset.
The PowerQuery of the PowerBI template was optimized to only read the scan files since the last full scan and the Azure Function setting 'PBIMONITOR_FullScanAfterDays' ensure a full scan will be executed every X days.
Its also possible to force a full scan by editing the State file (C:\home\data\pbimonitor\state.json) using Kudo
And remove properties: Catalog.LastRun, Catalog.LastFullScan (if exists)
Storage Account
If you dont want to use the built-in storage account of the Azure Function its possible to connect the Azure Function to another storage account by setting the connection string of the storage account in the configuration property: 'PBIMONITOR_StorageConnStr'
Power BI Report Template
Open the Power BI Report template PBI - Activity Monitor and set the parameters:
Change the parameter "DataLocation" and write the blob storage name:
You'll also need to copy the Access key from the Azure Portal:
And then paste it in the "Account key" box in the Azure Blob Storage credentials, which can be found in the Data Source Settings in Power BI Desktop:
Incremental Refresh
By default the Power BI template will read all the activity files from the storage account, but those files are not updatable and a possible optimization is to enable Incremental Refresh on the Activity Table.
The template is already prepared to support Incremental Refresh and filter only the new files, there is already a RangeStart & RangeEnd parameter:
On the "Activity" table, enable Incremental Refresh with the desired configuration:
The Dataset refresh should be significantly faster in the service after this configuration.
Setup - As a Local PowerShell
Install Required PowerShell Modules (as Administrator)
Install-Module -Name MicrosoftPowerBIMgmt -RequiredVersion 1.2.1026Configuration file
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 Template
Open the Power BI Template file and change the parameter "DataLocation" to the data folder.