Model Real-Time Extract Transform Load Pada Data Warehouse Populasi Ternak Kambing di Jawa Timur

Abstract

Ketersediaan data yang mampu menghasilkan analisis informatif dan cepat menjadi kebutuhan mendasar dalam bisnis modern. Pengembangan Data Warehouse (DW) dengan data yang tersimpan secara real-time membutuhkan optimalisasi proses ekstraksi, transformasi, dan pemuatan data (ETL). Proses ETL yang efisien dapat memastikan aliran informasi real-time, menjaga kualitas data, dan meminimalkan dampak pada kinerja basis data operasional. Salah satu tantangan utama dalam pengembangan model ETL adalah meningkatkan kemampuan deteksi perubahan data secara real-time untuk memastikan data di DW tetap berkualitas, serta memastikan hasil On-Line Analytical Processing (OLAP) tersedia secara konsisten dan mutakhir. Waktu menjadi faktor penting dalam pemrosesan data real-time. Untuk mengatasi tantangan tersebut, perlu dilakukan modifikasi model ETL menjadi Real-time ETL (RTETL) yang menggabungkan kemampuan pemrosesan data batch dan stream. Dengan demikian, integrasi data akan menghasilkan Real-time Data Warehouse (RTDW) yang mampu mendukung pengambilan keputusan yang lebih cepat dan akurat. Penelitian ini bertujuan untuk mengembangkan model Real-Time ETL (RTETL) yang mampu memproses data ke dalam Real-Time Data Warehouse (RTDW) dengan waktu respons minimal dan mengintegrasikan Spatial OLAP (SOLAP) untuk analisis data real-time pada dashboard. Model RTETL dirancang dan dibangun menggunakan metodologi penelitian eksperimen dengan pendekatan studi kasus di lingkungan peternakan kambing di Kabupaten Probolinggo dan Kabupaten Lumajang, Provinsi Jawa Timur, yang difokuskan pada pergerakan populasi ternak kambing. Tahapan penelitian meliputi analisis kebutuhan, pengumpulan data wilayah Provinsi Jawa Timur, data peternakan, dan data populasi ternak kambing; pengembangan aplikasi akuisisi data; pemodelan dan pembangunan RTETL; pembangunan RTDW dan SOLAP; serta implementasi prototipe dengan membangun dashboard website. Melalui akses real-time, pengguna dan pemangku kepentingan dapat memperoleh informasi aktual yang berpotensi meningkatkan nilai bisnis dari peternakan kambing. Hasil penelitian pada rancang bangun aplikasi akuisisi data ternak kambing berbasis Android dan website admin untuk Puska berhasil direalisasikan dan berjalan dengan baik. Mekanisme pengambilan data secara real-time dari Real-Time Extract Transform Load (RTETL) menuju Real-Time Data Warehouse (RTDW) berhasil dibangun. Pengujian performa RTETL menunjukkan rata-rata latensi sangat rendah senilai 15,284 milidetik, dan berdasarkan hasil uji Kafka Lag menunjukkan nilai latensi sebesar 1,10 milidetik. Pengujian laju pemrosesan data (throughput) menunjukkan bahwa RTETL mampu memproses 529.279 baris per detik, juga dengan skenario per peternakan didapatkan delapan record data secara real-time dalam hitungan detik. Selain itu, konsistensi data menunjukkan hasil pengukuran 100% yang menandakan konsistensi data dalam skenario pemrosesan real-time berhasil dicapai. Real-Time Data Warehouse (RTDW) berhasil dibangun dan memenuhi kebutuhan berdasarkan uji kesesuaian dan kualitas data. Modul Spasial OLAP (SOLAP) yang dikembangkan mampu menjalankan fungsi roll-up, drill-down, slice, dan dice dalam tampilan peta dinamis dengan latensi 3,304 milidetik sesuai dengan hasil penyaringan (filtering) data berdasarkan parameter hierarki seperti Tahun, Provinsi, Kabupaten/Kota, Jenis Ternak, Grup Usia, dan Jenis Kelamin. Prototipe dashboard Puska mampu menyajikan informasi penting mengenai populasi ternak kambing. Pengujian performa menunjukkan kondisi idle time dengan 10 pengguna yang mengakses secara bersamaan, dashboard website Puska.info memiliki performa baik dengan skor 80%, dan waktu muat website rata-rata sebesar 7,4 detik. Kontribusi kebaruan dalam bidang ilmu komputer yang dihasilkan dari penelitian ini adalah pengembangan model Real-Time ETL (RTETL) yang mampu menangani aliran data secara efisien untuk memenuhi kebutuhan penyimpanan dan analisis data historis serta data real-time dengan latensi minimal. Integrasi model dengan data spasial menghasilkan Spasial Real-Time Data Warehouse (Spasial RTDW) menggunakan Spatial OLAP, memungkinkan pengambilan keputusan berbasis lokasi. Sementara itu, kebaruan dalam bidang ilmu peternakan meliputi pembangunan aplikasi Android untuk akuisisi data ternak kambing secara langsung dari peternakan, serta implementasi Real-Time Data Warehouse (RTDW) populasi ternak kambing berbasis web, dengan visualisasi dashboard berbentuk peta, crosstab, dan grafik yang menggunakan data real-time.

The availability of data capable of producing informative and fast analysis is a fundamental need in modern business. The development of a Data Warehouse (DW) with real-time stored data requires optimization of the data extraction, transformation, and loading (ETL) process. An efficient ETL process can ensure real-time information flow, maintain data quality, and minimize the impact on operational database performance. One of the key challenges in ETL model development is to improve realtime data change detection capabilities to ensure the quality of data in the DW, as well

as ensuring On-Line Analytical Processing (OLAP) results are consistently available

and up-to-date. Time is an important factor in real-time data processing. To overcome

these challenges, it is necessary to modify the ETL model to Real-time ETL (RTETL)

which combines batch and stream data processing capabilities. Thus, data integration

will produce a Real-time Data Warehouse (RTDW) that is able to support faster and

more accurate decision making.

This research aims to develop a real-time ETL (RTETL) model capable of processing data into a real-time Data Warehouse (RTDW) with minimal response time and integrating Spatial OLAP (SOLAP) for real-time data analysis on dashboards. The RTETL model is designed and built using an experimental research methodology with a case study approach in a goat farming environment in Probolinggo Regency and Lumajang Regency, East Java Province, which focused on the movement of goat population. The research stages include needs analysis, data collection of East Java Province, farm data, and goat population data; development of data acquisition applications; modeling and building RTETL; building RTDW and SOLAP; and implementing prototypes by building website dashboards. Through real-time access, users and stakeholders can obtain actual information that has the potential to increase the business value of goat farming. The results of the research on the design of the Android-based goat livestock data acquisition application and website admin for Puska were successfully implemented and run properly. The real-time data retrieval mechanism from RealTime Extract Transform Load (RTETL) to Real-Time Data Warehouse (RTDW) was

successfully built.

RTETL performance testing showed very low average latency of 15,284 milliseconds, and based on the Kafka Time Lag testing results, the latency value was 1.10 milliseconds. Data processing rate (throughput) testing showed that the RTETL is capable of processing 529,279 rows per second, and per in-farm scenario, eight data records were obtained in real time within seconds. Additionally, data consistency measurements generated a 100% result, indicating that data consistency in real-time processing scenarios has been successfully achieved. The Real-Time Data Warehouse (RTDW) was successfully built and met the needs based on the suitability and data quality tests. The developed Spatial OLAP (SOLAP) module is able to perform roll-up, drill-down, slice, and dice functions in a dynamic map view with a latency of 3,304 milliseconds, in accordance with data filtering results based on hierarchical parameters such as Year, Province, District/City, Livestock Type, Age Group, and Gender. The Puska dashboard prototype is able to present important information about the goat population. Performance testing shows idle time conditions with 10 users accessing simultaneously, the Puska.info website dashboard has good performance with a score of 80%, and the average website load time is 7.4 seconds. The novelty contribution in the field of computer science resulting from this research is the development of a real-time ETL (RTETL) model that is able to handle data flows efficiently to meet the needs of storing and analyzing historical data and real-time data with minimal latency. Integrating the model with spatial data results in a Spatial Real-Time Data Warehouse (Spatial RTDW) using Spatial OLAP, enabling location-based decision making. Meanwhile, the novelty in the field of animal science includes the development of an Android application for the acquisition of goat livestock data directly from the farm, as well as the implementation of a web-based real-time Data Warehouse (RTDW) of goat livestock population, with dashboard visualization in the form of maps, crosstabs, and graphs using real-time data.