Accessing Power BI Data with Python & Power BI REST API

The Power BI Report Server REST API enables developers to programmatically access and manage the report server catalog, allowing for basic CRUD operations on folders, reports, KPIs, data sources, datasets, refresh plans, subscriptions, and more. Additionally, the REST API provides advanced functionality including the ability to navigate the folder hierarchy, discover folder contents, download report definitions, modify default report parameters, change or execute a refresh plan, and much more. As a RESTful successor to the legacy SOAP API, the Power BI Report Server REST API is a superset of the SQL Server Reporting Services REST API, leveraging the extended capabilities of Power BI Report Server as compared to SQL Server Reporting Services. This flexibility empowers developers to create custom applications, automate administrative tasks, and integrate Power BI Report Server functionality into various software solutions.

Authenticating with Power BI Report Server

Authenticating with the Power BI Report Server API involves using the requests_ntlm2 library and the HttpNtlmAuth method for NTLM authentication. Set the username, password, and baseurl variables, and then create an instance of HttpNtlmAuth with the provided credentials. This instance can be used in API requests to authenticate with the Power BI Report Server and access the API endpoints. The example code snippet demonstrates making a sample API request using Python to retrieve the list of Power BI reports available on the server.

from requests_ntlm2 import HttpNtlmAuth
import requests
import os
import pandas as pd

# please change username, password and localhost
username = "username"
password = 'password'
localhost = "localhost"

# baseurl
baseurl = "http://{}/Reports/api/v2.0".format(localhost)

# Auth
auth = HttpNtlmAuth(username=username, password=password)

# example
result = requests.get(os.path.join(baseurl, "PowerBIReports"), auth=auth).json()
print(result)

Get the List of PowerBI Reports

The provided code retrieves a list of Power BI reports from a Power BI Report Server using the PowerBIReports endpoint. It makes a GET request to the specified endpoint and authenticates using the provided credentials. The response is then parsed into a Pandas DataFrame for ease of manipulation and analysis.

reports_json = requests.get(os.path.join(baseurl, "PowerBIReports"), auth=auth).json()
reports_df = pd.DataFrame(reports_json['value'])

print(reports_df.head())

For further information, refer to the document available at Gets an array of PowerBIReport CatalogItems.

Get the List of Folders

You can use the following Python code to retrieve the list of folders in the Power BI Report Server. This code uses the requests library to send a GET request to the appropriate endpoint and processes the JSON response to create a pandas DataFrame containing information about the folders available in the Power BI Report Server.

folders_json = requests.get(os.path.join(baseurl, "Folders"), auth=auth).json()
folders_df = pd.DataFrame(folders_json['value'])

print(folders_df.head())

For further information, refer to the document available at Gets an array of Folder CatalogItems.

Get the List of Refresh Plan

To retrieve the CacheRefreshPlans for all Power BI Reports published in the Power BI Report Server, the process involves obtaining the information for all reports and then iterating through each report to retrieve its CacheRefreshPlans. This is achieved by initially fetching the list of all Power BI Reports and then accessing the CacheRefreshPlans for each individual report. By consolidating the results from each report, a comprehensive CacheRefreshPlans dataframe is generated, providing an overview of the refresh plans for all the Power BI Reports.

def get_report_CacheRefreshPlans(tag):
    result = requests.get(os.path.join(baseurl, tag), auth=auth).json()
    reports = result['value']
    cache_refresh_plans = [requests.get(os.path.join(baseurl, "PowerBIReports({})/CacheRefreshPlans".format(report['Id'])), auth=auth).json()['value'] for report in reports]
    df = pd.concat([pd.DataFrame(plans) for plans in cache_refresh_plans])
    return df

df_refresh_plans = get_report_CacheRefreshPlans("PowerBIReports")

For further information, refer to the document available at Gets the CacheRefreshPlans for a given Power BI Report.

Get the List of Users

To retrieve the list of Users associated with Power BI reports on the Power BI report server, you can use the provided Python code. This code uses the Power BI REST API to fetch the item policies for each Power BI report and extract the user information. After executing the code, you will have a DataFrame containing the report IDs, group user names, and roles of the users associated with each report. You can use this information to manage and analyze the access policies and user permissions for the Power BI reports on the server.

def get_Policies_for_all_reports(tag):
    result = requests.get(os.path.join(baseurl, tag), auth=auth).json()
    reports_df = pd.DataFrame(result['value'])

    policies = []
    for report_id in reports_df['Id']:
        baseurl_report = "{}/PowerBIReports({})".format(baseurl, report_id)
        report_result = requests.get(os.path.join(baseurl_report, 'Policies'), auth=auth).json()
        report_policies_df = pd.DataFrame(report_result['Policies'])
        report_policies_df['ReportID'] = report_id
        report_policies_df = report_policies_df[['ReportID', 'GroupUserName', 'Roles']]
        policies.append(report_policies_df)

    all_policies_df = pd.concat(policies, axis=0)
    return all_policies_df

df_users = get_Policies_for_all_reports("PowerBIReports")

For further information, refer to the document available at Gets ItemPolicies associated with the specified PowerBIReport CatalogItem

Get the List of the Row-level security (RLS)

The code utilizes the Power BI Report Server API to retrieve Row-level security information from Power BI reports. It consists of two functions: get_df_from_pbrs to obtain a DataFrame of reports and get_report_related_data to retrieve data model roles and role assignments for each report. The resulting DataFrames contain details of Row-level security settings within the Power BI reports on the Power BI Report Server. Simply call the functions with the appropriate parameters to retrieve the information.

def get_df_from_pbrs(tag):
    result = requests.get(os.path.join(baseurl, tag), auth=auth).json()
    df = pd.DataFrame(result['value'])
    return df

def get_report_related_data(tag, endpoint):
    re = []
    for i in range(len(get_df_from_pbrs(tag))):
        id = get_df_from_pbrs(tag)['Id'][i]
        url = "{}/PowerBIReports({})".format(baseurl, id)
        result = requests.get(os.path.join(url, endpoint), auth=auth).json()
        df_row = pd.DataFrame(result['value'])
        df_row['ReportID'] = id
        if endpoint == 'DataModelRoleAssignments' and len(df_row) != 0:
            df_row['DataModelRoles'] = df_row['DataModelRoles'].apply(lambda x: x[0])
        re.append(df_row)
    df = pd.concat(re, axis=0)
    return df

df_DataModelRoles = get_report_related_data(tag="PowerBIReports", endpoint="DataModelRoles")
df_DataModelRoleAssignments = get_report_related_data(tag="PowerBIReports", endpoint="DataModelRoleAssignments")

For further information, refer to the document available at Gets the data model roles that are associated with the specified PowerBIReport and Gets the data model role assignments that are associated with the specified PowerBIReport.

Conclusion

In conclusion, this article has shown how you can use Python code to access different parts of the Power BI Report Server via the Power BI Report Server REST API. The code examples demonstrate how you can retrieve Power BI Reports, Folders, Refresh Plans, Users, and Row-level security settings, giving you a good understanding of what you can do with the API.

While the examples mainly use the GET method to fetch data, it's worth mentioning that the REST API also supports other HTTP methods like POST and DELETE. If you're interested in exploring the Power BI Report Server REST API for other tasks, feel free to leave a comment for more detailed articles and further exploration.

Thank you for taking the time to explore data-related insights with me. I appreciate your engagement. If you find this information helpful, I invite you to follow me or connect with me on LinkedIn. Happy exploring!👋