More than 3 years have passed since last update.

[その1] Power BI で社内のリース車の利用状況を可視化してみた

Last updated at 2020-12-07Posted at 2019-11-16

(2020/12/07)
少なくとも私の環境では現在、本手順の Graph API を用いてデータを取得するクエリの「OData.Feed 関数」でエラーが出力され取得できない状態となっております。

エラーの内容と経緯は以下の Twitter のスレッドをご参照ください。
https://twitter.com/KodamaJn/status/1332285491471740929

同じ事象に見舞われた方は、代わりに Web.Contents 関数を利用するとデータを取得できますのでお試しください。ただし関数名を変えるだけでは不可なので、Json.Document 関数等も併用した取得したデータの解析・整形が別途必要となります。

#はじめに
私の会社では、取材活動や営業活動等で利用するためのリース車24台（2019/11/15 現在）が Office 365 のリソースとして登録されており、ユーザが Outlook の予定表から予約した上で利用しています。

これまでは各々の予定表を横断的に確認して利用状況を確認していたのですが、きちんと可視化して分析すれば、稼働率の低いリース車が見つかったりして台数減らせるんじゃね？と思い、~~成功した暁には削減経費の一部を自分の給与に上乗せしてくれるんじゃないかという無駄な期待を胸に~~ 勉強も兼ねて可視化してみました。

ということで、試行錯誤して作ったものですので、色々と間違いや煩わしい方法などあるかもしれません。「この手順でやるべきだ」というよりは「とりあえずこの方法でそれっぽくなるのね。へぇ～」という視点でご覧いただき、ご指摘やアドバイス等ありましたら是非是非お寄せいただきたいです！

#今回のゴール
最終的に、以下のようなレポートを作成します。

会社のリース車の稼働状況を #PowerBI で可視化してみた。
残念ながら"予約データ"が元なのと、日をまたいでいる予約データを正しく処理できていないため、よく見ると色々おかしいのは一旦目をつぶるとして。。
ボタン等を利用して分析期間を動的に変更する仕組みはこれからお勉強。

なかなか面白い😆 https://t.co/MTZ22KgusK pic.twitter.com/E03tC3gHYH
— Jun’ichi Kodama (@KodamaJn) November 8, 2019

#実装方法
Office 365 に保存されているリース車の予定表情報を Microsoft Graph を利用して取得し、Power BI Desktop で可視化します。
大まかな手順は以下の通りです。

Microsoft Graph を利用してリース車の予定表データを取得する
取得したデータを展開する
可視化する

本記事では、上記の手順のうち 1. について紹介します。

##1. Microsoft Graph を利用してリース車の予定表データを取得する
私が知る限り、2019/11/15 時点では Microsoft Graph からデータを取得するための専用コネクタがありませんでしたので、@kenakamu さんが紹介されている方法を用いて、自力で認証を通し、データを取得します。
Power BI Desktop 空のクエリを使った Azure AD 認証の活用

###Azure AD にアプリケーションを登録
まずは、Azure AD に Microsoft Graph が使えるアプリケーションを登録します。
この手順は上記の記事に記載されているのですが、最近画面表示が刷新されたようですので、改めて一からこちらで手順をまとめてみました。

Azure ポータルにアクセスして、ログインします。画面上部の検索窓に Azure と入力し、Azure Active Directory を選択します。
画面左側にある"アプリの登録"を選択し、"＋新規登録"を選択します。
"名前"に任意のアプリケーション名を入力し、"登録"を選択します。
"サポートされているアカウントの種類"と"リダイレクト URI"はそのままでOKです。
画面左側の"概要"を選択し、"アプリケーション ID"をコピーしてメモ帳などに貼り付けておきます。
画面左側にある"API のアクセス許可"を選択し、"アクセス許可の追加"を選択します。API の一覧の中から"Microsoft Graph"を選択します。
"アプリケーションに必要なアクセス許可の種類"で"アプリケーションの許可"を選択し、"Calendars"の中にある"Calendars.Read (Read calendars in all mailboxes)"にチェックを入れ、"アクセス許可の追加"を選択します。
なお、この"アクセス許可の種類"については、杉本さんのこちらの記事で詳しく解説されていますので、是非ご覧ください。
画面下部の"●●●（テナント名）に管理者の同意を与えます"を選択し、画面上部に表示されるポップアップで"はい"を選択します。
"要求されたアクセス許可の管理者の同意が正常に付与されました"と表示されたら、画面左側にある"証明書とシークレット"を選択します。
"新しいクライアントシークレット"を選択し、"説明"と"有効期限"を設定して"追加"を選択します。
クライアントシークレットが表示されますので、クリップボードにコピーしてメモ帳などに貼り付けておきます。
（一度でもこの画面から別画面に遷移すると、追加したクライアントシークレットが表示されなくなるため、ご注意ください）

###Power BI Desktop から Microsoft Graph に接続してデータを取得
先ほど取得したアプリケーション ID とクライアントシークレットを用いて Power BI Desktop から Microsoft Graph に接続し、リース車の予定表データを取得します。

こちらの手順はおおよそ先ほどの @kenakamu さんの記事と同じなので、まずは先ほどの記事内の"Power BI Desktop で接続を作成"の 1～6. まで実施してください。
→ Power BI Desktop で接続を作成

続いて先ほどの記事の 7. で、先ほどの記事のクエリの代わりに以下のクエリを追加します。
ここで使用する Graph API は、予定表のイベント情報を取得するものです。メールアドレスの箇所には、取得したいリース車の実際のメールアドレスを記載します。
[イベントを一覧表示する]

let
    query01 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車01のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query02 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車02のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query03 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車03のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query04 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車04のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query05 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車05のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query06 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車06のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query07 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車07のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query08 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車08のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query09 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車09のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query10 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車10のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query11 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車11のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query12 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車12のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query13 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車13のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query14 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車14のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query15 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車15のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query16 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車16のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query17 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車17のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query18 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車18のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query19 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車19のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query20 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車20のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query21 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車21のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query22 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車22のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query23 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車23のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    query24 = OData.Feed("https://graph.microsoft.com/v1.0/users/リース車24のメールアドレス/events?$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'&?$select=calendar,start,end,organizer", [Authorization = "Bearer " & AzureAccessToken]),
    queries = Table.Combine({query01, query02, query03, query04, query05, query06, query07, query08, query09, query10, query11, query12, query13, query14, query15, query16, query17, query18, query19, query20, query21, query22, query23, query24})
in
    queries

やっていることは、リース車の予定表に登録されているすべてのイベント情報を1台ずつ取得し、最後の行で1つのテーブルに結合しているだけです。
なお、余計なデータ取得を避けるため、

① 今日から過去1年分のデータのみを Filter して取得
　$filter=start/dateTime ge '" & Text.From(Date.AddYears(DateTime.FixedLocalNow(), -1)) & "'
　※ DateTime.FixedLocalNow() で現在時刻が取れるので、Date.AddYears() で -1 して 1 年前
② 必要な項目（calendar 列, start 列, end 列, organizer 列）のみを予め Select して取得
　$select=calendar,start,end,organizer

としています。

※本当は、[こちら] の API を使用してリソースの一覧を取得し、それを基にイベント情報を取得しようと思ったのですが、この API が beta 版だったため、今回は必要なリソースのアドレスを手入力することで対応しました。

クエリの記載が終わったら、再び先ほどの @kenakamu さんの記事内の"Power BI Desktop で接続を作成"の 8. 以降を実施してください。
→ Power BI Desktop で接続を作成

正常に進めば、こんなデータが取得できます。

クエリで取得列を指定した割には大量の列が取得されているように見えますが、クエリで指定した列以外の列は中身がnullとなっています。

これで、リース車の予定表データの取得作業は終了です。

次の記事（その2）では、取得したデータを展開する作業を行います。
その2はこちら

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