Bagikan melalui

Dukungan query bawaan di konektor kustom Power Query

Nota

Artikel ini membahas topik lanjutan mengenai implementasi dukungan kueri asli untuk konektor kustom, serta penggabungan 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 query folding sepanjang proses ini dan semua 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.

Ubah SQLCapabilities pada konektor Anda

Dalam catatan SqlCapabilities konektor sampel, Anda dapat menemukan bidang rekaman dengan nama Sql92Translation dan nilai PassThrough untuk bidang 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 error nanti saat menggunakan kemampuan yang tidak didukung karena belum 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

Nota

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.

Cuplikan layar konektor yang ditemukan di dalam pengalaman mendapatkan data Power BI Desktop.

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

Cuplikan layar dialog konektor dengan server dan database sebagai parameter.

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.

Cuplikan layar opsi transformasi data dari menu kontekstual di dalam jendela Navigator.

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 tahap sebelumnya yang, dalam hal ini, hanyalah fungsi konektor yang telah diterbitkan dengan parameter yang diberikan. 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 ditemukan, bagian di 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.

Cuplikan layar tabel yang memperlihatkan nilai dan bidang yang digunakan untuk langkah navigasi.

Menguji kueri asli

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

Cuplikan layar tombol fx di dalam rumus yang digunakan untuk membuat langkah kustom.

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.

Cuplikan layar izin diperlukan untuk menjalankan pesan peringatan kueri database asli ini.

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.

Cuplikan layar memperlihatkan cara menyetujui dialog kueri database asli.

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

Cuplikan layar kueri asli yang dijalankan dalam pengembangan dan pengujian konektor awal.

Menerapkan logika kueri asli di penyambung Anda

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

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

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 dikirimkan sebagaimana adanya dari apa yang dimasukkan pengguna akhir selama pengalaman memperoleh 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 sensitif terhadap 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 tetap atau telah ditentukan sebelumnya dan tidak dapat dikonfigurasi 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.

Opsi Default

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

Untuk mempertahankan penggulungan kueri setelah kueri bawaan, dan dengan asumsi bahwa konektor Anda memiliki kemampuan penggulungan 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 terpasang, luncurkan konektor dari fitur 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.

Cuplikan layar dialog konektor dengan bidang teks panjang kueri asli ditampilkan.

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

Cuplikan layar dialog dengan pratinjau tabel kueri asli yang dijalankan.

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

Nota

Jika konektor Anda memiliki kemampuan "query folding" dan EnableFolding=true telah didefinisikan secara eksplisit 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 dapat dilipat kembali ke sumber atau tidak.

  • Last updated on