Enhanced refresh with Power BI REST API
By using any programming language that supports REST calls, you can perform dataset refresh operations by using the Power BI REST API.
Optimizing refresh for large and complex partitioned datasets have traditionally been invoked with programming methods using TOM (Tabular Object Model), PowerShell cmdlets, or TMSL (Tabular Model Scripting Language). However, these methods can require often unreliable, long-running HTTP connections.
The Power BI Refresh Dataset REST API enables dataset-refresh operations to be carried out asynchronously. Long-running HTTP connections from client applications aren't necessary. Compared to standard refresh operations, Enhanced refresh with the Refresh Dataset REST API provides more customization options and features beneficial for large models:
- Batched commits
- Table and partition-level refresh
- Apply incremental refresh policies
- Get refresh operation details
- Cancel refresh operations
Note
- During preview, this feature was known as Asynchronous refresh with REST API. However, in Power BI, a standard refresh using the Refresh Dataset REST API by its inherent nature also runs asynchronously.
- Refresh operations performed by the enhanced refresh Power BI REST API, do not automatically refresh tile caches. Tile caches are only refreshed when a user accesses the report.
Base URL
The base URL follows this format:
https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId}/refreshes
By using the base URL, resources and operations can be appended based on parameters. Groups, Datasets, and Refreshes are collections. Group, Dataset, and Refresh are objects.
Requirements
Datasets in Power BI Premium, Premium per user, and Power BI Embedded.
GroupId and DatasetId are required.
The required permission scope is Dataset.ReadWrite.All. The number of refreshes is limited according to the general limitations for API-based refreshes for both Pro and Premium datasets.
Authentication
All calls must be authenticated with a valid Azure Active Directory (OAuth 2) token in the Authorization header. The token must meet the following requirements:
- The token must be either a user token or an application service principal.
- The token must have the correct audience set to https://api.powerbi.com.
- The user or application must have sufficient permissions on the dataset.
REST API modifications don't change permissions as they're currently defined for dataset refreshes.
POST /refreshes
To perform a refresh operation, use the POST verb on the /refreshes collection to add a new refresh object to the collection. The Location header in the response includes the requestId. Because the operation is asynchronous, a client application can disconnect and check the status later if necessary.
Sample request
POST https://api.powerbi.com/v1.0/myorg/groups/f089354e-8366-4e18-aea3-4cb4a3a50b48/datasets/cfafbeb1-8037-4d0c-896e-a46fb27ff229/refreshes
The body may resemble the following:
{
"type": "Full",
"commitMode": "transactional",
"maxParallelism": 2,
"retryCount": 2,
"objects": [
{
"table": "DimCustomer",
"partition": "DimCustomer"
},
{
"table": "DimDate"
}
]
}
Only one refresh operation at a time is accepted for a dataset. If there's a current running refresh operation and another is submitted, a 400 Conflict HTTP status code is returned.
Parameters
To perform an enhanced refresh operation, you must specify one or more parameters in the Request Body. Additional parameters can specify the default or an optional value. When specified, all other parameters are applied to the operation using the default value. If no additional parameters are specified, other parameters won't be applied and a standard refresh operation is performed.
Name | Type | Default | Description |
---|---|---|---|
type |
Enum | automatic |
The type of processing to perform. Types are aligned with the TMSL refresh command types: full , clearValues , calculate , dataOnly , automatic , and defragment . Add type isn't supported. |
commitMode |
Enum | transactional |
Determines if objects will be committed in batches or only when complete. Modes include: transactional , partialBatch . |
maxParallelism |
Int | 10 |
Determines the maximum number of threads on which to run processing commands in parallel. This value aligned with the MaxParallelism property that can be set in the TMSL Sequence command or by using other methods. |
retryCount |
Int | 0 |
Number of times the operation will retry before failing. |
objects |
Array | Process the entire dataset | An array of objects to be processed. Each object includes table when processing the entire table, or table and partition when processing a partition. If no objects are specified, the entire dataset is refreshed. |
applyRefreshPolicy |
Boolean | true |
If an incremental refresh policy is defined, applyRefreshPolicy will determine if the policy is applied or not. If the policy isn't applied, a process full operation will leave partition definitions unchanged and all partitions in the table will be fully refreshed. Modes are true or false . Supported behavior: If commitMode = transactional , then applyRefreshPolicy = true or false . If commitMode = partialBatch , then applyRefreshPolicy = false . Unsupported behavior: If commitMode = partialBatch , then applyRefreshPolicy = true . |
effectiveDate |
Date | Current date | If an incremental refresh policy is applied, the effectiveDate parameter overrides the current date. |
Response
202 Accepted
The response also includes a location response-header field to point the caller to the refresh operation that was just created/accepted. Location is that of the new resource created by the request, which includes the requestId
required for some enhanced refresh operations. For example, in the following response, requestId
is the last identifier in the response, 87f31ef7-1e3a-4006-9b0b-191693e79e9e
.
x-ms-request-id: 87f31ef7-1e3a-4006-9b0b-191693e79e9e
Location: https://api.powerbi.com/v1.0/myorg/groups/f089354e-8366-4e18-aea3-4cb4a3a50b48/datasets/cfafbeb1-8037-4d0c-896e-a46fb27ff229/refreshes/87f31ef7-1e3a-4006-9b0b-191693e79e9e
GET /refreshes
Use the GET verb on the /refreshes collection to list historical, current, and pending refresh operations.
Here's an example of the response body:
[
{
"requestId": "1344a272-7893-4afa-a4b3-3fb87222fdac",
"refreshType": "ViaEnhancedApi",
"startTime": "2020-12-07T02:06:57.1838734Z",
"endTime": "2020-12-07T02:07:00.4929675Z",
"status": "Completed",
"extendedStatus": "Completed"
},
{
"requestId": "474fc5a0-3d69-4c5d-adb4-8a846fa5580b",
"startTime": "2020-12-07T01:05:54.157324Z",
"refreshType": "ViaEnhancedApi",
"endTime": "2020-12-07T01:05:57.353371Z",
"status": "Unknown",
"extendedStatus": "InProgress"
}
{
"requestId": "85a82498-2209-428c-b273-f87b3a1eb905",
"refreshType": "ViaEnhancedApi",
"startTime": "2020-12-07T01:05:54.157324Z",
"endTime": "2020-12-07T01:05:57.353371Z",
"status": "Unknown",
"extendedStatus": "NotStarted"
}
]
Note
Power BI might drop additional requests if too many requests are sent in a short period of time. Power BI will perform a refresh, queue the next request, and drop all others. By design, you can't query status on requests that have been dropped.
Response properties
Name | Type | Description |
---|---|---|
requestId |
Guid | The identifier of the refresh request. requestId is required to query for individual refresh operation status or delete (cancel) an in-progress refresh operation. |
refreshType |
RefreshType | OnDemand indicates refresh was triggered interactively through the Power BI portal. Scheduled indicates refresh was triggered by the dataset refresh schedule setting. ViaApi indicates refresh was triggered by an API call. ViaEnhancedApi indicates an enhanced refresh triggered by an API call. |
startTime |
string | DateTime of start. |
endTime |
string | DateTime of end. |
status |
string | Completed * indicates the refresh operation completed successfully. Failed indicates the refresh operation failed. Unknown indicates a completion state that can't be determined. endTime will be empty with this status. Disabled indicates refresh was disabled by selective refresh. Cancelled indicates refresh was cancelled successfully. |
extendedStatus |
string | Augments the status property to provide additional information. |
* In Azure Analysis Services, this status result is 'succeeded'. If migrating an Azure Analysis Services solution to Power BI, you may have to modify your solutions.
Limit the number of refresh operations returned
The Power BI REST API supports limiting the requested number of entries in the refresh history by using the optional $top parameter. If not specified, the default is all available entries.
GET https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/refreshes?$top={$top}
GET /refreshes/<requestId>
To check the status of a refresh operation, use the GET verb on the refresh object by specifying the requestId. Here's an example of the response body. If the operation is in progress, inProgress
is returned in status.
{
"startTime": "2020-12-07T02:06:57.1838734Z",
"endTime": "2020-12-07T02:07:00.4929675Z",
"type": "Full",
"status": "InProgress",
"currentRefreshType": "Full",
"objects": [
{
"table": "DimCustomer",
"partition": "DimCustomer",
"status": "InProgress"
},
{
"table": "DimDate",
"partition": "DimDate",
"status": "InProgress"
}
]
}
DELETE /refreshes/<requestId>
To cancel an in-progress refresh operation, use the DELETE verb on the refresh object by specifying the requestId
.
For example,
DELETE https://api.powerbi.com/v1.0/myorg/groups/f089354e-8366-4e18-aea3-4cb4a3a50b48/datasets/cfafbeb1-8037-4d0c-896e-a46fb27ff229/refreshes/1344a272-7893-4afa-a4b3-3fb87222fdac
Limitations
Non-enhanced refresh operations
Scheduled and on-demand (manual) dataset refreshes can't be canceled by using DELETE /refreshes/<requestId>
.
Scheduled and on-demand (manual) dataset refreshes don't support getting refresh operation details by using GET /refreshes/<requestId>
.
Get Details and Cancel are new operations for enhanced refresh only. They aren't supported for non-enhanced refresh operations.
Power BI Embedded
If a capacity is paused manually in the Portal or by using PowerShell, or a system outage occurs, if there's an on-going enhanced refresh operation the status of the refresh will stay in In-Progress
for the maximum of six hours. If the capacity is resumed within six hours, the refresh operation will resume automatically. If the capacity is resumed after more than six hours, the refresh operation may return a time-out error. The refresh operation must then be run again.
Troubleshooting
Problem - Dataset eviction
Power BI uses dynamic memory management to optimize capacity memory. If during a refresh operation the dataset is evicted from memory, the following error may be returned:
{
"messages": [
{
"code": "0xC11C0020",
"message": "Session cancelled because it is connected to a database that has been evicted to free up memory for other operations",
"type": "Error"
}
]
}
Solution: Run the refresh operation again
To learn more about Dynamic memory management and dataset eviction, see Dataset eviction.
Code sample
Here's a C# code sample to get you started, RestApiSample on GitHub.
To use the code sample
- Clone or download the repo. Open the RestApiSample solution.
- Find the line client.BaseAddress = … and provide your base URL.
The code sample uses service principal authentication.
See also
Power BI Refresh Dataset REST API
Using the Power BI REST APIs
Feedback
Submit and view feedback for