Membuat model data dengan memori yang efisien menggunakan Excel dan Add-in Power Pivot

Di Excel 2013 atau yang lebih baru, Anda bisa 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.

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. Hanya terdapat sedikit perbedaan antar versi Excel ini.

Meskipun Anda bisa dengan mudah membuat model data yang besar di Excel, ada beberapa alasan untuk tidak. Pertama, model besar yang berisi banyak tabel dan kolom yang berlebihan untuk kebanyakan analisis, dan buat 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 Office 365, SharePoint online dan Excel Web App membatasi ukuran file Excel ke 10 MB. Untuk model data buku kerja yang berisi jutaan baris, Anda akan mengalami batas 10 MB dengan cepat. Lihat spesifikasi dan batasan model data.

Dalam artikel ini, Anda akan mempelajari cara menyusun model yang dibangun dengan ketat yang lebih mudah dikerjakan dan menggunakan lebih sedikit memori. Luangkan waktu untuk mempelajari praktik terbaik dalam desain model yang efisien yang akan melunasi jalan untuk model apa pun yang Anda buat dan gunakan, baik saat Anda menampilkannya di Excel 2013, Office 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

Tidak ada yang bisa mengalahkan kolom yang tidak ada untuk penggunaan memori yang rendah

Dua contoh kolom yang harus selalu dikecualikan

Cara mengecualikan kolom yang tidak diperlukan

Bagaimana dengan pemfilteran baris yang diperlukan saja?

Bagaimana jika kita membutuhkan kolom tersebut; Bisakah kita masih mengurangi biaya Ruang?

Mengubah kolom datetime

Mengubah kueri SQL

Menggunakan pengukuran terhitung DAX dan bukan kolom

2 kolom mana yang harus Anda Pertahankan?

Kesimpulan

Link terkait

Rasio kompresi dan mesin analitik dalam memori

Model data di Excel menggunakan mesin analitik dalam memori untuk menyimpan data dalam memori. Engine menerapkan teknik pemadatan yang kuat untuk mengurangi persyaratan penyimpanan, membuat penyusutan rangkaian hasil hingga pecahan dari ukuran aslinya.

Rata-rata, Anda dapat mengharapkan model data menjadi 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 bisa dengan mudah 1 MB atau kurang. Tingkat pemadatan sebenarnya tergantung pada jumlah nilai unik dalam setiap kolom. Nilai yang lebih unik, lebih banyak memori diperlukan untuk menyimpannya.

Mengapa kita berbicara tentang kompresi dan nilai unik? Karena membuat model yang efisien yang meminimalkan penggunaan memori adalah semua tentang maximisasi kompresi, dan cara termudah untuk melakukannya adalah membuang kolom apa pun yang tidak Anda perlukan, terutama jika kolom tersebut menyertakan sejumlah besar nilai unik.

Catatan:  Perbedaan dalam persyaratan penyimpanan untuk kolom individual dapat menjadi sangat besar. Dalam beberapa kasus, lebih baik memiliki beberapa kolom dengan jumlah nilai yang unik dan bukan satu kolom dengan jumlah nilai unik yang tinggi. Bagian pada optimalisasi datetime meliputi teknik ini secara mendetail.

Tidak ada yang bisa mengalahkan kolom yang tidak ada untuk penggunaan memori yang rendah

Kolom paling hemat memori adalah kolom yang tidak pernah diimpor di tempat pertama. Jika Anda ingin membuat model yang efisien, lihat setiap kolom dan tanyakan pada diri Anda Apakah ada kontribusi pada analisis yang ingin Anda lakukan. Jika tidak yakin atau Anda tidak yakin, biarkan. Anda selalu bisa menambahkan kolom baru jika Anda membutuhkannya.

Dua contoh kolom yang harus selalu dikecualikan

Contoh pertama berkaitan dengan data yang berasal dari gudang data. Di gudang data, umum untuk menemukan artefak proses ETL yang memuat dan merefresh data di gudang. Kolom seperti "Buat tanggal", "Perbarui tanggal", dan "ETL jalankan" dibuat saat data dimuat. Tidak satu pun dari kolom ini yang diperlukan dalam model dan tidak dapat dipilih saat mengimpor data.

Contoh kedua melibatkan menghilangkan kolom kunci utama saat mengimpor tabel fakta.

Banyak tabel, termasuk tabel fakta, memiliki kunci utama. Untuk sebagian besar tabel, seperti yang berisi pelanggan, karyawan, atau data penjualan, Anda akan ingin kunci utama tabel tersebut agar dapat digunakan untuk membuat hubungan dalam model.

Tabel fakta berbeda. Dalam tabel fakta, kunci utama digunakan untuk mengidentifikasi setiap baris secara unik. Sementara diperlukan untuk tujuan normalisasi, itu kurang berguna dalam model data di mana Anda hanya menginginkan kolom yang digunakan untuk analisis atau untuk menetapkan hubungan tabel. Untuk alasan ini, saat mengimpor dari tabel fakta, jangan sertakan kunci utamanya. Kunci utama dalam tabel fakta mengonsumsi dalam jumlah besar ruang dalam model, namun tidak memberikan manfaat karena tidak bisa digunakan untuk membuat hubungan.

Catatan:  Di gudang data dan database Multidimensional, tabel besar yang terdiri dari sebagian besar data numerik sering disebut sebagai "tabel fakta". Tabel fakta biasanya mencakup kinerja bisnis atau data transaksi, seperti poin data penjualan dan biaya yang diagregasi dan sejajar dengan unit organisasi, produk, segmen pasar, kawasan geografis, dan seterusnya. Semua kolom dalam tabel fakta yang berisi data bisnis atau yang dapat digunakan untuk data referensi silang 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 dalam tabel fakta dan 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 Anda. Jika Anda ingin mengontrol kolom 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 Anda memulai panduan impor tabel, Anda memilih tabel yang akan diimpor.

Panduan Impor Tabel dalam add-in PowerPivot

Untuk setiap tabel, Anda bisa mengklik tombol pratinjau & filter dan memilih bagian tabel yang Anda perlukan. Kami menyarankan agar Anda terlebih dahulu menghapus centang semua kolom, lalu melanjutkan untuk memeriksa kolom yang Anda inginkan, setelah mempertimbangkan apakah diperlukan untuk analisis.

Panel Pratinjau dalam panduan Impor Tabel

Bagaimana dengan pemfilteran baris yang diperlukan saja?

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

Menggunakan panduan impor tabel, Anda bisa memfilter data historis atau tidak terkait, dan dengan demikian menyimpan 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 akan diperlukan.

Panel filter dalam panduan Impor Tabel

Bagaimana jika kita membutuhkan kolom tersebut; Bisakah kita masih mengurangi biaya Ruang?

Ada beberapa teknik tambahan yang bisa Anda Terapkan untuk membuat kolom sebagai kandidat yang lebih baik untuk kompresi. Ingatlah bahwa satu-satunya karakteristik kolom yang mempengaruhi kompresi adalah jumlah nilai unik. Dalam bagian ini, Anda akan mempelajari bagaimana beberapa kolom dapat dimodifikasi untuk mengurangi jumlah nilai unik.

Mengubah kolom datetime

Dalam banyak kasus, kolom datetime memakan banyak ruang. Untungnya, ada beberapa cara untuk mengurangi persyaratan penyimpanan untuk tipe data ini. Teknik akan berbeda-beda tergantung pada cara Anda menggunakan kolom, dan tingkat kenyamanan Anda dalam menyusun kueri SQL.

Kolom datetime menyertakan bagian tanggal dan waktu. Saat Anda menanyakan pada diri Anda Apakah Anda memerlukan kolom, Ajukan pertanyaan yang sama beberapa kali untuk kolom datetime:

  • Apakah saya membutuhkan bagian waktu?

  • Apakah saya membutuhkan bagian waktu pada tingkatan jam? jam? Detik? milidetik?

  • Apakah saya memiliki beberapa kolom datetime karena saya ingin menghitung perbedaan di antaranya, atau hanya untuk menggabungkan data menurut tahun, bulan, kuartal, dan seterusnya.

Cara Anda menjawab setiap pertanyaan ini menentukan opsi Anda untuk menangani kolom datetime.

Semua solusi ini memerlukan modifikasi kueri SQL. Agar modifikasi kueri lebih mudah, Anda harus memfilter setidaknya satu kolom dalam setiap tabel. Dengan memfilter kolom, Anda mengubah pembuatan kueri dari format yang disingkat (pilih *) ke pernyataan SELECT yang menyertakan nama kolom yang memenuhi syarat, yang jauh lebih mudah untuk dimodifikasi.

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.

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 apa pun selama impor, kueri Anda menyertakan nama kolom yang memenuhi syarat:

Kueri SQL digunakan untuk mengambil data

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 dimodifikasi:

Kueri SQL menggunakan sintaks default yang lebih singkat.

Mengubah kueri SQL

Sekarang Anda tahu cara menemukan kueri, Anda bisa mengubahnya untuk memperkecil ukuran model Anda lebih lanjut.

  1. Untuk kolom yang berisi data mata uang atau desimal, jika Anda tidak memerlukan desimal, gunakan sintaks ini untuk menghilangkan desimal:

    "Pilih ROUND ([Decimal_column_name],0)... .”

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

  2. Jika Anda memiliki kolom datetime yang bernama DBO. Bigtable. [Tanggal waktu] dan Anda tidak memerlukan bagian waktu, gunakan sintaks untuk menghilangkan waktu:

    "Pilih CAST (DBO. Bigtable. [Tanggal waktu] sebagai tanggal) sebagai [tanggal waktu]) "

  3. Jika Anda memiliki kolom datetime yang bernama DBO. Bigtable. [Tanggal waktu] dan Anda memerlukan bagian tanggal dan waktu, menggunakan beberapa kolom dalam kueri SQL dan bukannya satu kolom datetime:

    "Pilih CAST (DBO. Bigtable. [Tanggal waktu] sebagai tanggal) sebagai [tanggal

    datepart (HH, DBO. Bigtable. [Tanggal waktu]) sebagai [jam waktu tanggal],

    datepart (MI, DBO. Bigtable. [Tanggal waktu]) sebagai [date time minutes],

    datepart (SS, DBO. Bigtable. [Tanggal waktu]) sebagai [detik waktu

    datepart (MS, DBO. Bigtable. [Tanggal waktu]) sebagai [tanggal waktu milidetik] "

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

  4. Jika Anda membutuhkan jam dan menit, dan Anda lebih memilihnya sebagai kolom satu kali, Anda bisa menggunakan sintaks tersebut:

    Timefromparts (datepart (HH, DBO. Bigtable. [Tanggal waktu]), datepart (mm, DBO. Bigtable. [Tanggal waktu])) sebagai [jam waktu tanggal]

  5. Jika Anda memiliki dua kolom datetime, seperti [mulai waktu] dan [End Time], dan apa yang Anda perlukan adalah selisih waktu di antara keduanya dalam hitungan detik sebagai kolom yang disebut [Duration], Hapus kedua kolom dari daftar dan tambahkan:

    "DateDiff (SS, [Start date], [end date]) sebagai [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 telah bekerja dengan bahasa ekspresi DAX sebelumnya, Anda mungkin sudah tahu bahwa kolom terhitung digunakan untuk mendapatkan kolom baru berdasarkan beberapa kolom lain di dalam model, sementara pengukuran terhitung sekali dalam model, tapi hanya dievaluasi saat digunakan dalam PivotTable atau laporan lainnya.

Satu teknik penghematan memori adalah mengganti kolom reguler atau terhitung dengan pengukuran terhitung. Contoh klasik adalah harga satuan, kuantitas, dan total. Jika Anda memiliki ketiga, Anda bisa menghemat ruang dengan mempertahankan hanya dua dan menghitung yang ketiga menggunakan DAX.

2 kolom mana yang harus Anda Pertahankan?

Dalam contoh di atas, Pertahankan kuantitas dan harga satuan. Kedua memiliki nilai yang lebih sedikit daripada total. Untuk menghitung total, tambahkan ukuran terhitung seperti:

"TotalSales: = sumx (' Sales Table ', ' Sales Table ' [satuan harga] * ' Sales Table ' [Quantity])"

Kolom terhitung seperti kolom biasa dalam kedua ruang yang diambil dalam model. Sebaliknya, pengukuran terhitung dihitung dengan cepat dan Jangan ambil ruang.

Kesimpulan

Dalam artikel ini, kami berbicara tentang beberapa pendekatan yang bisa membantu Anda membuat model yang lebih efisien dengan memori. Cara untuk mengurangi ukuran file dan persyaratan memori dari model data adalah 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 cara terbaik untuk menghemat ruang. Putuskan kolom mana yang Anda perlukan.

  • Terkadang Anda bisa 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 ke dalam beberapa bagian berbeda adalah cara yang baik untuk mengurangi jumlah nilai unik dalam kolom. Setiap bagian akan memiliki sedikit 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.

  • Beberapa kali, kolom berisi informasi lebih banyak dari yang Anda perlukan. Misalnya, misalkan kolom menyimpan desimal, tapi Anda telah menerapkan pemformatan untuk menyembunyikan semua desimal. Pembulatan bisa sangat efektif dalam mengurangi ukuran kolom numerik.

Setelah Anda menyelesaikan 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

Unduh Pengoptimal ukuran buku kerja

PowerPivot: Analisis data yang efektif dan pemodelan data di Excel

Kembangkan keterampilan Office 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.

×