Membuat Model Data dengan memori yang efisien menggunakan Excel dan Power Pivot add-in

Penting: Artikel ini adalah terjemahan mesin, lihat penafian. Temukan versi bahasa Inggris artikel di sini sebagai rujukan Anda.

Di Excel 2013 atau yang lebih baru, Anda bisa membuat model data yang berisi jutaan baris, dan kemudian melakukan analisis data yang efektif terhadap model ini. Model data bisa dibuat dengan atau tanpa Power Pivot add-in untuk mendukung beberapa PivotTable, bagan dan visualisasi Power View di buku kerja yang sama.

Catatan: Artikel ini menguraikan model data di Excel 2013. Namun, data yang sama pemodelan dan fitur Power Pivot diperkenalkan di Excel 2013 juga diterapkan ke Excel 2016. Ada secara efektif kecil perbedaan antara versi Excel tersebut.

Meskipun Anda bisa dengan mudah membuat model data yang besar di Excel, ada beberapa alasan tidak ke. Pertama, besar model yang berisi banyak tabel dan kolom yang berlebihan untuk sebagian besar analisis, dan buat daftar bidang rumit. Kedua, besar model menggunakan memori berharga, negatif mempengaruhi aplikasi lain dan laporan yang berbagi sumber daya sistem yang sama. Akhirnya, di Office 365, SharePoint Online dan Excel Web App membatasi ukuran file Excel untuk 10 MB. Untuk model data buku kerja yang berisi jutaan baris, Anda akan mengalami 10 MB batas cukup cepat. Lihat spesifikasi dan batasan Data Model.

Di artikel ini, Anda akan mempelajari cara menyusun model yang dikonstruksikan dengan padat yang lebih mudah pengerjaannya dan menggunakan lebih sedikit memori. Meluangkan waktu untuk mempelajari pratik terbaik dalam desain model yang efisien akan berguna untuk setiap model yang Anda buat atau gunakan, entah akan ditampilkan dalam Excel 2013, Office 365 SharePoint Online, di Office Online Server, atau di SharePoint 2013.

Pertimbangkan juga menjalankan Pengoptimal ukuran buku kerja. Menganalisis buku kerja Excel Anda dan jika memungkinkan, memadatkan lebih lanjut. Unduh Pengoptimal ukuran buku kerja.

Di artikel ini

Rasio pemadatan dan mesin analitik dalam memori

Tidak ada yang bisa mengalahkan kolom yang kosong dalam rendahnya penggunaan memori

Dua contoh kolom yang harus selalu dikecualikan

Cara mengeluarkan kolom yang tidak diperlukan

Bagaimana dengan pemfilteran baris yang diperlukan saja?

Bagaimana jika kita membutuhkan kolom tersebut; masih Bisakah kita mengurangi ruang yang digunakan?

Mengubah kolom Waktutanggal

Mengubah kueri SQL

Menggunakan DAX pengukuran terhitung dan bukannya kolom

Kolom 2 mana yang harus Anda Pertahankan?

Kesimpulan

Link terkait

Rasio pemadatan dan mesin analitik dalam memori

Model data dalam Excel menggunakan mesin analitik dalam memori untuk menyimpan data dalam memori. Mesin mengimplementasikan teknik pemadatan yang andal untuk mengurangi persyaratan penyimpanan, menyusutkan rangkaian hasil hingga mencapai pecahan dari ukuran aslinya.

Secara rata-rata, Anda bisa mengharapkan model data menjadi 7 sampai 10 kali lebih kecil dibandingkan data yang asli yang sama. Sebagai contoh, jika Anda mengimpor data sebesar 7 MB dari database SQL Server, model data dalam Excel bisa dengan mudah menjadi 1 MB atau lebih sedikit. Derajat pemadatan yang akan dicapai terutama bergantung pada jumlah nilai unik dalam setiap kolom. Semakin banyak nilai unik, semakin banyak memori dibutuhkan untuk menyimpannya.

Mengapa kita berbicara tentang pemadatan dan nilai unik? Karena menyusun model yang efisien yang meminimalkan penggunaan memori adalah tentang memaksimalkan pemadatan, dan cara yang paling mudah untuk melakukannya adalah dengan menghapus setiap kolom yang tidak benar-benar Anda butuhkan, terutama jika kolom tersebut menyertakan nilai unik dalam jumlah besar.

Catatan: Perbedaan persyaratan penyimpanan untuk kolom individual bisa sangat besar. Pada beberapa kasus, lebih baik memiliki beberapa kolom dengan jumlah nilai unik yang rendah dibandingkan satu kolom dengan jumlah nilai yang unik yang tinggi. Bagian pada optimalisasi WaktuTanggal mencakup teknik ini secara mendetail.

Tidak ada yang bisa mengalahkan kolom yang kosong dalam rendahnya penggunaan memori

Kolom yang memorinya paling efisien adalah kolom yang belum pernah Anda impor. Jika Anda ingin menyusun model yang efisien, lihat setiap kolom dan pikirkan lagi apakah kolom berkontribusi ke analisis yang ingin Anda lakukan. Jika tidak atau jika Anda tidak yakin, hapus saja. Anda selalu bisa menambahkan kolom baru nantinya jika Anda membutuhkannya.

Dua contoh kolom yang harus selalu dikecualikan

Contoh pertama berhubungan dengan data yang berasal dari gudang data. Di gudang data, hal yang biasa ketika menemukan artifak dari proses ETL yang memuat dan merefresh data dalam gudang. Kolom seperti “buat tanggal”, “perbarui tanggal”, dan “jalankan ETL” dibuat ketika data dimuat. Tidak ada satu pun kolom ini dibutuhkan dalam model dan seharus tidak dipilih ketika Anda mengimpor data.

Contoh yang kedua melibatkan penghilangan kolom kunci utama ketika mengimpor tabel fakta.

Kebanyakan 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 . Ketika diperlukan untuk tujuan normalisasi, kunci utama kurang berguna dalam model data di mana Anda ingin hanya kolom tersebut yang digunakan untuk menganalisis atau untuk menetapkan hubungan tabel. Untuk alasan ini, ketika mengimpor dari tabel fakta, jangan sertakan kunci utama. Kunci utama dalam tabel fakta menhabiskan ruang yang sangat banyak di model, tapi tidak memberikan manfaat, karena mereka tidak bisa digunakan untuk membuat hubungan.

Catatan: Pada gudang data dan database multidimensional, tabel besar kebanyakan terdiri dari data numerik yang sering dirujuk sebagai “tabel fakta”. Tabel fakta biasanya menyertakan kinerja bisnis atau data transaksi, seperti data penjualan dan agregat biaya dan diratakan untuk unit organisasi, produk, segmen pasar, wilayah geografis, dan lain-lain. Semua kolom dalam tabel fakta yang berisi data bisnis atau yang bisa digunakan untuk mereferensi-silang data disimpan di tabel lain harus disertakan dalam model untuk mendukung analisis data. Kolom yang Anda ingin keluarkan adalah kolom kunci utama dari tabel fakta, yang terdiri dari nilai unik yang ada hanya dalam tabel fakta dan tidak ada di tempat lain. Karena tabel fakta sangat besar, beberapa keuntungan terbesar dalam efisiensi model diperoleh dengan mengabaikan baris atau kolom dari tabel fakta.

Cara mengeluarkan kolom yang tidak diperlukan

Efisien model berisi hanya kolom yang benar-benar Anda butuhkan dalam buku kerja Anda. Jika Anda ingin mengontrol kolom mana yang disertakan dalam model, Anda harus menggunakan panduan impor tabel di Power Pivot add-in untuk mengimpor data dari kotak dialog "Mengimpor Data" di Excel.

Ketika Anda memulai Panduan Impor Tabel, Anda memilih tabel mana yang akan diimpor.

Panduan Impor Tabel dalam add-in PowerPivot

Untuk setiap tabel, Anda bisa mengklik tombol Pratinjau & Filter dan pilih bagian dari tabel yang Anda benar-benar butuhkan. Kami merekomendasikan bahwa Anda terlebih dahulu mengosongkan centang semua kolom, lalu lanjutkan dengan mencentang kolom yang Anda inginkan, setelah mempertimbangkan apakah kolom tersebut diperlukan untuk analisis.

Panel Pratinjau dalam panduan Impor Tabel

Bagaimana dengan pemfilteran baris yang diperlukan saja?

Banyak tabel dalam database perusahaan dan gudang data berisi data riwayat yang terakumulasi dalam waktu lama. Selain itu, Anda mungkin menemukan bahwa tabel yang Anda minati berisi informasi untuk area bisnis yang tidak diperlukan dalam analisis khusus Anda.

Dengan menggunakan panduan Impor Tabel, Anda bisa membuang data riwayat atau data yang tidak berhubungan, sehingga menghemat banyak ruang dalam model. Dalam gambar berikut ini, filter digunakan untuk mengambil hanya baris yang berisi data untuk tahun ini, mengeluarkan data riwayat yang tidak dibutuhkan.

Panel filter dalam panduan Impor Tabel

Bagaimana jika kita membutuhkan kolom tersebut; masih bisakah kita mengurangi ruang yang digunakan?

Ada beberapa teknik tambahan yang bisa Anda terapkan untuk membuat kolom menjadi kandidat yang lebih baik untuk pemadatan. Ingat bahwa satu-satunya karakteristik kolom yang mempengaruhi pemadatan adalah jumlah nilai unik. Pada bagian ini, Anda akan mempelajari bagaimana beberapa kolom bisa diubah untuk mengurangi jumlah nilai unik.

Mengubah kolom WaktuTanggal

Pada banyak kasus, kolom WaktuTangggal menghabiskan banyak ruang. Untungnya, ada beberapa cara mengurangi persyaratan penyimpanan untuk tipe data ini. Tekniknya beragam, bergantung pada cara Anda menggunakan kolom, dan tingkat kenyamanan Anda dalam menyusun kueri SQL.

Kolom WaktuTanggal menyertakan bagian tanggal dan waktu. Ketika Anda bertanya pada diri Anda sendiri apakah Anda membutuhkan kolom tersebut, tanyakan pertanyaan yang sama beberapa kali untuk kolom WaktuTanggal:

  • Apakah saya membutuhkan bagian waktu?

  • Apakah saya membutuhkan bagian waktu hingga tingkatan jam? , menit? , Detik? , millidetik?

  • Apakah saya memiliki beberapa kolom WaktuTanggal karena saya ingin menghitung perbedaan diantaranya, atau hanya untuk agregat data berdasarkan tahun, kuartal, dan seterusnya.

Bagaimana Anda menjawab setiap pertanyaan ini untuk menentukan opsi Anda untuk menangani kolom WaktuTanggal.

Semua solusi ini memerlukan modifikasi kueri SQL. Untuk membuat modifikasi kueri lebih mudah, Anda harus memfilter setidaknya satu kolom dalam setiap tabel. Dengan memfilter kolom, Anda mengubah konstruksi kueri dari format yang disingkat (SELECT *) ke pernyataan SELECT yang menyertakan nama kolom yang berkualifikasi penuh, yang jauh lebih mudah untuk diubah.

Mari kita lihat kueri yang dibuat untuk Anda. Dari kotak dialog Properti Tabel, Anda bisa beralih ke editor Kueri dan melihat kueri SQL Anda saat ini untuk setiap tabel.

Pita dalam jendela PowerPivot memperlihatkan perintah Properti Tabel

Dari Properti Tabel, pilih Editor Kueri.

Membuka Editor Kueri dari dialog Properti Tabel

Editor Kueri memperlihatkan kueri SQL yang digunakan untuk mengisi tabel. Jika Anda memfilter kolom mana pun selama mengimpor, kueri Anda menyertakan nama kolom yang berkualifikasi penuh:

Kueri SQL digunakan untuk mengambil data

Sebaliknya, jika Anda mengimpor tabel dengan seluruh isinya, tanpa mengosongkan kolom centang atau menerapkan filter apa pun, Anda akan melihat kueri sebagai “Pilih * dari ”, yang akan lebih sulit untuk diubah:

Kueri SQL menggunakan sintaks default yang lebih singkat.

Mengubah kueri SQL

Sekarang Anda mengetahui bagaimana cara menemukan kueri, Anda bisa mengubahnya untuk mengurangi ukuran model Anda nantinya.

  1. Untuk kolom yang berisi data mata uang atau data desimal, gunakan sintaks ini untuk membuang desimal:

    “SELECT ROUND([Decimal_column_name],0)… .”

    Jika Anda memerlukan sen tapi tidak membutuhkan pecahan dari sen, ganti 0 dengan 2. Jika Anda menggunakan bilangan negatif, Anda bisa membulatkan ke satuan, puluhan, ratusan, dll.

  2. Jika Anda memilki kolom WaktuTanggal yang dinamakan dbo.Bigtable.[Date Time] dan Anda tidak memerlukan bagian Waktu, gunakan sintaks untuk membuang waktu:

    “SELECT CAST (dbo.Bigtable.[Date time] as date) AS [Date time]) “

  3. Jika Anda memiliki kolom WaktuTanggal yang dinamakan dbo.Bigtable.[Date Time] dan Anda membutuhkan kedua bagian Waktu danTanggal, gunakan beberapa kolom dalam kueri SQL, bukan kolom WaktuTanggal tunggal:

    “SELECT CAST (dbo.Bigtable.[Date Time] as date ) AS [Date Time],

    datepart(hh, dbo.Bigtable.[Date Time]) sebagai [Date Time Hours],

    datepart(mi, dbo.Bigtable.[Date Time]) sebagai [Date Time Minutes],

    datepart(ss, dbo.Bigtable.[Date Time]) sebagai [Date Time Seconds],

    datepart(ms, dbo.Bigtable.[Date Time]) sebagai [Date Time Milliseconds]”

    Gunakan kolom sebanyak yang Anda perlukan untuk menyimpan setiap komponen dalam kolom terpisah.

  4. 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.[Date Time])) sebagai [Date Time HourMinute]

  5. Jika Anda memiliki dua kolom waktutanggal, seperti [Waktu Mulai] dan [Waktu Akhir], dan apa yang Anda butuhkan adalah perbedaan waktu keduanya dalam detik sebagai kolom yang dinamai [Durasi], hapus kedua kolom tersebut dari daftar dan tambahkan:

    “datediff(ss,[Start Date],[End Date]) sebagai [Duration]”

    Jika Anda menggunakan kata kunci ms dan bukannya ss, Anda akan mendapatkan durasi dalam millidetik

Gunakan pengukuran terhitung DAX dan bukannya kolom

Jika Anda telah bekerja dengan bahasa ekspresi DAX sebelumnya, Anda mungkin telah mengetahui kolom terhitung digunakan untuk membuat kolom baru berdasarkan beberapa kolom lainnya dalam model, saat pengukuran terhitung ditentukan dalam model, tapi hanya dievaluasi ketika digunakan dalam PivotTable atau laporan lainnya.

Salah satu teknik menghemat memori adalah dengan mengganti kolom reguler atau kolom terhitung dengan pengukuran terhitung. Contoh klasiknya adalah Harga Unit, Jumlah, dan Total. Jika Anda memilki ketiganya, Anda bisa menghemat ruang dengan hanya mempertahankan hanya dua dan menghitung yang ketiga menggunakan DAX.

Dua kolom mana yang harus Anda pertahankan?

Dalam contoh di atas, pertahankan Kuantitas dan Harga Unit. Keduanya memiliki nilai lebih sedikit dibandingkan denganTotal. Untuk menghitung Total, tambahkan pengukuran terhitung seperti:

“TotalSales:=sumx(‘Sales Table’,’Sales Table’[Unit Price]*’Sales Table’[Quantity])”

Kolom terhitung sama seperti kolom pada umumnya keduanya menghabiskan ruang dalam model. Sebaliknya, pengukuran terhitung dihitung secara dinamis dan tidak menghabiskan ruang.

Kesimpulan

Dalam artikel ini, kita membicarakan tentang beberapa pendekatan yang bisa membantu Anda menyusun model dengan memori yang lebih efisien. Cara untuk mengurangi ukuran file dan persyaratan memori dari model data adalah dengan mengurangi jumlah keseluruhan kolom dan baris, dan jumlah nilai unik yang muncul dalam setiap kolom. Berikut beberapa teknik yang kami bahas:

  • Menghapus kolom merupakan cara terbaik untuk menghemat ruang. Memutuskan kolom mana yang Anda benar-benar butuhkan.

  • Terkadang Anda bisa menghapus kolom dan menggantinya dengan pengukuran terhitung dalam tabel.

  • Anda mungkin tidak memerlukan semua baris dalam tabel. Anda bisa membuang baris dalam Panduan Impor Tabel.

  • Secara umum, memisahkan kolom tunggal menjadi beberapa bagian yang berlainan merupakan cara yang baik untuk mengurangi jumlah nilai unik dalam kolom. Setiap komponen akan memiliki jumlah nilai unik yang kecil, dan total yang digabungkan akan lebih kecil dibanding kolom terpadu yang asli.

  • Pada banyak kasus, Anda juga membutuhkan bagian yang berlainan untuk digunakan sebagai pemotong dalam laporan Anda. Saat dibutuhkan, Anda bisa membuat hierarki dari bagian seperti Jam, Menit, dan Detik.

  • Sering kali, kolom berisi lebih banyak informasi daripada yang Anda butuhkan. Sebagai contoh, anggaplah sebuah kolom yang menyimpan desimal, tapi Anda telah menerapkan pemformatan untuk menyembunyikan semua desimal. Pembulatan bisa sangat efektif dalam mengurangi ukuran kolom numerik.

Sekarang karena Anda sudah selesai apa yang Anda bisa mengurangi ukuran buku kerja Anda, pertimbangkan juga menjalankan Pengoptimal ukuran buku kerja. Menganalisis buku kerja Excel Anda dan jika memungkinkan, memadatkan lebih lanjut. Unduh Pengoptimal ukuran buku kerja.

Link terkait

Spesifikasi dan batasan Model Data

Pengoptimal ukuran buku kerja Unduh

Power Pivot: Analisis data yang efektif dan pemodelan data di Excel

Catatan: Penafian Terjemahan Mesin: Artikel ini telah diterjemahan oleh sistem komputer tanpa campur tangan manusia. Microsoft menawarkan terjemahan mesin ini untuk membantu pengguna yang bukan penutur bahasa Inggris agar dapat menikmati konten tentang produk, layanan, dan teknologi Microsoft. Karena artikel ini diterjemahkan oleh mesin, mungkin akan terdapat kesalahan kosa kata, sintaksis, atau tata bahasa.

Kembangkan keterampilan Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung ke Office Insiders

Apakah informasi ini bermanfaat?

Terima kasih atas umpan balik Anda!

Terima kasih atas umpan balik Anda! Sepertinya menghubungkan Anda ke salah satu agen dukungan Office kami akan sangat membantu.

×