Mengambil data dari Database dengan Power Query

Latar belakang

Berdasarkan penelitian software yang paling disukai oleh end users adalah microsoft excel. Kemudahan pemakaian dan fleksibilitas adalah beberapa penyebab microsoft excel disukai oleh end users. Excel merupakan aplikasi untuk membuat laporan dan analisa data. Sering sekali data yang diambil berada dalam Database dan biasanya end users harus mempelajari bahasa SQL (Structured Query Language).

Sekitar tahun 2013 lahir lah Power Query sebagai bagian dari teknologi Power Pivot. Power Query diposisikan sebagai Tool untuk mengambil dan mengubah data (Get and Transform). Data Source atau Sumber Data dari Power Query bisa bermacam-macam, bisa berupa Non-Database seperti file teks, excel, access, sharepoint, web page, dsb atau bisa juga berupa Database seperti Microsoft SQL Server, Microsoft SQL Server OLAP, Oracle, dsb.

Cara kerja Power Query adalah dengan menerjemahkan aktifitas yang dilakukan end users ke dalam bahasa pemrograman dari sumber data misalnya bahasa SQL untuk sumber data dari database Microsoft SQL Server. Contoh lain misalnya end users mengakses sumber data dari database Microsoft SQL Server OLAP maka aktifitas yang dilakukan end users akan diterjemahkan ke bahasa MDX.

Power Query tersedia sebagai Add-In yang harus di-install terpisah pada Microsoft Office 2010 dan 2013. Power Query tersedia sebagai Built-In Feature pada Microsoft Office 2016, di versi ini namanya sudah berubah menjadi “Query” saja. Namun demikian tidak semua fitur Power Query tersedia sepenuhnya pada semua versi baik Microsoft Office 2010, 2013 dan 2016. Dari sisi kestabilan fitur Power Query di Microsoft Office 2010 masih tidak stabil dan banyak bug nya, pada Microsoft Office 2016 fitur Query (Powe Query) bisa dibilang sudah stabil. Untuk lebih jelasnya bisa dilihat pada tabel di bawah ini :

Skenario Pengambilan Data

Dengan adanya Power Query maka End Users tidak perlu lagi mempelajari SQL terlebih dahulu bilamana ingin mengambil data dari Database namun demikian End Users tetap memerlukan pengetahuan dasar mengenai Relational Database Diagram.

Misalnya kita memiliki Relational Database Diagram sebagai berikut :

Kita ingin menampilkan data Sales yang dikelompokkan berdasarkan Category dan Year sebagai berikut :

Langkah-langkah yang harus kita ambil adalah sebagai berikut :

Mengidentifikasi kolom-kolom mana yang diperlukan beserta tabel-tabel nya.
Mengidentifikasi perhitungan-perhitungan apa yang harus dilakukan pada suatu kolom (kalau perlu)
Dengan menggunakan Power Query melakukan pemilihan tabel-tabel yang diperlukan.
Dengan menggunakan Power Query melakukan operasi-operasi yang dibutuhkan.

Mengidentifikasi kolom-kolom mana yang diperlukan beserta tabel-tabel nya.

Kita membutuhkan 3 kolom :

Category : Bisa diambil dari kolom categoryname di tabel Categories
Sales : Perlu dibuat perhitungan dari kolom-kolom sebagai berikut di tabel OrderDetails

Unitprice
Quantity
Discount

Year : Diambil dari kolom OrderDate di tabel Orders

Mengidentifikasi perhitungan-perhitungan apa yang harus dilakukan pada suatu kolom (kalau perlu)

Sales :
Perhitungan Sales = (UnitPrice * Quantity) * (-(Discount-1))
Perhitungan SUM(Sales) berdasarkan Category dan Year
Year : Diambil komponen tahun (Year) dari kolom OrderDate di tabel Orders

Dengan menggunakan Power Query melakukan pemilihan tabel-tabel yang diperlukan.

Pada Power Query kita tidak perlu mengambil semua tabel kita hanya perlu mengambil tabel-tabel yang mempunyai hubungan ke semua tabel yang kita perlu kan.

Tabel OrderDetails memiliki hubungan dengan tabel-tabel :

Products : Tabel Products mempunyai hubungan ke Tabel Categories
Orders

Jadi kita hanya perlu mengambil tabel OrderDetails saja.

Di Excel (contoh ini menggunakan versi 2016) pada tab Data klik New Query dan pilih From Database – From SQL Server Database. Kemudian ketik nama Server dan klik OK.

Pilih Database dan tabel yang akan digunakan kemudian klik Edit

Dengan menggunakan Power Query melakukan operasi-operasi yang dibutuhkan.

Langkah-langkah yang harus dilakukan di Power Query :

Hapus semua yang tidak dibutuhkan, misalnya hapus kolom-kolom yang tidak dibutuhkan
Ambil semua kolom yang dibutuhkan pada tabel-tabel lain yang terkait
Lakukan operasi-operasi yang perlu dilakukan
Hapus semua kolom-kolom yang tidak dibutuhkan
Rename atau ubahlah nama-nama kolom sesuai kebutuhan

Hapus semua yang tidak dibutuhkan, misalnya hapus kolom-kolom yang tidak dibutuhkan

Hapus kolom orderid dan product id :

Ambil semua kolom yang dibutuhkan pada tabel-tabel lain yang terkait

Mengambil kolom-kolom Production.Categories dari Production.Products :

Mengambil kolom CategoryName dari Production.Categories :

Mengambil kolom OrderDate dari Sales.Orders :

Lakukan operasi-operasi yang perlu dilakukan

Pertama-tama kita akan mengambil komponen Year di kolom OrderDate.

Klik kolom Sales.Orders.orderdate kemudian pada Menu masuk ke Tab “Add Colum”, pada Ribbon klik Date – Year – Year.

Hapus kolom Sales.Orders.orderdate.

Berikutnya kita melakukan operasi Perhitungan

Sales = (UnitPrice * Quantity) * (-(Discount-1)).

Pertama-tama kita lakukan perhitungan (UnitPrice * Quantity).

Pilih kolom unitprice dan qty kemudian pada Menu di Tab “Add Column” pilih Standard – Multiply :

Berikutnya kita lakukan perhitungan (Discount-1).

Pilih kolom discount kemudian pada Menu di Tab “Add Column” pilih

Standard-Substract:

Ketik angka “1” kemudian klik OK.

Berikutnya kita lakukan perhitungan (-(Discount-1)).

Klik kolom “Inserted Substraction” kemudian pada Menu di Tab “Add Column” pilih Standard – Multiply :

Ketik angka “1” kemudian klik OK.

Berikutnya kita lakukan operasi (UnitPrice * Quantity) * (-(Discount-1)).

Klik kolom “Inserted Multiplication” dan “Inserted Multiplication.1” kemudian pada Menu di Tab “Add Column” pilih Standard – Multiply :

Untuk lebih memudahkan kita akan rename beberapa kolom :

Kolom “Inserted Multiplication.2” menjadi “Total”.
Kolom “Production.Products.Production.Categories.categoryname” menjadi “Category”.

Klik kolom “Inserted Multiplication.2” klik kanan kemudian pilih Rename, ubah menjadi “Total”:

Klik kolom “Production.Products.Production.Categories.categoryname” klik kanan kemudian pilih Rename, ubah menjadi “Category”.

Berikutnya kita lakukan operasi SUM((UnitPrice * Quantity) * (-(Discount-1))) berdasarkan Category dan Year.

Klik kolom “Sales” klik kanan kemudian pilih Group By :

Tambahkan Grouping (Group By) untuk kolom-kolom Category dan Year.

Tambahkan Agregation :

New Column Name : Sales
Operation : Sum
Column : Total

Hasil akhirnya seperti ini, sesuai dengan yang diharapkan :

Hapus semua kolom-kolom yang tidak dibutuhkan

Langkah ini tidak perlu dilakukan lagi.

Rename atau ubahlah nama-nama kolom sesuai kebutuhan

Langkah ini tidak perlu dilakukan lagi.

Kesimpulan

End users bisa menggunakan fitur Power Query atau Query pada Excel (2010/2013/2016) untuk mengambil data dari database dengan mudah tanpa perlu mempelajari SQL (Structured Query Language), namun demikian perlu dicatat bahwa end users tetap perlu memahami dasar-dasar relational database diagram.