Problem statement
As a Power BI Admin you want to be able to see all Power BI related artifacts and acitivities in your tenant. You might want to manage licences and identify Power Users. In addition you might want to check which reports are viewed the most and what data sources are being used or create a dataset catalog. This is essential for governed self-service business intelligence.
While Power BI offers build in data lineage views and performance metrics, it will not give you a holistic overview. Microsoft announced new options using Azure Log Analytics or Azure Purview, but this will require additional Azure subscriptions and might not give you all the required information. A great alternative is to use the Power BI REST APIs with a service principal and schedule the calls without requiring a manual log-in. In the following you will be able to follow the required steps.
Required steps
1. Register a new application in portal.azure.com. You do not need to assign any roles. This will later be managed through the Power BI Admin tenant settings.
2. Give your application a distinctive name and register the application. Important: Do not assign any additional API permissions to the App. The access to the admin data will be granted through the security group and the Power BI Admin tenant settings.
3. Create a Azure AD security group with a distinctive name and add the previously created application as a member.
4. Please go back to the initially registered application. We need to generate a client secret. This will allow us to authenticate as a service principal later. Make sure you store the key value in a save way, you will not be able to access this key value later on. Not even in the portal. If your key expires or you lost the value, simply create a new client secret.
5. Log into app.powerbi.com and open the Amin portal. Please note: You have to be a O365 Global Admin or Powqer BI Platform Admin to be able to define the required settings.
6. Open the Admin API settings and "allow service principals to use read-only Power BI Admin APIs". Please enable and assign the previously created security group.
7. Now we can use the service principal to call the Power BI Admin read-only APIs without the sign-in pop-up for example through PowerShell. This is important if you want to run the query in an unattended mode on a scheduled basis. In this series we will use PowerShell for the extraction, but you could use other Extract, Transform, Load (ETL) tools like Azure Data Factory or SQL Server Integration Services SSIS as well. Open PowerShell ISE as admin and install the MicrosoftPowerBIMgmt module with the following code "Install-Module -Name MicrosoftPowerBIMgmt".
Afterwards paste the following script:
$applicationID = "your application ID here"
$clientsecret = "your client secret key here" | ConvertTo-SecureString -asPlainText -Force $credential = New-Object System.Management.Automation.PSCredential -ArgumentList $applicationID, $clientsecret Connect-PowerBIServiceAccount -ServicePrincipal -Credential $credential -TenantId "your tenant id here"
8. Enter your application ID, client secret key and tenant ID. All of his can be obtained from the portal.azure.com in the registered app "Overview" and "Certificates & secrets" section. Afterwards run the script. If you get no error and see the client ID, then you have successfully connected to the Power BI API with the service principal.
9. As it is not best practices to store sensitive data like your Client Secret Key value in the scripts, I highly recommand to store this key values in an encrypted file. With the following code you can generate the encrypted file locally. This can only be decrypted on the same machine with the same user logged on, but you might consider other options as well. Afterwards we adjust the script to use the encrypted file for authentication reasons.
#ClientSec
#This script encrypts the client secret and makes it only readable for the logged in user an PC.You only need to provide the password in the pop-up
Get-Credential | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString | Out-File "<>\Client.txt"
10. Adjust the authentification in the other scripts with the following code
#Connect to Power BI with credential of Power BI Service Administrator
$applicationId = "<>"
$clientSec = Get-Content "<>\Client.txt" | ConvertTo-SecureString
$credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $applicationId, $clientSec
Connect-PowerBIServiceAccount -ServicePrincipal -Credential $credential -TenantId "<>"
In the next blog post you will learn how to retrieve the Power BI artifacts and activities as an admin with PowerShell.