仕事をする上で、SQL Server について次のようなお悩みはありませんか？
- 必要なデータが SQL Server に入っているらしいが、利用方法が分からない
- SQL Server Management Studio からデータをコピー＆ペースト するのが面倒
このようなお悩みは Excel の Power Query を使えば初心者でも簡単に解決できます。
本記事は Power Query による SQL Server 活用 の基礎から応用の知識 をご紹介します。
皆様も是非 SQL Server のデータを活用して仕事力を上げていきましょう。
Power Query で SQL Server のデータを活用するメリット
SQL Server へのアクセス手段は、SQL Management Studio をはじめ、各種プログラミング言語や、BIツールなど様々あります。
通常では入手したデータを Excel で加工して集計したり、グラフ化したりすると思います。
Power Query なら入手したデータを シームレスに Excel 内で活用 することができます。具体的には次のような活用方法が想定されます。
- Power Query で 加工・集計して表を出力
- Power PIvot で 自在にデータを集計・分析・グラフ化
- Excel の SUMIFS関数 など で集計＆グラフ化
このように、Power Query は データ取得から最終アウトプットまで Excel で完結 することができ、業務効率が高くなる のが大きなメリットと言えます。
では早速、Power Query で SQL Server のデータを取得する方法をみていきましょう。
SQL Server 接続 標準手順
SQL Server の接続もほとんどがマウス操作でできてしまいます。図のようなステップで操作してください。
まず、Excel のメニューから操作します。
続いてサーバー名の入力画面になります。
利用したい SQL Server のサーバ名を入力します。通常ならサーバーのコンピュータ名 を入力をすればOKです。コンピューター名はシステム管理者に確認するなどして情報を入手してください。
接続でエラーになる場合
このサーバー名の指定の仕方によっては接続できない場合があります。エラーになってしまう場合は、以下の入力方法のいずれかを試してみて下さい。
- [サーバー名] （例： “DBServer”）
- [サーバー名]\[インスタンス名] （例： “DBServer\sqlexpress”）
- [サーバー名]\[インスタンス名],[ポート番号] （例： ”DBServer\sqlexpress,1433″)
インスタンス名：
インスタンス名とは、SQL Server のサービスつける名前です。一つのコンピュータに SQL Server のサービス（インスタンス）を複数立ち上げることが可能です。そのため、サービス（インスタンス）を指定するためにインスタンス名が必要になります。
SQL Server Express 版 の場合は、デフォルトで “sqlexpress” がインスタンス名に設定されます。
ポート番号：
SQL Server のネットワーク上の接続ポートです。こちらも デフォルトでは “1433” になります。接続できない場合はシステム管理者に確認してみて下さい。
続いて、資格情報の設定画面になります。これは SQL Server へのログイン資格の情報になります。
SQL Server へのログインは以下の2種類あります。ご自分が得ているアクセス権に合わせて入力してください。
Widows 認証（⇒ 現在の資格情報を使用する）
会社や学校などの 組織で Windows へのログインIDが与えられているケース で、このログインIDに対して、SQL Server 側でアクセス許可を設定されているケースです。
例えば、組織内の全員に対してデータの参照権限を付与されている場合は、この Windows 認証を利用できます。
この場合は、この設定画面では「現在の資格情報を使用する」のまま進めてOKです。
SQL Server 認証（⇒ 代替の資格情報を使用する）
SQL Server 側で独自にユーザIDを管理しているケース です。この場合は、SQL Server にログインするためのユーザIDとパスワードを入手する必要があります。
SQL Server の管理者からユーザIDとパスワードを入手してこの画面の入力欄に入力します。
続いて、暗号化サポートのメッセージが表示される場合があります。
LAN上で SQL Server を立ち上げる場合は、この暗号化のサポートはされていないケースもあります。
組織内でセキュリティの問題が無い場合はこのまま「OK」で進めます。
続いて、ナビゲーター・ウィンドウで SQL Server 上のどのデータを取得するかを選択します。
最初に開いた画面では、データベースの中身が展開されていないので、図の ① のように ▼マークを押して展開します。
すると、データベース上のテーブルやビューが表示されます。図の② のように、この中から必要なものを選択します。
次の処理の3つの選択肢
図の③ で次の処理については３つの選択肢があります。それぞれについて解説します。
「読み込み」
図の「読み込み」を押すと直ぐにデータ読み込みが開始され、読み込み結果は Excel のテーブルに出力されます。
これを選択してしまうと、Excel 上のテーブルに SQL Server のデータ全体がそのまま出力されてしまい、ファイルや動作が重くなってしまうので、あまり推奨できません。
「読み込み先…」
図の「読み込み先…」を押すと、読み込んだデータをどう扱うかのウィンドウが表示されます。データをそのまま利用する場合は図のように設定するのがオススメです。
しかし、これも不要なデータを大量に取得してしまう可能性があるため、あまり推奨できません。
「データの変換」
データベースには大量のデータが格納されており、それを全て取り寄せて使うことはあまりありません。
データは絞られた視点で評価するのが通常です。例えば、「今年度の売上の推移を確認する」とか、「先月の地区別の受注状況を確認する」などです。
これらの評価をするために、過去何年分もの大量のデータを取得する必要はありません。そうしてしまうと、データ容量やコンピュータの計算能力の両方の観点で無駄が発生してしまいます。
そのため、ここでは「データの変換」ボタンを押して、引き続き Power Query エディターでデータの編集を行うことが推奨されます。
取得データの絞り込みについては次の項から詳しく解説します。
データベース活用上の課題と対策
データベースは膨大なデータを格納できますが、通常はその一部にアクセスして利用します。
先ほどご紹介したように、データベースの テーブルやビューを丸ごと取得してしまうと、膨大なデータをダウンロードすることになってしまいます。そうすると、以下の課題が生じます。
- パフォーマンスの低下: データ量が多いと、クエリの実行速度が遅くなり、応答性が低下します。
- メモリ使用量の増加: 大量のデータをメモリ上で処理するため、メモリ使用量が増加し、システムの安定性が損なわれることがあります。
- ロード時間の延長: データのインポートやロードに時間がかかるため、全体の処理時間が長くなります。
そのため、Power Query でデータベースを活用する場合は、次のようなことに配慮が必要です。
- 必要な範囲に絞り込んで データを取得
- データ処理もなるべく データベース側で処理 させる
Power Query にはこのための機能「クエリフォールディング」が搭載されています。
【対策】クエリフォールディングを使う
クエリフォールディングとは Power Query エディターで組んだデータ処理を可能な限りデータベース側で処理させる機能 です。
データベースで様々なデータ処理をした上で、コンパクトになったデータを Power Query で受信するとでユーザーのPCの負荷を減らすことが可能です。
クエリフォールディングを機能させる
Power Query でデータベースのデータ処理の手順を組んでいると自動でクエリフォールディングが適用されますが、それでも全ての処理に対してクエリフォールディングが適用される訳ではありません。
クエリフォールディングが適用 されるためには、そのデータ処理をデータベース側で実行できる必要があります。
クエリフォールディングが適用できるデータ処理
- 列、行の絞り込みの処理
- 四則演算
- グループ集計
クエリフォールディングが適用できないデータ処理
- SQL言語に無い Power Query の M関数 の処理
- データベース側に無いテーブル との結合
- それまでの処理で 一度クエリフォールディングが途切れている
データ量を縮小する
クエリフォールディングでデータを縮小するために、データベースの テーブル読み込み後の最初の処理 として次の処理をします。
- 列の絞り込み： 不要な列を削除
- 行の絞り込み： いくつかの列でフィルター処理をしてデータを絞り込む
このようなデータ処理手順を組むことで、 Power Query は データベースに必要なデータだけを要求するコマンド を送信します。
データベースに送信するコマンド のことをPower Query エディターでは「ネイティブクエリ」と呼んでいます。これはデータベースで使われる SQLのコマンド そのものです。
クエリフォールディングの処理を確認する
実際にデータベースに接続した後に、不要な列と行の削除を行った Power Query でクエリフォールディングの処理がなされていることを確認してみます。
図のようにデータの削除処理まで行ったステップを右クリックしてメニューを出して、「ネイティブ クエリを表示」を選択します。
すると、図のように ネイティブ クエリ（SQLコマンド）の内容が表示されます。中身を見ると Power Query で組んだ 列の絞り込み と、行の絞り込み が適用されていることが分かります。
これによりデータベースから取得するデータを最初から小さくして、大規模データの課題を回避することが可能です。
毎回の動作で同じ絞り込み条件で良ければ クエリフォールディング は有効です。しかし、毎回の処理で絞り込み条件を変えたい場合には対応できません。
続いて、このための工夫について解説します。
クエリ処理をセル入力値で変更する
前の項のようにあらかじめ決められたデータの絞り込みをするクエリを作成することは比較的簡単です。
しかし、Power Query の処理をユーザーのニーズに応じて毎回変わるよう にしたいケースもあります。
この課題の解決策として、以下の流れで処理する方法をご紹介します。
ユーザーニーズに応じてデータの絞り込みを変更する手順
- ユーザーに データを絞り込む条件 をセルに入力して貰う
- Power Query の M数式 を活用して ユーザーが入力したセルの値を取得
- 読み込んだ値を使って、Power Query 上で SQLクエリ文を作成
- 作成した SQLクエリ文 を Power Query の SQL Server 接続コマンドで実行してデータを取得
設定方法
では、具体的な設定を順番にご説明します。
操作手順
- Excelシートのどこかに ユーザーの入力欄を作成 し、その セルに名前をつけます。ここでは「ユーザー入力１」としておきます。
- Power Query の詳細エディター に下記のコードを記述します。
let
ユーザー入力値 = Excel.CurrentWorkbook () {[Name = "ユーザー入力1"]} [Content] {0} [Column1],
sqlcom = "select * from SALES where SHOP = '" & ユーザー入力値 & "'",
ソース = Sql.Database("ServerName,1433", "TestDB", [Query=sql])
in
ソース上記のコードは下記の処理をしています。1行ごとに読んで理解して頂くと次から応用できるようになると思います。
コード解説
- １行目：「ユーザー入力１」と名前がついた セルの値を “ユーザー入力値” という変数に格納。
- ２行目：SQLクエリ文を作成します。SELECT文にWHERE句をつけて出力を絞り込みます。
この例では [SHOP]列 = ”ユーザー入力値” となる行のみを取得。 - ３行目：できた SQLクエリ文（変数 “sqlcom”）を SQL Server に送信 して実行結果のデータを取得します。
操作は以上です。
以上のようなクエリを作成することで、ユーザーのニーズに応じたデータを取得 できます。
大規模なデータであっても、このやり方で 動的なデータ絞り込み を設定することで実用的て 軽量なクエリを作成 することができます。
ここで、「セルに名前をつける方法」についても解説しておきます。
セルに名前を付ける方法
Excelのセルに名前をつける方法は簡単ですが、メニューを探しても分かりにくいので解説させて頂きます。
下の図のように名前をつけたいセルを選択して、メニューの セルアドレスが表示されている欄 につけたい 名前を上書きするだけです。
ちなみに、つけた名前を削除したい場合は、メニューから 「数式」⇒「名前の管理」 とすると名前の一覧が出てきますので、ここで削除します。
まとめ
この記事では Power Query を使って SQL Server のデータを活用する方法を解説しました。接続時の細かい留意点や、データの絞り込みの工夫についてもご紹介しました。
データベースのデータ活用は SQL Server Management Studio などを使い、SQL というデータベース言語を駆使しなければできませんでした。
しかし、Power Query の登場でデータベースの活用のハードルが一気に下がりました。この機会に SQL Server のデータを Power Query で活用して、是非業務の質を高めてみて下さい。
