SQL Serverに接続してインポートしたテーブルを結合する

 データベースに接続して一つのテーブルをインポートするのは比較的簡単であるが,複数のテーブルを結合した状態でインポートする方法が長らく分からないままだった.

 Power Query を使ってクエリを結合する方法で解決したので備忘録がてら記事とする.

データの取得

SQL Server への接続

 「データ」「データの取得」「データベースから」「SQL Server データベースから」と進む.

「データ」「データの取得」「データベースから」
「データ」「データの取得」「データベースから」
「サーバー」「データベース」を指定
「サーバー」「データベース」を指定

認証

 このデータベースは Windows 認証を用いているため,何も変更せずそのまま「接続」をクリックする.

認証法の選択
認証法の選択

 「暗号化のサポート」で注意書きが出るが,構わずにOKをクリックする.

暗号化のサポート
暗号化のサポート

ナビゲーターでテーブルを選択

 初期状態では「複数のアイテムの選択」にチェックが入っていない.

ナビゲーターの初期状態では「複数のアイテムの選択」のチェックがオフになっている
ナビゲーターの初期状態では「複数のアイテムの選択」のチェックがオフになっている

 ナビゲーターの「複数のアイテムの選択」のチェックをオンにすると複数のテーブルが選択できるようになる.

「複数のアイテムの選択」にチェックして目的のテーブルにチェックを入れる
「複数のアイテムの選択」にチェックして目的のテーブルにチェックを入れる

 ここで「読み込み」をクリックすると接続情報のみが記録される.

「クエリと接続」で選択したテーブルへの接続情報が作成される
「クエリと接続」で選択したテーブルへの接続情報が作成される

 「読み込み先…」を確認すると下図のようになっている.

接続情報のみ作成されている
接続情報のみ作成されている

 この後,ピボットテーブルを作成するのか,テーブルを作成するのかで選択肢が変わってくる.

ピボットテーブルを作成する場合

データモデルをデータソースとする

 「挿入」「ピボットテーブル」と進む.

ピボットテーブルの挿入
ピボットテーブルの挿入

 「ピボットテーブルの作成」で「このブックのデータモデルを使用する」にチェックが入っている.そのまま OK をクリックする.

「ピボットテーブルの作成」で「このブックのデータモデルを使用する」
「ピボットテーブルの作成」で「このブックのデータモデルを使用する」

ピボットテーブルのフィールド

 下図のようにチェックしたテーブルが二つ並んでいる.

ピボットテーブルのフィールド
ピボットテーブルのフィールド

 展開すると下図のようにフィールドが見えるようになる.

ピボットテーブルのフィールドでテーブルを展開したところ
ピボットテーブルのフィールドでテーブルを展開したところ

 この後はピボットグラフを作成していくのだが,そこは省略する.

スライサーの挿入

 ピボットテーブルにはスライサーなるものがある.テーブルのフィルターから,チェックボックスだけ引っ張り出したフローティングメニューのような代物である.

 「ピボットグラフ分析」「スライサーの挿入」と進む.

「ピボットグラフ分析」「スライサーの挿入」
「ピボットグラフ分析」「スライサーの挿入」

 下図のようなダイアログに遷移する.

「スライサーの挿入」でスライサーとするフィールドにチェックを入れる
「スライサーの挿入」でスライサーとするフィールドにチェックを入れる

スライサーとは何か?

 形式的には BI 界隈で言うところのディメンションである.リレーショナルデータベースの立場からは外部キーの一部である.ヒト,モノ,カネと言うが,事業を集計する際の集約のための次元である.

 SQL で言うところの GROUP BY に続くキーである.ここでは地方区分を意味する Region にチェックを入れるが,都道府県単位の粒度が必要なら PrefectureCode にチェックを入れることになる.

地方区分を意味するRegionにチェックを入れる
地方区分を意味するRegionにチェックを入れる

スライサーができた

 下図のようにスライサーができた.これはテーブルのフィルターのチェックボックスで「すべて選択」がチェックされているのと同じ状態である.

スライサーですべてにチェックが入っている状態
スライサーですべてにチェックが入っている状態

タイムラインの挿入は日付型のデータが入っていないと失敗する

 続けてタイムラインの挿入をしようとしたが,日付型のデータが無いとエラーが発生した.

「ピボットグラフ分析」「タイムラインの挿入」
「ピボットグラフ分析」「タイムラインの挿入」
日付型のデータがないため失敗する
日付型のデータがないため失敗する

テーブル間のリレーションシップ

 メジャーである D2211 というフィールドには数値型のデータが入っているのだが,このフィールドを「Σ値」のボックスにドロップすると EXCEL が自動的に警告を出してくる.

テーブル間のリレーションシップが必要であると警告してくる
テーブル間のリレーションシップが必要であると警告してくる

 確かに必要だ.自動検出をクリックしてみる.上手くいったらしい.

リレーションシップの検出と作成
リレーションシップの検出と作成

 念のため確認しておこう.「リレーションシップの管理…」をクリクする.

リレーションシップの管理

 下図の画面に遷移する.テーブルとキーが表示されている.大丈夫だと思うが,「編集…」をクリックしてさらに内容を確認する.

リレーションシップの管理
リレーションシップの管理

リレーションシップの編集

 「このリレーションシップに使用するテーブルと列の選択」と長い説明があるが,要はテーブル間の結合キーを示している.

リレーションシップの編集
リレーションシップの編集

 ふと疑問に思ったのだが,結合キーが一つなら上記ダイアログで良いのだが,二つ以上の結合キーが存在する場合はどうなるのだろう?

テーブルを作成する場合

読み込み先をテーブルに切り替える

 「クエリと接続」を右クリックして「読み込み先…」を選ぶ.

「クエリと接続」「読み込み先...」
「クエリと接続」「読み込み先…」

 「データのインポート」ダイアログで「テーブル」にチェックを付ける.

「データのインポート」で「テーブル」にチェック
「データのインポート」で「テーブル」にチェック

クエリの結合

 そのままインポートすると,テーブルが結合されずにインポートされるだけである.何か方法はないか探し回ってみると,「クエリ」タブがあるのに気がついた.「結合」とそれらしいキーワードが見える.

「クエリ」タブの「結合」
「クエリ」タブの「結合」

 クリックすると「マージ」画面に遷移する.「結合の種類」に「左外部」とある.それらしい匂いがしてきた.

「マージ」画面に遷移
「マージ」画面に遷移

 「結合の種類」をポップアップすると「左外部」「右外部」「完全外部」「内部」「左反」「右反」がメニューに現れる.最後の二つは不明だが,最初の四つでほぼ事足りる.

「結合の種類」をポップアップ
「結合の種類」をポップアップ

 テーブルと照合列を選択した状態である.これで一番下にチェックマークがついている.どうやらこれで大丈夫のようだ.OKをクリックする.

テーブルと照合列を選択した状態
テーブルと照合列を選択した状態

Power Query エディターに遷移する

 D2211 テーブルの右側に M_City テーブルが並んで表示されている.このまま慌てて「閉じて読み込む」を選んではいけない.

Power Query エディターに遷移
Power Query エディターに遷移

 M_City の列名の右側にそれらしいボタンがある.クリックしてみると下図のようにテーブルの列名がチェックボックス形式でポップアップする.

列を展開する必要がある
列を展開する必要がある

 不要な列のチェックを外し,OKをクリックする.これは下記のコードに該当する.

不要な列のチェックを外す
不要な列のチェックを外す

 無事,マスターの項目が展開された.

マスターの項目が展開された
マスターの項目が展開された

 「クエリと接続」では新しく Merge1 という名のクエリが出来ている.

新しいクエリ名が表示されている
新しいクエリ名が表示されている

 Power Query エディターの詳細エディターで DAX 式を確認してみる.

まとめ

 SQL Server に接続して複数のテーブルをインポートした.それらを Power Query で結合して EXCEL のテーブルにインポートした.クエリを結合することをマージという.結合キーが一つなら問題ないが,結合キーが複数の場合に課題が残る.その場合はデータベース側で結合するのが妥当と思われる.

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください