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

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

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

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

目次

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

そのため、 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で実行する場合と比べていかに効率的かつ高速であるかが理解できたでしょうか。

ではごきげんよう、

ハーフィズ


Power Automate の文字列関数: Substring と IndexOf

Power Automate の文字列関数: Substring と IndexOf

Microsoft フローで使用できる 2 つの複雑な Power Automate String 関数、substring 関数とindexOf 関数を簡単に学習します。

LuckyTemplates でビジュアル ツールチップを作成する

LuckyTemplates でビジュアル ツールチップを作成する

LuckyTemplates ツールチップを使用すると、より多くの情報を 1 つのレポート ページに圧縮できます。効果的な視覚化の手法を学ぶことができます。

Power Automate で HTTP 要求を行う

Power Automate で HTTP 要求を行う

Power Automate で HTTP 要求を作成し、データを受信する方法を学んでいます。

LuckyTemplates で日付テーブルを作成する方法

LuckyTemplates で日付テーブルを作成する方法

LuckyTemplates で簡単に日付テーブルを作成する方法について学びましょう。データの分析と視覚化のための効果的なツールとして活用できます。

2 つの方法による SharePoint 列の検証

2 つの方法による SharePoint 列の検証

SharePoint 列の検証の数式を使用して、ユーザーからの入力を制限および検証する方法を学びます。

SharePoint リストを Excel または CSV ファイルにエクスポート

SharePoint リストを Excel または CSV ファイルにエクスポート

SharePoint リストを Excel ファイルおよび CSV ファイルにエクスポートする方法を学び、さまざまな状況に最適なエクスポート方法を決定できるようにします。

Power Automate のオンプレミス データ ゲートウェイ

Power Automate のオンプレミス データ ゲートウェイ

ユーザーがコンピューターから離れているときに、オンプレミス データ ゲートウェイを使用して Power Automate がデスクトップ アプリケーションにアクセスできるようにする方法を説明します。

DAX 数式での LASTNONBLANK の使用

DAX 数式での LASTNONBLANK の使用

DAX 数式で LASTNONBLANK 関数を使用して、データ分析の深い洞察を得る方法を学びます。

CROSSJOIN 関数の使用方法 – LuckyTemplates および DAX チュートリアル

CROSSJOIN 関数の使用方法 – LuckyTemplates および DAX チュートリアル

LuckyTemplates で予算分析とレポートを実行しながら、CROSSJOIN 関数を使用して 2 つのデータ テーブルをバインドする方法を学びます。

TREATAS 関数を使用して LuckyTemplates で仮想リレーションシップを作成する

TREATAS 関数を使用して LuckyTemplates で仮想リレーションシップを作成する

このチュートリアルでは、LuckyTemplates TREATAS を使用して数式内に仮想リレーションシップを作成する方法を説明します。