クエリの折りたたみと SQL Server への接続

このチュートリアルでは、SQL サーバーに接続する方法を学びます。また、Power Query でのクエリの折りたたみの仕組みと、LuckyTemplates で SQL コマンドを実行する方法についても説明します。Power Query 内では、さまざまなソースからデータを抽出して操作できます。クエリの折りたたみを使用すると、データ変換を LuckyTemplates で実行するのではなく、ソースにオフロードできます。

クエリの折りたたみは、レポートのパフォーマンスを確実に向上させるために、大規模なデータベースで非常に効果的です。

目次

• クエリ折りたたみのための SQL Server データベースへの接続
• Power Query でのクエリの折りたたみについて
• 壊れたクエリの折りたたみを特定して修正する
• SQL Server に接続して SQL 言語を実行する利点
• 結論

クエリ折りたたみのための SQL Server データベースへの接続

このチュートリアルは Power Query エディターで実行します。まず、[新しいソース]の下にある[SQL Server]をクリックします。

すでに SQL サーバー マシンをインストールし、独立して動作している場合、サーバー名はlocalhostになります。ただし、組織で働いている場合は、組織からサーバー名とデータベースへのアクセスが提供されます。データベース名も指定する必要があります。この例では、 AdventureWorksDW2012を使用します。このサンプル データベースをダウンロードする方法は、このチュートリアルから学習できます。

データ接続モードの場合、 [インポート]を選択すると、選択するすべてのデータがモデルにロードされます。ただし、 DirectQueryを選択すると、データ モデルには何も読み込まれず、すべてがデータベースに読み込まれます。フィルターを適用すると、クエリが SQL Server に送り返されます。

ただし、これは更新に時間がかかるため効率的ではありません。したがって、データ接続モードとして[インポート]を選択します。

次に、[詳細オプション]をクリックすると、SQL ステートメントを作成できるセクションが表示されます。その方法については後で学びます。

この例では、一度に必要なテーブルは 1 つだけなので、リレーションシップ列やテーブルは取り込みません。その場合は、そのオプションのチェックを外す必要があります。

最後に、「OK」をクリックします。

その後、利用可能なテーブルが表示され、そこからいくつかの情報が得られます。この例では、実際のインターネット販売からデータを取得する必要があります。したがって、 「FactInternetSales」を検索して選択し、「OK」をクリックします。

その結果、Power Query エディターにデータが表示されるようになりました。

Power Query でのクエリの折りたたみについて

[ソース]パネル内で[ナビゲーション]を右クリックし、 [ネイティブ クエリの表示]を選択します。

これにより、このマシンによって実行されたコマンドを確認できるようになります。Power Query エンジンは、SQL Server で実行するためにこのコマンドを作成しました。[OK]をクリックしましょう。

例として、この表にフィルターをランダムに作成しましたが、これは、フィルターを作成すると、それらが[APPLIED STEPS]パネルに表示されることを示すためです。

フィルタの 1 つを右クリックすると、 [ネイティブ クエリの表示]オプションが引き続き使用できることがわかります。

フィルターからのこのSQL クエリはSQL Serverで実行されています。フィルターを適用していない場合は、500 万行が取得されます。フィルターを適用したので、取得できる行は 400 万行だけです。

これは、LuckyTemplates が SQL Server から 500 万行ではなく 400 万行を抽出するようになったことを意味します。これにより、行数が減り、ネットワークからの負荷の数も減りました。

Native Query が表示されている限り、クエリの折りたたみが機能していることを意味することに注意してください。したがって、すべての処理はソース システム内で実行されます。これは、特に大量のデータがある場合にデータを処理する最も効率的な方法です。

テーブルから 1 つの列を削除する別のステップを追加しました。それを右クリックすると、 [ネイティブ クエリの表示]オプションが表示され、まだ機能していることを意味します。

壊れたクエリの折りたたみを特定して修正する

列のデータ型の変更などの一部の変換を行うと、クエリの折りたたみが壊れます。たとえば、TaxAmt列のデータ型をWhole Numberに変更します。

これにより、 「APPLIED STEPS」の下に「Change Type」ステップが追加されます。これを右クリックすると、ビュー ネイティブ クエリが無効になっていることがわかります。これは、クエリの折りたたみが壊れていることを意味します。

クエリの折りたたみが壊れている場合、その他の変換は LuckyTemplates Power Query で実行されますが、ソース システムでは実行されなくなります。

たとえば、300 万行を取得する場合、それらはすべて Power Query で取得されます。フィルタリングを通じてこれらのレコードを減らすこともできます。ただし、これらの 300 万行はネットワーク経由で送信されるため、あまり効率的ではありません。

別の例として、 OrderDateをフィルターして、2012 年 1 月 1 日以降の日付のみを表示するとします。

APPLIED STEPSでそのフィルターを表示すると、[ネイティブ クエリの表示]オプションは表示されません。

繰り返しますが、これは、以前に作成した変換によってクエリの折りたたみが壊れたためです。私たちにできることは、これから実行するすべてのフィルタリング ステップを、クエリの折りたたみを壊す変換ステップの上に移動することです。

この例では、作成した最近のフィルタリング ステップを右クリックして [前に移動]をクリックするか、変更タイプ変換の上部にドラッグします。

そのフィルターを再度右クリックすると、 [ネイティブ クエリの表示]オプションが表示されていることがわかります。これは、クエリの折りたたみが再び機能していることを意味します。

SQL Server に接続して SQL 言語を実行する利点

たとえば、図に示すように、国別の総売上高形式でデータを表示したいとします。

FactInternetSalesテーブルにはSalesAmount列がありますが、そこには国情報がありません。

SalesTerritoryKey列があるため、引き続き国情報を取得できます。

必要なのは、DimSalesテーブルをここに持ってきて、 FactInternetSalesとマージできるようにすることです。次に、国列を取得して国列ごとにグループ化する必要がありますが、これは非常に複雑で時間がかかる可能性があります。したがって、非効率なPower Queryですべてを実行するのではなく、代わりにSQLで実行する必要があります。

これを行うには、[新しいソース] > [SQL Server]をクリックするだけです。

localhost という名前のサーバーに再度接続し、データベースとしてAdventureWorksDW2012 を接続しましょう。

今回は、 SQL ステートメントのフィールドにコマンドを書きたいので、詳細オプションを実行します。この例では、SQL ステートメントに入力するコマンドがすでに記述されています。SQL コマンドについては、他のチュートリアルで学習できます。

リレーションシップ列を含めたくないので、ここではこのオプションのチェックを外します。次に、「OK」ボタンをクリックしてこのコマンドを実行します。

コマンドを実行すると、このプレビュー ウィンドウに販売地域別の総売上高が表示されます。

そのため、 Power Query でさまざまなテーブルや列を操作する代わりに、単純なSQL コマンドを使用して、SalesBy Countryテーブルの同様の出力を取得することができました。

もう 1 つの利点は、すべてのデータを SQL で変換し、必要なデータのみをデータ モデルに取り込めることです。これにより、何の困難や問題もなく、計画に従って非常に効率的なデータ モデルを構築できます。

SQL Server のダウンロードとインストール
LuckyTemplates ユーザーのための SQL – 新しい LuckyTemplates コース
複数のデータ ソースからのデータのクエリ

結論

このチュートリアルでは、クエリ フォールディングとは何かを学び、その利点を発見しました。Power Query をSQL Serverに接続する方法の手順についても説明しました。

さらに、 SQL Serverに接続し、Power Queryで変換を実行するのではなくSQL Server上で変換を作成することの利点についても説明しました。

SQLですべての変換を実行する方が、 Power Queryで実行する場合と比べていかに効率的かつ高速であるかが理解できたでしょうか。

ではごきげんよう、

ハーフィズ