概要

以前、本ブログで紹介した「ETL ツール TROCCO で始める AWS コスト分析ダッシュボード構築」の仕組みを約 4 ヶ月運用する中で、新たな課題が見えてきました。

本記事では、その課題を解決するために「データマート」を活用して、月次のコスト比較が可能なダッシュボードを新たに構築した過程をご紹介します。

完成したダッシュボード

先に、本記事のゴールである、完成したダッシュボードをご紹介します。

今回構築した新しいダッシュボードでは、月ごとの AWS コストを棒グラフで比較できるようになりました。
これにより、コストの月次推移を直感的に把握することが可能になります。

既存の仕組みと課題

まず、今回の改善に至った背景をご説明します。
前提となる仕組みについては、上記の記事で詳しく解説しています。

既存の仕組みでは、日次で更新される AWS CUR のデータを BigQuery に連携していました。
しかし、この方法では「月ごとのコスト比較が難しい」という課題がありました。

AWS CUR は、その月のコストが確定するまで毎日データが更新され、レコードが追加されていきます。
そのため、ある月の最終的なコストは、その月の末日に生成された最新のレコードのみが該当します。

単一の月を可視化するだけであれば、ダッシュボード側で最新のレコードのみをフィルタリングすれば問題ありません。
しかし、複数の月を並べて比較する場合、月ごとにフィルタリング条件が異なってしまい、単純な設定では対応できません。

この課題を解決するため、月ごとに集計されたデータを格納する「データマート」を導入することにしました。

データマートとは？

データマートとは、データウェアハウス(DWH)に格納された膨大なデータの中から、特定の目的(今回は月次コスト分析)に合わせて必要な情報だけを抽出し、集計・加工して作成されるデータベースのことです。

ダッシュボードツール(Looker Studio)側で都度複雑な集計を行うのは非効率なため、あらかじめ BigQuery 上で集計した結果を別のテーブルとして保存(データマート化)し、ダッシュボードからはそのテーブルを参照するようにします。

データマート活用の流れ

1. 元データ(AWS CUR) から月次比較に必要な情報を抽出・集計する SQL を作成
2. SQL の実行結果を 新しいテーブル(データマート) に保存
3. ダッシュボード は、この新しいテーブルを参照してデータを可視化

より詳細な概念については、以下のドキュメントが参考になります。

データマートとは? – データマートの説明 – AWS
データレイク、データウェアハウス、データマートの比較 – AWS

1. BigQuery で月次コスト集計 SQL を作成

まず、データマートに格納するデータを生成するための SQL を BigQuery で作成します。
今回は、各月の最新レコードだけを抽出し、サービスごとにコストを集計するクエリを組み立てました。

WITH
  -- 各利用月における、最新のレポート転送日時を特定する
  LatestMonthlyReport AS (
    SELECT
      TIMESTAMP_TRUNC(line_item_usage_start_date, MONTH) AS usage_month,
      MAX(transferred_time) AS max_transferred_time
    FROM
      `[プロジェクト名].[データベース名].aws-cur-test`
    GROUP BY
      1
  )
-- 各月の最新レポートを、指定された区分（product_name, product_product_family）で集計する
SELECT
  latest.usage_month,
  cur.product_name,
  cur.product_product_family,
  SUM(cur.line_item_unblended_cost) AS total_monthly_cost
FROM
  `[プロジェクト名].[データベース名].aws-cur-test` AS cur
  INNER JOIN
  LatestMonthlyReport AS latest
  -- 条件1: 利用月が一致する
  ON TIMESTAMP_TRUNC(cur.line_item_usage_start_date, MONTH) = latest.usage_month
  -- 条件2: レポート転送日時が、その月の最新のものである
  AND cur.transferred_time = latest.max_transferred_time
GROUP BY
  1, 2, 3 -- usage_month, product_name, product_product_family で集計
ORDER BY
  1, 2, 3; -- 月、製品名の順で並び替え

このクエリを実行すると、月ごと・サービスごとの最新コストが正しく集計されていることが確認できます。

次に、このクエリ結果をテーブルとして保存します。
BigQuery の「結果をエクスポート」機能からテーブルとして保存先を指定します。

これで、分析用のデータマートテーブルが完成しました。

このテーブルに対して、月ごとで集計すればその月の合計コストを取得することができます。

2. TROCCO でデータマートを自動生成

一度テーブルを作成しただけでは、データが更新されません。
AWS CUR は日次更新されるため、データマートも追随して最新の状態を保つ必要があります。

そこで、ETL ツール「TROCCO」のデータマート機能を利用して、この更新作業を自動化します。

「データマート」メニューから「新規作成」を選びます。

DWH として「BigQuery」を選択します。

接続情報は、既存のデータ転送設定と同じものを利用します。

クエリ設定では、先ほど作成した SQL を入力します。

出力先として、先ほど作成したデータマートテーブルを指定します。

書き込みモードは「全件洗い替え」を選択します。
これにより、実行のたびに既存データを削除してから新しいデータを挿入し、常に最新のデータでテーブルが保たれるようになります。

最後に、この処理を日次で自動実行するためのスケジュールを設定します。
元の CUR データ転送ジョブが完了した後の時間帯に設定します。

これで、毎日自動で最新の月次コストデータが格納されたデータマートが作成されるようになりました。

3. Looker Studio でダッシュボードを可視化

最後の仕上げとして、Looker Studio のデータソースを、新しく作成したデータマートテーブルに接続します。

追加したデータソースを元に、月ごとのコスト比較が可能なダッシュボードを作成します。

このダッシュボードは、4 つの異なるグラフを配置し、それぞれが持つ役割を明確にすることで、迅速かつ正確なコスト分析を可能にしています。

1.月ごとのコスト結果を把握：ピボットテーブル（左上）

まず左上のテーブルで、各月のコスト結果を数値で素早く確認できます。

2.全体のコスト変化を把握：折れ線グラフ（右上）

右上の折れ線グラフは、コスト全体の変化を視覚的に捉えるためのものです。
月々の繋がりからコストの増減が把握でき、ピークや急な変動があった月を特定できます。

3.月ごとのサービスコスト割合を把握：積み上げ棒グラフ（左下）

左下の積み上げ棒グラフで、サービスごとのコスト割合を把握できます。
月ごとの総コストに対し、各サービスが占める割合を可視化。
コスト変動の主な要因となっているサービスがどれなのかを特定できます。

4.正確な数値で詳細を分析：ピボットテーブル（右下）

ピボットテーブルで、詳細な数値を分析します。
月ごと、サービスごとにコストが一覧表示されるため、具体的な金額の比較や、グラフでは見えにくい細かい変動まで正確に確認できます。

まとめ

本記事では、日次更新されるデータでは難しかった月次のコスト比較を実現するため、データマートを構築するプロセスを解説しました。

• 課題: 日次更新データでは月ごとの比較が困難
• 解決策: 月次で集計したデータマートを作成
• 自動化: TROCCO を使い、データマートの更新を日次で自動実行

分析したい切り口が増えるほど、元データのままでは対応が難しくなるケースがあります。
そのような場面でデータマートは非常に強力な解決策となります。

そして、TROCCO のような ETL ツールを活用することで、データマートの構築・運用もコーディングレスで手軽に実現できるため、データ分析の幅が大きく広がります。

参考資料

データマートとは? – データマートの説明 – AWS
データレイク、データウェアハウス、データマートの比較 – AWS

NHN テコラスの採用情報はこちら