Gunakan partisi tabel panas dan dingin untuk mengoptimalkan model data Power BI yang sangat besar

Artikel ini menjelaskan cara menggunakan partisi tabel panas dan dingin untuk mengoptimalkan model data yang sangat besar. Partisi menyediakan cara untuk membagi data tabel menjadi subset diskrit. Partisi tidak secara langsung diekspos di alat pemodelan data Power BI standar, tetapi Anda dapat memanfaatkan metode pemartisian tingkat lanjut dengan mengonfigurasi kebijakan refresh inkremental di Power BI Desktop. Refresh inkremental bergantung pada partisi, seperti yang dijelaskan dalam Refresh inkremental dan data real-time untuk himpunan data. Namun, mengonfigurasi partisi tabel panas dan dingin melampaui apa yang dapat dicapai oleh kebijakan refresh inkremental dan mengasumsikan keakraban dengan skema pemartisian tabel yang khas dan alat berbasis XMLA.

Prasyarat

Karena kompleksitas relatif dari teknik partisi ini, paling cocok untuk pengguna tingkat lanjut dengan pengalaman di area berikut:

1. Memahami konsep partisi tabel, cara kerja partisi mode impor, mode DirectQuery , dan Mode ganda .

2. Pengetahuan tentang cara membuat tabel hibrid menggunakan alat berbasis XMLA. Tabel hibrid menggunakan satu atau beberapa partisi mode impor dan satu partisi DirectQuery .

3. Pengetahuan tentang persyaratan fungsi DAX yang dapat Anda gunakan untuk menentukan DataCoverageDefinition. Ini adalah properti baru untuk partisi DirectQuery untuk menjelaskan data apa yang dimuat partisi DirectQuery dari tabel hibrid sehingga mesin Power BI dapat mengecualikan partisi ini dari pemrosesan kueri jika sesuai. Mengecualikan partisi DirectQuery dapat membantu menghindari kueri sumber data yang tidak perlu dan meningkatkan performa pemrosesan kueri DAX.

4. Memahami perbedaan antara hubungan tabel reguler dan terbatas. Misalnya, fungsi RELATED berguna jika Anda ingin menentukan cakupan data partisi tabel fakta berdasarkan nilai dari tabel dimensi tanggal terkait. Perlu diingat bahwa partisi tabel fakta adalah partisi DirectQuery dengan kemungkinan hubungan terbatas dengan tabel tanggal di mana fungsi RELATED tidak dapat mengambil nilai. Dalam skenario ini, RELATED hanya berfungsi jika tabel dimensi tanggal adalah tabel ganda. Tabel tanggal harus dalam mode DirectQuery atau Dual . Ini tidak bisa impor murni.

Ketahuilah bahwa yang salah ditentukan DataCoverageDefinition dapat menyebabkan hasil yang salah karena Power BI mungkin salah mengecualikan partisi DirectQuery dari pemrosesan kueri. Jadi, pastikan Anda membandingkan hasilnya dengan dan tanpa untuk memastikan hasilnya DataCoverageDefinition bertambah.

Kapan menggunakan partisi tabel panas dan dingin

Berikut adalah contoh di mana partisi panas dan dingin dapat membantu menyempurnakan tabel hibrid untuk analisis historis. Asumsikan Anda memiliki sumber data yang sangat besar, terakumulasi selama bertahun-tahun. Penggunaan utama adalah menganalisis data terbaru dari beberapa tahun terakhir. Terkadang, Anda juga ingin menganalisis data yang lebih lama. Mungkin Anda melihat peningkatan penjualan tajam baru-baru ini dari tahun ke tahun. Apakah itu pernah terjadi sebelumnya? Apakah lonjakan penjualan tertinggi sejak awal pelacakan penjualan?

Tanpa dukungan untuk partisi panas dan dingin, analisis historis semacam ini akan mengharuskan Anda untuk mengimpor semua data historis bersama dengan data yang lebih baru ke dalam tabel fakta. Yang terbaik, ini adalah penggunaan sumber daya yang tidak efisien karena analisis utama bahkan tidak menggunakan data historis yang lebih lama. Paling buruk, volume data sangat besar sehingga bahkan tidak dapat diimpor secara penuh. Anda harus mengalihkan model data ke mode DirectQuery dan menerima penalti performa dibandingkan dengan mode impor, atau Anda dapat membangun model terpisah dan memaksa pengguna anda untuk beralih antar laporan. Tabel hibrid dengan partisi panas dan dingin memberi Anda pilihan yang lebih baik.

Cara menggunakan partisi tabel panas dan dingin

Pertama, konfigurasikan tabel penjualan dengan partisi mode impor panas untuk data terbaru dan simpan data lama dalam partisi DirectQuerydingin, seperti yang diilustrasikan diagram berikut untuk tabel FactInternetSales dari model data sampel AdventureWorks. Setiap baris dengan OrderDateKey yang lebih besar dari atau sama dengan 20200101 diimpor ke dalam model data melalui partisi mode impor panas. Baris dengan OrderDateKey kurang dari 20200101 dicakup melalui partisi DirectQuery dingin. Sekarang, Power BI dapat mengirimkan kasus penggunaan utama dengan cepat dengan mode impor, dan Anda tidak perlu mengimpor volume data historis yang luas yang hanya Anda analisis sesekali karena partisi DirectQuery telah membahas hal ini.

Jika Anda memiliki gudang data sampel AdventureWorks dan ingin mengikutinya, berikut adalah langkah-langkah umumnya:

1. Buat himpunan data. Gunakan Power BI Desktop untuk membuat himpunan data dan laporan AdventureWorks. Sertakan semua tabel dalam mode DirectQuery murni. Kemudian, konversikan semua tabel kecuali tabel ke FactInternetSales mode Ganda . FactInternetSales Biarkan tabel dalam mode DirectQuery.

2. Unggah himpunan data. Gunakan ruang kerja yang dihosting di Power BI Premium dengan titik akhir XMLA diaktifkan untuk operasi tulis.

3. Perbarui tingkat kompatibilitas. Buka ruang kerja dengan himpunan data AdventureWorks Anda di SQL Server Management Studio (SSMS). Klik kananDatabase SkripSkrip> himpunan >data AdventureWorkssebagai Buat atau Ganti ke, dan pilih Jendela editor kueri baru. Atur properti compatibilityLevel ke 1603 (atau lebih tinggi). Pilih Jalankan atau tekan F5. Verifikasi bahwa operasi berhasil diselesaikan.

   

4. Konfigurasikan partisi tabel FactInternetSales. Klik kananDatabase SkripSkrip> himpunan >data AdventureWorkssebagai Buat atau Ganti ke, dan pilih Jendela editor kueri baru. Ganti seluruh bagian partisi dengan bagian berikut. Pastikan Anda memperbarui baris Sql.Database untuk menunjuk ke database AdventureWorksDW di lingkungan Anda. Pilih Jalankan atau tekan F5. Verifikasi bahwa operasi berhasil diselesaikan.

      "partitions": [ 
       { 
         "name": "FactInternetSales-DQ-Partition", 
         "mode": "directQuery", 
         "dataView": "full", 
         "source": { 
           "type": "m", 
           "expression": [ 
             "let", 
             "    Source = Sql.Database(\"demo.database.windows.net\", \"AdventureWorksDW\"),", 
             "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
             "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] < 20200101)", 
             "in", 
             "    #\"Filtered Rows\"" 
           ] 
         } 
       }, 
       { 
         "name": "FactInternetSales-Import-Partition", 
         "mode": "import", 
         "source": { 
           "type": "m", 
           "expression": [ 
             "let", 
             "    Source = Sql.Database(\"demo.database.windows.net\", \"AdventureWorksDW\"),", 
             "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
             "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] >= 20200101)", 
             "in", 
             "    #\"Filtered Rows\"" 
           ] 
         } 
       } 
     ],    
   

5. Memproses model data. Di portal Power BI, buka ruang kerja dengan himpunan data AdventureWorks Anda dan lakukan refresh set data sesuai permintaan untuk memuat partisi impor dengan data.

6. Verifikasi bahwa laporan menunjukkan data terbaru dan historis. Buka AdventureWorks Anda dan verifikasi bahwa laporan dapat menampilkan hasil untuk transaksi penjualan sebelum dan sesudah 1 Jan 2020, seperti pada cuplikan layar berikut.

Menentukan cakupan data partisi DirectQuery

Solusi ini bekerja dengan mulus atas data terbaru dan historis. Namun, secara default Power BI mengkueri semua partisi tabel, karena tidak tahu data apa yang dicakup setiap partisi. Oleh karena itu, Power BI masih meminta partisi DirectQuery bahkan untuk tahun-tahun yang tidak dicakup partisi DirectQuery. Data penjualan tersedia di partisi impor dan partisi DirectQuery tidak berkontribusi pada baris apa pun, tetapi kueri sumber yang berlebihan ini masih dapat menyebabkan beban yang nyata pada sumber data dan berkontribusi penundaan pemrosesan kueri DAX. Untuk menghindari kueri sumber yang berlebihan ini, gunakan DataCoverageDefinition.

Seperti yang ditunjukkan oleh cuplikan layar berikut, laporan Power BI masih mengirim beberapa kueri SQL yang tidak perlu untuk 2020 ke sumber data karena setiap kueri DAX visual menyebabkan Power BI mengkueri partisi DirectQuery .

Dengan mengatur dataCoverageDefinition properti pada partisi DirectQuery seperti dalam cuplikan TMSL berikut, kueri SQL ini dihindari. Namun, perlu diingat bahwa Anda harus merefresh himpunan data setelah menerapkan atau mengubah definisi cakupan data. Pengkalaan ulang proses sudah cukup untuk mengevaluasi definisi cakupan data. Jika Anda lupa langkah ini, kueri yang menyentuh partisi gagal dengan pesan kesalahan yang menyatakan "DataCoverageDefinition dari partisi DQ dalam tabel '[Nama Tabel]' belum dihitung setelah perubahan terbaru. Perlu diolah ulang".

        { 
          "name": "FactInternetSales-DQ-Partition", 
          "mode": "directQuery", 
          "dataView": "full", 
          "source": { 
            "type": "m", 
            "expression": [ 
              "let", 
              "    Source = Sql.Database(\"demopm.database.windows.net\", \"AdventureWorksDW2020\"),", 
              "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
              "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] < 20200101)", 
              "in", 
              "    #\"Filtered Rows\"" 
            ] 
          },  
"dataCoverageDefinition": {  
                  "description": "DQ partition with all sales from 2017, 2018, and 2019.",  
                  "expression": "RELATED('DimDate'[CalendarYear]) IN {2017,2018,2019}"  
                }  
        } 

Seperti disebutkan sebelumnya, dataCoverageDefinition properti membantu menghilangkan beban sumber data yang tidak perlu. Ini juga meningkatkan performa analisis untuk data terbaru karena sekarang Power BI dapat mengecualikan partisi DirectQuery dari pemrosesan kueri DAX jika sesuai. Anda dapat menentukan ekspresi cakupan data langsung untuk nilai tunggal serta rentang dengan operator AND, OR, dan NOT sederhana. Anda juga dapat menggunakan fungsi RELATED untuk menentukan cakupan data berdasarkan kolom dari tabel dimensi yang memiliki hubungan reguler dengan tabel fakta. Jika ekspresi cakupan data menggunakan kolom dari tabel dimensi, pastikan tabel dimensi dalam mode ganda . Anda juga dapat menentukan cakupan data berdasarkan kolom dari tabel fakta itu sendiri. Lihat tabel berikut untuk operasi yang didukung, dikategorikan ke dalam tiga grup. 

Properti DataCoverageDefinition pada partisi DirectQuery memungkinkan Anda mengoptimalkan bahkan model data Power BI terbesar berdasarkan partisi panas dalam mode impor dan partisi dingin dalam mode DirectQuery dengan menghindari kueri sumber data yang tidak perlu. Pengurangan kueri sumber ini membantu meningkatkan performa laporan saat menganalisis data panas. Ini juga membantu mengurangi beban pada sumber data, dan dengan cara ini membantu memaksimalkan skala sumber data Anda. Namun, perlu diingat bahwa mengoptimalkan model data dengan menggunakan dataCoverageDefinition properti masih merupakan skenario lanjutan. Pastikan Anda memverifikasi hasilnya dengan hati-hati.

Pertimbangan dan batasan

• Saat ini, DataCoverageDefinition properti pada partisi DirectQuery memerlukan nilai statis, seperti RELATED('Date'[Year]) = 2020 atau RELATED('Date'[Year]) DI {2020, 2021, 2022}. Penugasan dinamis tidak didukung, seperti RELATED('Date'[DateKey]) = TODAY().

• Refresh inkremental dengan data real-time tidak memanfaatkan DataCoverageDefinition properti . Jika Anda menerapkan definisi cakupan data ke partisi DirectQuery (real-time), Refresh Inkremental menghilangkan definisi cakupan data saat membuat ulang partisi.