Power Query や SQL Server で業務データを扱っていると、クエリがどんどん増えていき、どれが何の目的なのか分からなくなってくる…という経験はありませんか？
本記事では、そうした混乱を防ぐために必要なクエリの分類、命名ルール、データモデル設計の基本について解説します。
「とりあえず動く」から「後から見ても分かる、使える」クエリ管理を目指しましょう。
クエリ設計の全体像をつかむ
クエリは3層構造で考える
データ処理の流れは、以下のように 「ステージング → 中間 → マート」 という3段階に分けて考えると整理しやすくなります。
この3段階の役割を意識してクエリを組むことで クエリの全体設計がスッキリと整理 され、理解し易いものになります。
これは、クエリを作成している本人が再度クエリを確認する場合にも有効ですし、チームで仕事をしている場合はチーム内の理解の促進にも効果的です。
クエリの分類と役割
① ステージング（Staging）
ステージは英語では舞台を指しますが、ここではステージに上がる 準備 の意味になります。
このクエリで 元のデータを整えておきます。主な処理は次のようになります。
最初のステップとして重要な処理です。
主な処理
- 列名の修正
- 列の選別
- 型変換
- 不要な行の除去
② 中間クエリ
中間クエリではデータを分析、集計できるようにデータを加工して行きます。
中間クエリは主に ファクト と ディメンジョン の2種類のクエリに仕上げていくことが重要です。
②-1 ファクト（Fact）
データの中心となるもので、売上や注文、アクセスログなどのファクト（＝事実、実績）のデータになります。
②-2 ディメンジョン（Dimension）
ファクトの情報を分類するためのデータです。例えば従業員番号から氏名や所属部署名、勤務地など付随する情報を整理します。
③ マート（Mart）クエリ
最終的に可視化、分析、集計出力に使用するクエリです。
最終的にユーザーに提供する商品のようなものなので、市場（マーケット）の意味合いの言葉が使われています。
通常、ファクトとディメンジョンを結合した状態で作成されます
クエリ名の命名規則の導入
クエリを沢山作っていくと名前の付け方に悩むことがあると思います。
しかし、名前はルールに基づいて決めるようにすると、後からクエリの名前を見ただけで内容が理解がし易くなります。
従って、クエリ名の命名規則（ネーミングルール）を導入しましょう。ここでは推奨の命名規則をご紹介します。
接頭語（Prefix）
クエリ名の最初に来る単語は上記のクエリの分類に合わせて次のようなものを設定します。
| 接頭語 | 用途 | 例 |
|---|---|---|
stg_ | ステージング | stg_sales_raw |
fct_ | ファクト | fct_sales |
dim_ | ディメンジョン | dim_customer |
mrt_ | マート | mrt_sales_monthly |
名前本体
接頭語だけ設定するだけでもかなり理解が進みますが、その後ろにつける名前も工夫をします。
- 最初の単語 は
salesやorderなどのデータの中心的な 対象 を指し示す単語にします。 - 続く単語 は
by_monthやby_sectionなど データの粒度、又は、reportやdashboardなど 用途 を指し示す単語などにします。
単語はアンダースコアで繋げる
単語をそのまま繋げてしまうと区切れ目が分からなくなってしまいますので、fct_sales_by_month のようにアンダースコア _ で繋げます。
こうすることで名前の読み易さが向上します。
クエリ設計の基本を押さえる
ここではクエリを設計する上で理解しておくべき概念について解説します。
「主キー」と「外部キー」
主キー（Primary Key）
テーブル内のレコードを一意に識別するための列です。
例えば社員毎のデータがあった場合、特定の一人を識別するには社員番号を使えばできると思います。
この場合は、社員番号が 主キー になります。
外部キー（Foreign Key）
他のテーブルの 主キーを参照 する列です。
例えば、営業担当社員の社員番号と売上金額の入った売上データ（ファクト）があった場合、
この営業担当社員の社員番号が 外部キー となります。
外部キーを使って主キーを持つ 社員データ（ディメンジョン）と結合することで、部署毎の売上などの集計ができるようになります。
実務的には、「主キーはディメンジョンに」「外部キーはファクトに」が原則です。
スター型とスノーフレーク型の違い
ファクトとディメンジョンを結合して最終的に BI などで利用するデータに組み上げますが、これにも
大きく分けて2種類の型があります。
| 観点 | スター型 | スノーフレーク型 |
|---|---|---|
| 構造 | 単純構造（非正規化） | 階層的構造（正規化） |
| JOIN回数 | 少ない（1回） | 多い（2回以上） |
| 保守性 | やや低い（重複が多い） | 高い（冗長性が少ない） |
| 処理速度 | 高速 | やや低下する場合あり |
| BIツールとの相性 | 非常に良い | 複雑なモデリングが必要 |
小規模で管理コストが比較的小さい場合はスター型で良く、大規模で管理コストが大きくなるようであればスノーフレーク型が向いています。
以上のようなクエリ設計の基本を理解しておくと、クエリ作成においてポジションを見失うことなく作業を進められるようになります。
クエリのバージョン管理・視覚化をどうするか？
Excelでシンプルに履歴を管理する
手軽な方法として、以下のような形式でバージョン管理表を作っておくと便利です。
これは Power Qury でも SQL Server などのデータベースでも適用できる方法です。
| 日付 | クエリ名 | 内容 | 担当者 |
|---|---|---|---|
| 2024/07/01 | fct_sales | 売上金額列追加 | 田中 |
| 2024/07/03 | dim_product | JANコード追加 | 鈴木 |
クエリの更新履歴をExcelなどで残しておくだけでも、チーム運用が格段に楽になります。
Power Query エディターでクエリの依存関係を確認する
Power Query にはクエリ同士がどう繋がっているのかを視覚化する機能があります。
これを活用すればクエリの全体像の確認がかなり効率的になります。
Power Query エディターで以下の操作をすることで確認ができます。
- Power Query エディターのメニューから「表示」＞「クエリの依存関係」
- 小ウィンドウが表示され、クエリの依存関係が線図で示される
dbtを使ってクエリ管理を自動化する
SQL Server などのデータベースを運用していて、より高度な管理が必要な場合は、データモデル管理ツール「dbt」 の導入も検討できます。
dbt を活用すると以下を実現することができます。
- SQLベースでファイルごとにクエリをモジュール化
- Git によるバージョン管理 が可能
- 自動ドキュメント生成 や 依存関係の可視化 も可能
Power Queryベースからステップアップしたい場合や、DWH連携を考えるならおすすめです。
dbt の導入方法や使い方はまた別の記事で紹介したいと思います。
まとめ
クエリの整理は、分析の品質と効率を左右する重要な基盤です。
クエリが整理できていないと、誤ったデータが出力されるリスクが高くなります。
また、トラブル発生時の原因調査、対策においても難解なクエリを読み進めることになり、時間とコストがかかるリスクが高くなります。
この記事ではこういった問題を回避するためのクエリの整理法、分類、命名管理の基本について解説させて頂きました。
ここで得た知識を使えばデータ取扱い業務をより強力に効率的にできるようになると思いますので、皆さんも是非実践してみて下さい。
