Power BI service の XMLA Endpoint Read / Write をもうちょっとだけ試す

TMSL を出力できるから SSMS 使うのもよいのだけど、PowerShell でもいいかなと。

# PowerShell Gallery から
- [PowerShell Gallery | SqlServer](https://www.powershellgallery.com/packages/SqlServer/)

管理だったり開発だったりとかの場面で使えるのです。
>This module allows SQL Server developers, administrators and business intelligence professionals to automate database development and server administration, as well as both multidimensional and tabular cube processing.

# コマンドレットで操作

## SQL Server PowerShell
- [SQL Server PowerShell](https://docs.microsoft.com/en-us/sql/powershell/sql-server-powershell)

### Invoke-ASCmd
- [Invoke-ASCmd (sqlserver) | Microsoft Docs](https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-ascmd)

```json:CreateDataset.xmla
{
    "create": {
      "database": {
        "name": "Dataset1",
        "compatibilityLevel": 1520,
        "model": {
          "culture": "ja-JP",
          "tables": [
            {
              "name": "Table1",
              "columns": [
                {
                  "name": "Column1",
                  "dataType": "string",
                  "sourceColumn": "Column1"
                }
              ],
              "partitions": [
                {
                  "name": "Partition_Table1",
                  "source": {
                    "type": "m",
                    "expression": [
                      "let",
                      "    Source = #table(",
                      "        type table [Column1 = text],",
                      "        {",
                      "            {\"りんご\"}, {\"ごりら\"}, {\"らっぱ\"}",
                      "        }",
                      "    )",
                      "in",
                      "    Source"
                    ]
                  }
                }
              ]
            }
          ]
        }
      }
    }
  }
```

```powershell:PowerShell(SqlServer)
$PowerBI_WorkspaceConnection = "<WorkspaceConnection>"
$XMLAFile_TMSL = "<FileName>"

Invoke-ASCmd -Server $PowerBI_WorkspaceConnection -InputFile $XMLAFile_TMSL
```
Error要素がなければ成功

```xml:Output
<return xmlns="urn:schemas-microsoft-com:xml-analysis">
    <root xmlns="urn:schemas-microsoft-com:xml-analysis:empty"></root>
</return>
```

### Invoke-ProcessASDatabase
- [Invoke-ProcessASDatabase (sqlserver) | Microsoft Docs](https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-processasdatabase)
 
```powershell:ProcessDatabase.ps1
$PowerBI_WorkspaceConnection = "<WorkspaceConnection>"
$PowerBI_DatasetName = "<DatasetName>"

$Return = Invoke-ProcessASDatabase -Server $PowerBI_WorkspaceConnection -DatabaseName $PowerBI_DatasetName -RefreshType Automatic
$Return.Impact
```

```text:Output
AddedObjects        : {}
AddedSubtreeRoots   : {}
RemovedObjects      : {}
RemovedSubtreeRoots : {}
PropertyChanges     : {State, ModifiedTime, RefreshedTime, State...}
IsEmpty             : False
```

### DMV
- [Dynamic Management Views (DMVs) in Analysis Services | Microsoft Docs](https://docs.microsoft.com/en-us/analysis-services/instances/use-dynamic-management-views-dmvs-to-monitor-analysis-services)

```powershell
$PowerBI_WorkspaceConnection = "<WorkspaceConnection>"
$PowerBI_DatasetName = "<DatasetName>"
$DMVQuery = 'select * from $SYSTEM.TMSCHEMA_TABLES'

[xml]$Return = 
Invoke-ASCmd -Server $PowerBI_WorkspaceConnection -Database $PowerBI_DatasetName -Query $DMVQuery
$Return.return.root.row | select ID, TableID, Name, State, RefreshedTime, ModifiedTime
```
```powershell
$PowerBI_WorkspaceConnection = "<WorkspaceConnection>"
$PowerBI_DatasetName = "<DatasetName>"
$DMVQuery = 'select * from $SYSTEM.TMSCHEMA_PARTITIONS'

[xml]$Return = Invoke-ASCmd -Server $PowerBI_WorkspaceConnection -Database $PowerBI_DatasetName -Query $DMVQuery
$Return.return.root.row | select ID, TableID, Name, State, RefreshedTime, ModifiedTime
```

##ちょっと別件

```powershell:ToPremiumCapacity.ps1
$PowerBI_WorkspaceName = "<WorkspaceName>"
$PremiumCapacityName = "<PremiumCapacityName>"

Connect-PowerBIServiceAccount

$PowerBI_Workspace = Get-PowerBIWorkspace -Name $PowerBI_WorkspaceName
$PremiumCapacity = Get-PowerBICapacity | ? { $_.DisplayName -eq $PremiumCapacityName }

Set-PowerBIWorkspace -Scope Organization -CapacityId $PremiumCapacity.Id -Id $PowerBI_Workspace.Id
```

```powershell:ToSharedCapacity.ps1
$PowerBI_WorkspaceName = "<WorkspaceName>"
$SharedCapacityId = [guid]"00000000-0000-0000-0000-000000000000"

Connect-PowerBIServiceAccount
$PowerBI_Workspace = Get-PowerBIWorkspace -Name $PowerBI_WorkspaceName
Set-PowerBIWorkspace -Scope Organization -CapacityId $SharedCapacityId -Id $PowerBI_Workspace.Id

```

# 思ったこと🙄
Tablar Editor とか DAX Studio とか便利な道具を使う方針です。

# その他
- [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)