Implementasi Arsitektur OLAP pada Database OLTP dengan Log-Based CDC: Studi Kasus SIMKOPDES

Abstract

SIMKOPDES is an information system that supports the management and presentation of Koperasi Desa/Kelurahan Merah Putih information. Directly executing reporting workloads on the Online Transaction Processing (OLTP) database may create resource contention because analytical queries involve multiple joins, aggregations, and large-scale data processing. This study designs and implements an Online Analytical Processing (OLAP) architecture on the SIMKOPDES OLTP database using a log-based Change Data Capture (CDC) mechanism. The study adopts the Database System Development Life Cycle (DSDLC), while Kimball's Four-Step Dimensional Design is applied during database design. The OLAP schema consists of four dimension tables and three fact tables. Data changes are synchronized using PostgreSQL write-ahead logging, a replication slot, the wal2json plugin, and Python middleware. Performance evaluation using EXPLAIN (ANALYZE, BUFFERS) across five analytical scenarios yielded average efficiencies of 85.23% in execution time, 53.59% in I/O buffer usage, and 80.09% in query cost relative to OLTP. Across all five scenarios, the OLAP row counts and aggregate values matched the OLTP results. In addition, all seven tested data-change scenarios were successfully captured and applied to the affected OLAP tables. These results indicate that an OLAP architecture with log-based CDC can improve analytical query efficiency while maintaining consistency with the OLTP source data.