Monitoring Power BI using REST APIs from Python

Here's the reformatted content as clean, semantic HTML:

About the role

This tutorial explains how to monitor Power BI using REST APIs from Python, including accessing data about user/service principal permissions, workspaces, and other administrative information.

Tutorial Overview

This guide provides step-by-step instructions for calling Power BI REST APIs from Python, covering:

• Creating and configuring a Service Principal for Power BI API access
• Authenticating with Azure Identity library
• Making HTTP GET requests to Power BI APIs
• Normalizing JSON responses to dataframes

Step 1: Prepare a Service Principal (SP)

To use Power BI APIs, you'll need to:

• Register a new app (create the Service Principal) with correct API permissions
• Create or use an existing security group for the SP
• Configure Power BI Admin Portal tenant settings

Key configuration notes:

• 'Allow Public Flows' option may not be necessary depending on authentication method
• Tenant.Read.All or Tenant.ReadWrite.All permissions may be sufficient (admin consent may cause 401 errors)
• Store secrets in Azure Key Vault rather than in code

Step 2: Write an Authentication Flow

Use the azure.identity library for authentication with these options:

• ClientSecretCredential: Using a secret (recommended for SPs)
• CertificateCredential: Using a certificate
• InteractiveBrowserCredential: User logs in via browser
• UsernamePasswordCredential: Hard-coded credentials (doesn't work with MFA)

Example uses ClientSecretCredential with the created SP.

Step 3: Write the HTTP GET Request

Use the requests library with:

• API URI as string (e.g., https://api.powerbi.com/v1.0/myorg/groups)
• Authorization header with bearer token

Admin API requirements:

• $top argument required (e.g., $top=3000)
• $expand argument for related entities (e.g., $expand=users,reports,dashboards,datasets)
• Loop through results if returning more than 5000 items

Example endpoint: https://api.powerbi.com/v1.0/myorg/admin/groups?$expand=reports,datasets&$top=3000

Step 4: Parse and Write the Response

The API returns JSON that can be:

• Handled as JSON using the json library
• Flattened to a dataframe using pandas.json_normalize()

Example Code

Python script to get Power BI workspaces accessible by the Service Principal:

import json, requests, pandas as pd from azure.identity import ClientSecretCredential # Authentication variables tenant = 'Your-Tenant-ID' client = 'Your-App-Client-ID' client_secret = 'Your-Client-Secret-Value' api = 'https://analysis.windows.net/powerbi/api/.default' # Generate access token auth = ClientSecretCredential( authority='https://login.microsoftonline.com/', tenant_id=tenant, client_id=client, client_secret=client_secret ) access_token = auth.get_token(api).token # API request base_url = 'https://api.powerbi.com/v1.0/myorg/' header = {'Authorization': f'Bearer {access_token}'} groups = requests.get(base_url + 'groups', headers=header) # Process response groups = json.loads(groups.content) result_df = pd.concat([pd.json_normalize(x) for x in groups['value']])

Additional Resources

• REST API easy from Visual Studio Code by Mathias Thierbach
• REST API Power Query Repo by Štěpán Rešl
• Building a Power BI Admin View by BI Elite
• Download Report Authored in Browser as PBIX by James Bartlett