この記事はAWS Analytics Advent Calendar 2023の7日目の記事です。
こんにちは、SREチーム所属の@mashiikeです。
皆様はAmazon QuickSightを活用していますでしょうか？ QuickSightで言うところのデータセット。一般的にはデータマートとも呼ばれるものの設計・構築おけるTipsな話です。 他のBIツールにも応用できるとは思いますが、主にQuickSightをターゲットにした話になります。
データマートについて
AWSのサイト上ではデータマートについて以下のように書かれています。
データマートは、組織のビジネスユニットに固有の情報を含むデータストレージシステムです。これは、会社がより大規模なストレージシステムに格納するデータの小規模で厳選された部分を含みます。企業は、部門固有の情報をより効率的に分析するために、データマートを使用します。主要なステークホルダーが十分な情報に基づいた意思決定を迅速に行うために使用できる概要データを提供します。
QuickSightでは、データセットはAthenaやRedshift、SPICEなど様々なシステムから作ることが可能になっています。 多くの場合は、それらのシステム上に構築したデータマートにアクセスしてデータセットを構築することになると思います。 ここで、今回の記事中ではRedshift上に構築するデータマート、つまり、データセットに接続するための分析向けのテーブル設計について話を進めていきます。
一般に、Redshift上にデータマートを構築する場合、S3やAurora MySQL、DynamoDBなどのデータソースのデータをRedshiftからアクセスできるようにし、 Redshift上でSQLを用いてデータ変換を行ってデータマートを構築することになると思います。
このデータ変換が必要な理由を端的に言うと、『データソースのデータはプロダクトでデータを管理 することに最適化されてるが、分析 するには向いていない形になっているから』です。
データマートを構築する上で必要になるデータ変換の具体的な例として、非正規化を揚げます。
MySQLやPostgreSQLなどのリレーショナル・データベース（RDB）は、多くの場合データは正規化した上で保存されています。
RDBにおいては、データ管理の上では正規化したほうが何かと都合が良いのですが、仮にこれらの正規化されたテーブルをQuickSight上で使おうと思うと多数のデータセットを定義する必要が出てきてとても大変になります。
そこで、予め適度に非正規化したテーブルをRedshift上に作っておき、それをデータセットとして使うことで、データセット数を減らして使いやすくすると言うのが一例になります。
他にも、欠損値の補完やカラム名の変更、タイムゾーンの統一等たくさんあります。
まとめると、データソースの情報を分析向けにデータ変換しておいたテーブル郡というのが、Redshift上に構成する場合のデータマートということになります。
1:Nカーディナリィのデータにまつわるデータマート
データマートを構築する上では、構築するストレージシステムの特性や、データソースの保存形式等で様々なテクニックがあります。 話をシンプルにするために、RDBをデータソースとして、Redshiftをデータマートを構築し、そのデータマートにQuickSightから接続するというケースを想定します。 そして、RDB上には次のような1:Nカーディナリィにある２つのテーブルを考えてみます。
event (親テーブル)
| event_id | title | entry_fee | start_date |
|---|---|---|---|
| A7rF2 | クリスマスレース | 500 | 2023-12-25 |
| b9XpY | ケーキ早食い競争 | 1000 | 2023-12-24 |
| K3sL6 | 年末調整RTA | 0 | 2023-12-01 |
event_entry (子テーブル)
| event_id | entry_id | entry_name | entry_date |
|---|---|---|---|
| A7rF2 | 111 | 年末・ジョニー | 2023-12-13 |
| A7rF2 | 112 | 師走博士 | 2023-12-14 |
| A7rF2 | 113 | 太郎 | 2023-12-15 |
| b9XpY | 211 | ケーキ侍 | 2023-12-01 |
| K3sL6 | 311 | 忘年しました。 | 2023-11-18 |
| K3sL6 | 312 | わからな杉 | 2023-12-01 |
これは、ある大会プラットフォームサービスを模した模擬データです。あるイベントの情報とそのイベントに参加登録した人たちの情報を模しています。
event と event_entry は親子関係にあり、1:Nのカーディナリィになっています。
ここでQuickSightでイベントの分析を行いたい人向けに、データマートを構成するとします。以下のようなことが知りたいと仮定します。
- イベントの参加人数
- イベントの数
- 参加時に支払われた参加費の合計
- イベントの参加費の平均
このようなことを１つのデータマートで調べるなら、非正規化として２つのテーブルをJOINすることはよくあると思います。 仮にJOINしたとしたら、次のような形になると思います。
| event_id | title | entry_fee | start_date | entry_id | entry_name | entry_date |
|---|---|---|---|---|---|---|
| A7rF2 | クリスマスレース | 500 | 2023-12-25 | 111 | 年末・ジョニー | 2023-12-13 |
| A7rF2 | クリスマスレース | 500 | 2023-12-25 | 112 | 師走博士 | 2023-12-14 |
| A7rF2 | クリスマスレース | 500 | 2023-12-25 | 113 | 太郎 | 2023-12-15 |
| b9XpY | ケーキ早食い競争 | 1000 | 2023-12-24 | 211 | ケーキ侍 | 2023-12-01 |
| K3sL6 | 年末調整RTA | 0 | 2023-12-01 | 311 | 忘年しました。 | 2023-11-18 |
| K3sL6 | 年末調整RTA | 0 | 2023-12-01 | 312 | わからな杉 | 2023-12-01 |
実際に、QuickSight上で、このデータマートを使ってみましょう。
イベントの参加人数とイベントの数、参加時に支払われた参加費の合計は、QuickSightのGUIから選択すれば簡単に見れますね。
ところが、 イベントの参加費の平均 はどうでしょうか？
正しい値は (500 + 1000 + 0) /3 = 500 ですが、先程と同様にすると、次のような結果になってしまいます。
これは、1:NのカーディナリィにあるテーブルをJOINしたことによって、親テーブルであるevent側の行が増えてしまったことによっておきます。
おそらく、真っ当に解決するのであれば次の記事のようにLAC-A関数というものを使うことになるでしょう。
試しに計算フィールドとして avg(max({entry_fee}, [{event_id}])) を定義して使ってみると正しい値になります。
しかし、このLAC-A関数をつかった計算フィールドには問題があります。
2023/12/6時点では、データセット側で計算フィールドを定義しようとすると、エラーになってしまうのです。1
そのため、QuickSightで分析を作成するたびに、分析側で計算フィールドを定義する必要があります。 QuickSightに習熟している利用者であれば、問題ないかもしれませんが可能であればGUIでクリックするだけで求める値が手に入ると嬉しいです。
これが、データマート構築における1:Nカーディナリィにまつわる問題になります。
QuickSight向けの1:Nカーディナリィ対策済みデータマート
では、どうすればよいのでしょうか？ そこで考えた、1:Nカーディナリィにまつわるデータマート構築のTipsを紹介します。 内容としては簡単です。 データマートを次のようにすればいいのです。
| event_id | title | entry_fee | start_date | entry_id | entry_name | entry_date | paid_entry_fee |
|---|---|---|---|---|---|---|---|
| A7rF2 | クリスマスレース | 500 | 2023-12-25 | ||||
| b9XpY | ケーキ早食い競争 | 1000 | 2023-12-24 | ||||
| K3sL6 | 年末調整RTA | 0 | 2023-12-01 | ||||
| A7rF2 | クリスマスレース | 2023-12-25 | 111 | 年末・ジョニー | 2023-12-13 | 500 | |
| A7rF2 | クリスマスレース | 2023-12-25 | 112 | 師走博士 | 2023-12-14 | 500 | |
| A7rF2 | クリスマスレース | 2023-12-25 | 113 | 太郎 | 2023-12-15 | 500 | |
| b9XpY | ケーキ早食い競争 | 2023-12-24 | 211 | ケーキ侍 | 2023-12-01 | 1000 | |
| K3sL6 | 年末調整RTA | 2023-12-01 | 311 | 忘年しました。 | 2023-11-18 | 0 | |
| K3sL6 | 年末調整RTA | 2023-12-01 | 312 | わからな杉 | 2023-12-01 | 0 |
これは、eventの行をそのままダミーとして増やし、event_entryと結合した行の entry_feeを paid_entry_fee として別カラムに持たせるというものです。
このデータマートにおいては、 参加時に支払われた参加費の合計 は paid_entry_feeの合計を設定すれば良いです。
イベントの参加費の平均 に関しては、次のようにフィルタをかければればGUIのみで算出できます。
使い方の工夫が少し必要ですが、利用者が計算フィールドを覚えなくても良いので場合によってはとても有用になると思います。
ところで、こう思った方はいませんか？
『えっ、eventだけのデータセット作ればいいんじゃないか？』
ごもっともです。ところが、実際のプロダクトでは親子関係だけでなく、孫、ひ孫... というようになることもありますし、１つ１つデータセットを作っていくとデータセットの数がとても多くなることもあります。
その結果、利用者から『 データセットが多くて何をどう使ったらいいかわからない！どれか１つ使えれば良いようにできない？』という声が上がることもあります。2
そんなときに、このTipsが役に立つのこともあるので、似たようなケースに遭遇した人は試してみてください。
まとめ
QuickSight向けのデータマートを構築するときに、RDBのデータソース側のテーブルが1:Nのカーディナリィにある場合についてのTipsでした。 このTipsは 1:NのカーディナリィのテーブルをJOINする場合、親テーブル側の行が増えるため正しい集計ができないという問題に対するものでした。 LAC-A関数など、QuickSightの応用的な機能を使いこなしている方が多い環境では、今回のTipsはあまり役に立たないかもしれません。 しかし、そうでない環境では、データマート側の工夫で利用者が使いやすいデータセットを作ることができるという例でした。 他にもQuickSightで使いやすいデータセットやデータマートを構築するTipsがありますので、機会があれば紹介できれば嬉しいです。
