Temporal Healthcare Data Warehouse with SCD Type 2 for OLAPBased Executive Information Systems: A Galaxy Schema Approach

Authors

  • Muhammad Mukhlis Khoirudin IPB University; Prof. Dr. Margono Soekarjo Regional Public Hospital, Indonesia
  • Irman Hermadi IPB University, Indonesia
  • Hari Agung Adrianto IPB University, Indonesia
Pages Icon

DOI:

https://doi.org/10.63158/journalisi.v8i4.1651

Keywords:

healthcare data warehouse, executive information system, OLAP, SCD Type 2, galaxy schema

Abstract

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.

Downloads

Download data is not yet available.

References

[1] H. Dhayne, R. Haque, R. Kilany, and Y. Taher, “In search of big medical data integration solutions—A comprehensive survey,” IEEE Access, vol. 7, pp. 91265–91290, 2019, doi: 10.1109/ACCESS.2019.2927491.

[2] J. Thomason, “Big tech, big data and the new world of digital health,” Glob. Health J., vol. 5, no. 4, pp. 165–168, 2021, doi: 10.1016/j.glohj.2021.11.003.

[3] P. Densen, “Challenges and opportunities facing medical education,” Trans. Am. Clin. Climatol. Assoc., vol. 122, pp. 48–58, 2011.

[4] M. Younas, “Research challenges of big data,” Serv. Oriented Comput. Appl., vol. 13, no. 2, pp. 105–107, 2019, doi: 10.1007/s11761-019-00265-x.

[5] H. C. Lim et al., “Toward a learning health care system: A systematic review and evidence-based conceptual framework for implementation of clinical analytics in a digital hospital,” Appl. Clin. Inform., vol. 13, no. 2, pp. 339–354, 2022, doi: 10.1055/s-0042-1743243.

[6] E. Adel, S. El-Sappagh, S. Barakat, K. S. Kwak, and M. Elmogy, “Semantic architecture for interoperability in distributed healthcare systems,” IEEE Access, vol. 10, pp. 126161–126179, 2022, doi: 10.1109/ACCESS.2022.3223676.

[7] K. Arthofer and D. Girardi, “Data quality and master data management—A hospital case,” Stud. Health Technol. Inform., vol. 236, pp. 259–266, 2017, doi: 10.3233/978-1-61499-759-7-259.

[8] R. D. Thantilage, N. A. Le-Khac, and M. T. Kechadi, “Healthcare data security and privacy in data warehouse architectures,” Inform. Med. Unlocked, vol. 39, Art. no. 101270, 2023, doi: 10.1016/j.imu.2023.101270.

[9] B. Ozaydin, F. Zengul, N. Oner, and S. S. Feldman, “Healthcare research and analytics data infrastructure solution: A data warehouse for health services research,” J. Med. Internet Res., vol. 22, 2020, doi: 10.2196/18579.

[10] Z. Huo, K. Taylor, X. Zhang, S. Wang, and C. Pang, “Generating multidimensional schemata from relational aggregation queries,” World Wide Web, vol. 23, no. 1, pp. 337–359, 2020, doi: 10.1007/s11280-019-00706-9.

[11] Z. Wang et al., “Clinical data warehousing: A scoping review,” J. Soc. Clin. Data Manag., vol. 5, no. 2, pp. 1–19, 2024, doi: 10.47912/jscdm.320.

[12] P. Goyal and R. Malviya, “Challenges and opportunities of big data analytics in healthcare,” Health Care Sci., vol. 2, no. 5, pp. 328–338, 2023, doi: 10.1002/hcs2.66.

[13] P.-Y. Brossard, E. Minvielle, and C. Sicotte, “The path from big data analytics capabilities to value in hospitals: A scoping review,” BMC Health Serv. Res., vol. 22, no. 1, Art. no. 134, 2022, doi: 10.1186/s12913-021-07332-0.

[14] C. K. Leung, Y. Chen, C. S. H. Hoi, S. Shang, and A. Cuzzocrea, “Machine learning and OLAP on big COVID-19 data,” in Proc. IEEE Int. Conf. Big Data (Big Data), pp. 5118–5127, 2020, doi: 10.1109/BigData50022.2020.9378407.

[15] A. Delgado, T. Guimarães, J. Duarte, and M. F. Santos, “Time-sensitive clinical data handling and codification: A review and methodological approach,” Procedia Comput. Sci., vol. 239, pp. 1276–1283, 2024, doi: 10.1016/j.procs.2024.11.174.

[16] A. Yang and J. Wiratama, “Empowering data transformation: Transforming new data into a strategic planning e-commerce success,” J. Inf. Syst. Informatics, vol. 6, no. 1, pp. 1–12, 2024.

[17] Microsoft, “Transform healthcare experiences with Power BI,” Microsoft Corporation, 2025.

[18] 3Cloud, “Power BI Report Server: Benefits, how to purchase and more,” 3Cloud, 2025.

[19] R. Kimball and M. Ross, The Kimball Group Reader: Relentlessly Practical Tools for Data Warehousing and Business Intelligence. Indianapolis, IN, USA: Wiley, 2016.

[20] W. H. Inmon, Building the Data Warehouse, 4th ed. Indianapolis, IN, USA: Wiley, 2005.

[21] O. Moscoso-Zea, J. Castro, J. Paredes-Gualtor, and S. Luján-Mora, “A hybrid infrastructure of enterprise architecture and business intelligence & analytics for knowledge management in education,” IEEE Access, vol. 7, pp. 38778–38792, 2019, doi: 10.1109/ACCESS.2019.2906343.

[22] Kementerian Kesehatan Republik Indonesia and Direktorat Jenderal Bina Upaya Kesehatan, Juknis SIRS 2011: Sistem Informasi Rumah Sakit. Jakarta, Indonesia: Kementerian Kesehatan RI, 2011.

[23] N. H. Zasbita, M. E. Muzuh, W. Ode, and S. Budiaty, “Analysis of indicators (BOR, AVLOS, TOI, BTO, GDR, and NDR) children's spaces in improving health services,” J. Health Inform. Med. Res., vol. 1, no. 1, pp. 1–5, 2024.

[24] J. Gray, A. Bosworth, A. Layman, and H. Pirahesh, “Data cube: A relational aggregation operator generalizing group-by, cross-tab, and sub-totals,” Data Min. Knowl. Discov., vol. 1, pp. 29–53, 1997.

[25] A. Vaisman, “Mobility data warehouses,” ISPRS Int. J. Geo-Inf., vol. 8, no. 4, Art. no. 170, 2019, doi: 10.3390/ijgi8040170.

[26] S. Bimonte, E. Gallinucci, P. Marcel, and S. Rizzi, “Data variety: Come as you are in multi-model data warehouses,” Inf. Syst., vol. 102, Art. no. 101734, 2021, doi: 10.1016/j.is.2021.101734.

Downloads

Published

2026-08-29

Issue

Vol. 8 No. 4 (2026): August

Section

Articles

License

Copyright (c) 2026 Journal of Information Systems and Informatics

Creative Commons License

This work is licensed under a Creative Commons Attribution 4.0 International License.

Authors Declaration

  1. The Authors certify that they have read, understood, and agreed to the Journal of Information Systems and Informatics (JournalISI) submission guidelines, policies, and submission declaration. The submission has been prepared using the provided template.
  2. The Authors certify that all authors have approved the publication of this manuscript and that there is no conflict of interest.
  3. The Authors confirm that the manuscript is their original work, has not received prior publication, is not under consideration for publication elsewhere, and has not been previously published.
  4. The Authors confirm that all authors listed on the title page have contributed significantly to the work, have read the manuscript, attest to the validity and legitimacy of the data and its interpretation, and agree to its submission.
  5. The Authors confirm that the manuscript is not copied from or plagiarized from any other published work.
  6. The Authors declare that the manuscript will not be submitted for publication in any other journal or magazine until a decision is made by the journal editors.
  7. If the manuscript is finally accepted for publication, the Authors confirm that they will either proceed with publication immediately or withdraw the manuscript in accordance with the journal’s withdrawal policies.
  8. The Authors agree that, upon publication of the manuscript in this journal, they transfer copyright or assign exclusive rights to the publisher, including commercial rights

Most read articles by the same author(s)