Baca dalam bahasa Inggris

Bagikan melalui


Dukungan kueri asli di konektor kustom Power Query

Catatan

Artikel ini membahas topik lanjutan seputar implementasi dukungan kueri asli untuk konektor kustom, serta pelipatan kueri di atasnya. Artikel ini mengasumsikan Anda sudah memiliki pengetahuan kerja tentang konsep-konsep ini.

Untuk mempelajari selengkapnya tentang konektor kustom Power Query, buka Gambaran Umum Power Query SDK.

Di Power Query, Anda dapat menjalankan kueri asli kustom terhadap sumber data Anda untuk mengambil data yang Anda cari. Anda juga dapat mengaktifkan kemampuan untuk mempertahankan pelipatan kueri sepanjang proses ini dan proses transformasi berikutnya yang dilakukan di dalam Power Query.

Tujuan dari artikel ini adalah untuk menampilkan bagaimana Anda dapat menerapkan kemampuan tersebut untuk konektor kustom Anda.

Prasyarat

Artikel ini menggunakan sebagai titik awal sampel yang menggunakan driver ODBC SQL untuk sumber datanya. Implementasi kemampuan kueri asli saat ini hanya didukung untuk konektor ODBC yang mematuhi standar SQL-92.

Konektor sampel menggunakan driver SQL Server Native Client 11.0 . Pastikan Anda telah menginstal driver ini untuk mengikuti tutorial ini.

Anda juga dapat melihat versi selesai konektor sampel dari folder Selesai di Repositori GitHub.

Mengubah SQLCapabilities konektor Anda

SqlCapabilities Dalam catatan konektor sampel, Anda dapat menemukan bidang rekaman dengan nama Sql92Translation dan nilai PassThrough untuk konektor tersebut. Bidang baru ini diperlukan agar kueri asli diteruskan menggunakan Power Query tanpa validasi apa pun.

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"
]),

Pastikan bidang ini muncul di konektor Anda sebelum bergerak maju. Jika tidak, Anda akan menghadapi peringatan dan kesalahan nanti ketika turun menggunakan kemampuan yang tidak didukung karena tidak dideklarasikan oleh konektor.

Buat file konektor (sebagai .mez atau.pqx) dan muat ke Power BI Desktop untuk pengujian manual dan untuk menentukan target untuk kueri asli Anda.

Menguji kemampuan kueri asli konektor Anda secara manual

Catatan

Untuk artikel ini, kita akan menggunakan database sampel AdventureWorks2019. Tetapi Anda dapat mengikuti database SQL Server pilihan Anda dan membuat perubahan yang diperlukan ketika turun ke spesifikasi database yang dipilih.

Cara dukungan kueri asli akan diterapkan dalam artikel ini adalah bahwa pengguna akan diminta untuk memasukkan tiga nilai:

  • Nama server
  • Nama database
  • Kueri asli di tingkat database

Sekarang di dalam Power BI Desktop, buka Pengalaman Dapatkan Data dan temukan konektor dengan nama Sampel SqlODBC.

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

Untuk dialog konektor, masukkan parameter untuk server dan nama database Anda. Kemudian pilih OK.

Screenshot of connector dialog with server and database as parameters.

Jendela navigator baru muncul. Di Navigator, Anda dapat melihat perilaku navigasi asli dari driver SQL yang menampilkan tampilan hierarkis server dan database di dalamnya. Klik kanan database AdventureWorks2019, lalu pilih Transformasi Data.

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

Pilihan ini membawa Anda ke editor Power Query dan pratinjau apa yang secara efektif menjadi target kueri asli Anda karena semua kueri asli harus berjalan di tingkat database. Periksa bilah rumus dari langkah terakhir untuk lebih memahami bagaimana konektor Anda harus menavigasi ke target kueri asli Anda sebelum mengeksekusinya. Dalam hal ini bilah rumus menampilkan informasi berikut:

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

Sumber adalah nama langkah sebelumnya yang, dalam hal ini, hanyalah fungsi konektor Anda yang diterbitkan dengan parameter yang diteruskan. Daftar dan rekaman di dalamnya hanya membantu menavigasi tabel ke baris tertentu. Baris ditentukan oleh kriteria dari rekaman di mana bidang Nama harus sama dengan AdventureWorks2019 dan bidang Kind harus sama dengan Database. Setelah baris berada, bagian [Data] luar daftar {} memungkinkan Power Query mengakses nilai di dalam bidang Data , yang dalam hal ini adalah tabel. Anda dapat kembali ke langkah sebelumnya (Sumber) untuk lebih memahami navigasi ini.

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

Menguji kueri asli

Dengan target yang sekarang diidentifikasi, buat langkah kustom setelah langkah navigasi dengan memilih ikon fx di bilah rumus.

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

Ganti rumus di dalam bilah rumus dengan rumus berikut, lalu pilih Enter.

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

Setelah Anda menerapkan perubahan ini, peringatan akan muncul di bawah bilah rumus yang meminta izin untuk menjalankan kueri asli terhadap sumber data Anda.

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

Pilih Edit Izin. Dialog Kueri Database Asli baru ditampilkan yang mencoba memperingatkan Anda tentang kemungkinan menjalankan kueri asli. Dalam hal ini, kita tahu bahwa Pernyataan SQL ini aman, jadi pilih Jalankan untuk menjalankan perintah.

Screenshot showing how to approve a native database query dialog.

Setelah Anda menjalankan kueri, pratinjau kueri Anda muncul di editor Power Query. Pratinjau ini memvalidasi bahwa konektor Anda mampu menjalankan kueri asli.

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

Menerapkan logika kueri asli di konektor Anda

Dengan informasi yang dikumpulkan dari bagian sebelumnya, tujuannya sekarang adalah menerjemahkan informasi tersebut ke dalam kode untuk konektor Anda.

Cara Anda dapat menyelesaikan terjemahan ini adalah dengan menambahkan bidang rekaman NativeQueryProperties baru ke catatan Terbitkan konektor Anda, yang dalam hal ini adalah SqlODBC.Publish rekaman. Rekaman memainkan NativeQueryProperties peran penting dalam menentukan bagaimana konektor akan berinteraksi dengan Value.NativeQuery fungsi.

Bidang rekaman baru terdiri dari dua bidang:

  • NavigationSteps: Bidang ini menentukan bagaimana navigasi harus dilakukan atau ditangani oleh konektor Anda. Ini berisi daftar rekaman yang menguraikan langkah-langkah untuk menavigasi ke data tertentu yang ingin Anda kueri menggunakan fungsi .Value.NativeQuery Dalam setiap rekaman, ini mendefinisikan parameter apa yang diperlukan atau diperlukan agar navigasi tersebut mencapai target yang Anda inginkan.
  • DefaultOptions: Bidang ini membantu mengidentifikasi bagaimana parameter opsional tertentu harus disertakan atau ditambahkan ke Value.NativeQuery rekaman opsi. Ini menyediakan sekumpulan opsi default yang dapat digunakan saat mengkueri sumber data.

Langkah-langkah navigasi Anda dapat dikategorikan ke dalam dua grup. Yang pertama berisi nilai-nilai yang dimasukkan oleh pengguna akhir, seperti nama server atau database, dalam hal ini. Yang kedua berisi nilai-nilai yang diturunkan oleh implementasi konektor tertentu, seperti nama bidang yang tidak ditampilkan kepada pengguna selama mendapatkan pengalaman data. Bidang-bidang ini dapat mencakup Name, Kind, Data, dan lainnya tergantung pada implementasi konektor Anda.

Untuk kasus ini, hanya ada satu langkah navigasi yang terdiri dari dua bidang:

  • Nama: Bidang ini adalah nama database yang diteruskan oleh pengguna akhir. Dalam hal ini, itu adalah AdventureWorks2019, tetapi bidang ini harus selalu diteruskan apa adanya dari apa yang dimasukkan pengguna akhir selama mendapatkan pengalaman data.
  • Jenis: Bidang ini adalah informasi yang tidak terlihat oleh pengguna akhir dan khusus untuk konektor atau implementasi driver. Dalam hal ini, nilai ini mengidentifikasi jenis objek apa yang harus diakses. Untuk implementasi ini, bidang ini akan menjadi nilai tetap yang terdiri dari string Database.

Informasi tersebut akan diterjemahkan ke kode berikut. Kode ini harus ditambahkan sebagai bidang baru ke rekaman Anda SqlODBC.Publish .

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

Penting

Nama bidang peka huruf besar/kecil dan harus digunakan seperti yang ditunjukkan pada sampel di atas. Semua informasi yang diteruskan ke bidang, baik ConstantValue, IndexName, atau FieldDisplayName harus berasal dari kode M konektor.

Untuk nilai yang akan diteruskan dari apa yang dimasukkan pengguna, Anda dapat menggunakan pasangan FieldDisplayName dan IndexName. Untuk nilai yang diperbaiki atau ditentukan sebelumnya dan tidak dapat diteruskan oleh pengguna akhir, Anda dapat menggunakan pasangan ConstantValue dan IndexName. Dalam hal ini, catatan NavigationSteps terdiri dari dua bidang:

  • Indeks: Menentukan bidang dan nilai apa yang akan digunakan untuk menavigasi ke rekaman yang berisi target untuk fungsi tersebut Value.NativeQuery .
  • FieldAccess: Menentukan bidang apa yang menyimpan target, yang biasanya merupakan tabel.

DefaultOptions

Bidang ini DefaultOptions memungkinkan Anda meneruskan parameter opsional ke Value.NativeQuery fungsi saat menggunakan kemampuan kueri asli untuk konektor Anda.

Untuk mempertahankan pelipatan kueri setelah kueri asli, dan dengan asumsi bahwa konektor Anda memiliki kemampuan lipatan kueri, Anda dapat menggunakan kode sampel berikut untuk EnableFolding = true.

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

    DefaultOptions = [
        EnableFolding = true
    ]
]

Dengan perubahan ini, buat konektor dan muat ke Power BI Desktop untuk pengujian dan validasi.

Menguji dan memvalidasi konektor

Di Power BI Desktop dengan konektor kustom baru Anda di tempat, luncurkan konektor dari pengalaman Dapatkan Data . Saat meluncurkan konektor, Anda akan melihat bahwa dialog sekarang memiliki bidang teks panjang dengan nama Kueri asli dan, dalam tanda kurung, dialog memiliki bidang yang diperlukan agar berfungsi. Masukkan nilai yang sama untuk server, database, dan pernyataan SQL yang sebelumnya Anda masukkan saat menguji konektor.

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

Setelah Anda memilih OK, pratinjau tabel kueri asli yang dijalankan diperlihatkan dalam dialog baru.

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

Pilih OK. Kueri baru sekarang akan dimuat di dalam editor Power Query tempat Anda bisa melakukan pengujian konektor lebih lanjut sesuai kebutuhan.

Catatan

Jika konektor Anda memiliki kemampuan pelipatan kueri dan telah secara eksplisit didefinisikan EnableFolding=true sebagai bagian dari catatan opsional untuk Value.NativeQuery, maka Anda dapat menguji konektor Anda lebih lanjut di editor Power Query dengan memeriksa apakah transformasi lebih lanjut dilipat kembali ke sumber atau tidak.