Ambi tabel dari sql server ke mysql
Latar belakangBerdasarkan 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 DataDengan 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.Kita membutuhkan 3 kolom :
Mengidentifikasi perhitungan-perhitungan apa yang harus dilakukan pada suatu kolom (kalau perlu)
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 :
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 Hapus kolom orderid dan product id : Ambil semua kolom yang dibutuhkan pada tabel-tabel lain yang terkaitMengambil kolom-kolom Production.Categories dari Production.Products : Mengambil kolom CategoryName dari Production.Categories : Mengambil kolom OrderDate dari Sales.Orders : Lakukan operasi-operasi yang perlu dilakukanPertama-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 :
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 :
Hasil akhirnya seperti ini, sesuai dengan yang diharapkan : Hapus semua kolom-kolom yang tidak dibutuhkanLangkah ini tidak perlu dilakukan lagi. Rename atau ubahlah nama-nama kolom sesuai kebutuhanLangkah ini tidak perlu dilakukan lagi. KesimpulanEnd 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. Kursus Terkait55164A Quick Powerful Graphics with Power View, PowerPivot, Power Query, Power Map and Power BI#PowerBI #powerquery |