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

#はじめに
この記事では、Power BI で社内のリース車の利用状況を可視化する取り組みを3部構成で紹介しています。

#実装方法
本記事では、以下の手順の 3. について紹介します。

1. 1. をご覧になりたい方は、以下の手順名をクリックしてください。

2. Microsoft Graph を利用してリース車の予定表データを取得する

3. 取得したデータを展開する

4. 可視化する

##3. 可視化する
最後に、レポート作成画面でデータの可視化を行います。
今は、こんな画面になっているかと思います。

画面右側のクエリには、取得した予定表データの以下の列が表示されています。

列名	中身
calendar.owner.name	リース車名
end.dateTime	予約の終了日時
organizer.emailAddress.name	予約者名
start.dateTime	予約の開始日時

さて、これらを基に早速グラフを挿入しよう！となるわけですが、その前に今回グラフを作成することで何を達成したいのかを考えてみます（今更）。

###可視化の目的と必要な列の検討
最大の目的は「リース車の削減」ですので、そのためにまずは稼働率の低いリース車がないかを確認したいですよね。
稼働率と言っても、年単位で算出して数値が低くても、日別で見たらどこかにピークがあるかもしれません。
また、同じ稼働率でも、1回の利用時間が長くて回数が少ないものもあれば、1回の利用時間は短いけど回数が多いものもあるかもしれない。
その傾向を把握して予約を適正配分できれば、もっとリース車を削減できるかもしれない。
なーんてことを色々と考えた結果、何となく以下のようなグラフを作ってみることにしました。
（言語化って難しい…）

• 車別の利用時間の割合
• 日別の利用回数
• 1回の利用時間の平均と利用回数の傾向
• 稼働率

ということで、上記のグラフを作成するために、上記の列情報をもとに別途以下の列を用意します。

列名	型
利用時間	数値
稼働率	数値
開始日	日付
リース車名	テキスト
※リース車名は既に取得できているのですが、ナンバーや余計な文字が含まれているので取り除くために定義します。

###利用時間列（単位：h）を用意
start.dateTime と end.dateTime の差分を取ることで、1回の予約で利用した時間を算出します。
日付の差分は datediff 関数で取得できるようなので、早速追加してみます。
一旦画面左側の表マークをクリックしてデータ表示画面にし、画面右側の"クエリ"あたりを右クリックし、"新しい列"をクリックします。

画面上部に表示される数式バー（？）に、以下を入力します。

利用時間 = datediff([start.dateTime],[end.dateTime], MINUTE)/60

…あれれ？どうやら start.dateTime と end.dateTime の表記は日付型として認識してくれないようです。

start.dateTime と end.dateTime は以下のようなフォーマットで記載されています。

yyyy-mm-ddThh:mm:ss.00000000

これを、日付型として扱える以下のようなフォーマットに変換して、datediff 関数に入れてみました。

yyyy/mm/dd hh:mm:ss

利用時間 = datediff(datevalue(Left([start.dateTime], 10))&" "&timevalue(MID([start.dateTime], 12, 5)), datevalue(Left([end.dateTime], 10))&" "&timevalue(MID([end.dateTime], 12, 5)), MINUTE)/60

取れました。
※datediff 関数の単位をあえて MINUTE にしてから 60 で割って単位を時間にしている理由は、単位を HOUR で抽出してしまうと 1 時間未満の予約が 0 時間で抽出されてしまうためです。

###稼働率（単位：%）列を用意
先ほど算出した利用時間を使って、日別の稼働率を図化するための列を用意します。
今回は、まずは車別ではなく全体の稼働率がオーバーしていないかを確認するため、新しい列を用意し以下を入力します。

稼働率 = [利用時間]*100/(8*24)

取れました。

何をやっているかというと、とある日にすべてのリース車が8時間稼働している状態を100%として、その割合を計算しているだけです。
イメージできないかと思いますので、グラフ作成にて（結論から言うと結局うまくいかないのですが。汗）。

###開始日列を用意
これまで算出した数値を"日別"で可視化するには、軸の情報として日付型（日時ではなく）のデータが必要となるようです。
ということで、新しい列を用意し以下を入力します。

開始日 = datevalue(Left([start.dateTime], 10))

取れました。

やっていることは、start.dateTime 列から日付部分のみを抽出しているだけです。

###リース車名列を用意（任意）
弊社のリース車名には余計な定型句や通し番号、ナンバーが含まれるので、これを除去します。
ということで、新しい列を用意し以下を入力します（ただの文字列操作なので解説は割愛）。

車名 = LEFT(MID([calendar.owner.name], FIND("_", [calendar.owner.name], 5)+1, LEN([calendar.owner.name])-FIND("_", [calendar.owner.name], 5)), FIND("（", MID([calendar.owner.name], FIND("_", [calendar.owner.name], 5)+1, LEN([calendar.owner.name])-FIND("_", [calendar.owner.name], 5)),2)-1)

取れました。

これで、必要な列の用意は終了です。

###グラフ挿入
画面左側のレポートアイコン（グラフの形）をクリックしてレポート画面に切り替え、いよいよグラフを追加していきます。

ここでは、以下の4つのグラフを挿入してみます。

• 車別の利用時間の割合
• 日別の利用回数
• 1回の利用時間の平均と利用回数の傾向
• 稼働率

####車別の利用時間の割合
単純に、どの車が全体の何%利用されているかを図化します。
このような割合グラフは円グラフやドーナツグラフが多い気がするので、今回はドーナツグラフにしてみます（適当）。
画面右側にある"視覚化"から"ドーナツ グラフ"を選択してグラフを挿入。
"凡例"に車名を、"値"に利用時間をそれぞれドラッグ＆ドロップします。

おー。それっぽい。
"値"は、合計なのか平均なのかそれ以外なのかを選択できます。今回は合計利用時間を確認したいので、利用時間の右側の"∨"をクリックして、合計になっていることを確認します（デフォルトは合計っぽい）。

この図から、単純にどの車がどの程度利用されているのかが、利用されている順に把握できるため、最も稼働していない車がどれかがすぐに分かります。

####日別の利用回数
これは、標準のグラフの種類でピンとくるのがなかったので、"カスタム ビジュアル"を利用してみました。
カスタム ビジュアルの説明（英語）
"視覚化"のグラフアイコンの一番下にある"…"をクリックし、"Marketplace からインポートする"をクリックします。

今回は、Calendar Visual というものを使ってみます。検索窓に"Calendar Visual"と入力して、該当のビジュアルの"追加"をクリックします。

追加されました。

追加されたアイコンをクリックしてグラフを挿入。
"Category"に開始日を、"Y"に利用時間をそれぞれドラッグ＆ドロップし、利用時間の右側の"∨"をクリックして、"カウント"に切り替えます。

おー。それっぽい。
この図から、どの時期/曜日に車が多く利用されているのかが把握できます。
また、他のグラフと一緒に利用することで、車毎の利用実態も把握できます（後述）。

…ってあれ？このビジュアルは間もなくサポートされなくなるかもです。
使用される場合はご注意ください。

####1回の利用時間の平均と利用回数の傾向
1回の利用時間が長くて回数が少ないものもあれば、1回の利用時間は短いけど回数が多いものもあるかもしれないため、まずは1回の利用時間と回数の関係を把握してみます。
画面右側にある"視覚化"から"散布図"を選択してグラフを挿入。
"凡例"に車名を、"X 軸"と"Y 軸"と"サイズ"に利用時間をドラッグ＆ドロップします。
"X 軸"の利用時間を"平均"に、"Y 軸"の利用時間を"カウント"に切り替えます（"サイズ"の利用時間は"合計"のままでOK）。

おー。それっぽい。
この図から、仮説通り、1回の利用時間が長く利用回数が少ない車と、1回の利用時間が短く利用回数が多い車があることが分かります。
短時間利用車と長時間利用車の予約が混在すると、予約間隔が無駄に空いたり長時間の予約が取りにくくなる可能性があるため、例えば短時間利用専用車と長時間利用専用車を分けるなどすれば、今以上に集約できるかもしれません。

####稼働率
そもそも全体的に稼働率がオーバーしていないかを確認します。
稼働率 100% の定義が難しいので、まずは1日に24台の車すべてが8時間利用される状況を100%と定義し、グラフ化してみました。
画面右側にある"視覚化"から"折れ線グラフ"を選択してグラフを挿入。
"軸"に開始日を、"値"に稼働率をドラッグ＆ドロップします。
"軸"の開始日は、右側の"∨"をクリックして、"日付の階層"ではなく"開始日"に切り替えます。

うあーなんだこれ。。
1日8時間稼働を 100% とすると、MAX で 300% のはずなのに、300% を超えている。
この原因と考えられる対策方法については、後述する"今後の課題"で説明します。

#ひとまず完成
これまで作成したグラフをそれっぽく並べて、それっぽい背景を置いて、表示をそれっぽく整えて、とりあえず範囲を過去1年間に絞ってみたら、こうなりました。
リース車を選択すると、そのリース車がいつ利用されていたかが右上のカレンダーで分かるようになっています。
これで何か有益な情報が得られるわけではありませんが、見ていてちょっと楽しいですね。

#今後の課題
この段階ではまだ課題が多く、完成形には至っていません。
細かい点は置いておいて、大きな課題について記載してみました。

• 日をまたぐ予約データを日別に分解する
  予約データは、1つの予約を1レコードとして、予約の開始時間と終了時間が記録されています。つまり、例えば日をまたいで44時間で予約したデータは"開始日に44時間稼働した"と見なされてしまい、日別の稼働率が正常に計算できません（稼働率のグラフが 300% を超えていたのはこれが原因）。
  このような場合は日別にレコードを分けるなどの工夫が必要になりそうです（これはこれで利用回数が取れなくなりますが、そもそも利用回数が必要かどうかは議論の余地あり）。
  

• 適切な"稼働率"の計算方法を考える
  営業利用であれば1日を8時間として計算すればよいのですが、泊まりがけの予約に関しては24時間の方が適切かもしれません。泊まり用途は泊り専用車にするなどして分析方法を分けないと、適切な稼働率は計算できない気がしました。

• 利用実績をデータ化する仕組みを考える
  今回分析したのはあくまで"予約"データであり、現状データ化していない"利用実績"とは乖離がある可能性があります。
  正確な分析を行うためにはまず"利用実績"のデータ化に取り組む必要がありそうです。

• 利用者を軸にデータを可視化してみる
  利用者情報も取得したものの、今回は可視化までは到達できませんでした。
  極端に利用率が高い人は別の対策を講じるなど、利用者を軸に可視化することで何か別の対策が見えてくるかもしれません。

#まとめ
せっかく Power BI を使い始めたので、誰かのお役に立てればと思い記事にしてみました。
まだまだ分からないことが多く、手順や認識が間違っている可能性がありますので、あくまでご参考程度に留めていただき、ご指摘やアドバイス等ありましたら是非是非いただきたいです！

#参考文献（一部）
他にも沢山見たのですが覚えているものだけ。。

・Power BI Desktop 空のクエリを使った Azure AD 認証の活用 - Qiita
・Microsoft Graph を使ってみよう : Azure AD v1 エンドポイントのアプリケーション認証を ADAL で行う - Qiita
・Microsoft Graphってなに？ - Speaker Deck
・Office365 Graph で2種類のアクセス許可の仕様にハマった話 #o365jp - Speaker Deck
・user: findRooms - Microsoft Graph beta | Microsoft Docs