Tips MySQL‎ > ‎

Menghasilkan Dimensi Waktu dengan MySQL

Contents

  1. 1 Pendahuluan
  2. 2 Script Stored Procedure : DimWaktuProc.sql

Pendahuluan

Dimensi waktu merupakan salah satu dimensi terpenting yang selalu ada di data warehouse kita. Banyak cara untuk menghasilkan dimensi waktu itu dan biasanya melalui aplikasi ETL (Extract, Transform and Load).

Dari pengalaman, karena penggunaan ETL sering sekali membingungkan untuk menghasilkan dimensi waktu ini dan kecenderungan untuk menggunakan MySQL sebagai data staging dan production di data warehouse, maka penulis ingin membagi script stored procedure untuk menghasilkan dimensi waktu sederhana di bawah ini.

Script Stored Procedure : DimWaktuProc.sql

Stored procedure yang dihasilkan dari script ini akan membentuk data tabular dengan column dan keterangannya sebagai berikut :
  1. SK_Waktu : Surrogate Key dari dimensi waktu
  2. Tanggal : Tanggal dalam format lengkap  
  3. Hari_Dalam_Tahun : Menunjukkan hari dalam tahun (1,2,3 ... 365)
  4. Hari_Dalam_Bulan : Menunjukkan hari dalam bulan (1.. 31)
  5. Hari_Dalam_Minggu : Menunjukkan hari dalam minggu (1..7) dimana index 1 dimulai dari hari Minggu
  6. Nama_Hari_En  : Nama hari dalam bahasa Inggris (Sunday, Monday, ...)
  7. Nama_Hari_ID : Hari dalam bahasa Indonesia (Minggu, Senin, ...)
  8. Bulan : Angka yang menunjukkan bulan (1 ... 12)
  9. Nama_Bulan_En : Nama bulan dalam bahasa Inggris (January, February, ... December)
  10. Nama_Bulan_Id : Nama bulan dalam bahasa Inggris (Januari, Februari, ... Desember)
  11. Kuartal : Angka yang menunjukkan kuartal tahun (1, 2, 3 dan 4)
  12. Tahun : Angka yang menunjukkan tahun (1970, 1999... 2001, dan lain-lain).
Eksekusi script tersebut terhadap database MySQL Anda. Contoh untuk menjalankan script ini adalah sebagai berikut :

-- Menghasilkan kalender selama 400 hari dimulai dari 1 Januari 2010
CALL DimWaktuProc(2010, 400);

Script juga dapat di-download pada halaman http://code.google.com/p/phi-integration/downloads/detail?name=DimWaktuProc.sql. Silahkan modifikasi script ini untuk keperluan project Anda.

-- File              : DimWaktuProc.sql
-- Description       : Script Stored Procedure untuk menghasilkan data kalender waktu
--                     tabular yang bisa digunakan sebagai table dimensi waktu
--                     pada data warehouse Anda.
-- Nama Procedure    : DimWaktuProc(tahun, jumlah_hari)
--                     Keterangan :
--                     tahun = adalah awal tahun kalender dimulai.
--                     jumlah_hari = jumlah hari yang diinginkan dimulai dari spesifikasi
--                                tahun dari argumen pertama.
-- Contoh Penggunaan : call DimWaktuProc(2008,1000);
-- Author            : PHI-Integration
-- Email             : feris@phi-integration.com
-- Website           : http://www.kampusbi.com
-- Version           : 1.0
-- ===============================================================================
 
-- Blok Stored Procedure untuk menghasilkan table kalender atau dimensi waktu.
DELIMITER //
 
CREATE PROCEDURE DimWaktuProc(IN tahun INT, IN jumlah_hari INT)
BEGIN
  SET @loopvar := 1;
  SET @endloopvar := jumlah_hari;
  SET @tglawal = CONCAT(tahun,'-01-01');
 
  DROP TABLE IF EXISTS dim_waktu_temp;
 
  CREATE TABLE dim_waktu_temp(sk_waktu INT, tanggal DATETIME);
 
  read_loop: LOOP
    IF @loopvar > @endloopvar THEN
        LEAVE read_loop;
    ELSE
        INSERT INTO dim_waktu_temp(sk_waktu, tanggal) VALUES (@loopvar, @tglawal + INTERVAL (@loopvar-1) DAY);
        SET  @loopvar := @loopvar + 1;
    END IF;
  END LOOP;
 
  SELECT *
        , DAYOFYEAR(tanggal) AS `Hari_Dalam_Tahun`
        , DAYOFMONTH(tanggal) AS `Hari_Dalam_Bulan`
        , DAYOFWEEK(tanggal) AS `Hari_Dalam_Minggu`
        , DATE_FORMAT(tanggal, "%W") AS `Nama_Hari_En`
        , CASE DAYOFWEEK(tanggal)
                WHEN 1 THEN 'Minggu'
                WHEN 2 THEN 'Senin'
                WHEN 3 THEN 'Selasa'
                WHEN 4 THEN 'Rabu'
                WHEN 5 THEN 'Kamis'
                WHEN 6 THEN 'Jumat'
                WHEN 7 THEN 'Sabtu'
          END AS `Nama_Hari_ID`
        , MONTH(tanggal) AS `Bulan`
        , DATE_FORMAT(tanggal, "%M") AS `Nama_Bulan_En`
        , CASE MONTH(tanggal)
                WHEN 1 THEN 'Januari'
                WHEN 2 THEN 'Februari'
                WHEN 3 THEN 'Maret'
                WHEN 4 THEN 'April'
                WHEN 5 THEN 'Mei'
                WHEN 6 THEN 'Juni'
                WHEN 7 THEN 'Juli'      
                WHEN 8 THEN 'Agustus'  
                WHEN 9 THEN 'September'        
                WHEN 10 THEN 'Oktober'  
                WHEN 11 THEN 'November'        
                WHEN 12 THEN 'Desember'        
          END AS `Nama_Bulan_Id`
        , QUARTER(tanggal) AS `Kuartal`
        , DATE_FORMAT(tanggal, "%Y") AS `Tahun`
        FROM dim_waktu_temp;
  DROP TABLE IF EXISTS dim_waktu_temp;
END //
 
DELIMITER ;
-- Akhir dari blok CREATE PROCEDURE
Comments