スタースキーマでMap Bridge Tableを活用した階層データ分析のアプローチ

はじめに

マネーフォワードのアナリティクスエンジニア　奥野です。

先日出したブログに関連して、データモデリングネタです。
今回は、Map Bridge Tableの活用方法について書きます。前回と同じく、チームメンバーに共有したところ反応が良かったので、せっかくなのでブログにしました。

Map Bridge Tableって何？って感じかもしれません。

Map Bridge Tableは階層の深さが異なる親子構造を持つデータを柔軟に集計するためのテーブルです。例えば、親会社に紐づくグループ会社の従業員数をまとめて集計するときなどに有効なアプローチです。分析したことがある方なら分かると思いますが、どれだけの階層の子どもが紐づくかは会社によって違い、分析が難しくなりますよね。

また、決算書項目の集計においても、決算書項目の親子関係を把握して、それぞれの金額を集計するときにも役に立ちます。

この記事は、KimballのThe data warehouse toolkitのChapter 7 Ragged Variable Depth Hierarchyを参考に記載しています。

Map Bridge Tableの使い所

Map Bridge Tableは、階層の深さが異なる親子構造を持つデータにおいて、子どもも含めて集計するときに非常に有効なテーブルです。

例えば、グループ会社がある企業のような親子関係を持つ企業で、従業員数を集計したい場合を考えてみましょう。親企業では、グループ会社も含めた従業員数を知りたい、中間のグループ会社でも（親を含まない）子どもの会社も含めた従業員数を把握したい場合があるケースに威力を発揮します。

今回は例として、グループ会社4社の従業員数を管理するケースを想定します。

階層構造の例

A社（親会社）
└── B社（子会社）
    ├── C社（孫会社）
    └── D社（孫会社）

各社に1名ずつ従業員がいるとします。
このときモチベーションは、各企業について、グループ会社も含めた従業員数を知りたいというものです。
例えば、A社の従業員数は、（A社、B社、C社、D社の従業員数を合計した）4名となります。B社の従業員数を知りたい場合は3名となります。

スタースキーマにおけるMap Bridge Tableの構成方法

テーブル設計

Map Bridge Tableは、factテーブルとdimensionテーブルの中間に配置します。今回の例ではcompany_map_bridgeテーブルがMap Bridge Tableになります。

1. dim_company（会社ディメンションテーブル）

company_id	company_name
1	A社
2	B社
3	C社
4	D社

2. fact_company_member_count（従業員数ファクトテーブル）

date	company_id	member_count
2024-01-01	1	1
2024-01-01	2	1
2024-01-01	3	1
2024-01-01	4	1

3. company_map_bridge（Map Bridgeテーブル）

Map Bridge Tableの各カラムの説明：

• parent_company_id: 階層の親となる会社ID
• child_company_id: 階層の子となる会社ID
• depth_from_parent: 親からの階層の深さ（0=自分自身、1=直下の子会社、2=孫会社...）
• is_highest_parent: 最上位の親会社かどうか？
• is_lowest_child: 最下位の子会社かどうか？

id	parent_company_id	child_company_id	depth_from_parent	is_highest_parent	is_lowest_child
1	1	1	0	true	false
2	1	2	1	false	false
3	1	3	2	false	true
4	1	4	2	false	true
5	2	2	0	false	false
6	2	3	1	false	true
7	2	4	1	false	true
8	3	3	0	false	true
9	4	4	0	false	true

具体的なクエリを後述しますが、このMap Bridgeテーブルを使い、fact, dimensionテーブルと結合することで、以下の集計が可能になります。

• A社を選択時：A+B+C+D社の合計（4名）
• B社を選択時：B+C+D社の合計（3名）
• C社やD社を選択時：それぞれ単体（1名ずつ）

Map Bridge Tableの実装方法

Map Bridge Tableは、会社IDと親会社IDの情報さえあれば、WITH RECURSIVEを使って簡単に作成できます。

BigQueryでのMap Bridge Tableの実装サンプル

-- tempテーブルで企業とその親企業情報をもったテーブルを作成
create temp table company_hierarchy as (
  select 1 as company_id, 'A社' as company_name, null as parent_company_id
  union all select 2, 'B社', 1
  union all select 3, 'C社', 2
  union all select 4, 'D社', 2
);

-- Map Bridge Tableを作成。再帰クエリで階層の深さを計算
with
recursive hierarchy as (
  -- スタート: 自分自身（depth = 0）
  select
    company_id as parent_company_id,
    company_id as child_company_id,
    0 as depth_from_parent
  from company_hierarchy

  union all

  -- 再帰的に子をたどる
  select
    h.parent_company_id,
    ch.company_id as child_company_id,
    h.depth_from_parent + 1 as depth_from_parent
  from hierarchy as h
  join company_hierarchy as ch
    on h.child_company_id = ch.parent_company_id
  where h.child_company_id <> ch.company_id  -- 無限ループ回避
),

-- 重複排除
deduplicated as (
  select
    parent_company_id,
    child_company_id,
    max(depth_from_parent) as depth_from_parent
  from hierarchy
  group by parent_company_id, child_company_id
),

-- is_highest（誰からも子として参照されていない親）
highest_flags as (
  select distinct parent_company_id as company_id
  from deduplicated
  where parent_company_id not in (
    select child_company_id from deduplicated where parent_company_id <> child_company_id
  )
),

-- is_lowest（誰の親にもなっていない子）
lowest_flags as (
  select distinct child_company_id as company_id
  from deduplicated
  where child_company_id not in (
    select parent_company_id from deduplicated where parent_company_id <> child_company_id
  )
)

-- 最終的なMap Bridge Table
select
  row_number() over (order by parent_company_id, child_company_id) as id,
  parent_company_id,
  child_company_id,
  depth_from_parent,
  case when h.company_id is not null then true else false end as is_highest_parent,
  case when l.company_id is not null then true else false end as is_lowest_child
from deduplicated
left join highest_flags as h on deduplicated.child_company_id = h.company_id
left join lowest_flags as l on deduplicated.child_company_id = l.company_id
order by parent_company_id, child_company_id;

集計クエリのイメージ

Map Bridge Tableを使った集計は以下のようになります。

-- 各会社配下の全従業員数を集計
select
  dc.company_id,
  dc.company_name,
  sum(f.member_count) as total_members
from fact_company_member_count as f
    left join
        company_map_bridge as b
        on f.company_id = b.child_company_id
    left join
        dim_company as dc
        on b.parent_company_id = dc.company_id
where f.date = '2024-01-01'
group by dc.company_id, dc.company_name
order by dc.company_id;

-- 結果:
-- 1, A社: 4名（A+B+C+D社）
-- 2, B社: 3名（B+C+D社）
-- 3, C社: 1名（C社のみ）
-- 4, D社: 1名（D社のみ）

備考：
上記のような分析クエリはfact, dimensionテーブルの間にmap brdigeテーブルをジョインすることを覚えている必要があり、慣れていないユーザーは混乱してしまうかもしれません。すでにジョインをしたワイドテーブル（One big table）を配置するのも手ですが、レコード数がかなり多くなってしまいます。
そこで個人的には、このMap Bridge テーブルは動的に裏側でクエリを作成してくれるLooker等のセマンティックレイヤーのツールと相性が良いと思います。

まとめ

いかがだったでしょうか？
Map Bridge Tableを活用することで、階層の深さが異なる親子構造を持つデータにおいて、柔軟な集計が可能になります。

企業グループや組織階層、商品カテゴリ、決算書など、様々な階層データの分析において、Map Bridge Tableは非常に有効なアプローチです。ぜひ活用してみてください。