What is Power BI Report Server 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. Power BI Report Server REST API 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. requests_ntlm2 HttpNtlmAuth username password baseurl HttpNtlmAuth 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) 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. PowerBIReports reports_json = requests.get(os.path.join(baseurl, "PowerBIReports"), auth=auth).json()
reports_df = pd.DataFrame(reports_json['value'])

print(reports_df.head()) 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. 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()) 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. 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. CacheRefreshPlans CacheRefreshPlans CacheRefreshPlans CacheRefreshPlans 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") 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. 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") 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 Gets ItemPolicies associated with the specified PowerBIReport CatalogItem Get the List of the Row-level security (RLS) 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. get_df_from_pbrs get_report_related_data 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") 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. Gets the data model roles that are associated with the specified PowerBIReport 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. GET POST DELETE

The code in this story is for educational purposes. The readers are solely responsible for whatever they build with it.

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

Accessing Power BI Data with Python & Power BI REST API

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

A Quick Guide to Listing All Measures in Power BI via DAX Studio

100 Days of AI Day 1: From Newsletter to Podcast, Leveraging AI for Audio Transformation

10 Threats to an Open API Ecosystem

10 Indications That You Should Invest in Automation Via APIs

10 Best Practices for Securing Your API

The Noonification: Getting Your API Into Production (10/28/2022)

A Quick Guide to Listing All Measures in Power BI via DAX Studio

100 Days of AI Day 1: From Newsletter to Podcast, Leveraging AI for Audio Transformation

10 Threats to an Open API Ecosystem

10 Indications That You Should Invest in Automation Via APIs

10 Best Practices for Securing Your API

The Noonification: Getting Your API Into Production (10/28/2022)

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps