Data Warehouse dan Data Mart dari OLTP MySQL ke OLAP ClickHouse

Dalam dunia bisnis modern, pengambilan keputusan yang didasarkan pada data yang akurat dan terstruktur menjadi kunci keberhasilan. Untuk mencapai tujuan ini, perusahaan sering menggunakan infrastruktur data yang terdiri dari sistem OLTP untuk pengolahan transaksi sehari-hari, Data Mart untuk analisis yang terfokus, dan sistem OLAP untuk pengolahan dan analisis data yang kompleks. Dalam artikel ini, kami akan mengeksplorasi proses pembuatan Data Mart dari OLTP MySQL ke OLAP ClickHouse melalui ETL, dengan studi kasus fokus pada pengolahan transaksi harian menjadi data agregat bulanan yang siap untuk dikonsumsi oleh dashboard.

Apa itu Data Warehouse?

Data Warehouse adalah sebuah sistem yang dirancang untuk mengumpulkan, menyimpan, dan mengelola data dari berbagai sumber yang berbeda dalam satu lokasi sentral. Tujuan utamanya adalah menyediakan akses yang mudah dan terpadu terhadap data yang terintegrasi, yang nantinya dapat digunakan untuk analisis dan pengambilan keputusan. Data Warehouse biasanya menggunakan teknologi seperti database relasional, teknologi Big Data, atau kombinasi keduanya untuk menyimpan volume data yang besar.

Salah satu fitur utama dari Data Warehouse adalah proses ekstraksi, transformasi, dan pemuatan (ETL), yang merupakan langkah kunci dalam pembuatannya. Proses ETL ini melibatkan tiga tahap utama:

1. Ekstraksi (Extraction): Data diekstrak dari sumber-sumber yang berbeda seperti basis data operasional, file teks, atau sumber data lainnya. Proses ini dapat melibatkan ekstraksi seluruh data atau hanya data yang telah diubah sejak ekstraksi sebelumnya.
2. Transformasi (Transformation): Data yang diekstrak kemudian diubah, dibersihkan, dan dimodifikasi agar sesuai dengan struktur dan format yang diperlukan untuk penyimpanan dalam Data Warehouse. Transformasi juga melibatkan penggabungan data dari berbagai sumber, pembersihan data yang tidak valid atau tidak lengkap, serta penggunaan aturan bisnis untuk memperkaya dan mengubah data.
3. Pemuatan (Loading): Setelah proses transformasi selesai, data yang telah dimodifikasi dimuat ke dalam Data Warehouse. Proses pemuatan dapat dilakukan dalam berbagai cara, termasuk pemuatan batch, pemuatan real-time, atau pemuatan terjadwal.

Proses ETL ini memastikan bahwa data yang disimpan dalam Data Warehouse konsisten, terstandarisasi, dan siap untuk digunakan dalam analisis dan pengambilan keputusan.

Apa itu Data Mart?

Data Mart adalah subset dari Data Warehouse yang berfokus pada domain atau departemen tertentu dalam sebuah organisasi. Berbeda dengan Data Warehouse yang mencakup semua data perusahaan, Data Mart hanya berisi data yang relevan untuk kebutuhan spesifik pengguna atau kelompok pengguna tertentu. Data Mart sering kali dibangun untuk memenuhi kebutuhan analisis dan pengambilan keputusan yang spesifik dalam satu area fungsional tertentu, seperti pemasaran, keuangan, atau sumber daya manusia.

Proses pembuatan Data Mart mirip dengan pembuatan Data Warehouse, namun dengan fokus yang lebih spesifik dan terbatas. Langkah-langkah utamanya mencakup:

Langkah 1: Desain Data Mart

Pertama-tama, kita perlu merancang struktur Data Mart kita. Dalam studi kasus ini, kita akan memiliki tabel Data Mart yang menyimpan agregasi jumlah dan nominal transaksi per bulan. Pada case ini, Data Mart atau OLAP menggunakan Clickhouse.

OLAP menggunakan ClickHouse:

1. Columnar Storage: ClickHouse menggunakan penyimpanan kolom yang sangat efisien untuk data, yang mengoptimalkan kinerja analisis dan agregasi data. Pendekatan ini memungkinkan pembacaan data yang cepat dan efisien, khususnya saat melakukan operasi agregasi pada subset kolom yang besar.
2. Massive Parallel Processing (MPP): ClickHouse dirancang untuk memanfaatkan arsitektur MPP yang memungkinkan pemrosesan data dalam skala besar secara paralel. Hal ini sangat cocok untuk analisis data tingkat lanjut yang memerlukan kinerja tinggi dan skala yang luas.
3. Real-time Data Ingestion: ClickHouse mendukung ingest data secara real-time, yang memungkinkan pengguna untuk melakukan analisis data secara langsung pada data yang baru saja masuk. Ini penting untuk aplikasi yang membutuhkan informasi terkini dan pemantauan data yang real-time.

Struktur tabel Data Mart mungkin terlihat seperti ini:

CREATE TABLE transaction_summary (
    month DATE,
    total_transactions INT,
    total_amount DECIMAL(10, 2)
);

Tabel ini akan menyimpan data agregat per bulan yang dihasilkan dari data transaksi harian.

Langkah 2: Persiapan OLTP MySQL

Dalam contoh ini, kita akan menggunakan MySQL sebagai sistem OLTP untuk menyimpan data transaksi harian.

OLTP menggunakan MySQL:

1. Transactional Nature: MySQL merupakan sistem manajemen basis data relasional (RDBMS) yang sangat baik dalam menangani transaksi individual secara efisien. Ini membuatnya menjadi pilihan yang ideal untuk sistem OLTP yang membutuhkan penyimpanan dan pengelolaan data transaksional sehari-hari.
2. Relational Data Model: MySQL memungkinkan penggunaan model data relasional yang fleksibel dan dapat diatur dengan baik, yang cocok untuk aplikasi yang memerlukan konsistensi data dan integritas referensial.
3. Stability and Maturity: MySQL telah ada selama bertahun-tahun dan telah terbukti stabil dan matang sebagai sistem basis data OLTP, didukung oleh komunitas pengembang yang besar dan dukungan vendor yang solid.

Berikut adalah contoh struktur tabel transaksi:

CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    transaction_date DATE,
    amount DECIMAL(10, 2),
    description VARCHAR(255)
);

Tabel transactions akan menyimpan transaksi harian, termasuk tanggal transaksi, jumlah, dan deskripsi transaksi.

Langkah 3: Proses ETL

Setelah desain Data Mart selesai, langkah selanjutnya adalah membangun proses ETL untuk mengambil data dari tabel transaksi di MySQL, melakukan agregasi per bulan, dan memuat data ke tabel Data Mart di ClickHouse.

Berikut adalah langkah-langkah yang diperlukan dalam proses ETL:

Ekstraksi (Extraction):

SELECT 
    DATE_FORMAT(transaction_date, '%Y-%m') AS month,
    transaction_id,
    amount
FROM 
    transactions;

Query di atas akan mengambil data transaksi harian dari tabel transactions di MySQL dan akan mengembalikan kolom bulan (dalam format 'YYYY-MM'), ID transaksi, dan jumlah transaksi.

Transformasi (Transformation):

SELECT 
    month,
    COUNT(transaction_id) AS total_transactions,
    SUM(amount) AS total_amount
FROM 
    (
        SELECT 
            DATE_FORMAT(transaction_date, '%Y-%m') AS month,
            transaction_id,
            amount
        FROM 
            transactions
    ) AS transaksi_harian
GROUP BY 
    month;

Query di atas akan mengambil data yang diekstrak sebelumnya (dalam subquery transaksi_harian), kemudian akan menghitung jumlah transaksi dan total nominal transaksi per bulan. Hasilnya akan dikelompokkan berdasarkan kolom bulan.

Pemuatan (Loading):

INSERT INTO transaction_summary (month, total_transactions, total_amount)
VALUES (?, ?, ?);

Ini adalah contoh query untuk memuat data hasil transformasi ke tabel transaction_summary di ClickHouse. Tanda tanya (?) adalah placeholder yang kemudian akan digantikan oleh nilai yang sesuai dari hasil transformasi.

Query di atas akan dimasukkan ke dalam sebuah job ETL di Pentaho di mana langkah-langkah ekstraksi, transformasi, dan pemuatan akan didefinisikan secara terpisah. Proses ETL kemudian akan mengeksekusi query-query ini secara otomatis untuk mengambil, memproses, dan memuat data dari MySQL ke ClickHouse sesuai dengan jadwal yang telah ditentukan.

Dengan menggunakan query-query di atas dalam proses ETL, data transaksi harian dapat diolah menjadi data agregat bulanan yang siap untuk dikonsumsi oleh dashboard untuk analisis lebih lanjut.

Sumber Referensi:

1. Kimball, Ralph, and Margy Ross. The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling. Wiley, 2013.
2. Inmon, W. H., & Hackathorn, R. D. Using the Data Warehouse. Wiley, 1994.
3. Inmon, W. H., & Rymer, J. D. T. Building the Data Warehouse. Wiley, 1992.
4. Corr, D. W. Mastering Data Warehouse Design: Relational and Dimensional Techniques. Wiley, 2003.
5. Redmond, E., & Wilson, J. Practical Data Warehouse and Business Intelligence Solutions. McGraw-Hill Osborne Media, 2009