バラバラのデータを1つの答えにする — ETL・マスタ統合・メタデータ・DWH/BI
ETLとELTの違い、名寄せとゴールデンレコード、メタデータとデータリネージ、DWHとOLAPを、変換前後の表で追いながら学ぶ。
更新日 執筆: PassDojo 編集部
目次読了目安 約8分
同じ売上が部署ごとに違う — 数字がずれる理由
営業部の資料では昨日の売上が1,950円、経理部の資料では1,050円と出ました(想定)。同じ日の同じ店なのに数字が合わない原因は、次の4つのどれかであることが多く、この単元ではその解決の道具を順にたどります。
| ずれの原因 | 解決の道具 | DMBOK2の知識領域 |
|---|---|---|
| データの集め方が違う | ETL・ELT・API・バッチ | データ統合&相互運用性 |
| 同じ人や商品が別物扱い | 名寄せ・ゴールデンレコード | 参照データ&マスターデータ |
| 数字の意味がわからない | メタデータ・カタログ・リネージ | メタデータ |
| 集計の置き場がない | DWH・データマート・BI | データウェアハウジング&BI |
出典:DAMA International『DAMA-DMBOK』https://dama.org/learning-resources/dama-data-management-body-of-knowledge-dmbok/ 。このページでは、DMBOKは特定の製品や手法を定めない参照枠と説明されています。
変換してから入れるか、入れてから変換するか — ETLとELT
異なるシステムのデータを集めて、使える形にそろえる作業がデータ統合です。代表的な手順がETLとELTで、違いは変換(Transform)をどこで行うかです。ETLは抽出(Extract)→変換→格納(Load)の順で、DWHに入れる前に別の処理領域で変換します。ELTは抽出→格納→変換の順で、元の形のままDWHに入れ、DWHの中で変換します。
想定のPOS売上CSV 3行で確かめましょう。旧商品コードA-001を4901001、A-002を4901002へ変える対応表があるものとします。
| 店舗 | 旧商品コード | 数量 | 金額(円) | 変換後の商品コード |
|---|---|---|---|---|
| S01 | A-001 | 2 | 600 | 4901001 |
| S01 | A-002 | 1 | 450 | 4901002 |
| S02 | A-001 | 3 | 900 | 4901001 |
ETLでは、変換用の処理領域で右端の列まで作ってから、3行をDWHに格納します。ELTでは、旧コードのままの3行をまずDWH内の作業用の表に格納し、SQLで対応表と突き合わせて右端の列を作ります。どちらでも結果は同じで、件数は3行、数量は2+1+3=6、金額は600+450+900=1,950円のまま変わりません。件数と合計を前後で照合すると、取りこぼしに気づけます。
| 観点 | ETL | ELT |
|---|---|---|
| 順序 | 抽出→変換→格納 | 抽出→格納→変換 |
| 変換する場所 | DWHの外(専用の処理領域) | DWHの中 |
もう1つの区別が処理のタイミングです。前日分の3行を夜間にまとめて処理するのがバッチ処理、注文が確定した瞬間にAPI(システム間でデータをやり取りする決まった窓口)で在庫システムへ1件ずつ送るのがリアルタイム処理です。ETLかELTかは変換の順序、バッチかリアルタイムかは動かすタイミングの話で、別々の軸です。
田中太郎が4件いる — 名寄せとゴールデンレコード
次は、同じ人が別人として登録される問題です。顧客・商品・取引先のように業務の土台になる基幹データがマスターデータで、注文や請求のトランザクションデータより変更の頻度が低めです。営業システム・会計システム・ECサイトの顧客データが次のとおりだったとします(想定データです)。
| システム | 氏名 | 電話番号 | メール | 名寄せの判定 |
|---|---|---|---|---|
| 営業 | 田中 太郎 | 03-1234-5678 | (空欄) | 同一人物 |
| 会計 | 田中太郎 | 0312345678 | (空欄) | 同一人物 |
| EC | たなか たろう | 03-1234-5678 | tanaka@example.com | 同一人物 |
| EC | 田中 太郎 | 090-9999-0000 | other@example.com | 別人 |
名寄せは3段階です。第1に正規化で、空白とハイフンを取ると営業と会計は「田中太郎」「0312345678」にそろいます。第2に照合で、想定ルールは「正規化後の電話番号が一致すれば同一人物」とします。ECの「たなか たろう」は氏名が違っても電話番号が一致するので同一人物、氏名だけ同じ4件目は別人です。第3に統合で、項目ごとに採用する値を決め、信頼できる唯一のレコードであるゴールデンレコードを作ります。
| 項目 | ゴールデンレコードの値 | 採用元(想定ルール) |
|---|---|---|
| 氏名 | 田中太郎 | 会計(請求書の名義) |
| 電話番号 | 0312345678 | 3件で共通 |
| メール | tanaka@example.com | ECだけが保有 |
名寄せ前は4件で、DMを送ると4通です。名寄せ後は2件(同一人物の1件と別人の1件)なので、4−2=2通の重複送付を防げます。ただし、別人を統合する誤統合もあるため、照合ルールは慎重に決めます。
マスターデータの置き場や更新の仕組み(実装方式)は組織の事情で選びます。方式名の暗記より、名寄せの手順とゴールデンレコードの目的を押さえましょう。都道府県コードのような分類用のデータは、参照データとして別に管理します。
データの説明書を作る — メタデータ・カタログ・リネージ
メタデータは「データについてのデータ」で、データに意味を与えて見つけやすく、使いやすくします。次の3種に分けると整理できます。
| 種類 | 何を説明するか | 売上表の列amtの例(想定) |
|---|---|---|
| ビジネスメタデータ | 業務上の意味・定義 | 税込売上金額(円) |
| テクニカルメタデータ | 表名・列名・型などシステム上の情報 | 表salesの列amt、型は整数 |
| オペレーショナルメタデータ | 処理の実行状況・更新履歴 | 毎日2:15に更新、取込3行、成功 |
列名がamtだけでは、税込か税抜か、円かドルかはわかりません。意味を書いたビジネスメタデータがあって初めて、安心して集計できます。組織のデータ資産をメタデータ付きで一覧にして検索できるようにした目録がデータカタログで、図書館の蔵書目録にたとえられます。用語と定義の一覧はデータ辞書です。
もう1つが、データの出所と加工の経路をたどるデータリネージ(系譜)です。想定ケースで使います。ダッシュボードの売上が1,050円で、元のPOS 3行の合計1,950円と合いません。下流から上流へ、各段の金額を確かめます。
| 経路(下流→上流) | 金額合計 | 判定 |
|---|---|---|
| ダッシュボード | 1,050円 | 元データと不一致 |
| 集計バッチの出力 | 1,050円 | 不一致のまま |
| ETLの出力(DWH 3行) | 1,950円 | 一致 |
| POS CSV(3行) | 1,950円 | 基準 |
ETLの出力までは1,950円で合い、集計バッチの出力で1,050円です。差は1,950−1,050=900円で、店舗S02の行の金額と一致するので、その行を落とした原因は集計バッチの条件と絞れます。カタログは「探す目録」、リネージは「原因をさかのぼる経路図」と覚えましょう。
分析のための置き場 — DWH・データマート・OLAPとBI
集めて整えたデータを分析のために蓄える場所が、データウェアハウス(DWH)です。W・H・インモンの定義では、次の4つの特徴を持つデータの集まりとされます。
| 特徴 | 意味 |
|---|---|
| サブジェクト指向(主題指向) | 業務アプリごとでなく、顧客・商品などの主題で整理する |
| 統合 | 複数ソースの表記や形式をそろえて一元化する |
| 時系列 | 時間軸で変化の履歴を保持する |
| 不揮発 | 格納後は原則として上書き・削除せず蓄積する |
業務システムが担うOLTPと、DWHでの分析を指すOLAPは目的が違います。
| 観点 | OLTP | OLAP |
|---|---|---|
| 目的 | 注文・在庫更新などの業務処理 | 集計と傾向分析 |
| 1回の処理 | 1件から少数件の読み書き | 大量の履歴データの集計 |
| データの置き場 | 業務システムのDB | DWH・データマート |
DWHから営業部門や経理部門などの用途別に切り出した小規模な分析用データベースがデータマートで、必要な範囲だけを持つので使いやすくなります。そのデータをダッシュボードやグラフで見せ、意思決定を支援するツールの総称がBIです。
OLAPの代表的な操作を、売上3行(想定)の店舗×商品の集計表で確かめましょう。
| 店舗\商品 | A-001 | A-002 | 合計 |
|---|---|---|---|
| S01 | 600 | 450 | 1,050 |
| S02 | 900 | 0 | 900 |
| 合計 | 1,500 | 450 | 1,950 |
ロールアップは粒度を粗くする操作で、店舗別の1,050と900を足して全社の1,050+900=1,950にします。ドリルダウンは逆に細かくする操作で、S01の1,050を商品別の600と450に分けます。スライスは1つの軸(店舗=S01)で断面を切り出す操作で、S01の行だけを見ると600と450です。ダイスは複数の軸でそれぞれ値を絞り込む操作です。この表は店舗と商品の2軸だけなので、月の軸を加えた表なら「店舗S01、商品A-001、4月だけ」のように、立方体の一部を切り出せます(月別の数値は省略します)。
ここまでの要点 — 統合から分析までを5分で復習
- ETLは抽出→変換→格納、ELTは抽出→格納→変換です。違いは変換の位置で、バッチかリアルタイムかとは別の軸です
- 名寄せは正規化→照合→採用元の決定の順に進めます。作った信頼できる唯一のマスターがゴールデンレコードです
- メタデータはビジネス・テクニカル・オペレーショナルの3種で、カタログは探す目録、リネージは加工経路をたどる仕組みです
- DWHの4特徴は、サブジェクト指向・統合・時系列・不揮発(インモンの定義)で、データマートは用途別の小規模な分析用DBです
- OLTPは1件ずつの業務処理、OLAPは大量の履歴の集計です。操作はドリルダウン・ロールアップ・スライス・ダイスです
- 試験での問われ方:ETLとELTの順序、カタログとリネージの違い、OLTPとOLAPの対比を、事例に当てはめて見分ける形が中心です
学習した内容を予想模試で確認しよう。2027年新設のデータマネジメント試験を想定した全60問に挑戦できます。
データマネジメント試験 予想模試60問に挑戦する