May 9

May 9 Get Power BI Dataset Refresh History using Python

USE PYTHON TO GET DATA FROM POWER BI APIs

…such as limited dataset refresh history for datasets a service principle has access to

HOW TO SET IT UP?

I wrote a post about setting up a Service Principal, permissions & settings to get you started, as well as explaining the Authentication. You can find that post, here.

#########################################################################################
# Authentication - Replace string variables with your relevant values       
#########################################################################################

import json, requests, pandas as pd
try:
    from azure.identity import ClientSecretCredential
except Exception:
     !pip install azure.identity
     from azure.identity import ClientSecretCredential

# --------------------------------------------------------------------------------------#
# String variables: Replace with your own
tenant = 'Your-Tenant-ID'
client = 'Your-App-Client-ID'
client_secret = 'Your-Client-Secret-Value' # See Note 2: Better to use key vault
api = 'https://analysis.windows.net/powerbi/api/.default'
# --------------------------------------------------------------------------------------#

# Generates the access token for the Service Principal
auth = ClientSecretCredential(authority = 'https://login.microsoftonline.com/',
                                                        tenant_id = tenant,
                                                        client_id = client,
                                                        client_secret = client_secret)
access_token = auth.get_token(api)
access_token = access_token.token

print('
Successfully authenticated.')   

#########################################################################################

# Note 1: This code was authored for use in a Google Colab Notebook
# Note 2: Check here for info on using Azure Key Vault: 
          # https://docs.microsoft.com/en-us/azure/key-vault/secrets/quick-create-python    
          # The above code should be modified to programmatically get the secret from AKV

#########################################################################################
# Admin: Get Dataset Refreshes
#########################################################################################

base_url = 'https://api.powerbi.com/v1.0/myorg/'
header = {'Authorization': f'Bearer {access_token}'}

# The admin API doesn't work unless you provide a ?$top=n argument
# Top 5000
topn = '$top=5000'
admin_refreshables = f'{base_url}admin/capacities/refreshables?{topn}'
admin_refreshables_expand = f'{base_url}admin/capacities/refreshables?$expand=capacity,group&{topn}'

# HTTP GET Request
refreshes = requests.get(admin_refreshables_expand, headers=header)

print(refreshes)
try:
    refreshes = json.loads(refreshes.content)
except Exception as e:
    print(e)

try:
    result = pd.concat([pd.json_normalize(x) for x in refreshes['value']])
    print('Found', len(result['id'].dropna().unique().tolist()), 'refreshes.')
except Exception:
    print('No refreshes found.')

result