Business Intelligence for Multidimensional Retail Sales Analysis Using a Data Warehouse

Abstract

Retail transaction data must be transformed into reliable analytical information to support data-driven decision-making. This study develops a Business Intelligence (BI) framework based on a star-schema data warehouse for multidimensional retail sales analysis across customer, product, and time dimensions. A public Kaggle dataset containing 1,000 synthetic transactions from 2023 and nine attributes was processed through an Extract, Transform, and Load (ETL) pipeline implemented in Pentaho Data Integration and loaded into MySQL. Data validity was assessed using completeness, arithmetic-consistency, and referential-integrity criteria. The pipeline loaded all 1,000 transactions, with no missing required values, no discrepancies between Total Amount and Quantity × Price per Unit, and no unmatched foreign keys. The analysis produced a total sales value of 456,000 monetary units and 2,514 units sold. Electronics generated the highest sales value (156,905), whereas Clothing had the highest volume (894 units). Monthly sales peaked in May (53,150) and were lowest in September (23,620). Female and male customers contributed 51.06% and 48.94%, respectively. The framework demonstrates a traceable approach to multidimensional retail analytics, although generalization is limited by the synthetic dataset.