PowerBI/Fabric REST API unleashed!

My previous article looked at REST APIs in a general sense and signalled some topics of interest to Power BI developers. My impression was that the article was well received, but it left some dissatisfaction because the examples in it were quite general. In this article, I will try to address the use of the Power BI REST API in a more practical sense. The examples given are equally applicable to both Power BI and Fabric. Except for the fact that data governance in Fabric is a bit more complex due to the ability to use domains - when I gain relevant experience in this area, I will be happy to write about it. Using this tool and automating many routine tasks is one of the essential skills of an advanced Power BI developer, so let's dig in! As practical examples of using the REST API, I used dataset refresh and dataflows.

Introduction to Power BI REST API

The Power BI REST API offers a comprehensive interface to interact with Power BI programmatically, allowing developers to tap into its rich feature set and capabilities. Let’s explore a few key examples:

• Embed Reports and Dashboards: By utilizing the API, developers can directly embed reports and dashboards into external applications like web portals or custom apps, enhancing the user experience by offering data visualization without the need to navigate to the Power BI service.
• Example: A sales dashboard embedded directly into a CRM, showing live sales metrics to the CRM user without requiring them to leave the application.
• Dataset Management: Developers can upload new datasets, replace existing ones, or manipulate dataset schemas, thus automating the lifecycle of data sources.
• Bulk Operations: Whether it's deploying a report to multiple workspaces or making bulk updates to user roles, the API can handle tasks that would be tedious via GUI.
• Example: A large company with numerous departments wants to roll out a new report template. Instead of manually sharing it with each department's workspace, an automated script pushes the report to all at once.
• Extracting Metadata: This provides a more granular view into your Power BI assets. You can extract details about tables, columns, measures, and even relationships in a dataset.
• Example: Before migrating to a new version of a dataset, an audit is performed to understand which reports might be affected. The API fetches metadata to determine which reports utilize specific tables or measures.
• Workspace Automation: Create, update, or delete workspaces based on external triggers or organizational changes.
• Example: Whenever a new project is initiated in the company's project management tool, a corresponding Power BI workspace is automatically created, complete with predefined reports and dashboards tailored to the project's needs.

By leveraging these capabilities, organizations can weave Power BI's analytical prowess seamlessly into their broader IT infrastructure, workflows, and applications. The REST API, thus, serves as a bridge, extending Power BI beyond its native GUI and into the fabric of your data-driven enterprise.

Power BI REST API vs. GUI: Unpacking the Benefits

Choosing the Power BI REST API over the GUI offers unique advantages that can significantly enhance how organizations utilize Power BI:

• Automation: The GUI is sufficient for individual and immediate tasks, but when it comes to recurring or conditional actions, the API is far more versatile. For instance, while you might schedule dataset refreshes daily through the GUI, using the API allows you to trigger a refresh immediately following a specific event, like a significant data update in your primary system.
• Advanced User Management: The GUI offers basic user and access management. However, when you're dealing with larger teams or more complex organizational structures, the API provides the granular controls you need. Take, for instance, a scenario where you have to assign new roles to hundreds of users after an organizational reshuffle. With the API, what might take hours in the GUI can be completed in minutes.
• Differential Deployment: The GUI has its strengths, but if you're looking to deploy assets under specific criteria, the API is your best bet. Imagine you've developed a new report but want it visible only to departments that exceeded certain KPIs last quarter. With the API, such selective deployments can be scripted and executed efficiently.
• Custom Logging and Auditing: While Power BI provides standard logging, there are always specialized tracking needs. Suppose you want insights into which reports see the most traffic during year-end or budgeting cycles. The API allows you to craft custom logging rules, giving you the tailored insights you need.

Power BI Datasets, Dataflows, and Tackling Limitations with REST API

Datasets vs. Dataflows: A Brief Overview

• Datasets: In Power BI, datasets are collections of data that you can use to create reports and dashboards. They can be a combination of different data sources, like SQL databases, Excel files, or even other cloud-based data.
• Dataflows: Dataflows are a more advanced feature, focusing on ETL (Extract, Transform, Load) processes. Essentially, dataflows allow users to build reusable data preparation logic. They help in transforming raw data from various sources into a cleansed, transformed, and integrated form in Power BI, stored in Azure Data Lake Storage. Dataflows are available in Power BI Premium or for users with a dedicated cloud capacity.

Limitations of Scheduled Refresh in Power BI

• Datasets: With Power BI Pro, you're limited to eight daily refreshes. However, with Power BI Premium, this increases, allowing up to 48 refreshes per day.
• Dataflows: In Power BI Premium, dataflows can be refreshed up to 48 times daily, similar to datasets. However, the frequency and number of refreshes also depend on the complexity and volume of the data being processed.

While these scheduled refreshes offer convenience, the limitations can be a hindrance, especially for businesses with dynamic data needs.

Enhanced refresh with Power BI REST API

Power BI's REST API offers enhanced refresh capabilities that go beyond just triggering a full dataset refresh. These capabilities allow developers and administrators to have granular control over their data refresh strategies, optimizing performance and resource usage. Specifically, the REST API supports incremental refreshes, table-level refreshes, and partition-level refreshes.

1. Incremental Refresh: This type of refresh only updates data that has changed since the last refresh, reducing the amount of data processed and improving refresh times. If you need to refresh whole model instead of performing incremental refresh you can use this request body:

{
 "type": "Full",
? ? "commitMode": "transactional",
? ? "maxParallelism": 2,
? ? "retryCount": 2,
  ? "applyRefreshPolicy": false
  }        

2. Table-level Refresh: Instead of refreshing the entire dataset, you can target specific tables within the dataset. This is useful when only a subset of your data has changed. An example request body for a table-level refresh:

{
 "type": "Full",
? ? "commitMode": "transactional",
? ? "maxParallelism": 2,
? ? "retryCount": 2,
? ? "objects": [
? ? ? ? {
? ? ? ? ? ? "table": "Table1"
? ? ? ? }
? ? ]
}        

3.Partition-level Refresh: For large datasets that are partitioned, you might only need to refresh specific partitions. This level of granularity can further optimize refresh times. An example request body for a partition-level refresh:

{ 
"type": "Full",
? ? "commitMode": "transactional",
? ? "maxParallelism": 2,
? ? "retryCount": 2,
? ? "objects": [
? ? ? ? {
? ? ? ? ? ? "table": "Table1",
            "partition": "Partition1"
? ? ? ? }
? ? ]
}        

Bypassing Refresh Limitations with Power BI REST API

The Power BI REST API can be used to manage and interact with Power BI resources programmatically. One of its key utilities is initiating refreshes on-demand, giving users more control over when their data is updated. To perform following operations dataset must be Power BI Premium, Premium per user, or Power BI Embedded. Technically, you could refresh Power BI Pro dataset using REST API but It won't allow you to exceed limit of eight refreshes daily. Scheduled and REST API refreshes are counted together. Additionally, ehnahced refresh is not avaliable in Power BI datasets. Here's a step-by-step guide to using the API for refreshing datasets and dataflows:

1. Register Your Application in Azure AD

Before making any API requests, your application must be registered in Azure AD, which allows you to authenticate with the Power BI service.

• Go to the Azure Portal.
• Navigate to Azure Entra > App Registrations > New registration.
• Provide a name for the app and select the appropriate account type.
• Once registered, note down the Application (client) ID for future use.

2. Assign Permissions to Your Application

Your application needs appropriate permissions to interact with Power BI resources.

• In the Azure Portal, go to your registered app.
• Navigate to API permissions > Add a permission > Power BI Service.
• Add the necessary permission, such as Dataset.ReadWrite.All.

3. Obtain an Access Token

Use the client ID from step 1 and your application's secret to obtain an access token. This token will be used to authenticate your API requests. Here is sample Python code.

import request

url = "https://login.microsoftonline.com/{tenant_id}/oauth2/token"
headers = {
? ? "Content-Type": "application/x-www-form-urlencoded"
}
data = {
? ? "grant_type": "password",
? ? "client_id": "{client_id}",
? ? "client_secret": "{client_secret}",
? ? "resource": "https://analysis.windows.net/powerbi/api",
? ? "scope": "openid",
? ? "username": "{your_username}",
? ? "password": "{your_password}"
}

response = requests.post(url, headers=headers, data=data)
token = response.json()["access_token"]        

Replace placeholders like {tenant_id}, {client_id}, {client_secret}, {your_username}, and {your_password} with your actual values.

4. Craft and Send Your API Request

Use the access token from step 3 to authenticate your API calls. To refresh a dataset:

refresh_url = "https://api.powerbi.com/v1.0/myorg/groups/{group_id}/datasets/{dataset_id}/refreshes
headers = {
? ? "Authorization": f"Bearer {token}",
? ? "Content-Type": "application/json"
}

response = requests.post(refresh_url, headers=headers)

# Check response
if response.status_code == 202:
? ? print("Refresh started successfully!")
else:
? ? print(f"Failed to start refresh. Status code: {response.status_code}, Error: {response.text}")        

Similarly, you can refresh dataflow using following API call, it's pretty similar:

refresh_dataflow_url = "https://api.powerbi.com/v1.0/myorg/groups/{group_id}/dataflows/{dataflow_id}/refreshes
headers = {
? ? "Authorization": f"Bearer {token}",
? ? "Content-Type": "application/json"
}

response = requests.post(refresh_dataflow_url, headers=headers)

# Check response
if response.status_code == 202:
? ? print("Dataflow refresh started successfully!")
else:
? ? print(f"Failed to start dataflow refresh. Status code: {response.status_code}, Error: {response.text}")        

Again, replace {group_id} and {dataset_id} with your specific values.

5. Monitor the Refresh Status

You might want to check the status of your dataset refresh to ensure everything went as planned:

status_url = "https://api.powerbi.com/v1.0/myorg/groups/{group_id}/datasets/{dataset_id}/refreshes/{refresh_id}
response = requests.get(status_url, headers=headers)

status = response.json()["status"]
print(f"Refresh status: {status}")        

And for dataflow:

status_url = "https://api.powerbi.com/v1.0/myorg/groups/{group_id}/dataflows/{dataflow_id}/refreshes/{refresh_id}
response = requests.get(status_url, headers=headers)

status = response.json()["status"]
print(f"Dataflow refresh status: {status}")        

Integrating with Azure and Power Platform

While the Python code examples provided can be used and implemented, it's often easier and more manageable to use Azure services to perform the desired operations. The versatility of the Power BI REST API shines when integrated with Azure services and the Power Platform, allowing you to harness the true power of automating various tasks. Let's dive deeper:

Azure Data Factory (ADF)

Azure Data Factory is a cloud-based data integration service that orchestrates and automates the movement and transformation of data. Incorporating Power BI REST API within ADF broadens its horizon:

• Scenario: After performing ETL operations in ADF, you'd want your Power BI reports to reflect the latest data without manual intervention.
• Implementation: Create a pipeline in ADF and add a web activity. After your data operations, like copying or transforming data, invoke the Power BI REST API to refresh your datasets. It ensures that as soon as the data is processed and ready, Power BI visuals are updated.
• Benefits: Seamless data operations and visualization refresh. No more waiting or manual triggering of dataset updates in Power BI.

Azure Logic Apps

Azure Logic Apps help you automate workflows and integrate apps, data, services, and systems across enterprises or organizations.

• Scenario: Suppose you want your Power BI dataset to refresh when a new row is added to an Azure SQL database. Manually checking and updating isn't feasible.
• Implementation: Use the built-in Power BI connector in Logic Apps. Set up a Logic App that has a trigger for 'When an item is added' to the Azure SQL database. Upon trigger activation, use an action to refresh a Power BI dataset using the Power BI connector.
• Benefits: Real-time or near-real-time data update in Power BI visuals based on data changes in sources.

Power Apps

Power Apps enable rapid low-code development of apps. With Power BI integration, you can make data-driven apps more visually appealing and interactive:

• Scenario: You've created an app that tracks sales. For the management, you embed a Power BI visual showing monthly sales.
• Implementation: Use the Power BI Tile control in Power Apps to embed your report visual. Additionally, use Power BI REST API calls to manage or even trigger updates to the dataset based on app logic or user inputs.
• Benefits: Enhanced user experience by coupling live data visuals with app functionalities.

Key Takeaways

• Authentication: The sanctity of every API call to Power BI rests on its authentication. Azure AD OAuth stands as a trustworthy source for access tokens.
• Error Handling: Real-world scenarios warrant rigorous error handling. From rate limit breaches to unexpected issues, robust error handling ensures consistent operation and data reliability.
• API Call Limits: Power BI REST API has specific constraints for the number of permissible API calls:

Power BI Pro users:

• 30 requests per hour per user for POST and DELETE actions.
• 200 requests per hour per user for GET requests.

Power BI Premium Per User (PPU) or Premium capacities:

• 1,000 requests per hour per user for POST and DELETE actions.
• 10,000 requests per hour per user for GET requests.

Abiding by these limits is non-negotiable. Overstepping might lead to temporary account suspension or other potential issues, emphasizing the importance of efficient API call management.

Here are further considerations regarding API limits:

• The restrictions are per user, each hour. Hence, in an organization, every individual user of the Power BI REST API will have distinct limits.
• The constraints are resource-specific. Multiple requests to identical resources will each count against the set quota.
• Enforcement is real-time. Crossing the threshold will result in immediate request failures.

Marrying Power BI REST API with Azure and the Power Platform opens up possibilities that significantly boost data operations and visualization. Always ensure to stay updated, integrated, and within the confines of set limits.

Concluding Thoughts

Power BI REST API unlocks a realm of possibilities for Power BI professionals. It bridges the gap between the GUI and automated operations, granting unmatched flexibility. By leveraging the REST API, you can seamlessly integrate Power BI into your broader data ecosystem, promoting efficiency and consistency.