Di Excel 2013 atau yang lebih baru, Anda bisa membuat model data yang berisi jutaan baris, lalu melakukan analisis data yang efektif terhadap model ini. Model data bisa dibuat dengan atau tanpa add-in Power Pivot untuk mendukung sejumlah pivotTable, bagan, dan visualisasi Power View dalam buku kerja yang sama.
Catatan: Artikel ini menguraikan model data dalam Excel 2013. Namun, pemodelan data dan fitur Power Pivot yang sama yang diperkenalkan di Excel 2013 juga diterapkan untuk Excel 2016. Ada sedikit perbedaan yang efektif antara versi Excel ini.
Meskipun Anda bisa dengan mudah menyusun model data besar di Excel, ada beberapa alasan untuk tidak melakukannya. Pertama, model besar yang berisi banyak tabel dan kolom terlalu 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 memiliki sumber daya sistem yang sama. Terakhir, dalam Microsoft 365, baik SharePoint Online maupun Excel Web App membatasi ukuran file Excel menjadi 10 MB. Untuk model data buku kerja yang berisi jutaan baris, Anda akan menemukan batas 10 MB dengan cukup cepat. Lihat Spesifikasi dan batasan Model Data.
Dalam artikel ini, Anda akan mempelajari cara menyusun model yang disusun dengan rapat yang lebih mudah digunakan dan menggunakan lebih sedikit memori. Meluangkan waktu untuk mempelajari praktik terbaik dalam desain model yang efisien akan memuaskan setiap model yang Anda buat dan gunakan, baik Anda menampilkannya di Excel 2013, Microsoft 365 SharePoint Online, di Server Office Online , atau di SharePoint 2013.
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
Rasio kompresi dan mesin analitik dalam memori
Model data di Excel menggunakan mesin analitik dalam memori untuk menyimpan data dalam memori. Mesin mengimplementasikan teknik pemadatan yang kuat untuk mengurangi persyaratan penyimpanan, menyusutkan rangkaian hasil hingga ukuran aslinya pecahan.
Rata-rata, Anda bisa mengharapkan model data menjadi 7 hingga 10 kali lebih kecil dari data yang sama di titik asalnya. Misalnya, jika Anda mengimpor data 7 MB dari database SQL Server, model data di Excel bisa dengan mudah 1 MB atau kurang. Derajat pemadatan benar-benar dicapai terutama bergantung pada jumlah nilai unik di setiap kolom. Semakin banyak nilai yang 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 memaksimalkan pemadatan, dan cara term mudah 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 kolom individual 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 tentang optimisasi Datetime mencakup teknik ini secara mendetail.
Tidak ada yang mengalahkan kolom yang tidak ada untuk penggunaan memori rendah
Kolom yang paling efisien memori adalah kolom yang tidak pernah Anda impor di tempat pertama. Jika Anda ingin membuat 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, biarkan saja. Anda selalu bisa menambahkan kolom baru nanti jika Anda membutuhkannya.
Dua contoh kolom yang harus selalu dikecualikan
Contoh pertama berkaitan dengan data yang berasal dari gudang data. Di gudang data, adalah hal yang umum untuk menemukan artefak proses ETL yang memuat dan merefresh data di gudang. Kolom seperti "buat tanggal", "tanggal pembaruan", dan "ETL run" dibuat saat data dimuat. Tidak ada kolom ini yang 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 utama. Untuk sebagian besar tabel, seperti yang berisi data pelanggan, karyawan, atau penjualan, Anda mungkin menginginkan kunci utama tabel sehingga Anda bisa menggunakannya untuk membuat hubungan dalam model.
Tabel fakta berbeda. Dalam tabel fakta, kunci utama digunakan untuk mengidentifikasi setiap baris secara unik. Meskipun diperlukan untuk tujuan normalisasi, hal ini kurang berguna dalam model data di mana Anda hanya menginginkan kolom yang digunakan untuk analisis atau menetapkan hubungan tabel. Untuk alasan ini, ketika mengimpor dari tabel fakta, jangan sertakan kunci utamanya. Kunci utama dalam tabel fakta menggunakan jumlah ruang yang sangat besar dalam model, namun tidak memberikan manfaat, karena tidak dapat digunakan untuk membuat hubungan.
Catatan: Dalam gudang data dan database multidimensional, tabel besar yang sebagian besar terdiri dari data numerik sering disebut sebagai "tabel fakta". Tabel fakta biasanya menyertakan data kinerja bisnis atau transaksi, seperti poin data penjualan dan biaya yang digabungkan dan selaras dengan unit organisasi, produk, segmen pasar, kawasan geografis, dan lain sebagainya. Semua kolom dalam tabel fakta yang berisi data bisnis atau yang bisa digunakan untuk mereferensi silang data yang disimpan dalam tabel lain harus disertakan dalam model untuk mendukung analisis data. Kolom yang ingin Dikecualikan adalah kolom kunci utama tabel fakta, yang terdiri dari nilai unik yang hanya ada di tabel fakta dan tempat lain. Karena tabel fakta sangat besar, beberapa keuntungan terbesar dalam efisiensi model berasal dari tidak termasuk baris atau kolom dari tabel fakta.
Cara mengeluarkan kolom yang tidak diperlukan
Model yang efisien hanya berisi kolom yang benar-benar Anda perlukan dalam buku kerja Anda. Jika Anda ingin mengontrol kolom mana yang disertakan dalam model, Anda harus menggunakan Panduan Impor Tabel di add-in Power Pivot untuk mengimpor data daripada kotak dialog "Impor Data" di Excel.
Saat memulai Panduan Impor Tabel, Anda memilih tabel mana yang akan diimpor.
Untuk setiap tabel, Anda bisa mengklik tombol Pratinjau & Filter dan memilih bagian tabel yang benar-benar Anda perlukan. Kami menyarankan agar Anda terlebih dahulu mengkosongkan semua kolom, 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 dalam 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 panduan Impor Tabel, Anda dapat memfilter riwayat atau data yang tidak terkait, sehingga menghemat banyak ruang dalam model. Dalam gambar berikut, filter tanggal digunakan untuk mengambil hanya baris yang berisi data untuk tahun ini, tidak termasuk data riwayat yang tidak akan diperlukan.
Bagaimana jika kita membutuhkan kolom; masih dapatkah kita mengurangi biaya ruangnya?
Ada beberapa teknik tambahan yang bisa Anda terapkan untuk membuat kolom menjadi kandidat yang lebih baik untuk pemadatan. Ingatlah bahwa satu-satunya karakteristik kolom yang mempengaruhi pemadatan adalah jumlah nilai unik. Di bagian ini, Anda akan mempelajari bagaimana beberapa kolom bisa dimodifikasi untuk mengurangi jumlah nilai unik.
Memodifikasi kolom Datetime
Dalam banyak kasus, kolom Datetime membutuhkan banyak ruang. Untungnya, ada beberapa cara untuk mengurangi persyaratan penyimpanan untuk tipe data ini. Teknik akan bervariasi tergantung pada cara Anda menggunakan kolom, dan tingkat kenyamanan Anda dalam menyusun kueri SQL.
Kolom datetime menyertakan bagian tanggal dan waktu. Saat Anda bertanya pada diri sendiri apakah Anda memerlukan kolom, ajukan pertanyaan yang sama beberapa kali untuk kolom Datetime:
-
Apakah saya membutuhkan bagian waktu?
-
Apakah saya membutuhkan bagian waktu pada tingkat jam? Menit? Detik? Milidetik?
-
Apakah saya memiliki beberapa kolom Datetime karena saya ingin menghitung perbedaan antara kolom tersebut, atau hanya untuk mengagregasi data menurut tahun, bulan, kuartal, dan sebagainya.
Cara Anda menjawab setiap pertanyaan ini menentukan opsi Anda untuk menangani kolom Datetime.
Semua solusi ini memerlukan modifikasi kueri SQL. Untuk mempermudah modifikasi kueri, Anda harus memfilter setidaknya satu kolom dalam setiap tabel. Dengan memfilter kolom, Anda mengubah konstruksi kueri dari format yang disingkat (SELECT *) menjadi pernyataan SELECT yang menyertakan nama kolom yang sepenuhnya memenuhi syarat, yang jauh lebih mudah diubah.
Mari kita lihat kueri yang dibuat untuk Anda. Dari kotak dialog Properti Tabel, Anda bisa 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 pengimporan, kueri Anda menyertakan nama kolom yang sepenuhnya memenuhi syarat:
|
Sebaliknya, jika Anda mengimpor tabel secara keseluruhan, tanpa mengkosongkan 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 mengurangi ukuran model lebih lanjut.
-
Untuk kolom yang berisi data mata uang atau desimal, jika Anda tidak memerlukan desimal, gunakan sintaks ini untuk menghilangkan desimal:
"SELECT ROUND([Decimal_column_name],0)... .”
Jika Anda membutuhkan sen tetapi tidak pecahan sen, ganti 0 dengan 2. Jika Anda menggunakan angka negatif, Anda bisa membulatkan ke satuan, puluhan, ratusan dll.
-
Jika Anda memiliki kolom Datetime bernama dbo. Bigtable. [Tanggal Waktu] dan Anda tidak memerlukan bagian Waktu, gunakan sintaks untuk menghilangkan waktu:
"PILIH CAST (dbo. Bigtable. [Tanggal waktu] sebagai tanggal) AS [Tanggal waktu]) "
-
Jika Anda memiliki kolom Datetime bernama dbo. Bigtable. [Tanggal Waktu] dan Anda memerlukan bagian Tanggal dan Waktu, gunakan beberapa kolom dalam kueri SQL, bukan kolom Datetime tunggal:
"PILIH CAST (dbo. Bigtable. [Date Time] as date ) AS [Date Time],
datepart(hh, dbo. Bigtable. [Tanggal Waktu]) sebagai [Jam Waktu Tanggal],
datepart(mi, dbo. Bigtable. [Tanggal Waktu]) as [Date Time Minutes],
datepart(ss, dbo. Bigtable. [Tanggal Waktu]) sebagai [Tanggal Waktu Detik],
datepart(ms, dbo. Bigtable. [Tanggal Waktu]) sebagai [Tanggal Waktu Milidetik]"
Gunakan kolom sebanyak yang Anda perlukan untuk menyimpan setiap bagian dalam kolom terpisah.
-
Jika Anda membutuhkan jam dan menit, dan Anda lebih menyukainya sebagai satu kolom waktu, Anda bisa menggunakan sintaks :
Timefromparts(datepart(hh, dbo. Bigtable. [Date Time]), datepart(mm, dbo. Bigtable. [Tanggal Waktu])) as [Date Time HourMinute]
-
Jika Anda memiliki dua kolom datetime, seperti [Waktu Mulai] dan [Waktu Akhir], dan yang benar-benar Anda perlukan adalah perbedaan waktu di antaranya dalam detik sebagai kolom yang disebut [Durasi], hapus kedua kolom dari daftar dan tambahkan:
"datediff(ss,[Start Date],[End Date]) as [Duration]"
Jika Anda menggunakan kata kunci ms dan bukan ss, Anda akan mendapatkan durasi dalam milidetik
Menggunakan pengukuran terhitung DAX dan bukan kolom
Jika Anda pernah bekerja dengan bahasa ekspresi DAX sebelumnya, Anda mungkin sudah tahu bahwa kolom terhitung digunakan untuk mendapatkan kolom baru berdasarkan beberapa kolom lain dalam model, sementara pengukuran terhitung ditentukan sekali dalam model, tetapi dievaluasi hanya ketika digunakan dalam PivotTable atau laporan lainnya.
Satu teknik penghematan memori adalah mengganti kolom reguler atau terhitung dengan pengukuran terhitung. Contoh klasiknya adalah Harga Satuan, Kuantitas, dan Total. Jika memiliki ketiganya, Anda dapat menghemat ruang hanya dengan mempertahankan dua dan menghitung yang ketiga menggunakan DAX.
2 kolom mana yang harus Anda simpan?
Dalam contoh di atas, pertahankan Jumlah dan Harga Satuan. Kedua nilai ini memiliki nilai yang lebih sedikit dari Total. Untuk menghitung Total, tambahkan pengukuran terhitung seperti:
"TotalSales:=sumx('Tabel Penjualan','Tabel Penjualan'[Harga Satuan]*'Tabel Penjualan'[Jumlah])"
Kolom terhitung sama seperti kolom biasa di mana keduanya memakan ruang dalam model. Sebaliknya, pengukuran terhitung dihitung dengan cepat dan tidak memakan ruang.
Kesimpulan
Dalam artikel ini, kami berbicara tentang beberapa pendekatan yang dapat membantu Anda membangun model yang lebih efisien memori. Cara untuk mengurangi ukuran file dan persyaratan memori model data adalah dengan mengurangi jumlah keseluruhan kolom dan baris, dan 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 perlukan.
-
Terkadang, Anda dapat menghapus kolom dan menggantinya dengan pengukuran terhitung dalam tabel.
-
Anda mungkin tidak memerlukan semua baris dalam tabel. Anda dapat memfilter baris dalam Panduan Impor Tabel.
-
Secara umum, memisahkan satu kolom menjadi beberapa bagian yang berbeda adalah cara yang baik untuk mengurangi jumlah nilai unik dalam kolom. Setiap 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 Anda. Jika diperlukan, Anda dapat membuat hierarki dari bagian seperti Jam, Menit, dan Detik.
-
Sering kali, kolom berisi lebih banyak informasi daripada yang Anda perlukan juga. Misalnya, anggaplah kolom menyimpan desimal, tetapi Anda telah menerapkan pemformatan untuk menyembunyikan semua desimal. Pembulatan bisa sangat efektif dalam mengurangi ukuran kolom numerik.
Sekarang setelah Anda melakukan apa yang bisa Anda lakukan untuk mengurangi ukuran buku kerja Anda, pertimbangkan juga 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
