More than 1 year has passed since last update.

Power BI workout - Enhanced refresh with Power BI REST API

PowerBI

Posted at 2022-06-27

Power BI データセットの更新を Power BI REST API 経由で実行する。更新は長い時間が必要になることがあるから非同期処理できるようになっている。

Power BI Cmdlets for Windows PowerShell and PowerShell Core

Power BI Cmdlets for Windows PowerShell and PowerShell Core | Docs

Power BI REST API

Power BI REST APIs for embedded analytics and automation - Power BI REST API | Microsoft Docs

Datasets - Refresh Dataset In Group

Datasets - Refresh Dataset In Group - REST API (Power BI Power BI REST APIs) | Microsoft Docs

Rebinds the specified report from the specified workspace to the specified dataset.

If the specified dataset resides in a different new workspace experience workspace from the report or in My workspace, then a shared dataset will be created in the report's workspace.
On rebind, reports with a live connection will lose that connection and instead have a direct binding to the target dataset.

Permissions

This API call can be called by a service principal profile. For more information see: Service principal profiles in Power BI Embedded.

Required Scope

Dataset.ReadWrite.All

Limitations

For Shared capacities, a maximum of eight requests per day (including refreshes executed using a scheduled refresh) can be initiated. In the request body, only can be modified for Shared capacities. Therefore, asynchronous refresh operations cannot be triggered.notifyOption
For Premium capacities, the maximum requests per day is only limited by the available resources in the capacity. If available resources are overloaded, refreshes are throttled until the load is reduced. The refresh will fail if throttling exceeds 1 hour.

POST https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId}/refreshes

URI Parameters

Name	In	Required	Type	Description
datasetId	path	True	string uuid	The dataset ID
groupId	path	True	string uuid	The workspace ID

Request Body

Name	Required	Type	Description
notifyOption	True	NotifyOption	Mail notification options
applyRefreshPolicy		boolean	Determine if the policy is applied or not	💎
commitMode		DatasetCommitMode	Determines if objects will be committed in batches or only when complete	💎
effectiveDate		string	If an incremental refresh policy is applied, the parameter overrides the current date.effectiveDate	💎
maxParallelism		integer	The maximum number of threads on which to run parallel processing commands	💎
objects		DatasetRefreshObjects[]	An array of objects to be processed	💎
retryCount		integer	Number of times the operation will retry before failing	💎
type		DatasetRefreshType	The type of processing to perform	💎

NotifyOption

Name	Type	Description
MailOnCompletion	string	A mail notification indicating success or failure will be sent on refresh completion
MailOnFailure	string	A mail notification will be sent on refresh failure
NoNotification	string	No notification will be sent

DatasetRefreshType 💎

Name	Type	Description
Automatic	string	If the object needs to be refreshed and recalculated, refresh and recalculate the object and all its dependents. Applies if the partition is in a state other than Ready.
Calculate	string	Recalculate this object and all its dependents, but only if needed. This value doesn't force recalculation, except for volatile formulas.
ClearValues	string	Clear values in this object and all its dependents
DataOnly	string	Refresh data in this object and clear all dependents
Defragment	string	Defragment the data in the specified table. As data is added to or removed from a table, the dictionaries of each column can become polluted with values that no longer exist in the actual column values. The defragment option will clean up the values in the dictionaries that are no longer used.
Full	string	For all partitions in the specified partition, table, or database, refresh data and recalculate all dependents. For a calculation partition, recalculate the partition and all its dependents.

DatasetCommitMode 💎

Name	Type	Description
PartialBatch	string	Commit the refresh operation in batches
Transactional	string	Commit the whole refresh operation as a transaction

DatasetRefreshObjects 💎

Name	Type	Description
partition	string	Partition to refresh
table	string	Table to refresh

Responses

Name	Type	Description
202 Accepted		Accepted Headers x-ms-request-id: string Location: string

DEMO | Refresh Dataset In Group

いくつかの Refresh type と Cancel とか

Define

# Dataset in workspace
$WorkspaceName = "DEMO2"
$DatasetName = "RefreshDemo"

Login Power BI

Connect-PowerBIServiceAccount (MicrosoftPowerBIMgmt.Profile) | Microsoft Docs

Login-PowerBI | Out-Null

Get Power BI workspace | Get Power BI dataset

$Workspace = Get-PowerBIWorkspace -Name $WorkspaceName
$Dataset = Get-PowerBIDataset -Workspace $Workspace |
    Where-Object Name -EQ $DatasetName

"IsOnDedicatedCapacity : $($Workspace.IsOnDedicatedCapacity)" |
    Write-Host -BackgroundColor Yellow -ForegroundColor Black

Power BI dataset enhanced refresh

Refresh dataset

DatasetRefreshType

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.

$Uri = "https://api.powerbi.com/v1.0/myorg/" +
    "groups/$($Workspace.Id)/datasets/$($Dataset.Id)/refreshes"
$Token = Get-PowerBIAccessToken
$RequestBody = @"
{"type": "automatic"}
"@

Invoke-RestMethod -Method Post -Uri $Uri -Body $RequestBody `
    -ContentType "application/json" -Headers $Token `
    -ResponseHeadersVariable ResponseHeader   

$ResponseHeader.'x-ms-request-id'
$ResponseHeader.Location

$Uri = "https://api.powerbi.com/v1.0/myorg/" +
    "groups/$($Workspace.Id)/datasets/$($Dataset.Id)/refreshes"
$Token = Get-PowerBIAccessToken
$RequestBody = @"
{"type": "clearValues"}
"@

Invoke-RestMethod -Method Post -Uri $Uri -Body $RequestBody `
    -ContentType "application/json" -Headers $Token `
    -ResponseHeadersVariable ResponseHeader   

$ResponseHeader.'x-ms-request-id'
$ResponseHeader.Location

$Uri = "https://api.powerbi.com/v1.0/myorg/" +
    "groups/$($Workspace.Id)/datasets/$($Dataset.Id)/refreshes"
$Token = Get-PowerBIAccessToken
$RequestBody = @"
{"type": "dataOnly"}
"@

Invoke-RestMethod -Method Post -Uri $Uri -Body $RequestBody `
    -ContentType "application/json" -Headers $Token `
    -ResponseHeadersVariable ResponseHeader  

$ResponseHeader.'x-ms-request-id'
$ResponseHeader.Location

$Uri = "https://api.powerbi.com/v1.0/myorg/" +
    "groups/$($Workspace.Id)/datasets/$($Dataset.Id)/refreshes"
$Token = Get-PowerBIAccessToken
$RequestBody = @"
{"type": "calculate"}
"@

Invoke-RestMethod -Method Post -Uri $Uri -Body $RequestBody `
    -ContentType "application/json" -Headers $Token `
    -ResponseHeadersVariable ResponseHeader  

$ResponseHeader.'x-ms-request-id'
$ResponseHeader.Location

$Uri = "https://api.powerbi.com/v1.0/myorg/" +
    "groups/$($Workspace.Id)/datasets/$($Dataset.Id)/refreshes"
$Token = Get-PowerBIAccessToken
$RequestBody = @"
{
    "type": "full",
    "objects":[
        {
            "table": "Table2",
            "partition": "Table2Partition1"
        }
    ]
}
"@

Invoke-RestMethod -Method Post -Uri $Uri -Body $RequestBody `
    -ContentType "application/json" -Headers $Token `
    -ResponseHeadersVariable ResponseHeader  

$ResponseHeader.'x-ms-request-id'
$ResponseHeader.Location

Get refresh status

$RefreshStatus = 
    Invoke-PowerBIRestMethod -Method Get -Url "$($ResponseHeader.Location)" |
    ConvertFrom-Json

$RefreshStatus|Format-Table
$RefreshStatus.objects

Cancel refresh

Invoke-PowerBIRestMethod -Method Delete -Url "$($ResponseHeader.Location)"

Logout Power BI

Disconnect-PowerBIServiceAccount (MicrosoftPowerBIMgmt.Profile) | Microsoft Docs

Logout-PowerBI

思ったこと🙄

いちど試せばわかる。

その他

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up