Durasi Membaca: 5 menit
Beberapa waktu yang lalu, saya mendapatkan tugas untuk membuat analisis data mengenai log aktivitas user yang mengakses sistem kepegawaian. Data yang akan diolah sebenarnya bisa dilakukan melalui query langsung di database server. Namun, ketika proses query dilakukan dengan membuat penggabungan JOIN dengan table yang berelasi, maka waktu eksekusi memakan waktu yang sangat lama. Hal ini semakin menjadi dengan kemampuan server yang terbatas untuk pengolahan data tersebut. Solusi tearkhir yang dapat saya lakukan untuk mengolah data tersebut yaitu dilakukan dengan menggunakan Excel dalam pengolahan datanya. Proses extraksi data dapat berjalan dengan lancar karena proses querynya sederhana hanya Select * from NamaTable Where, untuk setiap tabel yang akan diolah.
Setelah data diextraksi dan dicopy-paste kedalam excel, maka muncul masalah baru. Pada saat data ditempel ke dalam sheet excel, maka row excel yang terakhir yang dapat ditampung hanya maksimal 1,048,576 rows. Padahal data log aktivitas mencapai 1.2 juta row. Bagaimana cara mengolah data relational dengan jumlah row mencapai 1.2 row ? Tool yang saya gunakan masih di dalam aplikasi excel yaitu Power Pivot and Power Query. Power Query dikenal sebagai tool untuk “Get and Transform” pada aplikasi Excel 2016.
Untuk persiapan mengolah data silakan download dahulu tooll Power Query melalui link ini: Download Power Query
Secara garis besar langkah-langkah yang harus kamu lakukan adalah sebagai berikut:
Catatan: Jadi sebetulkan fitur Power Query ini sudah bawaan pada Excel 2016, sedangkan pada Excel 2013 masih sebagai add-on. Lalu bagaimana cara mengaktifkan fitur Power Query yang sudah diinstal tadi?
Mengaktifkan Fitur Power Query pada Excel 2013
Pilih Menu FIle > lalu pilih Options > Klik sub menu Add-ins > pada dropdown pilih COM Add-ins.Setelah muncul kotak pilihan, maka ceklist pada pilihan Microsoft Office PowerPivot dan Microsoft Power Query, klik Ok.
Langkah 1 – Import Data Mentah
File yang akan diolah kita pecah menjadi menjadi 2 file CSV yaitu file1.csv dan file2.csv yang masing-masing isinya sekitar 500 ribu row data, atau bebas yang penting harus dibawah angka limit row excel dalam 1 sheet.Jika kamu menggunakan Excel 2016, maka tampilan untuk import data adalah sebagai berikut:
| Excel 2016 |
Sedangkan kamu yang menggunakan Excel 2013, maka fitur ini akan ada melalui Menu Power Query yang sudah kita download dan install tadi.
| Excel 2013 |
Setelah itu browse pada 2 file CSV yang sudah kita pecah tadi.
Bila file pecahan kamu tadi jumlahnya banyak, maka pada akan muncul penggabungan dari pecahan file tadi ke dalam data olahan excel. Pada tahap ini, pengolahan data sudah bisa lebih dari 1 juta row data.
Penting: dari hasil percobaan saya, maka ada hal yang harus kita cermati, bahwa 2 file yang kita pecah tadi harus memiliki nama header yang sama. Ketika kedua file di Load, maka header tadi akan menjadi kata kunci untuk kita cleansing, sehingga tersisa datanya saja.
Dalam pengolahan data dengan Power Query dan Power Pivot, data yang terlihat sebenarnya tidak di load semuanya. Hal ini untuk mengurangi beban dalam pemrosesan data. Jika ingin melakukan load data, klik pada icon panah bawah.
Langkah 2 – Cleansing Data
Data yang sudah diimport mengandung header yang harus kita exclude (cleansing). Untuk mengecualikan row header tersebut, lakukan filter pencarian dengan klik pada tanda panah, lalu pilih text filters. Pilih opsi Does Not Contain, artinya kita akan memilih semua row data kecuali yang mengandung kata dalam header.Tahap cleansing akan berjalan lancar bila nama header antara 2 file gabungan tadi sama, sesuai dengan catatan penting yang sudah saya tulis di atas. Setelah data bersih, maka lakukan load untuk membuat Data Model Pivot, dengan cara klik File > Close & Load to..
Pada pilihan kotak yang muncul, pilih Only Create Connection, artinya data yang diload hanya mereferensi kepada sumber data utama, dan tidak membuat file baru. Selain itu pilih pada worksheet mana data akan diload, serta jangan lupa cek list pilihan Add this to Data Model.
Tahap cleansing selesai.
Langkah 3 – Memodifikasi Data Model
Pada tahap ini, kita akan menggunakan tool yang kedua yaitu Power Pivot. Untuk membuka tools ini, klik pada menu Power Pivot, lalu pada klik pada icon warna hijau dengan tulisan Manage. Jika tidak muncul icon tersebut, berarti fitur belum di aktifkan. Silakan kembali ke penjelasan di awal artikel ini.Fungsi Data Model Pivot ini adalah untuk melakukan modifikasi data yang mungkin dibutuhkan. Misalnya ketika kita memiliki data tanggal, kita bisa memodifikasi untuk mengekstrak data tahun atau bulannya saja dalam satu kolom.
Klik pada header tabel, lalu masukkan formula yang ingin dilakukan.
Apabila telah selesai melakukan modifikasi data, maka data tersebut siap untuk diproses dengan pivot tabel. Klik menu Home, lalu pilih icon Pivot Table.
Pilih lokasi worksheet untuk meletakkan hasil dari Pivot Table.
Nah, sampai pada tahap ini tentu kawan sudah tidak asing lagi, karena proses Pivot Table dapat dilakukan seperti biasa. Proses di atas hanya membantu kita bagaimana melakukan load, cleansing, dan mempersiapkan data dengan jumlah row data yang lebih besar (Big Data).
Semoga bermanfaat.
Referensi: https://www.masterdataanalysis.com/ms-excel/analyzing-50-million-records-excel/