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

 

■データセットの作成


  <p>Azure AD の設定が終わったら次にデータセットの作成を行います。   <br /><a href="https://msdn.microsoft.com/ja-jp/library/mt203562.aspx" target="_blank">データセットの作成</a></p>  <p>今回は以下のようなスクリプトでデータセットを作成しています。   <br />(スクリプトでなく <a href="http://docs.powerbi.apiary.io/">Apiary での Power BI REST API</a> で REST をたたいてもよいかと)</p>  <p>このデータセットについては、<a href="https://github.com/MasayukiOzawa/SQLServer-Util/tree/master/Live%20Monitor" target="_blank">Live Monitor</a> のデータ連携用として使用する想定です。</p>  <p>user / password はデータセットを作成するユーザーの情報、clientID は AAD に登録したアプリケーションのアプリケーション ID、tenantid は は AAD のテナント ID を指定します。</p>  <div id="scid:C89E2BDB-ADD3-4f7a-9810-1B7EACF446C1:97da4dfd-59e4-4f57-8c69-630ae8287107" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=white-space:normal>
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 が変わりますが基本的な方法は同じですね。

Written by masayuki.ozawa

6月 3rd, 2017 at 5:47 pm

Posted in Power BI,PowerShell

Tagged with ,

Leave a Reply

*