Temporal Healthcare Data Warehouse with SCD Type 2 for OLAPBased Executive Information Systems: A Galaxy Schema Approach - Muhammad Mukhlis Khoirudin; Irman Hermadi; Hari Agung Adrianto (Asosiasi Doktor Sistem Informasi Indonesia, 2026)
Modern hospital information systems operate within fragmented and heterogeneous OLTP environments designed for transaction recording rather than strategic analytical workloads. This study designs and implements a temporal healthcare Data Warehouse for OLAP analytics supporting historical data consistency and multidimensional performance monitoring aligned with Indonesian Ministry of Health (Kemenkes RI) standards. The Kimball bottom-up methodology was applied to integrate two heterogeneous OLTP sources into a Galaxy Schema comprising two fact tables and seventeen dimension tables. The ETL pipeline was implemented using SSIS with a complete SCD Type 2 workflow preserving full patient dimension history across ward transfers and physician reassignments. The OLAP layer was constructed using SSAS Tabular with VertiPaq in-memory engine and DAX-based measures, visualized through a Power BI Executive Information System dashboard. Empirical validation using 52,321 discharged patient records over a one-year observation period demonstrated an average OLAP Speedup Ratio of 30.27× over equivalent relational SQL queries across ten benchmark runs, ranging from 2.98× (Slice) to 79.18× (Dice), with all response times below 40 ms and 100% computational accuracy across all six Kemenkes RI KPIs. The novelty lies in integrating a complete SCD Type 2 temporal model within a Galaxy Schema healthcare data warehouse, empirically validated against live heterogeneous hospital data and nationally aligned KPI standards.