次の方法で共有

Power Query カスタム コネクタでのネイティブ クエリのサポート

注

この記事では、カスタム コネクタの ネイティブ クエリ サポートの実装に関する高度なトピックと、その上に クエリ フォールディング について説明します。 この記事では、これらの概念に関する実用的な知識を既に持っていることを前提としています。

Power Query カスタム コネクタの詳細については、 Power Query SDK の概要に関するページを参照してください。

Power Query では、データ ソースに対してカスタム ネイティブ クエリを実行して、探しているデータを取得できます。 また、Power Query 内で実行されるこのプロセスとその後の変換プロセス全体にわたってクエリ フォールディングを維持する機能を有効にすることもできます。

この記事の目的は、カスタム コネクタにこのような機能を実装する方法を紹介することです。

[前提条件]

この記事では、データ ソースに SQL ODBC ドライバーを使用する サンプル を出発点として使用します。 ネイティブ クエリ機能の実装は、現在、SQL-92 標準に準拠する ODBC コネクタでのみサポートされています。

サンプル コネクタでは、 SQL Server Native Client 11.0 ドライバーを使用します 。 このチュートリアルに従うために、このドライバーがインストールされていることを確認します。

GitHub リポジトリの Finish フォルダー から、完成したバージョンのサンプル コネクタを表示することもできます。

コネクタの SQLCapabilities を変更する

サンプル コネクタの SqlCapabilities レコードには、 Sql92Translation という名前のレコード フィールドと、それに対する パススルー 値が含まれます。 この新しいフィールドは、検証なしで Power Query を使用してネイティブ クエリを渡すために必要です。

SqlCapabilities = Diagnostics.LogValue("SqlCapabilities_Options", defaultConfig[SqlCapabilities] & [
    // Place custom overrides here
    // The values below are required for the SQL Native Client ODBC driver, but might
    // not be required for your data source.
        SupportsTop = false,
        SupportsDerivedTable = true,
        Sql92Conformance = 8 /* SQL_SC_SQL92_FULL */,
        GroupByCapabilities = 4 /* SQL_GB_NO_RELATION */,
        FractionalSecondsScale = 3,
        Sql92Translation = "PassThrough"
]),

次に進む前に、コネクタにこのフィールドが表示されていることを確認してください。 そうでない場合は、コネクタによって宣言されていないため、サポートされていない機能の使用に関しては、後で警告とエラーが発生します。

コネクタ ファイル (.mez または .pqx) をビルドし、手動テスト用に Power BI Desktop に読み込み、ネイティブ クエリのターゲットを定義します。

コネクタのネイティブ クエリ機能を手動でテストする

注

この記事では、 AdventureWorks2019 サンプル データベースを使用します。 ただし、任意の SQL Server データベースに従って、選択したデータベースの詳細に従って必要な変更を行うことができます。

この記事でネイティブ クエリのサポートを実装する方法は、ユーザーに次の 3 つの値を入力するよう要求されることです。

  • サーバー名
  • データベース名
  • データベース レベルでのネイティブ クエリ

Power BI Desktop 内で、[ データの取得 ] エクスペリエンスに移動し、 SqlODBC サンプルという名前のコネクタを見つけます。

Power BI Desktop のデータ取得エクスペリエンス内にあるコネクタのスクリーンショット。

コネクタ ダイアログで、サーバーのパラメーターとデータベース名を入力します。 その後、OK を選択します。

サーバーとデータベースをパラメーターとして含むコネクタ ダイアログのスクリーンショット。

新しいナビゲーター ウィンドウが表示されます。 ナビゲーターでは、サーバーとその中のデータベースの階層ビューを表示する SQL ドライバーからネイティブ ナビゲーション動作を表示できます。 AdventureWorks2019 データベースを右クリックし、[データの変換] を選択します。

[ナビゲーター] ウィンドウ内のコンテキスト メニューの [データ変換] オプションのスクリーンショット。

この選択により、Power Query エディターと、すべてのネイティブ クエリをデータベース レベルで実行する必要があるため、ネイティブ クエリのターゲットとなる内容のプレビューが表示されます。 最後のステップの数式バーを確認し、コネクタを実行する前にネイティブクエリのターゲットにどのようにナビゲートするかを理解します。 この場合、数式バーには次の情報が表示されます。

= Source{[Name="AdventureWorks2019",Kind="Database"]}[Data]

ソース は、前の手順の名前です。この場合は、渡されたパラメーターを使用してコネクタのパブリッシュされた関数にすぎません。 リストとその中のレコードは、テーブルを特定の行に移動するのに役立ちます。 この行は、フィールド 名 が AdventureWorks2019 と等しく、 種類 フィールドが データベースと等しい必要があるレコードの条件によって定義されます。 行が見つからたら、リストの外部にある[Data]{}、Power Query はデータ フィールド内の値 (この場合はテーブル) にアクセスできるようにします。 前の手順 (ソース) に戻って、このナビゲーションについて理解を深めることができます。

ナビゲーション ステップに使用された値とフィールドを示すテーブルのスクリーンショット。

ネイティブ クエリをテストする

ターゲットが識別されたら、数式バーの fx アイコンを選択して、ナビゲーション ステップの後にカスタム ステップを作成します。

カスタム ステップの作成に使用される数式内の fx ボタンのスクリーンショット。

数式バー内の数式を次の数式に置き換えて、 Enter キーを押します。

= Value.NativeQuery( AdventureWorks2019_Database, "SELECT TOP (1000) *
  FROM [Person].[Address]")

この変更を適用すると、データ ソースに対してネイティブ クエリを実行するためのアクセス許可を要求する警告が数式バーの下に表示されます。

このネイティブ データベース クエリ警告メッセージを実行するには、アクセス許可が必要なスクリーンショット。

アクセス許可の編集を選択します。 ネイティブ クエリを実行する可能性について警告を表示する新しい [ネイティブ データベース クエリ] ダイアログが表示されます。 この場合、この SQL ステートメントは安全であることがわかっているので、[ 実行 ] を選択してコマンドを実行します。

ネイティブ データベース クエリ ダイアログを承認する方法を示すスクリーンショット。

クエリを実行すると、クエリのプレビューが Power Query エディターに表示されます。 このプレビューでは、コネクタがネイティブ クエリを実行できることを検証します。

初期コネクタの開発とテストで実行されたネイティブ クエリのスクリーンショット。

コネクタにネイティブ クエリ ロジックを実装する

前のセクションから収集した情報を使用して、コネクタのコードにそのような情報を変換することを目標としています。

この変換を実現する方法は、コネクタの発行レコードに新しい NativeQueryProperties レコード フィールドを追加することです。この場合は、SqlODBC.Publish レコードです。 NativeQueryProperties レコードは、コネクタが Value.NativeQuery 関数と対話する方法を定義する上で重要な役割を果たします。

新しいレコード フィールドは、次の 2 つのフィールドで構成されます。

  • NavigationSteps: このフィールドは、コネクタでナビゲーションを実行または処理する方法を定義します。 これには、 Value.NativeQuery 関数を使用してクエリを実行する特定のデータに移動する手順の概要を示すレコードの一覧が含まれています。 各レコード内では、ナビゲーションが目的のターゲットに到達するために要求される、または必要とされるパラメーターを定義します。
  • DefaultOptions: このフィールドは、特定の省略可能なパラメーターを含めたり、 Value.NativeQuery オプション レコードに追加したりする方法を識別するのに役立ちます。 データ ソースに対してクエリを実行するときに使用できる一連の既定のオプションが用意されています。

ナビゲーション手順は、2 つのグループに分類できます。 1 つ目には、サーバー名やデータベース名など、エンド ユーザーが入力した値が含まれます。この場合は、 2 つ目には、特定のコネクタの実装によって派生した値 (データの取得エクスペリエンス中にユーザーに表示されないフィールドの名前など) が含まれています。 これらのフィールドには、コネクタの実装に応じて、 Name、 Kind、 Dataなどが含まれます。

この場合、2 つのフィールドで構成されるナビゲーション ステップは 1 つだけでした。

  • 名前: このフィールドは、エンド ユーザーによって渡されたデータベースの名前です。 この場合は AdventureWorks2019されましたが、このフィールドは常に、エンド ユーザーがデータの取得エクスペリエンス中に入力した内容から as-is 渡す必要があります。
  • 種類: このフィールドは、エンド ユーザーには表示されず、コネクタまたはドライバーの実装に固有の情報です。 この場合、この値はアクセスする必要があるオブジェクトの種類を識別します。 この実装では、このフィールドは文字列 Databaseで構成される固定値になります。

このような情報は、次のコードに変換されます。 このコードは、 SqlODBC.Publish レコードに新しいフィールドとして追加する必要があります。

NativeQueryProperties = [
    NavigationSteps = {
        [
            Indices = {
                [
                    FieldDisplayName = "database",
                    IndexName = "Name"
                ],
                [
                    ConstantValue = "Database",
                    IndexName = "Kind"
                ]
            },
            FieldAccess = "Data"
        ]
    }
]

Important

フィールドの名前は大文字と小文字を区別し、上記のサンプルに示すように使用する必要があります。 フィールドに渡されるすべての情報 ( ConstantValue、 IndexName、または FieldDisplayName は、コネクタの M コードから派生する必要があります。

ユーザーが入力した値から渡される値の場合は、ペアの FieldDisplayName と IndexNameを使用できます。 固定または定義済みで、エンド ユーザーが渡すことのできない値の場合は、 ConstantValue と IndexNameのペアを使用できます。 この意味では、 NavigationSteps レコードは次の 2 つのフィールドで構成されます。

  • インデックス: Value.NativeQuery 関数のターゲットを含むレコードに移動するために使用するフィールドと値を定義します。
  • FieldAccess: ターゲットを保持するフィールド (通常はテーブル) を定義します。

デフォルトオプション

DefaultOptions フィールドを使用すると、コネクタのネイティブ クエリ機能を使用するときに、省略可能なパラメーターを Value.NativeQuery 関数に渡すことができます。

ネイティブ クエリの後にクエリ フォールディングを保持し、コネクタにクエリ フォールディング機能があると仮定するには、 EnableFolding = trueに次のサンプル コードを使用できます。

NativeQueryProperties = [
    NavigationSteps = {
        [
            Indices = {
                [
                    FieldDisplayName = "database",
                    IndexName = "Name"
                ],
                [
                    ConstantValue = "Database",
                    IndexName = "Kind"
                ]
            },
            FieldAccess = "Data"
        ]
    },

    DefaultOptions = [
        EnableFolding = true
    ]
]

これらの変更が行われたら、コネクタをビルドし、テストと検証のために Power BI Desktop に読み込みます。

コネクタをテストして検証する

新しいカスタム コネクタが配置された Power BI Desktop で、 データの取得 エクスペリエンスからコネクタを起動します。 コネクタを起動すると、ダイアログに ネイティブ クエリ という名前の長いテキスト フィールドが表示され、かっこ内に必要なフィールドが表示されます。 コネクタのテスト時に入力したサーバー、データベース、SQL ステートメントと同じ値を入力します。

ネイティブ クエリの長いテキスト フィールドが表示されているコネクタ ダイアログのスクリーンショット。

[OK] を選択すると、実行されたネイティブ クエリのテーブル プレビューが新しいダイアログに表示されます。

実行されたネイティブ クエリのテーブル プレビューを含むダイアログのスクリーンショット。

[OK] を選択. 新しいクエリが Power Query エディター内に読み込まれるようになり、必要に応じてコネクタをさらにテストできます。

注

コネクタにクエリ フォールディング機能があり、EnableFolding=trueの省略可能なレコードの一部としてValue.NativeQueryを明示的に定義している場合は、さらに変換がソースに折り返されるかどうかを確認することで、Power Query エディターでコネクタをさらにテストできます。

  • Last updated on