SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

スクリプトによる Power BI のプッシュデータセットの作成

leave a comment

Power BI のストリーミングデータセットについては、ポータルから簡単に作成することができます。
Power BI のリアルタイム ストリーミング
投稿作成時点では、ポータルから作成した、ストリーミングデータセットについては、Power BI デスクトップの「Power BI サービスからのデータの取得」では使用することができないため、PowerShell で Power BI のプッシュデータセットを作成してみました。
プッシュデータセットであれば、複数のテーブルをデータセットに含めることもできますので、ストリーミングデータセットより高度な内容ができるかと。
 
基本的な流れと REST API については以下の情報を参考にするとよいかと思います。


今回は、Power BI Free プランのユーザーを使用しますので、ユーザーについては最初に用意をしておきます。

■Azure AD にアプリケーションを登録

最初に、Azure AD にアプリケーションの登録が必要となります。
登録に関してはポータルから実施してしまうのがよいかと。
Register an Application for Power BI
今回は以下のような設定でアプリケーションを Azure AD に登録しています。
(Redirect URL には「https://login.live.com/oauth20_desktop.srf」を設定しています)
image
「Register App」をクリックすると「アプリケーション ID (クライアント ID)」が表示されますので、ID を控えておきます。
シークレット (キー) ではないので、アプリケーション ID については、Azure ポータルから再度確認可能です。
image
今回は、対話型のアクセス許可の要求をスキップしたいため、登録したアプリケーションの「必要なアクセス許可」から「アクセス許可の付与」をクリックして、裏でアクセス許可を設定しておきます。
image
 

■データセットの作成

Azure AD の設定が終わったら次にデータセットの作成を行います。
データセットの作成
今回は以下のようなスクリプトでデータセットを作成しています。
(スクリプトでなく Apiary での Power BI REST API で REST をたたいてもよいかと)
このデータセットについては、Live Monitor のデータ連携用として使用する想定です。
user / password はデータセットを作成するユーザーの情報、clientID は AAD に登録したアプリケーションのアプリケーション ID、tenantid は は AAD のテナント ID を指定します。

Param(
    [Parameter(Mandatory)]
    [string]$user,
    [Parameter(Mandatory)]
    [string]$password,
    [Parameter(Mandatory)]
    [string]$clientID,
    [Parameter(Mandatory)]
    [string]$tenantid
)
# 指定したユーザーにデータセットの作成
$redirectUri = "https://login.live.com/oauth20_desktop.srf"
$resourceUri = "https://analysis.windows.net/powerbi/api"
$authorityUri = "https://login.windows.net/common/oauth2/authorize"
$powerBIApiUrl = "https://api.powerbi.com/v1.0/myorg"
$tokenendpoint = "https://login.windows.net/{0}/oauth2/token" -f $tennantid
$Body = @{
        "resource"= $resourceUri
        "client_id" = $clientID
        "grant_type" = "password"
        "username" = $user
        "password" = $password
}
$params = @{
    ContentType = "application/x-www-form-urlencoded"
    Headers = @{"accept"="application/json"}
    Body = $Body
    Method = "Post"
    URI = $TokenEndpoint
}
$token = Invoke-RestMethod @params
########################################################
# データセットの作成
########################################################
$json = @"
{"name": "Live Monitor Dataset","tables":
    [
        {"name": "LiveMonitor", "columns":
            [
                {"name" : "Counter Date", "dataType": "DateTime"},
                {"name" : "Server Name", "dataType": "string"},
                {"name" : "CPU Usage %", "dataType": "Double"},
                {"name" : "Buffer Cache Hit %", "dataType": "Double"},
                {"name" : "Plan Cache Hit %", "dataType": "Double"},
                {"name" : "Page life expectancy", "dataType": "Double"},
                {"name" : "Database Cache Memory (MB)", "dataType": "Double"},
                {"name" : "Plan Chache Memory (MB)", "dataType": "Double"},
                {"name" : "Free Memory (MB)", "dataType": "Double"},
                {"name" : "Total Server Memory (MB)", "dataType": "Double"},
                {"name" : "Target Server Memory (MB)", "dataType": "Double"},
                {"name" : "Granted Workspace Memory (MB)", "dataType": "Double"},
                {"name" : "Memory Grants Outstanding", "dataType": "Double"},
                {"name" : "Memory Grants Pending", "dataType": "Double"},
                {"name" : "Batch Requests/sec", "dataType": "Double"},
                {"name" : "Page lookups (MB)/sec", "dataType": "Double"},
                {"name" : "Readahead pages (MB)/sec", "dataType": "Double"},
                {"name" : "Page reads (MB)/sec", "dataType": "Double"},
                {"name" : "Page writes (MB)/sec", "dataType": "Double"},
                {"name" : "Checkpoint pages (MS)/sec", "dataType": "Double"},
                {"name" : "Background writer pages (MB)/sec", "dataType": "Double"},
                {"name" : "Log Flushes/sec", "dataType": "Double"},
                {"name" : "Log MBytes Flushed/sec", "dataType": "Double"},
                {"name" : "Log Flush Waits/sec", "dataType": "Double"},
                {"name" : "Log Flush Wait Time", "dataType": "Double"},
                {"name" : "SQL Compilations/sec", "dataType": "Double"},
                {"name" : "SQL Re-Compilations/sec", "dataType": "Double"}
            ]
        }
    ]
}
"@
$bytes = [System.Text.Encoding]::UTF8.GetBytes($json)
$uri = [System.Uri]::EscapeUriString(("{0}/datasets" -f  $powerBIApiUrl))
$ret = Invoke-WebRequest -Method Post -ContentType "application/json" -Headers @{Authorization=("Bearer {0}" -f $token.access_token)} -Uri $uri -Body $bytes
return ($ret.Content | ConvertFrom-Json)

 
スクリプトを実行するとデータセットの ID が取得できます。
データセットの ID については、データセットにデータを登録する際に必要となりますので控えておきます。
データセットの ID を取得するための REST もありますが、ポータルの URL からも ID は確認可能です。
image
 

■データセットにデータをプッシュ

データセットの作成が終わったらデータをプッシュします。
データのプッシュについては以下のスクリプトを使用しています。
Live Monitor で取得した内容をプッシュするスクリプトとなっています。

Param(
    [Parameter(Mandatory)]
    [string]$user,
    [Parameter(Mandatory)]
    [string]$password,
    [Parameter(Mandatory)]
    [string]$clientID,
    [Parameter(Mandatory)]
    [string]$tenantid,
    [Parameter(Mandatory)]
    [string]$datasetid,
    [Parameter(Mandatory)]
    [string]$ConnectionString
)
$redirectUri = "https://login.live.com/oauth20_desktop.srf"
$resourceUri = "https://analysis.windows.net/powerbi/api"
$authorityUri = "https://login.windows.net/common/oauth2/authorize"
$powerBIApiUrl = "https://api.powerbi.com/v1.0/myorg"
$tokenendpoint = "https://login.windows.net/{0}/oauth2/token" -f $tennantid
$con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = $ConnectionString
$con.Open()
$cmd = $con.CreateCommand()
$cmd.CommandText = "usp_LiveMonitor"
$da = New-Object System.Data.SqlClient.SqlDataAdapter
$da.SelectCommand = $cmd
$ds = New-Object System.Data.DataSet
$count = $da.Fill($ds)
$tmp = $ds.Tables[0] | select $ds.Tables[0].Columns.columnname | ConvertTo-Json | ConvertFrom-Json
$datetime = (Get-Date ([System.TimeZoneInfo]::ConvertTimeFromUtc((Get-Date).ToUniversalTime(), ([System.TimeZoneInfo]::FindSystemTimeZoneById("Tokyo Standard Time")))) -format s)
$pushdata = @"
{"rows":
    [
        {
            "Counter Date":"$($datetime)",
            "Server Name":"$($tmp.'Server Name')",
            "CPU Usage %":$($tmp.'CPU Usage %'),
            "Buffer Cache Hit %":$($tmp.'Buffer Cache Hit %'),
            "Plan Cache Hit %":$($tmp.'Plan Cache Hit %'),
            "Page life expectancy":$($tmp.'Page life expectancy'),
            "Database Cache Memory (MB)":$($tmp.'Database Cache Memory (MB)'),
            "Plan Chache Memory (MB)":$($tmp.'Plan Chache Memory (MB)'),
            "Free Memory (MB)":$($tmp.'Free Memory (MB)'),
            "Total Server Memory (MB)":$($tmp.'Total Server Memory (MB)'),
            "Target Server Memory (MB)":$($tmp.'Target Server Memory (MB)'),
            "Granted Workspace Memory (MB)":$($tmp.'Granted Workspace Memory (MB)'),
            "Memory Grants Outstanding":$($tmp.'Memory Grants Outstanding'),
            "Memory Grants Pending":$($tmp.'Memory Grants Pending'),
            "Batch Requests/sec":$($tmp.'Batch Requests/sec'),
            "Page lookups (MB)/sec":$($tmp.'Page lookups (MB)/sec'),
            "Readahead pages (MB)/sec":$($tmp.'Readahead pages (MB)/sec'),
            "Page reads (MB)/sec":$($tmp.'Page reads (MB)/sec'),
            "Page writes (MB)/sec":$($tmp.'Page writes (MB)/sec'),
            "Checkpoint pages (MS)/sec":$($tmp.'Checkpoint pages (MS)/sec'),
            "Background writer pages (MB)/sec":$($tmp.'Background writer pages (MB)/sec'),
            "Log Flushes/sec":$($tmp.'Log Flushes/sec'),
            "Log MBytes Flushed/sec":$($tmp.'Log MBytes Flushed/sec'),
            "Log Flush Waits/sec":$($tmp.'Log Flush Waits/sec'),
            "Log Flush Wait Time":$($tmp.'Log Flush Wait Time'),
            "SQL Compilations/sec":$($tmp.'SQL Compilations/sec'),
            "SQL Re-Compilations/sec":$($tmp.'SQL Re-Compilations/sec')
        }
    ]
}
"@
$bytes = [System.Text.Encoding]::UTF8.GetBytes($pushdata)
$ds.Dispose()
$con.Close()
$con.Dispose()
$Body = @{
        "resource"= $resourceUri
        "client_id" = $clientID
        "grant_type" = "password"
        "username" = $user
        "password" = $password
}
$params = @{
    ContentType = "application/x-www-form-urlencoded"
    Headers = @{"accept"="application/json"}
    Body = $Body
    Method = "Post"
    URI = $TokenEndpoint
}
$token = Invoke-RestMethod @params
$uri = [System.Uri]::EscapeUriString(("{0}/datasets/{1}/tables/LiveMonitor/rows" -f  $powerBIApiUrl, $datasetid))
$ret = Invoke-WebRequest -Method Post -ContentType "application/json" -Headers @{Authorization=("Bearer {0}" -f $token.access_token)} -Uri $uri -Body $bytes

 
これでスクリプトによるデータセットへのデータの登録を行うことができます。
今回はマイワークスペース内のデータセットに対してデータの登録を行っていますが、Power BI ではワークスペース (アプリのワークスペース / グループ) に対してもデータセットを登録することが可能です。
ただし、Power BI Free については、アプリのワークスペースが作成できないため、ワークスペースのデータセットを登録するためには、Power BI Pro のライセンスがユーザーで REST をたたく必要があります。
ワークスペースを使用すする場合、URL が変わりますが基本的な方法は同じですね。

Share

Written by Masayuki.Ozawa

6月 3rd, 2017 at 5:47 pm

Posted in Power BI,PowerShell

Tagged with ,

«
»

Leave a Reply