Accessing Power BI Data with Python Using the Power BI REST API
Tags: python, powerbi, api, restapi
Published: 2024-01-02 09:30:15

## What is Power BI Report Server REST API?

The [Power BI Report Server REST API](https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0#/info) 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.

```python
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. 

```python
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](https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0#/PowerBIReports/GetPowerBIReports).

## 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. 

```python
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](https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0#/Folders/GetFolders).

## Get 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.

```python
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](https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0#/PowerBIReports/GetPowerBICacheRefreshPlans).

## Get 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.

```python
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](https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0#/PowerBIReports/GetPowerBIReportPolicies)

## 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.

```python
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](https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0#/PowerBIReports/GetPowerBIReportDataModelRoles) and [Gets the data model role assignments that are associated with the specified PowerBIReport](https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0#/PowerBIReports/GetPowerBIReportDataModelRoleAssignments).

## 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.

---

## Explore more

{% embed https://dev.to/luca1iu %}


Thank you for taking the time to explore data-related insights with me. I appreciate your engagement.

{% cta https://www.linkedin.com/in/lucaliu-data %} 🚀 Connect with me on LinkedIn {% endcta %}

{% cta https://twitter.com/Luca_DataTeam %} 🎃 Connect with me on X {% endcta %}


{% cta https://www.instagram.com/luca1iu?igsh=MmJneDFpdjNtYWU1&utm_source=qr %} 🌍 Connect with me on Instagram {% endcta %}