ご覧の皆さん、はじめまして。
ITサービスマネジメント本部 石井と申します。
保守・運用を担当されている方、誰しものアタマを悩ます課題のひとつが、「視覚化」です。
KPI・KGI の設定、そのためのデータ収集、システム化・自動化の費用対効果算出、そして定例報告のためのグラフ化……。
あれ？ 私、システム作って運用してるんじゃなかったっけ？？
システム触ってる時間はどこに？？？
というのは「あるある」だと思います。
事務的な時間は、できるだけ短く抑えたいですよね。
えらく挑発的なタイトルをつけましたが、なにを隠そう私は筋金入りの「Excel 大好き芸人」です。
私のチームでも、対応分類や工数の分析に使っていました。
数年前にドヤ顔でダッシュボードを作ったものの、一時表と数式が膨大なため、すべての計算を終えるまでに端末の CPU 2コア を専有し、待つこと数分。笑
重い、重すぎる……。
というワケで今回は大好きな Excel を卒業し、クラウド上のリソースを使ってハンズフリーで更新でき、誰でもどこでも参照できるダッシュボードを作ろうと思います。
現在のフロー
なんだかんだで手数が多いのと、グラフの更新（＝再計算）中、負荷が高くて仕事ができないのが難点。
新しいフロー
AppFlow と QuickSight の定期実行の設定まで、ほぼすべて GUI だけで完結します。
Athena を入れた理由は後述しますが、データ変換が必要だったためです。
それでは次のステップで詳しくご紹介します。
- Amazon AppFlow
1-1. フロー設定を編集
1-2. データフィールドを編集
1-3. フィルターを編集
1-4. 確認して保存 - Amazon Athena
2-1. テーブルの作成
2-2. 抽出クエリーの作成 - Amazon QuickSight
3-1. データセットの作成
3-2. 分析
3-2.1) 計算フィールドを追加
3-2.2) ビジュアル 1 – 主要業務指標
3-2.3) ビジュアル 2 – 積み上げ棒コンボグラフ
3-2.4) ビジュアル 3 – ピボットテーブル
3-3. ダッシュボードの公開 - おわりに
1. Amazon AppFlow
2020年4月にリリースされた、特定の SaaS からめちゃくちゃカンタンにデータを抜き出せるサービスです。
ソースコード書いて、API あれこれして、トリガーと実行間隔を……。
なんて考える必要はありません。たった数ページの GUI 操作で完了します。
何度も言いますがめちゃくちゃカンタンです。
1-1. フロー設定を編集
まず AppFlow がターゲットの SaaS へアクセスするための接続（コネクター）を作成し、フローを作成します。
接続はサービスにより異なります。
私が試した範囲では Dynatrace など、SaaS で API Key を生成し AppFlow に登録するものもありますが、Salesforce の場合は左の画面で「続行」をクリックし、自身の ID の場合は右のダイアログで「許可」するだけで完了します。
作成した接続は他のフローでも利用することができます。
※接続の作成に用いたターゲット SaaS のユーザーや API Key が、フローがアクセスするテーブルを参照可能であることが前提です。（SaaS へデータを投入したい場合は、更新権限も必要です。）
※また当該の接続を他人が利用しても、作成時に用いた ユーザーID・API Key でデータの export/import が行われるので、接続やフロー、ターゲット SaaS のセキュリティー設計には注意してください。
データ取得元の SaaS を決め、接続を指定したら、SaaS の対象テーブルを選択します。
Salesforce の場合はほぼすべてのオブジェクト（＝テーブル）が選択可能です。
送信先は今回、 Amazon S3 を選択しています。
フローのトリガーまで GUI で自動化できるのは嬉しいところ。
画面では抽出テストのため一旦オンデマンドにしています。
スケジュールはかなり細かく指定ができ、2020年10月 には「前回からの差分」の抽出にも対応しました！
1-2. データフィールドを編集
AppFlow が自動的にターゲット SaaS のテーブルへアクセスし、データを抽出することのできるカラムの一覧を表示してくれます。
カラムにセンシティブなデータが含まれる場合には、ここで当該カラムをマスキング（**** 表記に変換）することもできます。
試しにサービス名や管理番号などの項目をマスクしてみました。
結果は 2-2 で登場します、お楽しみに。（ちゃんとリマインドするので忘れていて頂いて構いません。笑）
1-3. フィルターを編集
Salesforce には過去数年間分、数万件のレコードが格納されていますが、ダッシュボードとして視覚化したいのは 2 会計年度分。
よって日付が「昨年度」以降のデータのみを抽出するようフィルタリングします。
ここには相対日付が使えないので、毎回全件を抽出する場合には、不要な過去データを抽出し続けないよう、ときどきメンテナンスが必要ですね。
1-4. 確認して保存
AppFlow は以上でおしまい。
この簡単な数ステップだけで、Salesforce のレポートを「詳細のエクスポート」ボタンで抽出するのと同様、「フローを実行」ボタンのワンクリックで S3 へ csv が取り出せます。
早速実行。
めちゃくちゃカンタンでしょう？(圧)
スケジュール実行の場合は、「フローを実行」ボタンの場所に表示される「フローをアクティブ化」ボタンでステータスを「アクティブ」にしていないとトリガーされないので、注意してください。
なおデータにはすべて一意のファイル名がつきます。
自動での定期実行の際は、差分抽出にしたり、S3 でライフサイクルの設定をするなど、不要データのお掃除設計をお忘れなく。
2. Amazon Athena
はじめてトライしたとき、QuickSight でハマったのが、次の 3 点。
-
Salesforce と AWS QuickSight との間での「日付形式の違い」
Salesforce: YYYY-MM-DDTHH:MM:SS.fff+ffff
QuickSight: YYYY-MM-DD HH:MM:SS.fffffffff
うう、微妙に違う……。 -
すべての列の値を囲う二重引用符（ダブルクオーテーション）
Salesforce から抽出したデータは、デリミタのカンマに加え、各フィールドの値がすべて二重引用符で囲まれています。
これをそのまま取り込むと、全フィールドが String型 になってしまい、日付でのフィルタリングや値に応じた書式設定などが使えません。
二重引用符を除去する文字列関数の計算フィールドを全カラムと同じだけ作って、そのフィールドのデータ型を変更……というのはカラムが多いと意外に手間です。 -
数値型のカラムに null
これは私たちの Salesforce の使い方の問題ですが、数値カラムが未入力なことが多々あり、QuickSight で見るとここに「null」という文字列が入ってしまっていました。
当然、このフィールドのカラム型は String に。
（違うんだ、全部「なし」だから「ゼロ」なんだ……。）
というワケで、Amazon Athena でサクッとデータ変換することにしました。
2-1. テーブルの作成
Athena では S3 にある csv を SQL を使ってデータベース化します。
作成したテーブルは常に location 配下の全 csv を対象として機能するため、create table が必要なのは初回のみ。csv （＝AppFlow で抽出したデータ）を差し替えたり追加すれば select の結果も変わります。
create table の際、OpenCSVSerDe（SerDe は SERialize/Deserialize の略）というクラスを用いることで、二重引用符を取り除き、文字列だけを取り込むことができます。
create external table if not exists {TABLENAME} (
{COLUMN_NAME} {COLUMN_TYPE}
-- csv のカラム順の通りにカラムを定義
--※ OpenCSVSerDe を使うため string のみ指定可
)
row format SerDe 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties ("field.delim" = ",", "escapeChar"="\\", "quoteChar"="\"")
-- "quoteChar" で二重引用符を指定することにより、「データではないもの」として除去
location 's3://{S3_BUCKET_NAME}/{S3_FOLDER_NAME}/'
-- location に指定した S3 プレフィックス配下の全 csv がクエリー対象
tblproperties('skip.header.line.count'='1');
-- tblproperties で Salesforce の見出し行をスキップし、純粋なデータ行のみ取り込み
※コメントに書いた通り、OpenCSVSerDe を使って取り込んだデータはすべて string型 になる仕様ですが、これは後に QuickSight 取り込んだ際、データ型を変更できるので問題ありません。
2-2. 抽出クエリーの作成
QuickSight はデータソースに Athena を指定した場合、データソース作成者が初回に入力したクエリーを Athena に発行させることでデータをインポートし、以降も同様にして更新を行います。
select * from {SCHEMA}.{TABLE};
でも良いのですが、私には先述の通り
- 1)日付形式の違い
- 3)データなしの null を 0 に改めたい
という残課題があるため、ここで変換することにしました。
select
-- 略
date_parse ( DateAccepted, '%Y-%m-%dT%H:%i:%s.000+0000') as DateAccepted,
-- 略
case when Duration = 'null' then '0.0' else Duration end as Duration,
-- 略
from {SCHEMA}.{TABLE};
試しに limit 10 をつけて、Run Query で 10 行だけ select してみます。
date 関連のカラムが変換され、duration カラムに 0.0 が入りました！
また 1-2 で AppFlow のフィールド マッピングの際に指定した通り、一部のカラムが * だけで表記されています！
（フィールドのマスキングの件を覚えていてくださった方、ありがとうございました。笑）
3. Amazon QuickSight
QuickSight は端的に言うと BI ツール。
「知ってるよ」感を出すために、一応ご説明しておきます。笑
データとは、レコードの一件一件がそのまま「数値」なワケではありません。
レコードは単なるレコード。抽出しただけの生データは、ビジネスに活用できるインテリジェンスではありません。
ここまでに書いてきたようにデータを加工・正規化したり、基準値を設けることで、見比べ、現状を評価し、未来を選択する（とか言うと芝居がかって滑稽ですが笑）指標になるのですが、当然、「見比べる」ためには、まず「視覚化」が必要です。
「ちょっと比較したいんだけど」とか、カンタンに言わないでほしいですよね！！！！！
（心の声！！！！！！！！）
この「視覚化」、それも「定期的な最新データの反映」というルーチンや、「増減のピックアップ」、「基準値との比較」などを、忙しい私たちに代わり行ってくれるのが BIツール、即ち QuickSight です。
毎週月曜日の朝、ただでさえ週明けは忙しいのに先週分のデータを手作業でグラフに反映して、上下している箇所を探して、配布してコメントするの……やめられるんですか！！！！！？
はい、やめられるんです！！！！！！（自作自演。）
3-1. データセットの作成
QuickSight はデフォルトでデータベースからの直接のデータ取り込みや、一部の SaaS から、または S3 に置かれた csv からの取り込みをサポートしていますが、今回は先程 Athena で作成したテーブルを使います。
データベースを指定すると、テーブルがリストされるので、クエリーを実行したいテーブルをチェックして「カスタム SQL を実行」ボタンをクリック。
ここで Athena で試した select 文を入力し、実行します。
（勿論、limit 10 は除いて実行してくださいね。）
なお 2回目 以降はこんな画面で「今すぐ更新」するだけで初回に入力した SQL が実行されます。
「更新スケジュール」を使って、定期的に Athena に同じクエリーを実行させ、結果をインポートする自動更新の設定ができます。
Athena では必要最低限の変換だけを行いましたが、実は工数の入ったカラムも、「分」で入力された数値を「時間」にしたい。
それ、QuickSight でできちゃいます。
「データセットの編集」画面へ進みましょう。
この画面では SQL の変更や、取り込んだフィールドのカラム名・カラム型の変更、カスタム フィールドの追加などを行うことができます。
早速「計算フィールドを追加」しましょう。
「計算フィールドを追加」ボタンで編集画面を開いたら、フィールド名を入力し、数式を記述します。
{hoursspentbyse} / 60
Excel でもお馴染みの算術演算子を使った簡単な計算です。
関数やフィールド名は入力候補が出力され、tab で入力補完してくれる他、tab でインデントを入れたり // や /* */ でコメントを書くこともでき、かなり親切設計！
なおシンタックスエラーがあると「保存」を押した際、該当箇所に赤いアンダーラインが引かれ、保存することができません。
アンダーライン部分をマウスオーバーしてエラーの詳細を確認し、解消してください。
Topic: QuickSight の利点 – 豊富な関数
私は Salesforce 大好き芸人も兼任しているのですが(笑)、Salesforce の数式フィールドは関数の種類が非常に少なく、複雑な分析ができないのが、私が Salesforce のダッシュボードを採用せず Excel を使っていた理由です。
作成した「データセット」は、複数の「分析」と呼ばれる視覚化プロセスから参照することができます。
ただし関数の中には、この「分析」でしか使うことができないものもあるので、そうした関数を使う計算フィールドは、この先の「分析」毎に作成する必要があります。
例えば私のダッシュボードに必要な
ex.) 特定カラムの「これまでの」値が、ソート順に従って累計加算されたフィールド
これを実現するには runningSum 関数が必要なのですが、「データセット」では使用できないため後回しに。
Topic: QuickSight の欠点 1 – 「期間」などの日付型フィールドを用いたグルーピング
ex.) 2020年のデータの対前年度同月比を参照するため、「年」と「月」でデータをグルーピングしたい
つまりこういうこと。
これを QuickSight で日付型だけで実現しようとすると
- 2019年のデータだけを抽出するフィルタ
- 2020年のデータだけを抽出するフィルタ
それぞれを使って、集計単位を「月」にしたビジュアル（グラフや表）を作成することに。
＝データの年度毎にビジュアルを増やすことに……。
（フィルタはデータそのものをフィルタリングしてしまうので、2019年 と 2020年 のデータは同一のビジュアルに共存できません。）
対前を並べて表示したい私たち(？)にとっては、ちょっと困ったところです。
これは formatDate関数 などを使って、日付型のカラムをグルーピングしたい単位の文字列へ変換する計算フィールドを作成することでやや強引に回避することができます。
formatDate({FILED_NAME}, "{FORMAT_STRING}")
// {FORMAT_STRING} は 年4桁 なら YYYY、月2桁 なら MM
Topic: QuickSight の欠点 2 – 会計年度が使えない
これも Salesforce では 4月始まり の集計がカンタンにできるのですが、QuickSight はデータの視覚化サービスであり、組織の会計年度開始月の情報を持っていないため、「年」や「週番号」の初まりは必ず 1月1日 です。
addDatetTime 関数を使って -3 カ月したフィールド DateFinancialYear を作成して、無理矢理に回避する手をご紹介しておきます。
addDatetTime(-3, "MM", {FILED_NAME})
ただしこの方法、単純に月を -3 カ月しているので、X軸に表示される日付やデータ範囲・期ズレなどに注意して、自己責任でご利用ください。
3-2. 分析
データセットの準備ができたら、「保存して視覚化」します。
ここからは Excel でピボットグラフを作るのと同じ要領です。
「ディメンション」や「メトリクス」といった AWS 用語にややクラクラします。（X軸・Y軸って言ってよ！！！！！）
が、UI は直感的なので、あちこち触っていればなんとなく形になります。笑
3-2.1) 計算フィールドを追加
まずはさきほど後回しにした「累計」の計算フィールドを作成しましょう。
画面上部のメニューで 追加 > 計算フィールドを追加 を選択します。
計算フィールドの作成画面はデータセットのときと完全に同一。
先程はグレーアウトされていた関数が、すべて利用可能になっています。
ex.) 年次で 0 から週単位に累計加算される、エンジニア工数のフィールド
runningSum(
sum({HoursSpentByEngineers}), // 集計対象フィールドへの集計関数
[truncDate("WK", {DateFinancialYear}) ASC], // 集計単位
[truncDate("YYYY", {DateFinancialYear})] // 集計期間とする単位
)
こっちは応用編。
ex.) 年次で 0 から週単位に累計加算される、お問い合わせ対応件数フィールド
runningSum(
countIf( // 単純な sum ではなく、集計対象レコードを絞り込みました。
{number},
{categorey} = "問い合わせ" // 複数条件の場合は AND/OR を使います。
),
[truncDate("WK", {DateFinancialYear}) ASC],
[truncDate("YYYY", {DateFinancialYear})]
)
ここからは実際のビジュアル（＝図表）作成の操作を何種類かご紹介して、「3-3. ダッシュボードの公開」でやっといよいよ完成です！
3-2.2) ビジュアル 1 – 主要業務指標
KPI の達成状況を端的に表現するビジュアル（図表）です。
フィールドウェルで「値」に分子となるフィールドを「ターゲット値」に分母となるフィールドを入れることで、分母に対する現在の値を評価することができます。
number（管理番号）カラムは文字列フィールドのため、レコード件数としてカウントされます。よってフィールドウェルでのターゲット値（分母）の算出に計算フィールドは必要ありません。
エスカレーション有無を表すフィールド escalationbool は Athena までは boolean型 でしたが、QuickSight へ取り込まれた際、自動的に 0: false、1: true に変換され数値型に変わっています。
このためフィールドウェルのプルダウンで集計を「カウント」から「合計」に変えることで、1 をもつレコードの合計値＝エスカレーションの件数を算出できます。
パーセンテージや予実差異などの「数値の見せ方」は、ビジュアルの右端にある歯車マークのアイコンで「ビジュアルのフォーマット」ペインを開いて設定しましょう。
このビジュアルに「フィルタ」をかけて、全レコードを対象としている現状を「今年度」の「依頼」対応のみに絞り込みます。
フィルタの AND 条件と OR 条件は、次のように使い分けます。
- AND: [ フィルタ ]ペインで[ + ]アイコンを使ってフィルタを足す
- OR: [ フィルタの編集 ]へ進み、[ フィルタ条件を追加 ]ボタンでフィルタを足す
全然絞り込めない！ というときは、フィルタを作成している画面を確認してみてください。OR 条件を足しまくっているかもしれません。
ちなみにフィルタは使い回しができます。
定義を作成して使い回せるところは、いかにも AWS らしいですね。
もう本当に好き。
※なお使い回しする場合、「フィルタの削除」をすると、適用していたすべてのビジュアルからフィルタが消えてしまうので、注意してください。チェックを外すことで適用対象から外すことができます。
最後に「条件付き書式設定」で、値に応じた色やアイコンを設定しましょう。
KPI が達成できていれば、目に優しい色で「いいね！」マークが表示されるようにしました。
「いいね！」がもらえるように頑張ろう、という気になる程度には単純です私は。笑
3-2.3) ビジュアル 2 – 積み上げ棒コンボグラフ
続いて週別累計の件数と工数を表す組み合わせグラフを作ります。
フィールドウェルはシンプルです。
X 軸には DateFinancialYear を入れ、「集計」を週にしておきます。
表示形式も週番号を表す「W」に改めましょう。
棒グラフと折れ線グラフにはそれぞれ、先程作成した累計加算フィールドを入れました。
フィルタで DateFinancialYear を相対日付で「今年」にし、カテゴリーを絞ったら、ビジュアルの右端にあるメニューで「ビジュアルの複製」をします。
※ DateFinancialYear には受付日時の 3 カ月前の値が入っているため、4/1 ではなく 1/1 から現在までのレコードが抽出され、週番号が 1 始まりになります。
もうひとつのビジュアルのフィルタは年を「前年度」に変更して並べました。
※フィルタにおける「年度」は Salesforce とは異なり、あくまでも 1月1日 を基準日としています。
それぞれの「ビジュアルのフォーマット」で表示形式やスコープを揃えたら、週別の累計件数・工数の年間グラフが完成です。
対前を同じグラフで表示できてないじゃん、と思われるかもしれませんが、実はこれ、Excel でもできないコンボグラフの限界。
私の Excel ダッシュボードも、背景を透過色にしたグラフをふたつ、少しズラして重ねていたんです……ちからわざ。
こういうことをするとメンテナンスしづらくなり、業務が属人化します。
これを機にやめましょう。はい……。
3-2.4) ビジュアル 3 – ピボットテーブル
ダッシュボードに表をそのまま入れるのは、特に具体的なレコード内容を直接参照したいときです。
escalationtz は、エスカレーションのあった時間帯 HH が入った数値型です。
これをフィールドウェルで行に入れることで、テーブルの行が各時間帯になります。
続いてデータセットの作成時に用意しておいた、グルーピング用の年月（文字列）フィールドを列に入れます。
順番は敢えて「月」、「年」としました。
月をベースにしたグルーピングに、小項目として年を入れることで、対前年同月比をわかりやすくしています。
各カラム名は、データセットのフィールド名そのままではなく、3-2.2 でも登場した歯車アイコン > ビジュアルのフォーマット ペインを表示することで任意の名前に変更することができます。
対前年同月比での増減によって条件付き書式をかけるには、そもそも「対前年同月比の増減」というフィールドが必要ですね。
計算フィールドで difference関数 や windowSum関数 を使うことで、対前年同月比の差分は算出できますが、やりたい場合は別のデータセットを用意した方が素直で良さそうです。
3-3. ダッシュボードの公開
完成した分析は「ダッシュボード」として QuickSight のユーザーへ共有することができます。
Enterprise Edition では参照用ユーザーの利用料は、閲覧時間 ～30分間 を 1 セッションと数えた従量課金制。
ただしどれだけ閲覧しても、ユーザー当たり 5 USD までしか課金されません。
また Enterprise Edition ではメールでの配信（メール内にダッシュボードが埋め込まれるため、「リンクをクリック」してもらう必要はありません）や、他のアプリケーションへの埋め込みも可能です。
というわけで完成！
あとは AppFlow と QuickSight が定期的にデータを抽出＆更新してくれます！
月曜の朝が、コーヒー片手にダッシュボードに目を通すオシャレ時間に！
（なってほしい！！！！！！）
4. おわりに
この他にもワードクラウドや散布図、ヒートマップなど楽しそうなビジュアルがたくさん用意されている他、MLインサイトによる機械学習での予測や、Excel 関数のようにデータの値に応じ条件判定で文面を変えられる自動説明文など、Enterprise Edition には更に追加要素があります。
保守・運用をする人には欠かせない、月次でのキャパシティー・パフォーマンス分析などにも活用できそうですね。リソース利用増減の予測に使うのも面白そうです！
Athena でデータ変換をした部分を除けば(※)操作も GUI のみで直感的なので、システム担当ではなくデータ・アナリストの方にもダッシュボードが作れるのでは？
いやむしろ QuickSight の「分析」の作成は、その手のプロの方が得意なのではないか？ と作っていて感じました。
（Excel 大好き芸人の私も、関数の使い方やそもそも「分析の観点」の勉強がまだまだ足りません……。）
※この記事を書いているつい先日、ETL サービス AWS Glue の新機能 AWS Glue DataBrew が発表されました。
QuickSight は Glue もデータソースとしてサポートしているので、データ変換も直感的な GUI で設定ができるかもしれません！
以上、皆さんの QODL （Quolity of Dashboard Life）向上の一助になれば嬉しいです。
長々とご覧頂き、ありがとうございました！