Masuk dengan Microsoft
Masuk atau buat akun.
Halo,
Pilih akun lain.
Anda memiliki beberapa akun
Pilih akun yang ingin Anda gunakan untuk masuk.

Dalam Excel 2013 atau yang lebih baru, Anda dapat membuat model data yang berisi jutaan baris, lalu melakukan analisis data yang hebat terhadap model ini. Model data dapat dibuat dengan atau tanpa add-in Power Pivot mendukung visualisasi PivotTable, bagan, dan 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.

Meskipun Anda bisa dengan mudah menyusun model data besar Excel, ada beberapa alasan untuk tidak. Pertama, model besar yang berisi banyak tabel dan kolom secara berlebihan untuk sebagian besar analisis, dan menghasilkan Daftar Bidang yang rumit. Model besar kedua menggunakan memori yang berharga, secara negatif mempengaruhi aplikasi dan laporan lain yang berbagi sumber daya sistem yang sama. Terakhir, di Microsoft 365, SharePoint Online dan Excel Web App membatasi ukuran file Excel menjadi 10 MB. Untuk model data buku kerja yang berisi jutaan baris, Anda akan mendapatkan batas sebesar 10 MB cukup cepat. Lihat Spesifikasi dan batasan Model Data.

Di artikel ini, Anda akan mempelajari cara menyusun model yang dirancang dengan erat yang lebih mudah digunakan dan menggunakan lebih sedikit memori. Meluangkan waktu untuk mempelajari praktik terbaik dalam desain model yang efisien akan berguna bagi model apa pun yang Anda buat dan gunakan, baik 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 Workbook Size Optimizer.

Di artikel ini

Rasio kompresi dan mesin analitik dalam memori

Model data Excel menggunakan mesin analitik dalam memori untuk menyimpan data dalam memori. Mesin menerapkan teknik pemadatan yang efektif untuk mengurangi persyaratan penyimpanan, menciutkan kumpulan hasil hingga merupakan pecahan dari ukuran aslinya.

Secara rata-rata, Anda bisa memperkirakan model data akan 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 dalam database Excel bisa dengan mudah 1 MB atau kurang. Derajat pemadatan yang sebenarnya dicapai bergantung pada jumlah nilai unik di setiap kolom. Semakin banyak nilai unik, semakin banyak memori yang diperlukan untuk menyimpannya.

Mengapa kita berbicara tentang pemadatan dan nilai unik? Karena membangun model yang efisien yang meminimalkan penggunaan memori adalah tentang pemadatan maksimalisasi, dan cara termudah untuk melakukannya adalah dengan menghilangkan kolom apa pun yang tidak benar-benar Anda perlukan, terutama jika kolom tersebut berisi sejumlah besar nilai unik.

Catatan: Perbedaan persyaratan penyimpanan untuk kolom individual bisa sangat besar. Dalam beberapa kasus, lebih baik membuat beberapa kolom dengan jumlah nilai yang unik rendah daripada satu kolom dengan jumlah nilai yang unik yang tinggi. Bagian optimisasi Datetime mencakup teknik ini secara mendetail.

Tidak ada yang berdetak pada kolom yang tidak ada untuk penggunaan memori rendah

Kolom paling efisien menggunakan memori adalah kolom yang tidak pernah Anda impor di tempat pertama. Jika Anda ingin menyusun model yang efisien, lihat setiap kolom dan tanyakan diri Anda apakah ini berkontribusi pada analisis yang ingin Anda lakukan. Jika tidak atau Anda tidak yakin, tinggalkan saja. Anda selalu dapat menambahkan kolom baru nanti jika membutuhkannya.

Dua contoh kolom yang harus selalu dikecualikan

Contoh pertama berhubungan dengan data yang berasal dari gudang data. Dalam gudang data, umumnya ditemukan artifak dari proses ETL yang memuat dan merefresh data dalam gudang. Kolom seperti "buat tanggal", "perbarui tanggal", dan "ETL berjalan" dibuat saat data dimuat. Kolom tersebut tidak diperlukan dalam model dan harus tidak dipilih saat Anda mengimpor data.

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

Banyak tabel, termasuk tabel fakta, memiliki kunci utama. Untuk sebagian besar tabel, seperti tabel yang berisi data pelanggan, karyawan, atau penjualan, Anda pasti menginginkan 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. Meskipun diperlukan untuk tujuan normalisasi, akan lebih mudah jika Anda menggunakan kolom tersebut saja untuk analisis atau untuk menetapkan hubungan tabel. Karena alasan ini, ketika mengimpor dari tabel fakta, jangan sertakan kunci utamanya. Kunci utama di tabel fakta menghabiskan jumlah ruang yang sangat besar di dalam model, namun tidak menyediakan manfaat, karena tidak bisa digunakan untuk membuat hubungan.

Catatan: Dalam gudang data dan database multidimensi, tabel besar yang terdiri dari sebagian besar data numerik sering disebut sebagai "tabel fakta". Tabel fakta biasanya berisi kinerja bisnis atau data transaksi, seperti titik data penjualan dan biaya yang diagregatkan dan diratakan ke 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 yang disimpan di tabel lain harus disertakan dalam model untuk mendukung analisis data. Kolom yang ingin Anda keluarkan adalah kolom kunci utama dari tabel fakta, yang berisi nilai unik yang hanya ada di 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 akan Anda butuhkan dalam buku kerja Anda. Jika ingin mengontrol kolom yang disertakan dalam model, Anda harus menggunakan Panduan Impor Tabel di add-in Power Pivot untuk mengimpor data, bukan kotak dialog "Impor Data" dalam Excel.

Saat memulai Panduan impor Tabel, Pilih tabel mana yang akan diimpor.

Panduan Impor Tabel dalam add-in PowerPivot

Untuk setiap tabel, Anda bisa mengklik tombol & Filter Pratinjau dan memilih bagian tabel yang benar-benar Anda perlukan. Kami merekomendasikan agar Anda terlebih dahulu menghapus centang semua kolom, lalu melanjutkan untuk memeriksa kolom yang Anda inginkan, setelah mempertimbangkan apakah kolom itu diperlukan untuk analisis.

Panel Pratinjau dalam panduan Impor Tabel

Bagaimana dengan memfilter baris yang diperlukan saja?

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

Dengan menggunakan panduan 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 riwayat yang tidak akan diperlukan.

Panel filter dalam panduan Impor Tabel

Bagaimana jika kita memerlukan kolom tersebut; can we still reduce its space cost?

Ada beberapa teknik tambahan yang bisa Anda terapkan untuk membuat kolom 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 dapat dimodifikasi untuk mengurangi jumlah nilai yang unik.

Mengubah kolom Datetime

Dalam banyak kasus, kolom Tanggal waktu menggunakan 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 SQL kueri.

Kolom datetime menyertakan bagian tanggal dan waktu. Saat Anda bertanya pada diri Anda sendiri apakah Anda memerlukan kolom, ajukan pertanyaan yang sama beberapa kali untuk kolom Datetime:

  • Apakah saya memerlukan komponen waktu?

  • Apakah saya memerlukan bagian waktu dalam tingkatan jam? , menit? , Detik? , milidetik?

  • Apakah saya memiliki beberapa kolom Tanggal waktu karena ingin menghitung perbedaan di antaranya, atau hanya mengagregasi data menurut tahun, bulan, kuartal, dan lain-lain.

Cara Anda menjawab setiap pertanyaan ini menentukan opsi Anda untuk berhadapan dengan kolom Datetime.

Semua solusi ini memerlukan modifikasi kueri SQL. Untuk membuat modifikasi kueri lebih mudah, Anda harus memfilter setidaknya satu kolom di 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 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 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 SQL kueri yang digunakan untuk mengisi tabel. Jika Anda memfilter kolom apa pun selama pengi impor, kueri Anda menyertakan nama kolom yang sepenuhnya memenuhi syarat:

Kueri SQL digunakan untuk mengambil data

Sebaliknya, jika Anda mengimpor tabel secara keseluruhan, tanpa menghapus centang kolom apa pun 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.

Memodifikasi kueri SQL

Setelah mengetahui cara menemukan kueri, Anda dapat mengubahnya untuk mengurangi ukuran model lebih lanjut.

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

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

    Jika Anda memerlukan sen tetapi bukan pecahan sen, ganti 0 dengan 2. Jika Anda menggunakan angka negatif, Anda bisa membulatkan ke unit, sepuluh, ratusan dll.

  2. Jika Anda memiliki kolom Datetime bernama dbo. Bigtable. [Tanggal Waktu] dan Anda tidak memerlukan bagian Waktu, gunakan sintaks untuk menghilangkan waktu:

    "SELECT CAST (dbo. Bigtable. [Tanggal waktu] sebagai tanggal) AS [Tanggal waktu]) "

  3. 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:

    "SELECT CAST (dbo. Bigtable. [Tanggal Waktu] sebagai tanggal ) AS [Tanggal Waktu],

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

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

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

    datepart(ms, dbo. Bigtable. [Tanggal Waktu]) as [Date Time Milliseconds]"

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

  4. Jika Anda membutuhkan jam dan menit, dan Anda lebih suka mereka bersama-sama sebagai satu kolom waktu, Anda bisa menggunakan sintaks:

    Timefromparts(datepart(hh, dbo. Bigtable. [Tanggal Waktu]), datepart(mm, dbo. Bigtable. [Tanggal Waktu])) as [Date Time HourMinute]

  5. Jika Anda memiliki dua kolom tanggal, seperti [Waktu Mulai] dan [Waktu Selesai], dan yang benar-benar Anda butuhkan 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, bukan ss, Anda akan mendapatkan durasi dalam milidetik

Menggunakan pengukuran terhitung DAX, bukan kolom

Jika Anda pernah bekerja dengan bahasa ekspresi DAX, 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, tapi dievaluasi hanya saat digunakan dalam PivotTable atau laporan lainnya.

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

Mana 2 kolom yang harus Anda pertahankan?

Dalam contoh di atas, pertahankan Kuantitas dan Harga Satuan. Kedua nilai ini memiliki nilai yang lebih sedikit daripada Total. Untuk menghitung Total, tambahkan ukuran terhitung seperti:

"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"

Kolom terhitung adalah seperti kolom reguler di mana keduanya menggunakan ruang dalam model. Sebaliknya, pengukuran terhitung dihitung di mana saja dan tidak mengambil ruang.

Kesimpulan

Dalam artikel ini, kami menjelaskan beberapa pendekatan yang dapat membantu Anda menyusun model dengan memori yang lebih efisien. Cara untuk mengurangi ukuran file dan persyaratan memori model data adalah untuk mengurangi jumlah keseluruhan kolom dan baris, dan jumlah nilai unik yang muncul di setiap kolom. Berikut beberapa teknik yang dibahas:

  • 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 ukuran terhitung dalam tabel.

  • Anda mungkin tidak memerlukan semua baris dalam tabel. Anda dapat memfilter baris dalam Panduan Impor Tabel.

  • Secara umum,pisahkan kolom tunggal menjadi beberapa bagian yang berbeda adalah cara yang baik untuk mengurangi jumlah nilai unik dalam sebuah kolom. Setiap bagian akan memiliki sejumlah kecil nilai unik, dan total gabungan akan lebih kecil dari kolom terpadu asli.

  • Dalam banyak kasus, Anda juga membutuhkan bagian yang berbeda untuk digunakan sebagai pemotong dalam laporan Anda. Jika diperlukan, Anda dapat membuat hierarki dari berbagai bagian seperti Jam, Menit, dan Detik.

  • Berkali-kali, kolom berisi lebih banyak informasi daripada yang Anda butuhkan. Sebagai contoh, misalkan kolom menyimpan desimal, tapi Anda telah menerapkan pemformatan untuk menyembunyikan semua desimal. Membulatkan bisa sangat efektif dalam mengurangi ukuran kolom numerik.

Setelah melakukan apa yang dapat dilakukan untuk mengurangi ukuran buku kerja, 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 Workbook Size Optimizer.

Link terkait

Spesifikasi dan batasan Model Data

Pengoptimal Ukuran Buku Kerja

PowerPivot: Analisis data yang efektif dan pemodelan data di Excel

Perlu bantuan lainnya?

Ingin opsi lainnya?

Jelajahi manfaat langganan, telusuri kursus pelatihan, pelajari cara mengamankan perangkat Anda, dan banyak lagi.

Komunitas membantu Anda bertanya dan menjawab pertanyaan, memberikan umpan balik, dan mendengar dari para ahli yang memiliki pengetahuan yang luas.

Apakah informasi ini berguna?

Seberapa puaskah Anda dengan kualitas bahasanya?
Apa yang memengaruhi pengalaman Anda?
Dengan menekan kirim, umpan balik Anda akan digunakan untuk meningkatkan produk dan layanan Microsoft. Admin TI Anda akan dapat mengumpulkan data ini. Pernyataan Privasi.

Terima kasih atas umpan balik Anda!

×