Di Excel, Anda dapat membuat model data yang berisi jutaan baris, lalu melakukan analisis data yang kuat terhadap model ini. Model data dapat dibuat dengan atau tanpa add-in Power Pivot untuk mendukung sejumlah PivotTable, bagan, dan visualisasi Power View dalam buku kerja yang sama.
Meskipun Anda dapat dengan mudah membuat model data besar di Excel, ada beberapa alasan untuk tidak melakukannya. Pertama, model besar yang berisi banyak tabel dan kolom berlebihan untuk sebagian besar analisis, dan membuat Daftar Bidang yang rumit. Kedua, model besar menggunakan memori yang berharga, berdampak negatif pada aplikasi dan laporan lain yang berbagi sumber daya sistem yang sama. Terakhir, di Microsoft 365, baik SharePoint Online maupun Excel Web App membatasi ukuran file Excel hingga 10 MB. Untuk model data buku kerja yang berisi jutaan baris, Anda akan mengalami batas 10 MB dengan cukup cepat. Lihat spesifikasi dan batas Model Data.
Dalam artikel ini, Anda akan mempelajari cara membuat model yang dibuat dengan rapat yang lebih mudah digunakan dan menggunakan lebih sedikit memori. Meluangkan waktu untuk mempelajari praktik terbaik dalam desain model yang efisien akan membuahkan hasil di kemudian hari untuk model apa pun yang Anda buat dan gunakan, baik Anda menampilkannya di Excel, Microsoft 365 SharePoint Online, di Office Web Apps Server, atau di SharePoint.
Pertimbangkan juga untuk menjalankan Workbook Size Optimizer. Workbook Size Optimizer akan menganalisis buku kerja Excel Anda dan jika memungkinkan, lebih lanjut memadatkan buku kerja Excel tersebut. Unduh Pengoptimal Ukuran Buku Kerja.
Di artikel ini
Tidak ada yang dapat mengalahkan kolom yang tidak ada untuk penggunaan memori yang rendah
Bagaimana jika kita membutuhkan kolom; Masih bisakah kita mengurangi biaya ruangnya?
Rasio kompresi dan mesin analitik dalam memori
Model data di Excel menggunakan mesin analitik dalam memori untuk menyimpan data dalam memori. Mesin menerapkan teknik kompresi yang kuat untuk mengurangi kebutuhan penyimpanan, menyusutkan rangkaian hasil hingga sebagian kecil dari ukuran aslinya.
Rata-rata, Anda dapat mengharapkan model data 7 hingga 10 kali lebih kecil dari data yang sama pada titik asalnya. Misalnya, jika Anda mengimpor data sebesar 7 MB dari database SQL Server, model data di Excel dapat dengan mudah berukuran 1 MB atau kurang. Tingkat kompresi yang benar-benar dicapai terutama tergantung pada jumlah nilai unik di setiap kolom. Semakin banyak nilai unik, semakin banyak memori yang diperlukan untuk menyimpannya.
Mengapa kita berbicara tentang kompresi dan nilai unik? Karena membangun model efisien yang meminimalkan penggunaan memori adalah tentang maksimalisasi kompresi, dan cara termudah untuk melakukannya adalah dengan menyingkirkan kolom apa pun yang tidak benar-benar Anda perlukan, terutama jika kolom tersebut menyertakan sejumlah besar nilai unik.
Catatan
Perbedaan persyaratan penyimpanan untuk setiap kolom bisa sangat besar. Dalam beberapa kasus, lebih baik memiliki beberapa kolom dengan jumlah nilai unik yang rendah daripada satu kolom dengan jumlah nilai unik yang tinggi. Bagian pengoptimalan Datetime membahas teknik ini secara rinci.
Tidak ada yang dapat mengalahkan kolom yang tidak ada untuk penggunaan memori yang rendah
Kolom yang paling hemat memori adalah kolom yang tidak pernah Anda impor sejak awal. Jika Anda ingin membangun model yang efisien, lihat setiap kolom dan tanyakan pada diri Anda apakah itu berkontribusi pada analisis yang ingin Anda lakukan. Jika tidak atau Anda tidak yakin, tinggalkan. Anda selalu dapat menambahkan kolom baru nanti jika diperlukan.
Dua contoh kolom yang harus selalu dikecualikan
Contoh pertama terkait data yang berasal dari gudang data. Di gudang data, biasanya menemukan artefak proses ETL yang memuat dan merefresh data di gudang. Kolom seperti "tanggal buat", "tanggal pembaruan", dan "eksekusi ETL" dibuat saat data dimuat. Tidak satu pun dari kolom ini diperlukan dalam model dan harus dibatalkan pilihannya saat Anda mengimpor data.
Contoh kedua melibatkan penghilangan kolom kunci utama saat mengimpor tabel fakta.
Banyak tabel, termasuk tabel fakta, memiliki kunci primer. Untuk sebagian besar tabel, seperti yang berisi data pelanggan, karyawan, atau penjualan, Anda menginginkan kunci utama tabel agar dapat menggunakannya untuk membuat hubungan dalam model.
Tabel fakta berbeda. Dalam tabel fakta, kunci primer digunakan untuk mengidentifikasi setiap baris secara unik. Meskipun diperlukan untuk tujuan normalisasi, ini kurang berguna dalam model data yang hanya menginginkan kolom tersebut yang digunakan untuk analisis atau untuk membuat hubungan tabel. Untuk alasan ini, ketika mengimpor dari tabel fakta, jangan sertakan kunci utamanya. Kunci utama dalam tabel fakta menghabiskan banyak ruang dalam model, tetapi tidak memberikan manfaat, karena tidak dapat digunakan untuk menciptakan hubungan.
Catatan
Dalam gudang data dan database multidimensi, tabel besar yang sebagian besar terdiri dari data numerik sering disebut sebagai "tabel fakta". Tabel fakta biasanya mencakup data kinerja bisnis atau transaksi, seperti poin data penjualan dan biaya yang digabungkan dan diselaraskan dengan unit organisasi, produk, segmen pasar, wilayah geografis, dan sebagainya. Semua kolom dalam tabel fakta yang berisi data bisnis atau yang dapat digunakan untuk mereferensikan silang data yang disimpan di tabel lain harus disertakan dalam model untuk mendukung analisis data. Kolom yang ingin Anda kecualikan adalah kolom kunci utama dari tabel fakta, yang terdiri dari nilai unik yang hanya ada di tabel fakta dan tidak ada di tempat lain. Karena tabel fakta sangat besar, beberapa keuntungan terbesar dalam efisiensi model berasal dari mengecualikan baris atau kolom dari tabel fakta.
Cara mengecualikan kolom yang tidak diperlukan
Model yang efisien hanya berisi kolom yang benar-benar Anda perlukan dalam buku kerja. Jika ingin mengontrol kolom mana yang disertakan dalam model, Anda harus menggunakan Panduan Impor Tabel di add-in Power Pivot untuk mengimpor data , bukan kotak dialog "Impor Data" di Excel.
Saat memulai Panduan impor Tabel, Anda memilih tabel mana yang akan diimpor.
Untuk setiap tabel, Anda dapat mengklik tombol Pratinjau & Filter dan memilih bagian tabel yang benar-benar Anda butuhkan. Kami menyarankan Anda menghapus centang semua kolom terlebih dahulu, lalu melanjutkan untuk memeriksa kolom yang Anda inginkan, setelah mempertimbangkan apakah kolom tersebut diperlukan untuk analisis.
Bagaimana dengan memfilter baris yang diperlukan saja?
Banyak tabel dalam database perusahaan dan gudang data berisi data historis yang terakumulasi selama periode waktu yang lama. Selain itu, Anda mungkin menemukan bahwa tabel yang Anda minati berisi informasi untuk area bisnis yang tidak diperlukan untuk analisis spesifik Anda.
Dengan menggunakan wizard Impor Tabel, Anda dapat memfilter data historis atau tidak terkait, dan dengan demikian menghemat banyak ruang dalam model. Dalam gambar berikut, filter tanggal digunakan untuk mengambil hanya baris yang berisi data untuk tahun ini, tidak termasuk data historis yang tidak diperlukan.
Bagaimana jika kita membutuhkan kolom; Masih bisakah kita mengurangi biaya ruangnya?
Ada beberapa teknik tambahan yang dapat Anda terapkan untuk menjadikan kolom sebagai kandidat kompresi yang lebih baik. Ingatlah bahwa satu-satunya karakteristik kolom yang mempengaruhi kompresi adalah jumlah nilai unik. Di bagian ini, Anda akan mempelajari bagaimana beberapa kolom dapat diubah untuk mengurangi jumlah nilai unik.
Memodifikasi kolom Tanggalwaktu
Dalam banyak kasus, kolom Tanggalwaktu memakan banyak ruang. Untungnya, ada beberapa cara untuk mengurangi persyaratan penyimpanan untuk tipe data ini. Tekniknya akan bervariasi tergantung pada cara Anda menggunakan kolom dan tingkat kenyamanan Anda dalam membuat kueri SQL.
Kolom tanggalwaktu mencakup tanggal, bagian, dan waktu. Saat Anda bertanya pada diri sendiri apakah Anda memerlukan kolom, ajukan pertanyaan yang sama beberapa kali untuk kolom Tanggalwaktu:
- Apakah saya memerlukan bagian waktu?
- Apakah saya memerlukan bagian waktu pada tingkat jam? , menit? , Detik? , milidetik?
- Apakah saya memiliki beberapa kolom Tanggalwaktu karena ingin menghitung selisih di antaranya, atau hanya untuk menggabungkan data menurut tahun, bulan, kuartal, dan seterusnya.
Cara menjawab setiap pertanyaan ini menentukan opsi Anda untuk menangani kolom Tanggalwaktu.
Semua solusi ini memerlukan modifikasi kueri SQL. Untuk mempermudah modifikasi kueri, Anda harus memfilter setidaknya satu kolom di setiap tabel. Dengan memfilter kolom, Anda mengubah konstruksi kueri dari format singkat (SELECT *) menjadi pernyataan SELECT yang menyertakan nama kolom yang memenuhi syarat penuh, yang jauh lebih mudah untuk diubah.
Mari kita lihat kueri yang dibuat untuk Anda. Dari kotak dialog Properti Tabel, Anda dapat beralih ke editor Kueri dan melihat kueri SQL saat ini untuk setiap tabel.
Dari Properti Tabel, pilih Editor Kueri.
Editor Kueri memperlihatkan kueri SQL yang digunakan untuk mengisi tabel. Jika Anda memfilter kolom apa pun selama impor, kueri Anda menyertakan nama kolom yang memenuhi syarat:
Sebaliknya, jika Anda mengimpor tabel secara keseluruhan, tanpa menghapus centang pada kolom apa pun atau menerapkan filter apa pun, Anda akan melihat kueri sebagai "Pilih * dari ", yang akan lebih sulit untuk diubah:
|
|---|
Memodifikasi kueri SQL
Setelah mengetahui cara menemukan kueri, Anda dapat mengubahnya untuk lebih mengurangi ukuran model Anda.
- Untuk kolom yang berisi data mata uang atau desimal, jika Anda tidak memerlukan desimal, gunakan sintaks ini untuk menghilangkan desimal:
"PILIH PUTARAN([Decimal_column_name],0)... .”
Jika Anda membutuhkan sen tetapi bukan pecahan sen, ganti 0 dengan 2. Jika Anda menggunakan angka negatif, Anda dapat membulatkan ke unit, puluhan, ratusan, dll. - Jika Anda memiliki kolom Tanggalwaktu bernama dbo. Tabel besar. [Tanggal Waktu] dan Anda tidak memerlukan bagian Waktu, gunakan sintaks untuk menyingkirkan waktu:
"PILIH TRANSMISI (dbo. Tabel besar. [Tanggal waktu] sebagai tanggal) SEBAGAI [Tanggal waktu]) " - Jika Anda memiliki kolom Tanggalwaktu bernama dbo. Tabel besar. [Date Time] dan Anda memerlukan bagian Tanggal dan Waktu, gunakan beberapa kolom dalam kueri SQL, bukan satu kolom Datetime:
"PILIH TRANSMISI (dbo. Tabel besar. [Tanggal, Waktu] sebagai Tanggal) SEBAGAI [Tanggal, Waktu],
Datepart(HH, DBO. Tabel besar. [Tanggal waktu]) sebagai [Tanggal, Waktu, Jam],
Datepart(mi, dbo. Tabel besar. [Tanggal waktu]) sebagai [Tanggal Waktu, Menit],
Datepart(ss, dbo. Tabel besar. [Tanggal waktu]) sebagai [tanggal waktu detik],
Datepart(MS, DBO. Tabel besar. [Tanggal waktu]) sebagai [milidetik tanggal, waktu]"
Gunakan kolom sebanyak yang Anda butuhkan untuk menyimpan setiap bagian di kolom terpisah. - Jika Anda membutuhkan jam dan menit, dan Anda lebih suka mereka bersama-sama sebagai satu kolom waktu, Anda dapat menggunakan sintaks :
Timefromparts(datepart(hh, dbo. Tabel besar. [Tanggal waktu]), datepart(mm, dbo. Tabel besar. [Tanggal, Waktu])) sebagai [Tanggal, Waktu, JamMenit] - Jika Anda memiliki dua kolom tanggalwaktu, seperti [Waktu Mulai] dan [Waktu Selesai], dan yang benar-benar Anda butuhkan adalah perbedaan waktu di antara keduanya dalam hitungan detik sebagai kolom yang disebut [Durasi], hapus kedua kolom dari daftar dan tambahkan:
"datediff(ss,[tanggal mulai],[tanggal selesai]) sebagai [durasi]"
Jika menggunakan kata kunci ms alih-alih ss, Anda akan mendapatkan durasi dalam milidetik
Menggunakan pengukuran terhitung DAX, bukan kolom
Jika Anda pernah bekerja dengan bahasa ekspresi DAX sebelumnya, Anda mungkin sudah tahu bahwa kolom terhitung digunakan untuk menurunkan kolom baru berdasarkan beberapa kolom lain dalam model, sementara pengukuran terhitung ditentukan sekali dalam model, tetapi hanya dievaluasi saat digunakan dalam PivotTable atau laporan lainnya.
Salah satu teknik penghemat memori adalah dengan mengganti kolom biasa atau terhitung dengan pengukuran terhitung. Contoh klasik adalah Harga Satuan, Kuantitas, dan Total. Jika Anda memiliki ketiganya, Anda dapat menghemat ruang dengan memelihara dua saja dan menghitung yang ketiga menggunakan DAX.
2 kolom mana yang harus Anda simpan?
Dalam contoh di atas, simpan Kuantitas dan Harga Satuan. Keduanya memiliki nilai yang lebih sedikit daripada Total. Untuk menghitung Total, tambahkan pengukuran terhitung seperti:
"TotalSales:=sumx('Tabel Penjualan','Tabel Penjualan'[Harga satuan]*'Tabel Penjualan'[Kuantitas])"
Kolom terhitung seperti kolom biasa karena keduanya memakan ruang dalam model. Sebaliknya, ukuran yang dihitung dihitung dengan cepat dan tidak memakan ruang.
Kesimpulan
Pada artikel ini, kami membahas beberapa pendekatan yang dapat membantu Anda membangun model yang lebih hemat memori. Cara mengurangi ukuran file dan persyaratan memori model data adalah dengan mengurangi jumlah keseluruhan kolom dan baris, serta jumlah nilai unik yang muncul di setiap kolom. Berikut adalah beberapa teknik yang kami bahas:
- Menghapus kolom tentu saja merupakan cara terbaik untuk menghemat ruang. Tentukan kolom mana yang benar-benar Anda butuhkan.
- Terkadang Anda dapat menghapus kolom dan menggantinya dengan ukuran terhitung dalam tabel.
- Anda mungkin tidak memerlukan semua baris dalam satu tabel. Anda dapat memfilter baris di Panduan Impor Tabel.
- Secara umum, memecah satu kolom menjadi beberapa bagian berbeda adalah cara yang baik untuk mengurangi jumlah nilai unik dalam kolom. Masing-masing bagian akan memiliki sejumlah kecil nilai unik, dan total gabungan akan lebih kecil dari kolom terpadu asli.
- Dalam banyak kasus, Anda juga memerlukan bagian yang berbeda untuk digunakan sebagai pemotong dalam laporan. Jika sesuai, Anda dapat membuat hierarki dari bagian-bagian seperti Jam, Menit, dan Detik.
- Sering kali, kolom juga berisi lebih banyak informasi daripada yang Anda butuhkan. Misalnya, sebuah kolom menyimpan desimal, tetapi Anda telah menerapkan pemformatan untuk menyembunyikan semua desimal. Pembulatan bisa sangat efektif dalam mengurangi ukuran kolom numerik.
Setelah Anda melakukan apa yang dapat Anda lakukan untuk mengurangi ukuran buku kerja, pertimbangkan juga untuk menjalankan Pengoptimal Ukuran Buku Kerja. Workbook Size Optimizer akan menganalisis buku kerja Excel Anda dan jika memungkinkan, lebih lanjut memadatkan buku kerja Excel tersebut. Unduh Pengoptimal Ukuran Buku Kerja.
Link terkait
Spesifikasi dan batasan Model Data
PowerPivot: Analisis data yang efektif dan pemodelan data di Excel