Menggunakan MS Query untuk menghubungkan Data – (MS Query Bag. 1)

Bismillah..

MS Query mungkin bukan tools untuk analisa data yang canggih seperti layaknya tools analisa canggih seperti PowerPivot, PowerQuery dan lainnya. Apalagi kalau mengingat tampilan nya yang mirip sebuah Aplikasi taun ’90 an yang terlihat agak kuno.

Tapi dengan kemampuan MS Query yang dapat menghubungkan tiap-tiap kolom dalam Excel sheet dan menjadikan nya selayaknya seperti Tabel dalam sebuah Database yang saling terkoneksi, maka dengan penggunaan MS Query ini untuk proses Lookup data yang biasa kita lakukan dengan Formula, dapat dilakukan dengan lebih cepat dan Praktis.

Ya, saya paham.. terkait dengan postingan sebelumnya mengenai Excel sebagai Database disini :). Tapi sepertinya mindset saya harus berubah saat baca buku Excel Advanced Report Development nya Timothy Zapawa.

Beberapa kelebihan penggunaan Microsoft Query dalam pengolahan data yang banyak (Big Data) dibandingkan dengan formula Lookup (Vlookup, Index – Match, dll) antara lain :

1. Proses awal saat membuka (loading) file hasil MS Query akan jauh lebih cepat. Dibandingkan dengan file yang penuh dengan formula lookup yang akan jauh lebih lambat.

2. Dengan adanya penambahan baris baru / data baru pada sumber data, yang perlu kita lakukan hanyalah klik Refresh pada data hasil Query.

3. Proses Refresh data juga dapat di atur pada tiap interval waktu.

4. Ditambah dengan menambahkan sedikit perintah SQL dalam proses filter data, proses Import dapat menjadi lebih efisien.

5. Proses analisa data dapat segera dilakukan dengan adanya fasilitas untuk menghasilkan data dalam bentuk Pivot Table / Data Table.

Ok. Langsung aja. Untuk contoh kasus kali ini kita gunakan file Northwind.xlsx yang bisa di unduh disini. Kasusnya adalah gabung beberapa Sheet untuk membuat sebuah laporan berapa banyak transaksi (OrderID) yang dibuat oleh masing-masing karyawan dengan menampilkan produk untuk tiap transaksi.

Sebelumnya bukalah 1 Workbook baru. Untuk mengakses MS Query dapat dilakukan melalui Tab Data (saya gunakan Excel 2010) --> Grup Get External Data --> From Other Sources -->  from Microsoft Query. Untuk lebih praktis nya, Keyboard shortcut nya adalah Alt + d + d + n.

1. Pada dialog box Choose Data Source pilih Excel Files* dan klik OK

2. Pilih directory dimana file MSQuery_Northwind.xlsx disimpan pada dialog box Select Workbook dan pada kotak Database Name lalu klik OK

3. Sheet-sheet yang ada dalam Workbook Northwind.xlsx tersebut akan dimunculkan didalam dialog box Add Tables. Kalau ada Sheet yang tidak muncul, klik Options.. dan pastikan kotak System Tables di centang √

4. Pilih Table / Sheet Employees dan klik Add. Atau klik dua kali pada Tabel Employees. Lakukan hal yang sama untuk Tabel / Sheet Order Details, Orders, dan Products lalu klik Close. Klik tombol Auto Query untuk segera menampilkan hasil Query.

5. Klik field EmployeeID pada Tabel Employees, dan tarik (drag) untuk menghubungkan field tersebut ke field EmployeeID di Tabel Orders. Lakukan hal yang sama untuk Field OrderID, dan ProductID pada Tabel Orders, Orders Details dan ProductID

6. Pilih EmployeeID dari Tabel Empoyees dan seret ke Data Grid. Lakukan hal yang sama untuk field FirstName dan LastName, OrderID dan ProductID dari Tabel OrderDetails, ProductName dan UnitPrice dari Tabel Products sehingga tampilan nya akan seperti contoh gambar dibawah.

7. Klik Tombol Return Data pada toolbar dan pastikan pilihan Tabel dan Existing Worksheet terpilih pada saat dialog box Import Data muncul. Dalam gambar contoh dipilih Range A1 pada Sheet1 (=Sheet1!$A$1). Klik OK.

Dan data sudah disajikan dalam format Data Table dan terhubung :)

Pada posting berikut insya Allah akan dibahas lebih lanjut mengenai MS Query ini.