Power Query へそのゴマ　第２１章　データモデル

データモデルは、データを効果的に整理し、Power BIやExcelでの分析を簡単に行うための基盤です。特に、ファクトテーブルとディメンションテーブルを活用して構築するスタースキーマやスノーフレークスキーマは、パフォーマンスと可読性を高める設計方法として広く採用されています。本章では、データモデルの基礎から高度なテクニックまで、Power QueryとDAXの役割を交えながら解説します。

# 21.1 データモデルの基礎

## 21.1.1 ファクトテーブルとは

ファクトテーブルは、ビジネスのパフォーマンスを測定するための数値データを含みます。例えば、売上高、利益、販売数量などが該当します。ファクトテーブルは通常、以下の特徴を持っています。

- 数値データを含む
- 外部キーとしてディメンションテーブルへの参照を持つ
- 行数が多くなる傾向がある

**例**: 売上データ
  | **OrderID** | **ProductID** | **CustomerID** | **SalesAmount** | **Date**       |
  |-------------|---------------|----------------|------------------|----------------|
  | 1           | P001          | C001           | 100              | 2023-01-01     |
  | 2           | P002          | C002           | 200              | 2023-01-02     |

## 21.1.2 ディメンションテーブルとは

ディメンションテーブルは、ファクトテーブルの数値データを補完するための属性情報を提供します。例えば、顧客情報、製品情報、時間情報などが含まれます。ディメンションテーブルの特徴は以下の通りです。

- 説明的な属性を持つ
- 外部キーとしてファクトテーブルへの参照を持つ
- 行数が比較的少ない

**例**: 顧客データ
  | **CustomerID** | **Name**       | **Region** |
  |----------------|----------------|------------|
  | C001           | John Doe       | North      |
  | C002           | Jane Smith     | South      |

# 21.2 Power QueryとDAXの役割

データモデルを構築する際、**Power Query**と**DAX**（Data Analysis Expressions）は異なる役割を担います。それぞれの機能を適切に使い分けることで、効率的で柔軟なデータモデルを構築できます。

## 21.2.1 Power Queryの役割

Power Queryは、データを取り込み、変換、整形するためのツールです。データクレンジングや統合、不要なデータの削除などの前処理を行い、データモデルの基盤を作成します。

**主な役割**:
  1. **データ取得と統合**: 異なるデータソース（Excel、CSV、SQLなど）からデータを取り込み、統合する
  2. **データ変換**: 列の追加、データ型の変更、フィルタリングなどを行い、分析に適した形式に整形する
  3. **正規化または非正規化**: テーブルの構造を最適化してリレーションシップを整理する

## 21.2.2 DAXの役割

DAXは、データモデルに基づいて計算を行うための関数言語です。分析用のメジャーや計算列を作成し、データモデルに動的な計算機能を追加します。

**主な役割**:
  1. **集計と分析**: 売上合計、平均、割合などの計算を実現する
  2. **動的フィルタリング**: 条件に応じてデータをフィルタリングして結果を動的に変化させる
  3. **タイムインテリジェンス**: 年次、月次、前年同期比などの時間ベースの分析をサポートする


# 21.3 データモデル設計の実践

## 21.3.1 スタースキーマの構築

スタースキーマは、その名の通り、星の形をした構造を持っています。中心にファクトテーブルがあり、その周囲に複数のディメンションテーブルが配置されます。この構造により、データのクエリがシンプルになり、パフォーマンスが向上します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/5c4b5c92-b4b3-bae6-5d01-bb9587a0f2f1.png)

- **例**: 売上分析モデル
  - **ファクトテーブル**: `Sales`
  - **ディメンションテーブル**: `Products`, `Customers`, `Date`

- **リレーションシップの例**
  ```plaintext
  Products[ProductID] → Sales[ProductID]
  Customers[CustomerID] → Sales[CustomerID]
  ```

**スタースキーマの利点**

スタースキーマには多くの利点があります。主な利点は以下の通りです。

- **クエリのパフォーマンス向上**: シンプルな構造により、クエリの実行速度が向上します。
- **理解しやすさ**: データモデルが直感的であるため、ビジネスユーザーにも理解しやすいです。
- **データの整合性**: ディメンションテーブルを通じて、データの整合性が保たれます。

**実装のポイント**

スタースキーマを実装する際のポイントは以下の通りです。

- **適切なファクトとディメンションの選定**: ビジネスニーズに基づいて、どのデータをファクトとして扱うか、どの属性をディメンションとして扱うかを慎重に選定します。
- **正規化と非正規化のバランス**: ディメンションテーブルは通常、非正規化されているため、データの冗長性を考慮しつつ、パフォーマンスを最適化します。
- **インデックスの活用**: クエリのパフォーマンスを向上させるために、適切なインデックスを設定します。

スタースキーマは、データウェアハウスにおけるデータモデリングの基本的な手法であり、ビジネスインテリジェンスの実現に向けて非常に有効です。ファクトテーブルとディメンションテーブルの明確な分離により、データの分析が容易になり、パフォーマンスが向上します。データ分析を行う際には、スタースキーマの構造を理解し、適切に実装することが重要です。

## 21.3.2 スノーフレークスキーマの構築

スノーフレークスキーマは、ファクトテーブルと呼ばれる中心的なテーブルと、複数のディメンションテーブルから構成されます。ディメンションテーブルはさらに正規化され、サブディメンションテーブルに分割されることが特徴です。この構造により、データの冗長性が減少し、データの整合性が向上します。

**特徴**

- **正規化**: ディメンションテーブルが正規化されているため、データの重複が少なく、データの整合性が保たれます。
- **複雑な構造**: スノーフレークスキーマは、複数のテーブルが関連し合うため、クエリが複雑になることがあります。
- **柔軟性**: データの追加や変更が容易で、ビジネスの変化に対応しやすいです。

**利点**

- **データの整合性**: 正規化により、データの重複が減少し、整合性が向上します。
- **ストレージの効率性**: データの冗長性が少ないため、ストレージの使用効率が向上します。
- **クエリの最適化**: データが整理されているため、特定のクエリに対して最適化が可能です。

**デメリット**

- **クエリの複雑さ**: 複数のテーブルを結合する必要があるため、クエリが複雑になりがちです。
- **パフォーマンスの低下**: 正規化された構造は、時にはパフォーマンスの低下を招くことがあります。
- **設計の難しさ**: スノーフレークスキーマの設計には、データの理解と経験が必要です。

スノーフレークスキーマは、データの整合性と効率性を重視したデータモデリング手法です。正規化された構造により、データの冗長性を減少させる一方で、クエリの複雑さやパフォーマンスの問題が生じることもあります。ビジネスニーズに応じて、スノーフレークスキーマを採用するかどうかを慎重に検討することが重要です。

# 21.4 陥りがちな失敗

## 21.4.1. 適切なデータモデリング手法を選ばない

データをすべて1つの大きなテーブルにまとめる「フラットモデル」を作ってしまうことがあります。これは初めは簡単に見えますが、大量のデータを扱うときにパフォーマンスが落ちたり、データの整理が難しくなったりします。

データをファクトテーブル（数値や計算用のデータ）とディメンションテーブル（カテゴリや属性のデータ）に分けて整理する「スタースキーマ」を使うことで、分析しやすくなり、動きも速くなります。

## 21.4.2. テーブルの粒度が一致していない

データの細かさ（粒度）が一致していないと、正確な分析ができません。たとえば、売上データが日単位なのに、カレンダーデータが月単位だと、正しい日付で計算できなくなります。

Power Queryでデータを処理して、すべてのテーブルが同じ粒度になるよう揃えます。

## 21.4.3. 不適切なリレーションシップ

テーブルをつなぐリレーションシップを正しく設定しないと、分析結果がおかしくなります。たとえば、多対多の関係をそのまま使うと、データが重複して計算されることがあります。

1対多のリレーションシップを作るようにし、多対多の問題がある場合は「ブリッジテーブル」という中間テーブルを作って解決します。

### 多対多リレーションシップ

多対多リレーションシップでは、2つのテーブル間で1つのレコードが複数の関連レコードを持つことが可能です。例えば、大学の学生と講義の関係です。一人の学生は複数の講義に参加でき、１つの講義には複数の学生が参加できます。

この関係をそのままモデル化すると、以下の課題が発生します。

- **データの重複**: 同じ情報が複数回計算され、誤った集計結果が得られる。
- **クエリの複雑化**: 複雑なリレーションシップを持つテーブルに対するクエリは、処理負荷が高くなる。
- **モデルの管理が困難**: 他のリレーションシップとの整合性を保つのが難しい。

### 橋渡しテーブル（ブリッジテーブル）

橋渡しテーブルは、多対多リレーションシップを解消するために使用される中間テーブルです。このテーブルには、関連する2つのテーブル間のリレーションシップを表すデータが含まれます。

**基本構造**:
橋渡しテーブルは、それぞれのテーブルに存在するキー（主キーまたは外部キー）をペアとして格納します。

**例**: 顧客と製品を結びつけるブリッジテーブル
- **Customers**: 顧客情報を格納。
- **Products**: 製品情報を格納。
- **CustomerProducts**: 顧客と製品の関係を示す橋渡しテーブル。

| **CustomerID** | **ProductID** |
|----------------|---------------|
| C001           | P001          |
| C001           | P002          |
| C002           | P001          |

**リレーションシップの設定**
- `Customers` と `CustomerProducts` を 1 対多のリレーションシップで接続。
- `Products` と `CustomerProducts` を 1 対多のリレーションシップで接続。

## 21.4.4. カーディナリティ（独自値数）の高いキーを使用する

GUIDや長い文字列をキーとして使うと、モデルの動きが遅くなります。キーとは、テーブルをつなぐために使う識別子のことです。

GUIDや文字列の代わりに、シンプルな数値型のキー（サロゲートキー）を使うようにします。

### サロゲートキー（Surrogate Key）とは

サロゲートキーは、データベースやデータモデルにおいて、各レコードを一意に識別するために使用される**人工的なキー**です。通常はシステムによって生成され、データの意味やビジネス上の属性とは直接関連しません。

**サロゲートキーの特徴**
1. **一意性**: レコードごとに一意であることを保証。
2. **シンプルなデータ型**: 通常、整数型やUUID（Universally Unique Identifier）を使用。
3. **データの意味を持たない**: 業務的な情報とは無関係な識別子。

**サロゲートキーの利点**

1. **一貫性のある識別子**
   - 業務要件やデータの内容に依存しないため、変更されることがない。

2. **リレーションシップの単純化**
   - テーブル間のリレーションをシンプルに保つ。

3. **パフォーマンスの向上**
   - 数値型のキーは検索や結合操作が高速。

4. **データ統合の容易さ**
   - 異なるシステムやデータソースからのデータ統合時に、競合や冗長性を回避。

**サロゲートキーの欠点**

1. **意味の欠如**
   - キー自体にはデータの内容に関する情報が含まれないため、意味を把握するには他の列の参照が必要。

2. **データの冗長性**
   - 自然キーがすでに存在する場合でも、サロゲートキーを追加することで冗長な情報が増える。

3. **複雑さの増加**
   - サロゲートキーを導入することで、データモデルが複雑になることがある。


### 自然キーとの違い

自然キーは、データの内容に基づく識別子です。

| **比較項目**     | **サロゲートキー**                          | **自然キー**                          |
|------------------|---------------------------------------------|---------------------------------------|
| **生成方法**      | システムによって生成                       | 業務データに基づく                   |
| **意味**         | データに意味を持たない                     | データの意味を反映                   |
| **変更の可能性**  | 基本的に変更されない                       | ビジネス要件の変化で変更される可能性 |
| **データ型**      | 数値型（例: 1, 2, 3）またはUUIDなど         | テキストや複数の列の組み合わせなど   |
| **パフォーマンス**| 一般的に高速                              | データ型や複雑さに依存               |


## 21.4.5. 必要以上の列や行をモデルに取り込む

すべてのデータをインポートすると、モデルが大きくなりすぎて遅くなります。使わないデータを取り込むと、必要な情報を見つけにくくなることもあります。

Power Queryで、必要な列や行だけを選んでインポートするようにします。

## 21.4.6. 時系列分析のためのカレンダーテーブルがない

日付フィールドだけを直接使っていると、年ごとの比較や累積計算がうまくいきません。カレンダーテーブルは、時系列の分析をするための基本です。

特定の粒度（日、月、年）を持つカレンダーテーブルを作り、日付フィールドとつなげます。これにより、分析がスムーズになります。

## 21.4.7. 誤ったDAX計算を使用

DAXで複雑な計算をすべて「計算列」で処理しようとすると、モデルが大きくなりすぎて遅くなります。

動的な計算が必要な場合は「計算列」ではなく「メジャー」を使い、必要なときにだけ計算列を行うようにします。

また、ファクトテーブルの列は全て非表示にし、リレーションシップを張ったディメンションテーブルと、メジャーを使うようにします。

## 21.4.8. データの更新やリフレッシュ設定が不適切

大量のデータをフルリフレッシュすると、更新に時間がかかり、サービスのパフォーマンスに影響します。

Power BIの「増分更新」を使うことで、新しいデータだけをリフレッシュし、時間を節約します。

## 21.4.9. モデルのネーミング規則が一貫していない

テーブルや列の名前を適当に設定していると、どのデータが何を表しているのか分かりにくくなり、混乱の原因になります。

すべての名前に意味を持たせ、一貫性のある命名規則を使います。たとえば、「SalesAmount」や「CustomerName」のように、何のデータか一目で分かる名前を付けます。


## 21.4.10. 直感的なビジュアル化が難しいデータモデル

リレーションシップが複雑で循環参照があると、モデル全体が分かりにくくなります。

モデルをできるだけ簡単にし、スタースキーマを作ります。複雑なロジックはPower QueryやDAXに分離します。モデルがシンプルであるほど、分析がしやすく、他の人も理解しやすくなります。

## まとめ

データモデルを正しく構築することで、分析の精度やスピードが向上します。適切なスキーマ設計、リレーションシップの管理、必要なデータだけを取り込むようにすることで、Power BIの性能を最大限に引き出せます。また、DAXやPower Queryの機能を使い分けることで、シンプルかつ効率的なモデル構築が可能になります。