Go to Qiita Advent Calendar Top
LoginSignup
9
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

Day 5

BrainPad Advent Calendar 2025

@take-yoda(依田 健人)inBrainPad Inc.

SQLで理解するOLAP入門 - BIツールの多次元分析をPostgreSQLで完全再現

Last updated at Posted at 2025-12-04

株式会社ブレインパッドプロダクトユニットでRtoaster GenAIの開発をしている依田です。
今回はデータ分析で使われるOLAPとその基本について記します。

はじめに

Tableau1、Power BI2、Looker3、MotionBoard4などのBIツールを効果的に活用するには、その背後にある OLAP（Online Analytical Processing） の理解が欠かせません。

しかし、OLAPの解説を読んでも、抽象的な説明ばかりで実践的なイメージが湧きにくいと感じたことはありませんか。

この記事では、SQLの知識がある方を対象に、その課題を解決します。PostgreSQLの具体的なコード例を交えながら、OLAPの基本概念と代表的な操作（ドリリング、スライシング、ダイシング）を段階的に解説。本記事を通して、BIツールが高速に動作する仕組みの一つである、スタースキーマを中心としたデータ構造について理解できます。

OLAPとは

OLAP（Online Analytical Processing：オンライン分析処理）は、大量のデータを多次元的に分析するための技術です。売上分析、財務分析、マーケティング分析など、ビジネスの意思決定に必要な情報を素早く取得することを目的としています。

OLTPとの違い

OLAPを理解する上で、OLTP（Online Transaction Processing：オンライントランザクション処理）との違いを把握しておくことが重要です。

観点 OLTP OLAP
主な用途 日常業務処理 データ分析・意思決定支援
処理の特徴 大量の小さなトランザクション 少数の大きなクエリ
データの更新 頻繁（INSERT/UPDATE/DELETE） 定期的なバッチロード
データの鮮度 リアルタイム 過去データ中心
データモデル 正規化されたリレーショナル 非正規化（スタースキーマ、スノーフレークスキーマ）
応答時間 ミリ秒～秒 秒～分
データ量 GB～TB TB～PB

例えば、ECサイトで注文を登録する処理はOLTP、過去の売上データを集計して傾向を分析する処理がOLAPです。

多次元分析の概念

OLAPの最大の特徴は「多次元分析」が可能なことです。ビジネスデータは複数の「次元」で分析できます。

次元（Dimension）とメジャー（Measure）

データ分析では、「次元」と「メジャー」の要素を区別して使い分けます。次元とは分析軸となる属性、具体的には、時間や地域、商品カテゴリなどが挙げられます。メジャーとは、分析対象となる数値、具体的には、売り上げ金額や販売数量、粗利などが挙げられます。
例えば、売上データを分析する場合は以下のようなSQLでデータを集計します。

-- 通常のSQLクエリ例
SELECT
    year,
    quarter,
    region,
    product_category,
    SUM(sales_amount) as total_sales,
    SUM(quantity) as total_quantity
FROM
    sales_fact
GROUP BY
    year, quarter, region, product_category;

この例では、次元にyear（年）、quarter（四半期）、region（地域）、product_category（商品カテゴリ）を使用しています。メジャーにはtotal_sales（売上金額）、total_quantity（販売数量）を使用しています。

キューブ（Cube）

多次元データは「キューブ」という概念で表現されます。3次元以上のデータであっても「キューブ」と呼ばれます。

3次元キューブの例

「商品カテゴリ × 地域 × 時間」の3次元キューブは下図のように表現できます。

image.png
図1: キューブ

各セル（立方体の1マス）には、その組み合わせに対応するメジャー値が格納されます。

4次元以上のキューブ

実務では、3次元を超える多次元データを扱うことが一般的です。一例としては「時間」、「地域」、「商品カテゴリ」、「顧客セグメント」などの組み合わせも考えられます。

SELECT
    year,
    region,
    product_category,
    customer_segment,
    SUM(sales_amount) as total_sales
FROM
    sales_fact
GROUP BY
    year, region, product_category, customer_segment;

もちろん、それ以上のキューブを扱うこともあります。前述した4つの次元に加え、「販売チャネル」、「プロモーション方法」、「配送方法」などを加えると、次元の数は7となります。
この7次元キューブでは、以下のような分析が可能です。

  • 2025年第3四半期（10-12月）の関東地域における電化製品の売上（法人顧客向け、ECサイト経由、年末キャンペーン適用、速達配送）
  • 特定の条件下での売上パフォーマンスを多角的に分析
SELECT
    sale_date,
    region,
    product_category,
    customer_segment,
    sales_channel,
    promotion_type,
    shipping_method,
    SUM(sales_amount) as total_sales,
    SUM(quantity) as total_quantity,
    COUNT(*) as transaction_count
FROM
    sales_fact
GROUP BY
    sale_date,
    region,
    product_category,
    customer_segment,
    sales_channel,
    promotion_type,
    shipping_method;

7次元ともなると複雑に感じますが、業態によっては珍しくない数です。

多次元キューブのデータモデル実例（PostgreSQL）

ここでは、実際にPostgreSQLで多次元キューブを構築する例を示します。
OLAP分析では、通常、データウェアハウス（DWH）と呼ばれる分析専用のデータベースにデータを格納します。DWHは、日々の業務システム（OLTP）からデータを抽出・変換・ロードして構築されます。

スタースキーマ

OLAPのデータモデルとして最も一般的なのがスタースキーマです。スタースキーマは、中心に1つのファクトテーブル（売上や受注などのデータを格納）と、周囲に複数のディメンションテーブル（時間、商品、顧客などの分析軸となるマスタデータを格納）で構成されます。そして、ファクトテーブルから各ディメンションテーブルへ放射上に外部キーが伸びる星型の構造になることが特徴です。

image.png
図2: スタースキーマ

通常のOLTPデータベースは正規化（重複排除・テーブル分割）されています。一方、スタースキーマは非正規化（意図的なデータ重複・テーブル統合）されており、JOIN数を減らして分析クエリを高速化しています。

ディメンションテーブルをさらに正規化したスノーフレークスキーマという構成もありますが、本記事ではシンプルなスタースキーマを使用します。

テーブル構成

それでは、スタースキーマ構成で、1つのファクト表と複数のディメンションテーブルを作成します。

1. 時間ディメンション

2. 地域ディメンション

3. 商品ディメンション

4. 顧客セグメントディメンション

5. 販売チャネルディメンション

6. プロモーションディメンション

7. 配送方法ディメンション

ファクトテーブル

サンプルデータの投入

ディメンションテーブルのサンプルデータ

ファクトテーブルのサンプルデータ

7次元キューブを活用したクエリ例

全次元での集計クエリは以下のようになります。

SELECT
    dt.fiscal_year,
    dt.quarter,
    dr.region_name,
    dp.category,
    dcs.segment_name,
    dsc.channel_name,
    dpr.promotion_name,
    ds.shipping_name,
    SUM(fs.sales_amount) as total_sales,
    SUM(fs.profit_amount) as total_profit,
    COUNT(*) as transaction_count
FROM
    fact_sales fs
    JOIN dim_time dt ON fs.time_id = dt.time_id
    JOIN dim_region dr ON fs.region_id = dr.region_id
    JOIN dim_product dp ON fs.product_id = dp.product_id
    JOIN dim_customer_segment dcs ON fs.segment_id = dcs.segment_id
    JOIN dim_sales_channel dsc ON fs.channel_id = dsc.channel_id
    JOIN dim_promotion dpr ON fs.promotion_id = dpr.promotion_id
    JOIN dim_shipping ds ON fs.shipping_id = ds.shipping_id
WHERE
    dt.fiscal_year = 2025
    AND dt.quarter = '3Q'
GROUP BY
    dt.fiscal_year, dt.quarter, dr.region_name, dp.category,
    dcs.segment_name, dsc.channel_name, dpr.promotion_name, ds.shipping_name
ORDER BY
    total_sales DESC;

別の例として、特定条件下での詳細を分析するクエリは以下の通りです。

SELECT
    dt.sale_date,
    dp.product_name,
    fs.quantity,
    fs.unit_price,
    fs.sales_amount,
    fs.profit_amount
FROM
    fact_sales fs
    JOIN dim_time dt ON fs.time_id = dt.time_id
    JOIN dim_region dr ON fs.region_id = dr.region_id
    JOIN dim_product dp ON fs.product_id = dp.product_id
    JOIN dim_customer_segment dcs ON fs.segment_id = dcs.segment_id
    JOIN dim_sales_channel dsc ON fs.channel_id = dsc.channel_id
    JOIN dim_promotion dpr ON fs.promotion_id = dpr.promotion_id
    JOIN dim_shipping ds ON fs.shipping_id = ds.shipping_id
WHERE
    dr.region_code = 'KANTO'
    AND dp.category = '電化製品'
    AND dcs.segment_code = 'B2B'
    AND dsc.channel_code = 'EC'
    AND dpr.promotion_code = 'WINTER25'
    AND ds.shipping_code = 'EXPRESS'
ORDER BY
    dt.sale_date;

以降の章では、このデータモデルを使いながらキューブの代表的な操作を見ていきます。

ドリリング操作

ドリリングは、データの詳細度（粒度）を変更する操作です。主に3つの種類があります。

ドリルダウン（Drill Down）

下図のように、より詳細なレベルへ掘り下げる操作です。

例えば、年次の集計データから四半期別へ、さらに月次別へと、徐々に細かい粒度で分析していきます。

image.png
図3: ドリルダウンのイメージ

SQLにおけるクエリ例、グラフ例は以下の通りです。

レベル1: 年次集計

SELECT
    dt.fiscal_year,
    SUM(fs.sales_amount) as total_sales
FROM
    fact_sales fs
    JOIN dim_time dt ON fs.time_id = dt.time_id
WHERE
    dt.fiscal_year = 2025
GROUP BY
    dt.fiscal_year;

image.png
グラフ1: 年次売上高

レベル2: 四半期集計

年次集計から1階層ドリルダウンします。

SELECT
    dt.fiscal_year,
    dt.quarter,
    SUM(fs.sales_amount) as total_sales
FROM
    fact_sales fs
    JOIN dim_time dt ON fs.time_id = dt.time_id
WHERE
    dt.fiscal_year = 2025
GROUP BY
    dt.fiscal_year, dt.quarter;

image.png
グラフ2: 期別売上高

レベル3: 月次集計

四半期単位から、さらにドリルダウンして詳細化します。

SELECT
    dt.fiscal_year,
    dt.quarter,
    dt.month,
    SUM(fs.sales_amount) as total_sales
FROM
    fact_sales fs
    JOIN dim_time dt ON fs.time_id = dt.time_id
WHERE
    dt.fiscal_year = 2025
GROUP BY
    dt.fiscal_year, dt.quarter, dt.month
ORDER BY
    TO_CHAR(MIN(dt.sale_date), 'YYYYMM');

image.png
グラフ3: 月別売上高

例えば、年間売上が目標に届かなかったとき、「どの四半期で失速したのか」「特にどの月が悪かったのか」を深掘りしていく際に使います。

ドリルアップ（Drill Up）

より粗いレベルへ集約する操作で、ドリルダウンの逆です。

image.png
図4: ドリルアップのイメージ

SQLのクエリ例と、グラフ例はドリルダウンのものと同じなので割愛します。
ドリルダウンとは逆に、細かい月次データではノイズが多すぎるとき、四半期や年次に集約して全体トレンドを掴みたい場合に便利です。

ドリルスルー（Drill Through）

集計データから元の詳細データ（トランザクションレベル）へアクセスする操作です。先に示したグラフ3の中から、11月分の注文明細を出力するような操作になります。

SELECT
    dt.sale_date,
    dr.region_name,
    dp.category,
    dcs.segment_name,
    dsc.channel_name,
    dpr.promotion_name,
    ds.shipping_name,
    fs.sales_amount,
    fs.profit_amount
FROM
    fact_sales fs
    JOIN dim_time dt ON fs.time_id = dt.time_id
    JOIN dim_region dr ON fs.region_id = dr.region_id
    JOIN dim_product dp ON fs.product_id = dp.product_id
    JOIN dim_customer_segment dcs ON fs.segment_id = dcs.segment_id
    JOIN dim_sales_channel dsc ON fs.channel_id = dsc.channel_id
    JOIN dim_promotion dpr ON fs.promotion_id = dpr.promotion_id
    JOIN dim_shipping ds ON fs.shipping_id = ds.shipping_id
WHERE
    dt.fiscal_year = 2025
    AND dt.month = 'Nov'
ORDER BY
    dt.sale_date;

集計結果に異常値を見つけたとき、「どの取引が原因なのか」と元データを確認したくなります。そんなときに使うのがドリルスルーです。

ドリリング操作の違い

操作 方向 粒度の変化 データレベル
ドリルダウン 下へ 粗い → 細かい 集計データ内
ドリルアップ 上へ 細かい → 粗い 集計データ内
ドリルスルー 横断 集計 → 詳細 集計データ → トランザクションデータ

スライシング（Slicing）

キューブから特定の1つの次元の値を固定して、部分的なデータを切り出す操作です。いくつか具体例を見ていきます。

まずは、「2025年2Q」という時間次元の特定値で切り出します。図にすると下図のように黄色い部分をキューブから取り出す形です。キューブの全体を食パンに見立てて、薄くスライスした一枚を取り出すようなイメージになります。

image.png

図5: 時間軸でのスライシングイメージ

SELECT
    dr.region_name,
    dp.category,
    SUM(fs.sales_amount) as total_sales
FROM
    fact_sales fs
    JOIN dim_time dt ON fs.time_id = dt.time_id
    JOIN dim_region dr ON fs.region_id = dr.region_id
    JOIN dim_product dp ON fs.product_id = dp.product_id
WHERE
    dt.fiscal_year = 2025
    AND dt.quarter = '2Q'
GROUP BY
    dr.region_name,
    dp.category
ORDER BY
    dr.region_name,
    dp.category;

image.png
グラフ4: 2025年2Qの地域/商品カテゴリ別売上高

「2Qの業績だけ見たい」といったように、特定の期間に絞って地域別・商品別の内訳を分析する際に使います。

もちろん、スライシングは時間軸以外の次元でも行うことが可能です。もう1つの具体例として、地域次元でスライスするクエリ例を以下に示します。

SELECT
    dp.category,
    SUM(fs.profit_amount) as total_profit
FROM
    fact_sales fs
    JOIN dim_region dr ON fs.region_id = dr.region_id
    JOIN dim_product dp ON fs.product_id = dp.product_id
WHERE
    dr.region_name = '関東' -- この条件がスライシング
GROUP BY
    dp.category
ORDER BY
    dp.category;

このクエリで、関東エリアだけのカテゴリ別利益を取得できます。

ダイシング（Dicing）

複数の次元で条件を指定して、キューブから小さな部分キューブを切り出す操作です。スライシングを複数の次元で同時に行います。視覚的には、大きなキューブから小さな「サイコロ（dice）」を切り出すイメージです。具体例として、「関東地域かつ電化製品かつ販売チャネルがオンライン」のキューブを切り出してみましょう。

image.png
図6: ダイシングのイメージ

SELECT
    dp.subcategory,
    dsc.channel_name,
    SUM(fs.sales_amount) as total_sales
FROM
    fact_sales fs
    JOIN dim_region dr ON fs.region_id = dr.region_id
    JOIN dim_product dp ON fs.product_id = dp.product_id
    JOIN dim_sales_channel dsc ON fs.channel_id = dsc.channel_id
WHERE
    dr.region_name = '関東'            -- 地域次元の条件
    AND dp.category = '電化製品'        -- 商品次元の条件
    AND dsc.channel_type = 'オンライン' -- チャネル次元の条件
GROUP BY
    dp.subcategory,
    dsc.channel_name;

「関東のオンライン販売に絞って、電化製品の売れ行きを見たい」のように、複数の条件を組み合わせて分析範囲を絞り込む際に活用します。

まとめ

この記事では、OLAPの基本概念と代表的な操作について解説しました。

  1. OLAP vs OLTP: OLAPは分析用、OLTPは業務処理用
  2. 多次元分析: 次元（分析軸）とメジャー（数値）でデータを多角的に分析
  3. ドリリング:
    • ドリルダウン: 詳細レベルへ
    • ドリルアップ: 集約レベルへ
    • ドリルスルー: 集計から元データへ
  4. スライシング: 1つの次元で切り出し
  5. ダイシング: 複数の次元で切り出し

これらの操作を組み合わせることで、様々な角度からデータを分析できるようになります。

次のステップ

OLAPの基本を理解したら、以下のトピックに進むことをお勧めします。

  • スタースキーマ・スノーフレークスキーマ（OLAPのデータモデリング）
  • ウィンドウ関数を使った高度な分析SQL
  • OLAP集計を加速するPostgreSQL固有のSQL句（ROLLUPとCUBE）
  • 具体的なBIツールの使い方
  • データウェアハウスの設計

ROLLUP句とCUBE句
本記事では標準的なGROUP BY句を用いて集計を行いましたが、PostgreSQLでは、OLAPの多次元集計をより簡潔に、かつ効率的に記述できるROLLUP句やCUBE句が提供されています。

  • ROLLUP: 階層的な小計（Subtotal）を一度に計算し、ドリルダウン/アップの集計を効率化します
  • CUBE: 全ての次元の組み合わせ（全パターンの集計）を一度に計算し、多次元分析を効率化します

BIとデータ分析の世界へようこそ。この記事が、その第一歩となれば幸いです。

  1. https://www.brainpad.co.jp/services/products/Tableau.html ↩

  2. https://www.microsoft.com/ja-jp/power-platform/products/power-bi ↩

  3. https://cloud.google.com/looker?hl=ja ↩

  4. https://www.wingarc.com/product/motionboard/ ↩

9
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
9
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?