Day 31: SQL Untuk Data Warehousing

Learning In Home-Original Date: 12 Februari 2009

Tidak terasa sudah sebulan berada di Pontianak, dan sebentar lagi hari Valentine akan tiba. Dalam suasana penuh cinta ini, aku akan menikmatinya dengan mempelajari SQL yang kerap dipakai dalam query yang berkaitan dengan data warehousing. Aku akan mulai dengan klausa GROUP BY CUBE dan fungsi GROUPING. Anggap saja aku punya tabel TBL_PEMBELIAN dan tabel TBL_KATEGORI. Lalu, aku ingin mendapatkan laporan pembelian per-hari untuk setiap kategori yang ada. Untuk itu, aku dapat menggunakan query berikut:


SELECT pb.tanggal, kat.nama, COUNT(*)
FROM TBL_PEMBELIAN pb, TBL_KATEGORI kat
WHERE pb.barang_id = kat.id GROUP BY CUBE (pb.tanggal, kat.nama);


GROUP BY CUBE pada query di atas selain mengembalikan kombinasi tanggal dan kategori, juga mengembalikan total untuk masing-masing kombinasi dalam bentuk nilai NULL seperti:

----------------------------------
BA
RIS TANGGAL    NAMA      COUNT(*)
----------------------------------
1  NULL       NULL       16
2  NULL       KATEGORI1  8
3  NULL       KATEGORI2  8
4  01/01/2009 NULL       5
5  01/01/2009 KATEGORI1  3
6  01/01/2009 KATEGORI2  2
7  02/01/2009 NULL       11
8  02/01/2009 KATEGORI1  5
9  02/01/2009 KATEGORI2  6
----------------------------------

Nilai ‘NULL’ disini memiliki arti khusus, yaitu subtotal dari sebuah baris. Sebagai contoh, baris 4 adalah baris yang menunjukkan jumlah transaksi pada tanggal 01/01/2009 yaitu 5 (3+2). Begitu juga dengan baris 7. Baris 2 menunjukkan total COUNT untuk seluruh kategori 1, yaitu 8 (3+5). Baris 3 menunjukkan total COUNT untuk seluruh kategori 2, yaitu 8 (2+6).

Agar hasil-nya lebih rapi, aku dapat menggunakan fungsi GROUPING dan DECODE untuk memberi tulisan tertentu pada label untuk nilai subtotal, seperti:


SELECT decode(grouping(pb.tanggal),1, 'Total', pb.tanggal),
decode(grouping(kat.nama), 1, 'Seluruh Kategori', kat.nama),
COUNT(*) FROM TBL_PEMBELIAN pb, TBL_KATEGORI kat WHERE pb.barang_id = kat.id GROUP BY CUBE (pb.tanggal, kat.nama);


Jika aku tidak menginginkan hasil pada baris 2, dan 3, dimana terdapat subtotal untuk masing-masing tanggal, maka aku dapat menggunakan GROUP BY ROLLUP yang tidak berbeda jauh dengan GROUP BY CUBE, sehingga query-nya menjadi:


SELECT decode(grouping(pb.tanggal),1, 'Total', pb.tanggal),
decode(grouping(kat.nama), 1, 'Seluruh Kategori', kat.nama),
COUNT(*) FROM TBL_PEMBELIAN pb, TBL_KATEGORI kat WHERE pb.barang_id = kat.id GROUP BY ROLLUP
(pb.tanggal, kat.nama);


Query yang mengandung fungsi GROUPING() dapat menjadi sulit dibaca jika banyak atribut yang harus diperiksa dengan fungsi tersebut. Oleh sebab itu, fungsi GROUPING_ID() hadir untuk mempermudah. GROUPING_ID() akan mengambil nilai 0 dan 1 dari GROUPING(), lalu merangkainya sesuai urutan atribut menjadi deretan angka 0 dan 1 sebagai bilangan biner. GROUPING() akan menghasilkan konversi bilangan biner ke bilangan desimal dari deretan angka 0 dan 1 ini. Hal ini terlihat dari hasil query berikut:


SELECT
GROUPING(pb.tanggal) as "TANGGAL",
GROUPING(kat.nama) as "KATEGORI",
GROUPING_ID(pb.tanggal, kat.nama)
FROM
tbl_pembelian pb, tbl_kategori kat
WHERE
pb.barang_id = kat.id
GROUP BY
ROLLUP (pb.tanggal, kat.nama);


-------------------------
  TANG KATE   GROUPING
  GAL  GORI   _ID
-------------------------
  0    0         0
  0    0         0
  0    1         1
  0    0         0
  0    0         0
  0    0         0
  0    1         1
  ...  ...       ...
  1    1         3
--------------------------

Dengan demikian, aku dapat memilih level detail subtotal yang ingin ditampilkan berdasarkan GROUPING_ID, dengan menambahkan klausa seperti berikut ini di akhir query:


HAVING
GROUPING_ID(pb.tanggal, brg.nama, brg.harga) >= 1;


Day 30: Dimension

Learning In Home-Original Date: 11 Februari 2009

Data warehousing membutuhkan struktur data khusus yang tidak ditemui di OLTP, salah satunya adalah dimension/dimensi. Contoh dimension antara lain time, product dan customer. Dimensi dapat memiliki hierarki, misalnya, dimensi time dapat di-roll up menjadi quarters, dan quarters dapat di-roll up menjadi years, dan years di-roll up menjadi all years. Analisa data umumnya dimulai dari dimensi dengan hierarki tertinggi dan perlahan-lahan di-drill down sesuai kebutuhan.

Sebagai contoh, terdapat sebuah denormalized table dengan nama times yang memiliki atribut seperti calendar_year, calendar_quarter_number, calendar_month_number, calendar_week_number, dan day_number_in_week, dan lainnya. Aku dapat merepresentasikan hubungan hierarki antar-kolom di tabel tersebut dengan membuat dimensi sebagai berikut:




CREATE DIMENSION LATIHAN_TIMES
  LEVEL year IS 
    (times.calendar_year)
  LEVEL quarter IS 
    (times.calendar_quarter_number)
  LEVEL month IS 
    (times.calendar_month_number)
  LEVEL week IS 
    (times.calendar_week_number)
  LEVEL day IS 
    (times.day_number_in_week)
  HIERARCHY times_rollup (
    day  CHILD OF
    week CHILD OF
    month CHILD OF
    quarter CHILD OF
    year
  );

Untuk melihat keterangan mengenai dimensi yang baru saja aku buat tadi, aku dapat menggunakan perintah:


EXECUTE dbms_dimension.describe_dimension('LATIHAN_TIMES');


Untuk memeriksa apakah dimensi yang baru aku buat valid, aku akan menggunakan API dbms_dimension.validate_dimension(). Tapi sebelum itu, aku akan membuat sebuah tabel DIMENSION_EXCEPTIONS terlebih dahulu dengan menjalankan script %ORA_HOME%\rdbms\admin\utldim.sql. Setelah itu, aku menjalankan kode berikut:


EXECUTE dbms_dimension.validate_dimension('LATIHAN_TIMES', FALSE, TRUE, 'check1');


Aku akan mendapatkan daftar kesalahan di tabel DIMENSION_EXCEPTION. Disini terdapat rowid baris mana yang invalid. Kesalahan yang aku buat adalah hubungan relasi 1:n antara sebuah parent dan anak-anaknya. Sebuah ‘year’ dapat memiliki banyak ‘quarter number’, tetapi sebuah ‘quarter number’ tidak dapat mengandung lebih dari satu parent ‘year’. Untuk itu, aku harus menggunakan id agar mendapatkan quarter number yang unik di setiap year yang ada. Begitu juga dengan hierarki di bawahnya sehingga dimensi-nya kira-kira seperti berikut ini:




DROP DIMENSION LATIHAN_TIMES;

CREATE DIMENSION LATIHAN_TIMES
  LEVEL year IS 
    (times.calendar_year)
  LEVEL quarter IS 
    (times.calendar_quarter_id)
  LEVEL month IS 
    (times.calendar_month_id)  
  LEVEL day IS 
    (times.time_id)
  HIERARCHY times_rollup (
    day  CHILD OF    
    month CHILD OF
    quarter CHILD OF
    year
  );