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.
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.
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.
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.
Untuk dialog konektor, masukkan parameter untuk server dan nama database Anda. Kemudian pilih OK.
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.
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.
Dengan target yang sekarang diidentifikasi, buat langkah kustom setelah langkah navigasi dengan memilih ikon fx di bilah rumus.
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.
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.
Setelah Anda menjalankan kueri, pratinjau kueri Anda muncul di editor Power Query. Pratinjau ini memvalidasi bahwa konektor Anda mampu menjalankan kueri asli.
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.
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.
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.
Setelah Anda memilih OK, pratinjau tabel kueri asli yang dijalankan diperlihatkan dalam dialog baru.
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.