Asynchronous refresh with the Power BI REST API (Preview)

  • Article
  • 6 minutes to read

By using any programming language that supports REST calls, you can perform asynchronous data-refresh operations on your Power BI datasets.

Dataset refresh operations can take some time depending on a number of factors including data volume, level of optimization using partitions, etc. Traditionally, optimizing refresh for large and complex datasets have been invoked with existing programming methods using TOM (Tabular Object Model), PowerShell cmdlets, or TMSL (Tabular Model Scripting Language). These methods can, however, require often unreliable, long-running HTTP connections.

The Power BI REST API enables dataset-refresh operations to be carried out asynchronously. By using the REST API, long-running HTTP connections from client applications aren't necessary. Asynchronous refresh also includes additional reliability features such as auto retries and batched commits.

Important

This feature is in Preview. When in preview, functionality and documentation are likely to change.

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.

Asynchronous refresh flow

Requirements

  • 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 do not change permissions as they are 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 refreshId. Because the operation is asynchronous, a client application can disconnect and check the status later if required.

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 409 Conflict HTTP status code is returned.

Parameters

Specifying parameters is not required. If not specified, the default is applied.

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 is not 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 is not 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 which was created by the request, which includes the refreshId.

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:

[
    {
        "refreshId": "1344a272-7893-4afa-a4b3-3fb87222fdac",
        "refreshType": "ViaApi",
        "startTime": "2020-12-07T02:06:57.1838734Z",
        "endTime": "2020-12-07T02:07:00.4929675Z",
        "status": "succeeded"
    },
    {
        "refreshId": "474fc5a0-3d69-4c5d-adb4-8a846fa5580b",
        "startTime": "2020-12-07T01:05:54.157324Z",
        "refreshType": "ViaApi",
        "endTime": "2020-12-07T01:05:57.353371Z",
        "status": "inProgress"
    }
    {
        "refreshId": "85a82498-2209-428c-b273-f87b3a1eb905",
        "refreshType": "ViaApi",
        "startTime": "2020-12-07T01:05:54.157324Z",
        "endTime": "2020-12-07T01:05:57.353371Z",
        "status": "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. You cannot query status on requests that have been dropped. This is by design.

Response properties

Name Type Description
refreshId Guid The identifier of the refresh request. refreshId 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.
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 - serviceExceptionJson will contain the error.
unknown indicates a completion state that cannot be determined. endTime will be empty with this status.
disabled indicates refresh was disabled by selective refresh.
extendedStatus string Augments the status property to provide additional information.
serviceExceptionJson string Failure error code in json format (not empty only on error).

* 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/<refreshId>

To check the status of a refresh operation, use the GET verb on the refresh object by specifying the refreshId. 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/<refreshId>

To cancel an in-progress refresh operation, use the DELETE verb on the refresh object by specifying the refreshId.

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-asynchronous refresh operations

Scheduled and on-demand (manual) dataset refreshes cannot be cancelled by using DELETE /refreshes/<refreshId>.

Scheduled and on-demand (manual) dataset refreshes do not support getting refresh operation details by using GET /refreshes/<refreshId>.

Power BI Embedded

If a capacity is paused manually in the Portal or by using PowerShell, or a system outage occurs, if there is an on-going asynchronous 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 asynchronous refresh operation will resume automatically. If the capacity is resumed after more than six hours, the asynchronous refresh operation may return a time out error. The asynchronous refresh operation must then be run again.

Troubleshooting

Problem - Dataset eviction

Power BI uses dynamic memory management to optimize capacity memory. If during an asynchronous 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 asynchronous refresh operation again

To learn more about Dynamic memory management and dataset eviction, see What is Power BI Premium - How capacities function.

Code sample

Here's a C# code sample to get you started, RestApiSample on GitHub.

To use the code sample

  1. Clone or download the repo. Open the RestApiSample solution.
  2. Find the line client.BaseAddress = … and provide your base URL.

The code sample uses service principal authentication.

See also

Using the Power BI REST APIs