Agregasi yang ditentukan pengguna
Agregasi di Power BI dapat meningkatkan performa kueri melalui model semantik DirectQuery besar. Dengan menggunakan agregasi, Anda meng-cache 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 dalam 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 bagi kolom agregasi dengan ringkasan, tabel detail, dan properti kolom detail.
Sumber data dimensional, seperti gudang data dan mart data, dapat menggunakan agregasi berbasis hubungan. Sumber big data 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 menampilkan baris untuk setiap kolom dalam tabel, di mana Anda bisa menentukan perilaku agregasi. Dalam contoh berikut, kueri ke tabel detail Sales (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 dicapai, 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 untuk fungsi Ringkasan Jumlah dan Jumlah baris tabel. Jumlah dan Jumlah baris tabel hanya tersedia untuk kolom agregasi bilangan bulat, dan tidak memerlukan tipe 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 ke Tabel C.
- Agregasi duplikat, di mana dua entri menggunakan fungsi Ringkasan yang sama dan merujuk ke Tabel Detail dan Kolom Detail yang sama, tidak diperbolehkan.
- 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 mencapai 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 dropdown dan menunjukkan teks penjelasan di tip alat.
Tabel agregasi disembunyikan
Pengguna dengan akses baca-saja ke model tidak dapat mengkueri tabel agregasi. Akses baca-saja menghindari masalah keamanan saat digunakan dengan keamanan tingkat baris (RLS). Konsumen dan kueri merujuk ke tabel detail, bukan tabel agregasi, dan tidak perlu tahu tentang tabel agregasi.
Karena 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. Power BI tabel dapat menggunakan mode penyimpanan DirectQuery, Impor, atau Ganda. DirectQuery meminta backend secara langsung, sementara Import meng-cache data dalam memori dan mengirim kueri ke data yang di-cache. Semua sumber data Impor Power BI dan DirectQuery non-multidimensi dapat bekerja dengan agregasi.
Untuk mengatur mode penyimpanan tabel agregat ke Import guna mempercepat kueri, pilih tabel agregat dalam tampilan Model Power BI Desktop. Di panel Properti, perluas Lanjutan, buka 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 yang diagregat. 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 yang tidak disarankan, karena kueri pengguna dari peran ini tidak mendapat manfaat dari temuan agregasi.
Ekspresi RLS yang hanya memfilter tabel agregasi Agg Penjualan 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 Agg Penjualan. Dalam tabel Agg Penjualan, jumlah baris sama dengan jumlah SalesAmount yang dikelompokkan menurut 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
- Produk Subkategori
- Produk Kategori
Gambar berikut menampilkan model ini.
Tabel berikut menampilkan agregasi untuk tabel Agg Penjualan.
Catatan
Tabel Agg Penjualan, seperti tabel lainnya, memiliki fleksibilitas pemuatan dengan berbagai cara. Agregasi dapat dilakukan dalam basis data sumber menggunakan proses ETL/ELT, atau dengan ekspresi M untuk tabel. Tabel agregat dapat menggunakan mode Penyimpanan impor, dengan atau tanpa refresh bertahap untuk model semantik, atau dapat menggunakan DirectQuery dan dioptimalkan untuk kueri cepat menggunakan indeks penyimpan kolom. Fleksibilitas ini memungkinkan arsitektur seimbang yang dapat mengurangi beban kueri dan menghindari penyempitan.
Mengubah mode penyimpanan tabel Agg Penjualan agregat ke Impor membuka kotak dialog yang mengatakan bahwa tabel dimensi terkait dapat diatur ke mode penyimpanan Ganda.
Mengatur tabel dimensi terkait ke Ganda memungkinkan tabel bertindak sebagai Impor atau DirectQuery, tergantung pada subkueri. Dalam contoh:
- Kueri yang mengagregat metrik dari tabel Agg Penjualan mode Impor, dan mengelompokkan menurut atribut dari tabel Ganda yang terkait, dapat dikembalikan dari cache dalam memori.
- Kueri yang mengagregat metrik dari tabel Penjualan DirectQuery, dan mengelompokkan menurut atribut dari tabel Dual Ganda yang terkait, dapat dikembalikan di mode DirectQuery. Logika kueri, termasuk operasi GroupBy, diteruskan ke basis data sumber.
Untuk informasi selengkapnya tentang mode penyimpanan Ganda, lihat Mengelola mode penyimpanan di Power BI Desktop.
Hubungan reguler vs. terbatas
Capaian agregasi berdasarkan hubungan memerlukan hubungan reguler.
Hubungan reguler mencakup kombinasi mode penyimpanan berikut, di mana kedua tabel berasal dari satu sumber:
| Tabel di banyak sisi | Tabel di 1 sisi |
|---|---|
| Ganda | Ganda |
| Impor | Impor atau Ganda |
| DirectQuery | DirectQuery atau Ganda |
Satu-satunya kasus di mana hubungan lintas sumber dianggap biasa adalah jika kedua tabel diatur ke Impor. Hubungan banyak ke banyak selalu dianggap terbatas.
Untuk capaian agregasi lintas sumber yang tidak bergantung pada hubungan, lihat Agregasi berdasarkan kolom GroupBy.
Contoh kueri agregasi berbasis hubungan
Kueri berikut mencapai agregasi, karena kolom dalam tabel Tanggal berada pada granularitas yang bisa menemukan agregasi. Kolom SalesAmount menggunakan agregasi Sum.
Kueri berikut ini tidak mencapai agregasi. Meski meminta jumlah SalesAmount, kueri melakukan operasi GroupBy pada kolom dalam kolom di tabel Produk, yang bukan pada granularitas yang dapat mencapai 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 tidak hanya untuk hitungan sederhana yang melakukan penjumlahan yang mudah. Agregasi juga dapat dimanfaatkan untuk hitungan kompleks. 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 di semua kasus karena optimalisasi rencana kueri, tetapi secara umum seharusnya berlaku. Contoh berikut menemukan agregasi:
Fungsi COUNTROWS dapat memperoleh manfaat dari agregasi. Kueri berikut menekan agregasi karena ada agregasi Baris tabel Hitung yang ditentukan untuk tabel Penjualan.
Fungsi AVERAGE dapat memperoleh manfaat dari agregasi. Kueri berikut mencapai agregasi karena AVERAGE secara internal dilipat ke SUM dibagi dengan COUNT. Karena kolom UnitPrice memiliki agregasi yang ditentukan untuk SUM dan COUNT, agregasi akan dicapai.
Dalam beberapa kasus, fungsi DISTINCTCOUNT dapat memperoleh manfaat dari agregasi. Kueri berikut mencapai agregasi karena ada entri GroupBy untuk CustomerKey, yang mempertahankan perbedaan 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 memiliki performa lebih cepat daripada memindai tabel dengan miliaran baris, meski telah di-cache ke dalam memori.
Fungsi kecerdasan waktu Data Analysis Expressions (DAX) sadar agregasi. Kueri berikut mencapai agregasi karena fungsi DATESYTD menghasilkan tabel nilai CalendarDay, dan tabel agregasi berada pada granularitas yang dicakup untuk kolom GroupBy dalam tabel Tanggal. Ini adalah contoh filter bernilai tabel ke fungsi CALCULATE, yang dapat berfungsi dengan agregasi.
Agregasi berdasarkan kolom GroupBy
Model big data berbasis Hadoop memiliki karakteristik yang berbeda dibandingkan model dimensi. Untuk menghindari gabungan antara tabel besar, model big data sering kali tidak menggunakan hubungan, tetapi melakukan denormalisasi atribut dimensi ke tabel fakta. Anda dapat membuka kunci model big data tersebut untuk analisis interaktif menggunakan agregasi berdasarkan kolom GroupBy.
Tabel berikut berisi kolom numerik Gerakan yang akan diagregasi. Semua kolom lainnya adalah atribut untuk dikelompokkan. Tabel berisi data IoT dan sejumlah besar baris. Mode penyimpanannya adalah DirectQuery. Kueri pada sumber data yang menggabungkan seluruh model lambat karena volume tipis.
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. Tindakan ini secara dramatis mengurangi jumlah baris, dan cukup kecil untuk masuk ke dalam cache dalam memori.
Anda menentukan pemetaan agregasi untuk tabel Driver Activity Agg dalam dialog Kelola agregasi.
Dalam agregasi berdasarkan kolom GroupBy, entri GroupBy tidak bersifat opsional. Tanpa mereka, agregasi tidak terpukul. Ini berbeda dari menggunakan agregasi berdasarkan hubungan, di mana entri GroupBy bersifat opsional.
Tabel berikut menampilkan agregasi untuk tabel Driver Activity Agg.
Anda dapat mengatur mode penyimpanan tabel Driver Activity Agg agregat ke Impor.
Contoh kueri agregasi GroupBy
Kueri berikut mencapai agregasi, karena kolom Tanggal Aktivitas dicakup oleh tabel agregasi. Fungsi COUNTROWS menggunakan agregasi baris tabel terhitung.
Terutama untuk model yang berisi atribut filter dalam tabel fakta, ada baiknya menggunakan agregasi Jumlah 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 agregasi gabungan
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 mereplikasiBulan, Kuartal, Semester, dan Tahun dalam tabel Agg Penjualan. Tidak ada hubungan antara Sales Agg dan tabel Tanggal , tetapi ada hubungan dengan Pelanggan dan Subkategorer Produk. Mode penyimpanan Agg Penjualan adalah Impor.
Tabel berikut ini memperlihatkan entri yang diatur dalam dialog Kelola agregasi untuk tabel Agg Penjualan. Entri GroupBy di mana Tanggal adalah tabel detail wajib, untuk mencapai agregasi untuk kueri yang dikelompokkan menurut atribut Tanggal. Seperti dalam contoh sebelumnya, entri GroupBy untuk CustomerKey dan ProductSubcategoryKey tidak memengaruhi pencapaian agregasi, kecuali DISTINCTCOUNT, karena adanya hubungan.
Contoh kueri agregasi gabungan
Kueri berikut mencapai agregasi, karena tabel agregasi yang mencakup CalendarMonth, dan CategoryName dapat diakses melalui hubungan satu ke banyak. SalesAmount menggunakan agregasi SUM.
Kueri berikut ini 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.
Prioritas agregasi
Prioritas agregasi memungkinkan beberapa tabel agregasi untuk 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 big data. Tabel ini melayani kueri penelusuran untuk melihat pembacaan IoT individual dalam konteks filter terkontrol.
- Tabel Driver Activity Agg adalah tabel agregasi perantara dalam mode DirectQuery. Tabel ini berisi lebih dari satu miliar baris di Azure Synapse Analytics (sebelumnya SQL Data Warehouse) dan dioptimalkan di sumber menggunakan indeks penyimpan kolom.
- Tabel Impor Driver Activity Agg2 berada pada granularitas tinggi, karena atribut group-by sedikit dan kardinalitasnya rendah. Jumlah baris bisa serendah ribuan, sehingga dapat dengan mudah masuk ke cache dalam memori. Atribut ini kebetulan digunakan oleh dasbor eksekutif profil tinggi, sehingga kueri yang merujuknya haruslah secepat mungkin.
Catatan
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 SQL Data Warehouse).
Jejak memori model ini relatif kecil, tetapi membuka kunci model besar. Tabel ini mewakili arsitektur yang 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 Agg2 Aktivitas Driver terlebih dahulu. Subkueri yang tidak berada pada granularitas yang dapat dijawab oleh Driver Activity Agg2 dapat mempertimbangkan Agg Aktivitas Driver 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 menampilkan agregasi untuk tabel Driver Activity Agg2.
Mendeteksi apakah kueri mencapai atau melewatkan 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 dicapai. Untuk informasi selengkapnya, lihat Kueri yang mencapai 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.
- mapping (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 seperti itu di sumbernya, jika perlu. Optimalisasi performa harus digunakan hanya dengan cara yang tidak membahayakan kemampuan Anda untuk memenuhi persyaratan bisnis. Anda bertanggung jawab untuk mengetahui aliran data dan harus merancangnya dengan tepat.
Pertimbangan dan batasan
Agregasi tidak mendukung Parameter Kueri M Dinamis.
Mulai Agustus 2022, karena perubahan fungsionalitas, Power BI mengabaikan tabel agregasi mode impor dengan sumber data yang diaktifkan akses menyeluruh (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 otomatis, pastikan Anda memeriksa sumber daya tambahan ini: