Di balik layar Firewall Privasi Data

Jika Anda telah menggunakan Power Query untuk waktu yang lama, Kemungkinan Anda telah mengalaminya. Di sana Anda, mengkueri, ketika Anda tiba-tiba mendapatkan kesalahan yang tidak ada jumlah pencarian online, penyesatan kueri, atau bashing keyboard yang dapat diperbaiki. Kesalahan seperti:

Formula.Firewall: Query 'Query1' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Atau mungkin:

Formula.Firewall: Query 'Query1' (step 'Source') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

Kesalahan ini Formula.Firewall adalah hasil dari Firewall Privasi Data Power Query (juga dikenal sebagai Firewall), yang kadang-kadang mungkin tampak seperti ada hanya untuk membuat frustrasi analis data di seluruh dunia. Percaya atau tidak, bagaimanapun, Firewall melayani tujuan penting. Dalam artikel ini, kita akan mempelajari di bawah tenda untuk lebih memahami cara kerjanya. Berbekal pemahaman yang lebih besar, Anda mudah-mudahan dapat mendiagnosis dan memperbaiki kesalahan Firewall dengan lebih baik di masa mendatang.

Apakah maksudnya?

Tujuan Firewall Privasi Data sederhana: ada untuk mencegah Power Query secara tidak sengaja membocorkan data antar sumber.

Mengapa hal ini dibutuhkan? Maksudku, kau pasti bisa menulis beberapa M yang akan meneruskan nilai SQL ke umpan OData. Tetapi ini akan menjadi kebocoran data yang disengaja. Penulis mashup akan (atau setidaknya harus) tahu mereka melakukan ini. Mengapa kemudian kebutuhan akan perlindungan terhadap kebocoran data yang tidak disengaja?

Jawabannya? Lipat.

Lipat?

Lipatan adalah istilah yang mengacu pada konversi ekspresi dalam M (seperti filter, ganti nama, gabungan, dan sebagainya) menjadi operasi terhadap sumber data mentah (seperti SQL, OData, dan sebagainya). Sebagian besar kekuatan Power Query berasal dari fakta bahwa PQ dapat mengonversi operasi yang dilakukan pengguna melalui antarmuka penggunanya menjadi SQL kompleks atau bahasa sumber data backend lainnya, tanpa pengguna harus mengetahui bahasa yang dikatakan. Pengguna mendapatkan manfaat performa dari operasi sumber data asli, dengan kemudahan penggunaan UI di mana semua sumber data dapat diubah menggunakan sekumpulan perintah umum.

Sebagai bagian dari lipatan, PQ terkadang dapat menentukan bahwa cara paling efisien untuk menjalankan mashup tertentu adalah dengan mengambil data dari satu sumber dan meneruskannya ke sumber lain. Misalnya, jika Anda menggabungkan file CSV kecil ke tabel SQL besar, Anda mungkin tidak ingin PQ membaca file CSV, membaca seluruh tabel SQL, lalu menggabungkannya di komputer lokal Anda. Anda mungkin ingin PQ menginline data CSV ke dalam pernyataan SQL dan meminta database SQL untuk melakukan gabungan.

Ini adalah bagaimana kebocoran data yang tidak disengaja dapat terjadi.

Bayangkan jika Anda menggabungkan data SQL yang menyertakan Nomor Jaminan Sosial karyawan dengan hasil umpan OData eksternal, dan Anda tiba-tiba menemukan bahwa Nomor Jaminan Sosial dari SQL dikirim ke layanan OData. Kabar buruk, kan?

Ini adalah jenis skenario Firewall dimaksudkan untuk mencegah.

Bagaimana cara kerjanya?

Firewall ada untuk mencegah data dari satu sumber dikirim secara tidak sengaja ke sumber lain. Cukup sederhana.

Jadi bagaimana menyelesaikan misi ini?

Tindakan ini dilakukan dengan memba lagi kueri M Anda menjadi sesuatu yang disebut partisi, lalu memberlakukan aturan berikut:

• Partisi dapat mengakses sumber data yang kompatibel, atau mereferensikan partisi lain, tetapi tidak keduanya.

Sederhana... namun membingungkan. Apa itu partisi? Apa yang membuat dua sumber data "kompatibel"? Dan mengapa Firewall harus peduli jika partisi ingin mengakses sumber data dan mereferensikan partisi?

Mari kita uraikan ini dan lihat aturan di atas satu bagian pada satu waktu.

Apa itu partisi?

Pada tingkat yang paling dasar, partisi hanyalah kumpulan dari satu atau beberapa langkah kueri. Partisi paling terperinci yang mungkin (setidaknya dalam implementasi saat ini) adalah satu langkah. Partisi terbesar terkadang dapat mencakup beberapa kueri. (Selengkapnya tentang ini nanti.)

Jika Anda tidak terbiasa dengan langkah-langkah, Anda bisa menampilkannya di sebelah kanan jendela Editor Power Query setelah memilih kueri, di panel Langkah yang Diterapkan. Langkah-langkah melacak semua yang telah Anda lakukan untuk mengubah data Anda menjadi bentuk akhir.

Partisi yang mereferensikan partisi lain

Saat kueri dievaluasi dengan Firewall aktif, Firewall membagi kueri dan semua dependensinya menjadi partisi (yaitu, grup langkah). Setiap kali satu partisi mereferensikan sesuatu di partisi lain, Firewall mengganti referensi dengan panggilan ke fungsi khusus yang disebut Value.Firewall. Dengan kata lain, Firewall tidak mengizinkan partisi untuk mengakses satu sama lain secara langsung. Semua referensi dimodifikasi untuk melalui Firewall. Pikirkan Firewall sebagai penjaga gerbang. Partisi yang mereferensikan partisi lain harus mendapatkan izin Firewall untuk melakukannya, dan Firewall mengontrol apakah data yang dirujuk akan diizinkan atau tidak ke dalam partisi.

Ini semua mungkin tampak cukup abstrak, jadi mari kita lihat contohnya.

Asumsikan Anda memiliki kueri yang disebut Karyawan, yang menarik beberapa data dari database SQL. Asumsikan Anda juga memiliki kueri lain (EmployeesReference), yang hanya mereferensikan Karyawan.

shared Employees = let
    Source = Sql.Database(…),
    EmployeesTable = …
in
    EmployeesTable;

shared EmployeesReference = let
    Source = Employees
in
    Source;

Kueri ini akan dibagi menjadi dua partisi: satu untuk kueri Karyawan, dan satu untuk kueri EmployeesReference (yang akan mereferensikan partisi Karyawan). Ketika dievaluasi dengan Firewall aktif, kueri ini akan ditulis ulang seperti itu:

shared Employees = let
    Source = Sql.Database(…),
    EmployeesTable = …
in
    EmployeesTable;

shared EmployeesReference = let
    Source = Value.Firewall("Section1/Employees")
in
    Source;

Perhatikan bahwa referensi sederhana ke kueri Karyawan telah digantikan oleh panggilan ke Value.Firewall, yang disediakan nama lengkap kueri Karyawan.

Ketika EmployeesReference dievaluasi, panggilan ke Value.Firewall("Section1/Employees") dicegat oleh Firewall, yang sekarang memiliki kesempatan untuk mengontrol apakah (dan bagaimana) data yang diminta mengalir ke partisi EmployeesReference. Ini dapat melakukan sejumlah hal: menolak permintaan, buffer data yang diminta (yang mencegah pelipatan lebih lanjut ke sumber data aslinya terjadi), dan sebagainya.

Ini adalah cara Firewall mempertahankan kontrol atas data yang mengalir di antara partisi.

Partisi yang langsung mengakses sumber data

Katakanlah Anda menentukan kueri Query1 dengan satu langkah (perhatikan bahwa kueri satu langkah ini sesuai dengan satu partisi Firewall), dan bahwa langkah tunggal ini mengakses dua sumber data: tabel database SQL dan file CSV. Bagaimana Firewall menangani hal ini, karena tidak ada referensi partisi, dan dengan demikian tidak ada panggilan untuk Value.Firewall mencegat? Mari kita tinjau aturan yang dinyatakan sebelumnya:

• Partisi dapat mengakses sumber data yang kompatibel, atau mereferensikan partisi lain, tetapi tidak keduanya.

Agar kueri sumber-partisi-tapi-dua-data tunggal Anda diizinkan untuk dijalankan, dua sumber datanya harus "kompatibel". Dengan kata lain, perlu baik-baik saja agar data dibagikan secara dua arah di antara mereka. Ini berarti bahwa tingkat privasi kedua sumber harus Publik, atau keduanya adalah Organisasi, karena ini adalah satu-satunya dua kombinasi yang memungkinkan berbagi di kedua arah. Jika kedua sumber ditandai Privat, atau salah satunya ditandai Publik dan salah satunya ditandai Organisasi, atau ditandai menggunakan beberapa kombinasi tingkat privasi lainnya, maka berbagi dua arah tidak diizinkan, dan dengan demikian tidak aman bagi mereka untuk dievaluasi dalam partisi yang sama. Melakukannya berarti kebocoran data yang tidak aman dapat terjadi (karena pelipatan), dan Firewall tidak akan memiliki cara untuk mencegahnya.

Apa yang terjadi jika Anda mencoba mengakses sumber data yang tidak kompatibel dalam partisi yang sama?

Formula.Firewall: Query 'Query1' (step 'Source') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

Semoga Anda sekarang lebih memahami salah satu pesan kesalahan yang tercantum di awal artikel ini.

Perhatikan bahwa persyaratan kompatibilitas ini hanya berlaku dalam partisi tertentu. Jika partisi mereferensikan partisi lain, sumber data dari partisi yang dirujuk tidak harus kompatibel satu sama lain. Ini karena Firewall dapat menyangga data, yang akan mencegah pelipatan lebih lanjut terhadap sumber data asli. Data akan dimuat ke dalam memori dan diperlakukan seolah-olah berasal dari mana pun.

Mengapa tidak melakukan keduanya?

Katakanlah Anda menentukan kueri dengan satu langkah (yang akan kembali sesuai dengan satu partisi) yang mengakses dua kueri lain (yaitu, dua partisi lainnya). Bagaimana jika Anda ingin, dalam langkah yang sama, untuk juga langsung mengakses database SQL? Mengapa partisi tidak dapat mereferensikan partisi lain dan langsung mengakses sumber data yang kompatibel?

Seperti yang Anda lihat sebelumnya, ketika satu partisi mereferensikan partisi lain, Firewall bertindak sebagai penjaga gerbang untuk semua data yang mengalir ke partisi. Untuk melakukannya, data apa yang diizinkan harus dapat dikontrol. Jika ada sumber data yang diakses dalam partisi, dan data yang mengalir dari partisi lain, sumber data kehilangan kemampuannya untuk menjadi penjaga gerbang, karena data yang mengalir dapat bocor ke salah satu sumber data yang diakses secara internal tanpa mengetahuinya. Dengan demikian Firewall mencegah partisi yang mengakses partisi lain agar tidak diizinkan untuk langsung mengakses sumber data apa pun.

Jadi apa yang terjadi jika partisi mencoba mereferensikan partisi lain dan juga langsung mengakses sumber data?

Formula.Firewall: Query 'Query1' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Sekarang Anda mudah-mudahan lebih memahami pesan kesalahan lain yang tercantum di awal artikel ini.

Partisi secara mendalam

Seperti yang mungkin dapat Anda tebak dari informasi di atas, bagaimana kueri dipartisi akhirnya menjadi sangat penting. Jika Anda memiliki beberapa langkah yang mereferensikan kueri lain, dan langkah lain yang mengakses sumber data, Anda sekarang mudah-mudahan mengenali bahwa menggambar batas partisi di tempat-tempat tertentu akan menyebabkan kesalahan Firewall, sambil menggambarnya di tempat lain akan memungkinkan kueri Anda berjalan dengan baik.

Jadi bagaimana tepatnya kueri dipartisi?

Bagian ini mungkin yang paling penting untuk memahami mengapa Anda melihat kesalahan Firewall, dan memahami cara mengatasinya (jika memungkinkan).

Berikut adalah ringkasan tingkat tinggi dari logika partisi.

• Pemartisian Awal
  • Membuat partisi untuk setiap langkah di setiap kueri
• Fase Statis
  • Fase ini tidak bergantung pada hasil evaluasi. Sebaliknya, ini bergantung pada bagaimana kueri disusun.
    • Pemangkasan Parameter
      • Memangkas partisi parameter-esque, yaitu, salah satu yang:
        • Tidak mereferensikan partisi lain
        • Tidak berisi pemanggilan fungsi apa pun
        • Bukan siklik (artinya, itu tidak merujuk ke dirinya sendiri)
      • Perhatikan bahwa "menghapus" partisi secara efektif menyertakannya dalam partisi lain apa pun yang mereferensikannya.
      • Pemangkasan partisi parameter memungkinkan referensi parameter yang digunakan dalam panggilan fungsi sumber data (misalnya, Web.Contents(myUrl)) berfungsi, alih-alih melemparkan kesalahan "partisi tidak dapat mereferensikan sumber data dan langkah-langkah lain".
    • Pengelompokan (Statis)
      • Partisi digabungkan dalam urutan dependensi bawah ke atas. Dalam partisi gabungan yang dihasilkan, berikut ini akan terpisah:
        • Partisi dalam kueri yang berbeda
        • Partisi yang tidak mereferensikan partisi lain (dan dengan demikian diizinkan untuk mengakses sumber data)
        • Partisi yang mereferensikan partisi lain (dan dengan demikian dilarang mengakses sumber data)
• Fase Dinamis
  • Fase ini tergantung pada hasil evaluasi, termasuk informasi tentang sumber data yang diakses oleh berbagai partisi.
  • Pemangkasan
    • Memangkas partisi yang memenuhi semua persyaratan berikut:
      • Tidak mengakses sumber data apa pun
      • Tidak mereferensikan partisi apa pun yang mengakses sumber data
      • Bukan siklik
  • Pengelompokan (Dinamis)
    • Sekarang partisi yang tidak perlu telah dipangkas, cobalah untuk membuat partisi Sumber yang sebesar mungkin. Ini dilakukan dengan menggabungkan partisi menggunakan aturan yang sama yang dijelaskan dalam fase pengelompokan statis di atas.

Apa artinya semua ini?

Mari kita telusuri contoh untuk menggambarkan cara kerja logika kompleks di atas.

Berikut adalah skenario sampel. Ini adalah penggabungan file teks (Kontak) yang cukup mudah dengan database SQL (Karyawan), di mana server SQL adalah parameter (DbServer).

Tiga kueri

Berikut adalah kode M untuk tiga kueri yang digunakan dalam contoh ini.

shared DbServer = "MySqlServer" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true];

shared Contacts = let

    Source = Csv.Document(File.Contents("C:\contacts.txt"),[Delimiter="   ", Columns=15, Encoding=1252, QuoteStyle=QuoteStyle.None]),

    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ContactID", Int64.Type}, {"NameStyle", type logical}, {"Title", type text}, {"FirstName", type text}, {"MiddleName", type text}, {"LastName", type text}, {"Suffix", type text}, {"EmailAddress", type text}, {"EmailPromotion", Int64.Type}, {"Phone", type text}, {"PasswordHash", type text}, {"PasswordSalt", type text}, {"AdditionalContactInfo", type text}, {"rowguid", type text}, {"ModifiedDate", type datetime}})

in

    #"Changed Type";

shared Employees = let

    Source = Sql.Databases(DbServer),

    AdventureWorks = Source{[Name="AdventureWorks"]}[Data],

    HumanResources_Employee = AdventureWorks{[Schema="HumanResources",Item="Employee"]}[Data],

    #"Removed Columns" = Table.RemoveColumns(HumanResources_Employee,{"HumanResources.Employee(EmployeeID)", "HumanResources.Employee(ManagerID)", "HumanResources.EmployeeAddress", "HumanResources.EmployeeDepartmentHistory", "HumanResources.EmployeePayHistory", "HumanResources.JobCandidate", "Person.Contact", "Purchasing.PurchaseOrderHeader", "Sales.SalesPerson"}),

    #"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"ContactID"},Contacts,{"ContactID"},"Contacts",JoinKind.LeftOuter),

    #"Expanded Contacts" = Table.ExpandTableColumn(#"Merged Queries", "Contacts", {"EmailAddress"}, {"EmailAddress"})

in

    #"Expanded Contacts";

Berikut adalah tampilan tingkat yang lebih tinggi, memperlihatkan dependensi.

Mari kita partisi

Mari kita perbesar sedikit dan sertakan langkah-langkah dalam gambar, dan mulai berjalan melalui logika partisi. Berikut adalah diagram dari tiga kueri, memperlihatkan partisi firewall awal berwarna hijau. Perhatikan bahwa setiap langkah dimulai dalam partisinya sendiri.

Selanjutnya, kita memangkas partisi parameter. Dengan demikian, DbServer secara implisit disertakan dalam partisi Sumber.

Sekarang kita melakukan pengelompokan statis. Ini mempertahankan pemisahan antara partisi dalam kueri terpisah (perhatikan misalnya bahwa dua langkah terakhir Karyawan tidak dikelompokkan dengan langkah-langkah Kontak), dan antara partisi yang mereferensikan partisi lain (seperti dua langkah terakhir Karyawan) dan yang tidak (seperti tiga langkah pertama Karyawan).

Sekarang kita memasuki fase dinamis. Dalam fase ini, partisi statis di atas dievaluasi. Partisi yang tidak mengakses sumber data apa pun dipangkas. Partisi kemudian dikelompokkan untuk membuat partisi sumber yang sebesar mungkin. Namun, dalam skenario sampel ini, semua partisi yang tersisa mengakses sumber data, dan tidak ada pengelompokan lebih lanjut yang dapat dilakukan. Partisi dalam sampel kami sehingga tidak akan berubah selama fase ini.

Mari kita berpura-pura

Namun, demi ilustrasi, mari kita lihat apa yang akan terjadi jika kueri Kontak, alih-alih berasal dari file teks, dikodekan secara permanen di M (mungkin melalui dialog Masukkan Data ).

Dalam hal ini, kueri Kontak tidak akan mengakses sumber data apa pun. Dengan demikian, itu akan dipangkas selama bagian pertama dari fase dinamis.

Dengan partisi Kontak dihapus, dua langkah terakhir Karyawan tidak akan lagi mereferensikan partisi apa pun kecuali yang berisi tiga langkah pertama Karyawan. Dengan demikian, kedua partisi akan dikelompokkan.

Partisi yang dihasilkan akan terlihat seperti ini.

Contoh: Meneruskan data dari satu sumber data ke sumber data lainnya

Oke, penjelasan abstrak yang cukup. Mari kita lihat skenario umum di mana Anda mungkin mengalami kesalahan Firewall dan langkah-langkah untuk mengatasinya.

Bayangkan Anda ingin mencari nama perusahaan dari layanan OData Northwind, lalu menggunakan nama perusahaan untuk melakukan pencarian Bing.

Pertama, Anda membuat kueri Perusahaan untuk mengambil nama perusahaan.

let
    Source = OData.Feed("https://services.odata.org/V4/Northwind/Northwind.svc/", null, [Implementation="2.0"]),
    Customers_table = Source{[Name="Customers",Signature="table"]}[Data],
    CHOPS = Customers_table{[CustomerID="CHOPS"]}[CompanyName]
in
    CHOPS

Selanjutnya, Anda membuat kueri Pencarian yang mereferensikan Perusahaan dan meneruskannya ke Bing.

let
    Source = Text.FromBinary(Web.Contents("https://www.bing.com/search?q=" & Company))
in
    Source

Pada titik ini Anda mengalami masalah. Mengevaluasi Pencarian menghasilkan kesalahan Firewall.

Formula.Firewall: Query 'Search' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Ini karena langkah Sumber Pencarian merujuk sumber data (bing.com) dan juga merujuk kueri/partisi lain (Perusahaan). Ini melanggar aturan yang disebutkan di atas ("partisi dapat mengakses sumber data yang kompatibel, atau mereferensikan partisi lain, tetapi tidak keduanya").

Apa yang harus dilakukan? Salah satu opsinya adalah menonaktifkan Firewall sama sekali (melalui opsi Privasi berlabel Abaikan Tingkat Privasi dan berpotensi meningkatkan performa). Tetapi bagaimana jika Anda ingin membiarkan Firewall diaktifkan?

Untuk mengatasi kesalahan tanpa menonaktifkan Firewall, Anda dapat menggabungkan Perusahaan dan Mencari ke dalam satu kueri, seperti ini:

let
    Source = OData.Feed("https://services.odata.org/V4/Northwind/Northwind.svc/", null, [Implementation="2.0"]),
    Customers_table = Source{[Name="Customers",Signature="table"]}[Data],
    CHOPS = Customers_table{[CustomerID="CHOPS"]}[CompanyName],
    Search = Text.FromBinary(Web.Contents("https://www.bing.com/search?q=" & CHOPS))
in
    Search

Semuanya sekarang terjadi di dalam satu partisi. Dengan asumsi bahwa tingkat privasi untuk dua sumber data kompatibel, Firewall sekarang akan senang, dan Anda tidak akan lagi mendapatkan kesalahan.

Itu bungkus

Meskipun ada lebih banyak yang dapat dikatakan tentang topik ini, artikel pengantar ini sudah cukup lama. Mudah-mudahan itu memberi Anda pemahaman yang lebih baik tentang Firewall, dan akan membantu Anda memahami dan memperbaiki kesalahan Firewall ketika Anda menemukannya di masa depan.