Artikel Teknis

Subscribe to posts

Window Function: Position Awareness Query di PostgreSQL

posted Jul 6, 2014, 6:19 AM by Feris Thia   [ updated Jul 7, 2014, 7:39 AM ]

Pendahuluan

Analisa data selalu dilakukan dengan membandingkan dua atau lebih data. Contohnya perbandingan data penjualan dua tahun terakhir untuk melihat pertumbuhan, membandingkan data penjualan bulan terkini dengan kontribusi penjualan secara keseluruhan, dan sebagainya.

Pada berbagai kesempatan, telah saya jelaskan kalau kemampuan ini sangat kurang di SQL yang tidak sensitif terhadap posisi data. Analisa data menuntut suatu syarat agar dapat melakukan navigasi data dengan mudah. Ini berujung pada terciptanya konsep cube dan bahasa query MDX (Multi Dimensional Expression).

Namun ternyata dari informasi yang saya dapatkan di milis id-python, di PostgreSQL ada fungsi SQL khusus yang dapat membantu kita untuk melakukan navigasi data, yang dalam beberapa hal mirip dengan MDX.

Window Functions dan Contoh Penggunaannya

Position awareness function ini disebut dengan Window Functions pada PostgreSQL, dan rincian penjelasannya dapat Anda baca pada halaman situs http://www.postgresql.org/docs/8.4/static/functions-window.html. 

Function-function ini disebut windows atau jendela karena berfokus pada suatu partisi data, misalkan partisi data terhadap kelompok data berdasarkan kolom "bulan" dan "tahun". 

Dan setiap window didefinisikan dengan klausa over. Ada beberapa syntax, tapi salah satu yang akan digunakan adalah dengan konstruksi berikut:

NAMA_FUNGSI(ekspresi) OVER ( ORDER BY ekspresi [ASC | DESC| USING operator] )

Artikel ini akan fokus ke dua contoh fungsi ini, yaitu lag dan penggunaan sum dari partisi / window. Cobalah perhatikan contoh data sederhana yang akan kita gunakan berikut ini. Script untuk table ini dapat Anda download di bagian akhir artikel ini.



Kita ingin mendapatkan hasil akhir sebagai berikut:


Fungsi lag yang akan kita gunakan akan menggunakan syntax berikut:

LAG(field, [, jarak / offsset [, default any ]]) OVER ( ORDER BY ekspresi [ASC | DESC| USING operator] )

Dan query lengkapnya untuk menghasilkan output di atas adalah sebagai berikut:

  1. SELECT
  2.   tahun,
  3.   bulan,
  4.   nilai_penjualan,
  5.   LAG(nilai_penjualan, 1) OVER (ORDER BY tahun, bulan) AS "penjualan_sebelumnya",
  6.   nilai_penjualan - (COALESCE(LAG(nilai_penjualan, 1) OVER (ORDER BY tahun, bulan), 0)) AS "pertumbuhan"
  7. FROM summary_penjualan;

Penjelasan Query

  • Function LAG lengkap yang kita gunakan adalah sebagai berikut:

    LAG(nilai_penjualan, 1) OVER (ORDER BY tahun, bulan) AS "penjualan_sebelumnya"

  • Function akan mengambil data nilai_penjualan dari 1 window dari konstruksi LAG(nilai_penjualan,1).
  • Kemudian window yang akan digunakan adalah bulan sebelumnya, ditunjukkan dengan konstruksi OVER (ORDER BY tahun, bulan).
  • Selesai

Penutup 

Demikian artikel singkat ini saya buat, semoga bermanfaat. Pada kesempatan lain, akan penulis coba buat lagi berbagai case analytic lain seperti running total atau moving average. Stay tuned! 

Sumber Referensi

| Attachments: summary_penjualan.sql

Export data SQL Server ke Excel dengan Pentaho Data Integration

posted Jun 11, 2012, 7:07 PM by Feris Thia   [ updated Jun 11, 2012, 10:27 PM ]

Kenapa Excel dengan ETL ?

Pada implementasi kebanyakan proyek data warehouse / BI kami, Excel adalah output yang banyak diminta oleh berbagai divisi.

Selain karena Excel adalah aplikasi perkantoran standar. Banyak pengolahan lanjutan yang bisa dilakukan oleh user untuk mendapatkan laporan akhir.

Beruntung, Pentaho Data Integration - sebagai aplikasi ETL utama kita - mendukung dengan sangat baik output ke format Excel ini dan bahkan dengan performa yang sangat baik. 

Di samping itu berbagai konfigurasi advanced bisa dilakukan juga, sehingga fleksibilitas tinggi.

Pada artikel kali ini, penulis menunjukkan contoh penggunaan output Excel dengan sumber data dari SQL Server 2008 R2. 

Konfigurasi Input SQL Server

  1. Contoh sumber data yang akan adalah suatu table SQL Server 2008 R2 dengan nama ms_harga_harian dari database phi_minimart, ditunjukkan pada gambar screenshot berikut.

    Table ini memiliki 45,018 row data.


  2. Jalankan aplikasi Spoon - Pentaho Data Integration.
  3. Buatlah satu transformation baru. Simpan dengan nama yang Anda inginkan, misalkan export_excel.ktr. 


  4. Masukkan step Table Input ke ruang kerja transformation kita.


  5. Buka dialog step Table Input tersebut, namakan dengan ms_harga_harian. Klik tombol New untuk membuat koneksi baru.


  6. Pada dialog Database Connection, lakukan konfigurasi untuk koneksi SQL Server Anda. 


  7. Klik tombol Test untuk memastikan koneksi Anda sudah berhasil, klik tombol OK untuk dialog yang muncul.


  8. Klik tombol OK pada dialog Database Connection untuk mengakhiri konfigurasi.
  9. Anda akan dibawa kembali ke dialog Table Input. Masukkan query SQL yang Anda inginkan untuk mengambil data. Klik tombol Preview jika diperlukan untuk melihat contoh data yang diambil.


  10. Klik tombol OK.
  11. Konfigurasi sumber data Anda sudah selesai, tahap selanjutnya adalah mengirimkan output dalam format Excel.

Konfigurasi Step Microsoft Excel Output

  1. Tambahkan step Microsoft Excel Output pada ruang kerja kita.


  2. Hubungkan kedua step tersebut, caranya seperti tampak pada gambar berikut ini.


  3. Buka step Microsoft Excel Output untuk melakukan konfigurasi lebih lanjut.
  4. Pada tab File, masukkan lokasi nama file output (tanpa extension).


    Jika Anda ingin tanggal dan jam proses dimasukkan, pilih opsi Include date in filename? dan Include time in filename ?.

  5. Pilih tab Fields, dan klik tombol Get Fields untuk mendapatkan daftar field yang akan dijadikan output.


  6. Agar outputnya dapat memiliki size column yang disesuaikan dengan isinya, klik tab Content dan centang Auto size columns.


  7. Klik tombol OK untuk menutup dialog.


  8. Konfigurasi output telah selesai, sekarang kita akan mencoba menjalankan transformation ini dan melihat hasilnya.


Menjalankan Transformation

  1. Simpan transformation Anda dengan mengklik tombol Save.


  2. Masih pada ruang kerja yang sama, klik tombol Run this transformation or job.


  3. Pada dialog yang muncul, klik tombol Launch.
  4. Transformation akan dijalankan, tunggu sampai selesai. Terlihat pada gambar di bawah ini statistik kecepatan flow ini, yaitu data 45,018 diselesaikan dalam waktu 2 detik.


  5. Hasil file output dalam bentuk Excel akan Anda dapatkan seperti tampak pada gambar berikut.


  6. Untuk menjalankan otomatisasi atas script ETL ini, buatlah batch script. Petunjuknya dapat Anda baca disini.

Output Excel yang lebih Baik ?

Selain step Microsoft Excel Output, pada Pentaho Data Integration versi 4.2 terdapat Microsoft Excel Writer dengan kemampuan  format dan fleksibilitas yang lebih baik.

Untuk informasi lebih lanjut mengenai step ini dapat Anda baca referensinya disini.

Sumber Referensi


Pentaho Data Integration : Masalah Zero Date pada MySQL

posted Jun 5, 2012, 2:39 AM by Feris Thia   [ updated Jun 5, 2012, 3:46 AM ]

Permasalahan Zero Date

Masalah zero date (nilai tanggal berisi angka nol) pada table MySQL merupakan penyebab error yang  cukup memusingkan developer ETL Pentaho Data Integration (PDI). 

Apalagi jika field dan table yang terlibat cukup banyak sehingga solusi conditional query di statement SQL pasti akan sangat membebani produktivitas pekerjaan.

Lalu apakah ada solusi yang efisien, efektif dan menyeluruh untuk hal ini ? 

Jawabannya ada dan sangat sederhana, kita cukup memberikan opsi zeroDateTimeBehavior=convertToNull pada konfigurasi koneksi kita. 

Dengan konfigurasi ini maka setiap zero date yang ada akan dikonversikan menjadi nilai null yang dapat dimengerti oleh PDI. Berikut adalah contoh dan rincian langkah konfigurasi pada Pentaho Data Integration versi 4.2.

Contoh dan Solusi pada Pentaho Data Integration

  1. Diberikan contoh suatu table yang memiliki struktur terlihat sebagai berikut. Dua field diantaranya bertipe datetime, yaitu tanggal_surat dan tanggal_kirim.


  2. Data dari table tersebut terdiri dari dua row dengan isi seperti terlihat pada gambar berikut. Pada field tanggal_surat maupun tanggal_kirim terdapat zero date value (0000-00-00 00:00:00).


  3. Ketika pada Pentaho Data Integration (PDI) kita membaca table tersebut akan terjadi error, seperti terlihat pada gambar screenshot berikut.


  4. Untuk memperbaiki hal ini, buka terlebih dahulu dialog koneksi database terkait. Pada contoh ini, nama koneksinya  adalah kampusbi. 

    Setelah dialog terbuka klik menu Options pada panel bagian kiri.


  5. Tambahkan entri baru zeroDateTimeBehavior=convertToNull. Klik tombol OK.


  6. Jalankan kembali transformation kita, error tersebut sudah tidak akan terjadi lagi.


  7. Selesai.

Sumber Referensi

1-3 of 3