Agregasi yang ditentukan pengguna

Agregasi di Power BI dapat meningkatkan performa kueri melalui model semantik DirectQuery besar. Dengan menggunakan agregasi, Anda menyimpan data di tingkat agregat dalam memori. Agregasi di Power BI dapat dikonfigurasi secara manual dalam model data, seperti yang dijelaskan dalam artikel ini. Untuk langganan Premium, secara otomatis dengan mengaktifkan fitur Agregasi otomatis di Pengaturan model.

Membuat tabel agregasi

Bergantung pada jenis sumber data, tabel agregasi dapat dibuat di sumber data sebagai tabel atau tampilan, kueri asli. Untuk performa terbesar, buat tabel agregasi sebagai tabel impor yang dibuat di Power Query. Anda kemudian menggunakan dialog Kelola agregasi di Power BI Desktop untuk menentukan agregasi untuk kolom agregasi dengan ringkasan, tabel detail, dan properti kolom detail.

Sumber data dimensi, seperti gudang data dan mart data, dapat menggunakan agregasi berbasis hubungan. Sumber data besar berbasis Hadoop sering kali mendasarkan agregasi pada kolom GroupBy. Artikel ini menjelaskan perbedaan pemodelan data Power BI umum untuk setiap jenis sumber data.

Mengelola agregasi

Di panel Data dari tampilan Power BI Desktop apa pun, klik kanan tabel agregasi, lalu pilih Kelola agregasi.

Dialog Kelola agregasi memperlihatkan baris untuk setiap kolom dalam tabel, di mana Anda bisa menentukan perilaku agregasi. Dalam contoh berikut, kueri ke tabel detail Penjualan dialihkan secara internal ke tabel agregasi Sales Agg.

Dalam contoh agregasi berbasis hubungan ini, entri GroupBy bersifat opsional. Kecuali untuk DISTINCTCOUNT, mereka tidak memengaruhi perilaku agregasi dan terutama untuk keterbacaan. Tanpa entri GroupBy, agregasi masih akan terjadi, berdasarkan hubungan. Ini berbeda dari contoh big data nanti di artikel ini, di mana entri GroupBy diperlukan.

Validasi

Dialog Kelola agregasi memberlakukan validasi:

• Kolom Detail harus memiliki tipe data yang sama dengan Kolom Agregasi, kecuali pada fungsi Ringkasan untuk baris Hitungan dan baris pada tabel Hitungan. Hitung dan penghitungan baris tabel hanya tersedia untuk kolom agregasi bilangan bulat, dan tidak memerlukan jenis data yang cocok.
• Agregasi berantai yang mencakup tiga tabel atau lebih tidak diizinkan. Misalnya, agregasi pada Tabel A tidak dapat merujuk ke Tabel B yang memiliki agregasi yang mengacu pada Tabel C.
• Agregasi duplikat, di mana dua entri menggunakan fungsi Ringkasan yang sama dan merujuk ke Tabel Detail dan Kolom Detail yang sama, tidak diizinkan.
• Tabel Detail harus menggunakan mode penyimpanan DirectQuery, bukan Impor.
• Pengelompokan menurut kolom kunci asing yang digunakan oleh hubungan yang tidak aktif, dan mengandalkan fungsi USERELATIONSHIP untuk temuan agregasi, tidak didukung.
• Agregasi berdasarkan kolom GroupBy dapat menggunakan hubungan antara tabel agregasi tetapi hubungan penulisan antar tabel agregasi tidak didukung di Power BI Desktop. Jika perlu, Anda dapat membuat hubungan antara tabel agregasi dengan menggunakan alat pihak ketiga atau solusi pembuatan skrip melalui titik akhir XML untuk Analisis (XMLA).

Sebagian besar validasi diberlakukan dengan menonaktifkan nilai dalam menu tarik-turun dan menampilkan teks penjelasan di tooltip.

Tabel agregasi disembunyikan

Pengguna dengan akses baca-saja ke model tidak dapat mengkueri tabel agregasi. Akses baca-saja menghindari masalah keamanan ketika digunakan dengan keamanan tingkat baris (RLS). Konsumen dan kueri merujuk ke tabel detail, bukan tabel agregasi, dan tidak perlu tahu tentang tabel agregasi.

Untuk alasan ini, tabel agregasi disembunyikan dari tampilan Laporan . Jika tabel belum disembunyikan, dialog Kelola agregasi mengaturnya ke tersembunyi saat Anda memilih Terapkan semua.

Mode penyimpanan

Fitur agregasi berinteraksi dengan mode penyimpanan tingkat tabel. Tabel Power BI dapat menggunakan mode penyimpanan DirectQuery, Impor, atau Dual. DirectQuery meminta backend secara langsung, sementara Import menyimpan data dalam memori dan mengirim kueri ke data yang tersimpan tersebut. Semua sumber data Import dan DirectQuery non-multidimensional di Power BI dapat bekerja dengan agregasi.

Untuk mengatur mode penyimpanan tabel agregat ke Impor untuk mempercepat kueri, pilih tabel agregat dalam tampilan Model Desktop Power BI. Di panel Properti , perluas Tingkat Lanjut, turun bawah pilihan di bawah Mode penyimpanan, dan pilih Impor. Mengubah Impor tidak dapat diubah.

Untuk mempelajari selengkapnya tentang mode penyimpanan tabel, lihat Mengelola mode penyimpanan di Power BI Desktop.

RLS untuk agregasi

Untuk bekerja dengan benar untuk agregasi, ekspresi RLS harus memfilter tabel agregasi dan tabel detail.

Dalam contoh berikut, ekspresi RLS pada tabel Geografi berfungsi untuk agregasi, karena Geografi berada di sisi pemfilteran hubungan ke tabel Penjualan dan tabel Sales Agg . Kueri yang mencapai tabel agregasi dan kueri yang tidak berhasil diterapkan RLS.

Ekspresi RLS pada tabel Produk hanya memfilter tabel Penjualan detail, bukan tabel Agg Penjualan agregat. Karena tabel agregasi adalah representasi lain dari data dalam tabel detail, tidak aman untuk menjawab kueri dari tabel agregasi jika filter RLS tidak dapat diterapkan. Hanya memfilter tabel detail tidak disarankan, karena kueri pengguna dari peran ini tidak mendapat manfaat dari hasil agregasi.

Ekspresi RLS yang memfilter hanya tabel agregasi Penjualan Agg dan bukan tabel detail Penjualan tidak diizinkan.

Untuk agregasi berdasarkan kolom GroupBy, ekspresi RLS yang diterapkan ke tabel detail dapat digunakan untuk memfilter tabel agregasi, karena semua kolom GroupBy dalam tabel agregasi dicakup oleh tabel detail. Di sisi lain, filter RLS pada tabel agregasi tidak dapat diterapkan ke tabel detail, sehingga tidak diizinkan.

Agregasi berdasarkan hubungan

Model dimensi biasanya menggunakan agregasi berdasarkan hubungan. Model Power BI dari gudang data dan mart data menyerupai skema bintang/snowflake, dengan hubungan antara tabel dimensi dan tabel fakta.

Dalam contoh berikut, model mendapatkan data dari satu sumber data. Tabel menggunakan mode penyimpanan DirectQuery. Tabel Fakta penjualan berisi miliaran baris. Mengatur mode penyimpanan Penjualan ke Impor untuk penembolokan akan mengonsumsi memori dan overhead sumber daya yang cukup besar.

Sebagai gantinya, buat tabel agregasi Sales Agg. Dalam tabel Sales Agg, jumlah baris sama dengan jumlah total SalesAmount yang dikelompokkan berdasarkan CustomerKey, DateKey, dan ProductSubcategoryKey. Tabel Sales Agg berada pada granularitas yang lebih tinggi daripada Penjualan, jadi alih-alih miliaran, tabel tersebut mungkin berisi jutaan baris, yang lebih mudah dikelola.

Jika tabel dimensi berikut digunakan paling umum untuk kueri dengan nilai bisnis tinggi, tabel tersebut dapat memfilter Sales Agg, menggunakan hubungan satu-ke-banyak atau banyak-ke-satu .

• Geografi
• Pelanggan
• Tanggal
• Subkategori Produk
• Kategori Produk

Gambar berikut menunjukkan model ini.

Tabel berikut ini memperlihatkan agregasi untuk tabel Sales Agg .

Nota

Tabel Sales Agg , seperti tabel apa pun, memiliki fleksibilitas dimuat dengan berbagai cara. Agregasi dapat dilakukan dalam database sumber menggunakan proses ETL/ELT, atau dengan ekspresi M untuk tabel. Tabel agregat dapat menggunakan mode penyimpanan import, dengan atau tanpa refresh bertahap untuk model semantik, atau dapat menggunakan DirectQuery dan dioptimalkan untuk kueri cepat menggunakan indeks kolom. Fleksibilitas ini memungkinkan arsitektur seimbang yang dapat menyebarkan beban kueri untuk menghindari penyempitan.

Mengubah mode penyimpanan tabel agregat Agg Penjualan menjadi mengimpor membuka kotak dialog yang menyatakan bahwa tabel dimensi terkait dapat diubah ke mode penyimpanan ganda.

Mengatur tabel dimensi terkait ke Dual memungkinkan mereka bertindak sebagai Impor atau DirectQuery, tergantung pada subkueri. Dalam contoh:

• Kueri yang menggabungkan metrik dari tabel Sales Agg mode Impor, dan mengelompokkan menurut atribut dari tabel Ganda terkait, dapat dikembalikan dari cache dalam memori.
• Kueri yang menggabungkan metrik dari tabel DirectQuery Sales , dan mengelompokkan menurut atribut dari tabel Ganda terkait, dapat dikembalikan dalam mode DirectQuery. Logika kueri, termasuk operasi GroupBy, diteruskan ke database sumber.

Untuk informasi selengkapnya tentang Mode penyimpanan ganda, lihat Mengelola mode penyimpanan di Power BI Desktop.

Hubungan reguler vs. terbatas

Hit agregasi berdasarkan hubungan memerlukan hubungan yang teratur.

Hubungan reguler mencakup kombinasi mode penyimpanan berikut, di mana kedua tabel berasal dari satu sumber:

Tabel di banyak sisi	Tabel di bagian 1
Ganda	Ganda
Mengimpor	Impor atau Rangkap
Kueri Langsung	DirectQuery atau Dual

Satu-satunya kasus di mana hubungan lintas sumber dianggap reguler adalah jika kedua tabel disetel ke Impor. Hubungan banyak ke banyak selalu dianggap terbatas.

Untuk agregasi lintas sumber yang tidak bergantung pada hubungan, lihat Agregasi berdasarkan kolom GroupBy.

Contoh kueri agregasi berbasis hubungan

Kueri berikut mengenai agregasi, karena kolom dalam tabel Tanggal memiliki tingkat granularitas yang sesuai untuk mencapai agregasi. Kolom SalesAmount menggunakan agregasi Jumlah .

Kueri berikut tidak menghasilkan agregasi. Meskipun meminta jumlah SalesAmount, kueri melakukan operasi GroupBy pada kolom dalam tabel Produk, yang tidak berada pada tingkat granularitas yang mendukung agregasi. Jika Anda mengamati hubungan dalam model, subkategori produk dapat memiliki beberapa baris Produk. Kueri tidak akan dapat menentukan produk mana yang akan diagregasi. Dalam hal ini, kueri kembali ke DirectQuery dan mengirimkan kueri SQL ke sumber data.

Agregasi bukan hanya untuk perhitungan sederhana yang melakukan penjumlahan biasa. Perhitungan kompleks juga dapat menguntungkan. Secara konseptual, perhitungan kompleks dipecah menjadi subkueri untuk setiap SUM, MIN, MAX, dan COUNT. Setiap subkueri dievaluasi untuk menentukan apakah subkueri dapat mencapai agregasi. Logika ini tidak berlaku dalam semua kasus karena pengoptimalan rencana kueri, tetapi secara umum harus berlaku. Contoh berikut memicu agregasi:

Fungsi COUNTROWS dapat memperoleh manfaat dari agregasi. Kueri berikut memicu agregasi karena ada agregasi Hitung baris tabel yang ditentukan untuk tabel Penjualan.

Fungsi AVERAGE dapat memperoleh manfaat dari agregasi. Kueri berikut mencapai agregasi karena AVERAGE secara internal dikonversi menjadi SUM dibagi dengan COUNT. Karena kolom UnitPrice memiliki agregasi yang ditentukan untuk SUM dan COUNT, agregasi terpukul.

Dalam beberapa kasus, fungsi DISTINCTCOUNT dapat memperoleh manfaat dari agregasi. Kueri berikut ini mengenai agregasi karena terdapat entri GroupBy untuk CustomerKey, yang menjaga keunikan CustomerKey dalam tabel agregasi. Teknik ini mungkin masih mencapai ambang performa di mana lebih dari dua hingga lima juta nilai yang berbeda dapat memengaruhi performa kueri. Namun, ini dapat berguna dalam skenario di mana ada miliaran baris dalam tabel detail, tetapi dua hingga lima juta nilai berbeda dalam kolom. Dalam hal ini, DISTINCTCOUNT dapat berkinerja lebih cepat daripada memindai tabel dengan miliaran baris, bahkan jika di-cache ke dalam memori.

Fungsi intelijensi waktu pada Data Analysis Expressions (DAX) menyadari agregasi. Kueri berikut mengenai agregasi karena fungsi DATESYTD menghasilkan tabel nilai CalendarDay dan tabel agregasi berada pada granularitas yang dicakup oleh kolom pengelompokan dalam tabel Tanggal. Ini adalah contoh filter dengan nilai tabel untuk fungsi CALCULATE, yang dapat digunakan dengan agregasi.

Agregasi berdasarkan kolom GroupBy

Model big data berbasis Hadoop memiliki karakteristik yang berbeda dari model dimensi. Untuk menghindari gabungan antara tabel besar, model big data sering tidak menggunakan hubungan, tetapi mendenormalisasi atribut dimensi ke tabel fakta. Anda dapat membuka kunci model big data tersebut untuk analisis interaktif dengan menggunakan agregasi berdasarkan kolom GroupBy.

Tabel berikut berisi kolom numerik Gerakan yang akan diagregasi. Semua kolom lainnya adalah atribut untuk dikelompokkan menurut. Tabel berisi data IoT dan sejumlah besar baris. Mode penyimpanannya adalah DirectQuery. Kueri pada sumber data yang mengagregasi seluruh model lambat karena volume besar.

Untuk mengaktifkan analisis interaktif pada model ini, Anda dapat menambahkan tabel agregasi yang mengelompokkan menurut sebagian besar atribut, tetapi mengecualikan atribut kardinalitas tinggi seperti garis bujur dan lintang. Ini secara dramatis mengurangi jumlah baris, dan cukup kecil untuk masuk dengan nyaman ke dalam cache dalam memori.

Anda menentukan pemetaan agregasi untuk tabel Agg Aktivitas Driver dalam dialog Kelola agregasi .

Dalam agregasi berdasarkan kolom GroupBy, entri GroupBy tidak opsional. Tanpa mereka, agregasi tidak terpukul. Ini berbeda dari menggunakan agregasi berdasarkan hubungan, di mana entri GroupBy bersifat opsional.

Tabel di bawah ini menunjukkan agregasi untuk tabel Aktivitas Pengemudi Agg.

Anda dapat mengatur mode penyimpanan tabel agregat Aktivitas Driver ke Impor.

Contoh kueri agregasi GroupBy

Kueri berikut mengakses agregasi, karena kolom Tanggal Aktivitas dicakup oleh tabel agregasi. Fungsi COUNTROWS menggunakan agregasi baris yang dihitung dalam tabel.

Terutama untuk model yang berisi atribut filter pada tabel faktanya, lebih baik menggunakan agregasi Menghitung baris tabel. Power BI dapat mengirimkan kueri ke model menggunakan COUNTROWS jika tidak diminta secara eksplisit oleh pengguna. Misalnya, dialog filter memperlihatkan jumlah baris untuk setiap nilai.

Teknik penggabungan agregasi

Anda dapat menggabungkan hubungan dan teknik kolom GroupBy untuk agregasi. Agregasi berdasarkan hubungan dapat mengharuskan tabel dimensi yang didenormalisasi dibagi menjadi beberapa tabel. Jika ini mahal atau tidak praktis untuk tabel dimensi tertentu, Anda dapat mereplikasi atribut yang diperlukan dalam tabel agregasi untuk dimensi tersebut, dan menggunakan hubungan untuk orang lain.

Misalnya, model berikut mereplikasi Bulan, Kuartal, Semester, dan Tahun dalam tabel Sales Agg . Tidak ada hubungan antara Sales Agg dan tabel Tanggal , tetapi ada hubungan dengan Pelanggan dan Subkategorer Produk. Mode penyimpanan Sales Agg adalah Impor.

Tabel berikut menunjukkan entri yang diatur dalam dialog Kelola agregasi untuk tabel Sales Agg. Entri GroupBy di mana Tanggal adalah tabel detail yang bersifat wajib, untuk memastikan aggregasi pada kueri yang dikelompokkan berdasarkan atribut Tanggal. Seperti dalam contoh sebelumnya, entri GroupBy untuk CustomerKey dan ProductSubcategoryKey tidak memengaruhi hit agregasi, kecuali untuk DISTINCTCOUNT, karena adanya relasi.

Contoh kueri agregasi gabungan

Kueri berikut mencapai agregasi, karena tabel agregasi mencakup CalendarMonth, dan CategoryName dapat diakses melalui hubungan satu-ke-banyak. SalesAmount menggunakan agregasi SUM .

Kueri berikut tidak mencapai agregasi, karena tabel agregasi tidak mencakup CalendarDay.

Kueri inteligensi waktu berikut tidak mencapai agregasi, karena fungsi DATESYTD menghasilkan tabel nilai CalendarDay , dan tabel agregasi tidak mencakup CalendarDay.

Urutan agregasi

Prioritas agregasi memungkinkan beberapa tabel agregasi dipertimbangkan oleh satu subkueri.

Contoh berikut adalah model komposit yang berisi beberapa sumber:

• Tabel DirectQuery Aktivitas Driver berisi lebih dari satu triliun baris data IoT yang bersumber dari sistem data besar. Ini melayani kueri drillthrough untuk melihat data IoT individual dalam konteks penyaringan yang dikendalikan.
• Tabel Driver Activity Agg adalah tabel agregasi perantara dalam mode DirectQuery. Ini berisi lebih dari satu miliar baris di Azure Synapse Analytics (sebelumnya Gudang Data SQL) dan dioptimalkan di sumber menggunakan indeks penyimpan kolom.
• Tabel Impor Driver Activity Agg2 memiliki tingkat granularitas yang tinggi, karena atribut pengelompokan sedikit dan memiliki kardinalitas rendah. Jumlah baris bisa serendah ribuan, sehingga dapat dengan mudah masuk ke dalam cache dalam memori. Kebetulan, atribut-atribut ini digunakan oleh dashboard eksekutif yang memiliki profil tinggi, sehingga kueri yang merujuk padanya harus secepat mungkin.

Nota

Tabel agregasi DirectQuery yang menggunakan sumber data yang berbeda dari tabel detail hanya didukung jika tabel agregasi berasal dari sumber SQL Server, Azure SQL, atau Azure Synapse Analytics (sebelumnya Gudang Data SQL).

Jejak memori model ini relatif kecil, tetapi membuka kunci model besar. Ini mewakili arsitektur seimbang karena menyebarkan beban kueri di seluruh komponen arsitektur, menggunakannya berdasarkan kekuatannya.

Dialog Agregasi terkelola untuk Agg2 Aktivitas Driver mengatur bidang Prioritas ke 10, yang lebih tinggi dari untuk Agg Aktivitas Driver. Pengaturan prioritas yang lebih tinggi berarti kueri yang menggunakan agregasi mempertimbangkan Driver Activity Agg2 terlebih dahulu. Subkueri yang tidak berada pada granularitas yang dapat dijawab oleh Driver Activity Agg2 dapat mempertimbangkan Driver Activity Agg sebagai gantinya. Kueri detail yang tidak dapat dijawab oleh tabel agregasi dapat diarahkan ke Aktivitas Driver.

Tabel yang ditentukan dalam kolom Tabel Detail adalah Aktivitas Driver, bukan Agg Aktivitas Driver, karena agregasi berantai tidak diizinkan.

Tabel berikut ini memperlihatkan agregasi untuk tabel Aktivitas Pengemudi Agg2.

Mendeteksi apakah kueri mencapai atau gagal pada agregasi

SQL Profiler dapat mendeteksi apakah kueri dikembalikan dari mesin penyimpanan cache dalam memori, atau didorong ke sumber data oleh DirectQuery. Anda dapat menggunakan proses yang sama untuk mendeteksi apakah agregasi terpengaruh. Untuk informasi selengkapnya, lihat Kueri yang mengenai atau melewatkan cache.

SQL Profiler juga menyediakan peristiwa Query Processing\Aggregate Table Rewrite Query yang diperluas.

Cuplikan JSON berikut menunjukkan contoh output peristiwa saat agregasi digunakan.

• matchingResult menunjukkan bahwa subkueri menggunakan agregasi.
• dataRequest memperlihatkan kolom GroupBy dan kolom agregat subkueri yang digunakan.
• Pemetaan memperlihatkan kolom dalam tabel agregasi yang dipetakan.

Menjaga cache tetap sinkron

Agregasi yang menggabungkan mode penyimpanan DirectQuery, Import, dan/atau Dual dapat mengembalikan data yang berbeda kecuali cache dalam memori tetap sinkron dengan data sumber. Misalnya, eksekusi kueri tidak mencoba menutupi masalah data dengan memfilter hasil DirectQuery agar sesuai dengan nilai cache. Ada teknik yang mapan untuk menangani masalah tersebut di sumbernya, jika perlu. Pengoptimalan performa harus digunakan hanya dengan cara yang tidak membahayakan kemampuan Anda untuk memenuhi persyaratan bisnis. Anda bertanggung jawab untuk mengetahui aliran dan desain data Anda yang sesuai.

Pertimbangan dan batasan

• Agregasi tidak mendukung Parameter Kueri M Dinamis.

• Mulai Agustus 2022, karena perubahan fungsionalitas, Power BI mengabaikan tabel agregasi mode impor yang memiliki sumber data diaktifkan dengan single sign-on (SSO) karena potensi risiko keamanan. Untuk memastikan performa kueri yang optimal dengan agregasi, sebaiknya nonaktifkan SSO untuk sumber data ini.

Komunitas

Power BI memiliki komunitas semarak di mana MVP, pro BI, dan rekan berbagi keahlian dalam grup diskusi, video, blog, dan banyak lagi. Saat mempelajari tentang agregasi, pastikan untuk memeriksa sumber daya tambahan ini:

• Komunitas Power BI
• Lakukan pencarian "Agregasi Power BI" di Bing

Konten terkait

• Agregasi otomatis
• Model komposit