Power BI workout - Enhanced refresh with Power BI REST API

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](https://docs.microsoft.com/en-us/powershell/power-bi/overview)
# Power BI REST API
- [Power BI REST APIs for embedded analytics and automation - Power BI REST API | Microsoft Docs](https://docs.microsoft.com/en-US/rest/api/power-bi/)

## Datasets - Refresh Dataset In Group
- [Datasets - Refresh Dataset In Group - REST API (Power BI Power BI REST APIs) | Microsoft Docs](https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/refresh-dataset-in-group)

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 <br/>x-ms-request-id: string<br/>Location: string|
## DEMO | Refresh Dataset In Group
いくつかの Refresh type と Cancel とか

### Define
```powershell
# Dataset in workspace
$WorkspaceName = "DEMO2"
$DatasetName = "RefreshDemo"
```
### Login Power BI
- [Connect-PowerBIServiceAccount (MicrosoftPowerBIMgmt.Profile) | Microsoft Docs](https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.profile/connect-powerbiserviceaccount)
```powershell
Login-PowerBI | Out-Null
```
### Get Power BI workspace | Get Power BI dataset
- [Get-PowerBIWorkspace (MicrosoftPowerBIMgmt.Workspaces) | Microsoft Docs](https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.workspaces/get-powerbiworkspace)
- [Get-PowerBIDataset (MicrosoftPowerBIMgmt.Data) | Microsoft Docs](https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.data/get-powerbidataset)

```powershell
$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
- [Enhanced refresh with the Power BI REST API is now generally available | Microsoft Power BI Blog | Microsoft Power BI](https://powerbi.microsoft.com/en-us/blog/enhanced-refresh-with-the-power-bi-rest-api-is-now-generally-available/)
- [Enhanced refresh with Power BI REST API - Power BI | Microsoft Docs](https://docs.microsoft.com/en-us/power-bi/connect-data/asynchronous-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`.<br/>`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`.<br/><br/>Supported behavior:<br/>If `commitMode` = `transactional`,<br/>then `applyRefreshPolicy` = `true` or `false`.<br/>If `commitMode` = `partialBatch`,<br/>then `applyRefreshPolicy` = `false`.<br/><br/>Unsupported behavior:<br/>If `commitMode` = `partialBatch`,<br/>then `applyRefreshPolicy` = `true`.|
|`effectiveDate`|Date|Current date|If an incremental refresh policy is applied, the `effectiveDate` parameter overrides the current date.|


```powershell
$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
```
```powershell
$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
```
```powershell
$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
```
```powershell
$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
```
```powershell
$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
```powershell
$RefreshStatus = 
    Invoke-PowerBIRestMethod -Method Get -Url "$($ResponseHeader.Location)" |
    ConvertFrom-Json

$RefreshStatus|Format-Table
$RefreshStatus.objects
```
##### Cancel refresh
```powershell
Invoke-PowerBIRestMethod -Method Delete -Url "$($ResponseHeader.Location)"
```
### Logout Power BI
- [Disconnect-PowerBIServiceAccount (MicrosoftPowerBIMgmt.Profile) | Microsoft Docs](https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.profile/disconnect-powerbiserviceaccount)
```powershell
Logout-PowerBI
```
# 思ったこと🙄
いちど試せばわかる。
# その他
https://powerbi.connpass.com/

- [Power BI 勉強会 - connpass](https://powerbi.connpass.com/)
- [Power BI Desktop を使い始めるには](http://qiita.com/PowerBIxyz/items/6c4735cb5b5712e70981)
- [Excel で Power Query を使い始めるには](http://qiita.com/PowerBIxyz/items/52e563037b855a099ca9)