Share via

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

  • [アーティクル]

Note

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

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 という名前のレコード・フィールドと、 PassThrough という値のレコード・フィールドがあります。 この新しいフィールドは、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 にロードして手動でテストし、ネイティブ クエリのターゲットを定義します。

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

Note

今回は、AdventureWorks2019サンプルデータベースを使用します。 しかし、選択したデータベースの仕様が決まったら、好きなSQL Serverデータベースに従って必要な変更を加えることができます。

この項目でネイティブ・クエリー・サポートは、ユーザーに3つの値を入力するよう要求することによって実行されます：

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

Power BI Desktop 内で、Get Data エクスペリエンスに移動し、SqlODBC Sample という名前のコネクタを見つけます。

Screenshot of the connector found inside the get data experience of Power BI Desktop.

コネクタ・ダイアログでは、サーバーのパラメータとデータベース名を入力します。 [OK] をクリックします。

Screenshot of connector dialog with server and database as parameters.

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

Screenshot of the transform data option from the contextual menu inside the Navigator window.

すべてのネイティブクエリはデータベースレベルで実行されるため、この選択によりPower Queryエディタが表示され、ネイティブクエリのターゲットがプレビューされます。 最後のステップの数式バーを見て、ネイティブクエリを実行する前に、コネクタがどのようにネイティブクエリのターゲットにナビゲートすべきかをよりよく理解してください。 この場合、数式バーには以下の情報が表示される：

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

Sourceは前のステップの名前で、この場合、パラメータが渡されたコネクタの公開関数になります。 リストとその中のレコードは、テーブルを特定の行にナビゲートするのに役立つ。 行は、NameフィールドがAdventureWorks2019と等しく、KindフィールドがDatabaseと等しいというレコードの条件で定義されます。 行が見つかったら、リスト{}の外側の[Data]によって、Power QueryはDataフィールド（この場合はテーブル）内の値にアクセスすることができます。 このナビゲーションをよりよく理解するには、前のステップ（ソース）に戻ることができます。

Screenshot of a table that shows the values and fields that were used for the navigation step.

ネイティブ クエリのテスト

これでターゲットが特定されたので、フォーミュラバーの fx アイコンを選択して、ナビゲーションステップの後にカスタムステップを作成する。

Screenshot of the fx button inside the formula that's used to create a custom step.

数式バー内の数式を以下の数式に置き換え、 Enterを選択する。

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

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

Screenshot of the permission is required to run this native database query warning message.

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

Screenshot showing how to approve a native database query dialog.

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

Screenshot of the native query executed in initial connector development and testing.

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

前のセクションで集めた情報をもとに、今度はその情報をコネクタのコードに変換することが目標です。

この変換を達成する方法は、新しい NativeQueryProperties レコード・フィールドをコネクタの Publish レコード（この場合は SqlODBC.Publish レコード）に追加することです。 NativeQueryProperties レコードは、コネクタが Value.NativeQuery 関数とやり取りする方法を定義する上で重要な役割を果たします。

新記録フィールドは2つのフィールドからなる：

  • NavigationSteps: このフィールドは、ナビゲーションがどのように実行されるか、またはコネクタによって処理されるかを定義します。 これには、Value.NativeQuery 関数を使用してクエリを実行する特定のデータに移動する手順の概要を示すレコードの一覧が含まれています。 それぞれのレコード内では、このようなナビゲーションが希望するターゲットに到達するためには、どのようなパラメータが必要であるかを定義します。
  • DefaultOptions: このフィールドは、Value.NativeQuery オプションレコードに特定のオプションパラメータをどのように含めるか、または追加するかを特定するのに役立ちます。 データ ソースに対してクエリを実行するときに使用できる一連のデフォルトのオプションが用意されています。

ナビゲーションのステップは2つのグループに分類できます。 最初の値には、エンドユーザーが入力する値（この場合はサーバー名やデータベース名など）が含まれます。 2番目は、データ取得中にユーザーに表示されないフィールド名など、特定のコネクタ実装によって導出される値を含みます。 これらのフィールドには、 Name、 Kind、 Data などがあり、コネクタの実装に依存します。

このケースでは、2つのフィールドからなるナビゲーション・ステップが1つだけありました：

  • Name：このフィールドはエンドユーザーから渡されたデータベースの名前である。 この場合、AdventureWorks2019であったが、このフィールドは常にエンドユーザーがデータ取得時に入力したものをそのまま渡すべきである。
  • Kind:このフィールドはエンドユーザーには見えない情報で、コネクタやドライバの実装に固有のものです。 この場合、この値は、アクセスされるべきオブジェクトのタイプを特定します。 この実装では、このフィールドは文字列で構成される固定値になります Database。

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

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

重要

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

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

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

DefaultOptions

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 で、データの取得 エクスペリエンスからコネクタを起動します。 コネクタを起動すると、ダイアログに Native query という名前の長いテキスト フィールドが表示され、括弧内にコネクタが機能するために必要なフィールドが含まれていることがわかります。 コネクタのテスト時に以前に入力したサーバー、データベース、SQL ステートメントと同じ値を入力します。

Screenshot of the connector dialog with the native query long text field shown.

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

Screenshot of the dialog with a table preview of the executed native query.

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

Note

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