Application of data warehouse and OLAP processes for retail analytics - Victorio Palben Medel (Jejaring Penelitian dan Pengabdian Masyarakat, 2026)
Retail organizations increasingly rely on heterogeneous operational platforms, including point-of-sale systems, customer relationship management applications, cloud data stores, and locally administered databases. Although these platforms are valuable for transaction processing, they often generate fragmented, duplicated, and semantically inconsistent data that constrain enterprise reporting, forecasting, and customer intelligence. This paper substantially extends a conceptual SwiftMart case into a full design-and-evaluation study of a retail data warehouse and Online Analytical Processing (OLAP) framework. The proposed artifact combines a Kimball-style dimensional architecture, a governed extract-transform-load (ETL) pipeline, conformed dimensions, and materialized OLAP summaries for managerial analytics. To ground the case empirically, the framework is evaluated using the open-access UCI Online Retail dataset, which contains 541,909 transaction records from a UK-based online retailer covering 1 December 2010 to 9 December 2011. The experiment transforms raw transactions into a star schema with 524,878 curated fact rows, 19,960 orders, 4,355 customer members, 4,158 product members, and 38 countries. Four representative analytical workloads are benchmarked across three storage designs: a normalized operational data store, a dimensional warehouse, and materialized aggregate tables. The dimensional warehouse reduces mean latency by 42.3% relative to baseline joins, while materialized aggregates reduce latency by approximately 99.9%. A forecasting demonstration on warehouse-generated daily revenue aggregates further shows that a random forest model outperforms a naive benchmark, achieving an RMSE of 23,715.84 versus 34,055.29. The paper contributes an end-to-end reference architecture for retail analytics, together with dimensional design rationale, mathematical formulations, algorithms, empirical results, and implementation guidance relevant to both academic researchers and practitioners.