Model Slowly Changing Dimension pada OLAP untuk Executive Information System Data Pasien Rumah Sakit

Abstract

Rumah sakit tradisional menggunakan pencatatan data kertas atau sistem non-realtime, menyebabkan keterlambatan waktu pengiriman hasil, kesalahan sistem, dan risiko fatal. Rumah sakit modern menghadapi tantangan kompleks dalam pengelolaan data operasional dan klinis. Beberapa permasalahan utama meliputi keterbatasan database di banyak aplikasi, tingginya volume transaksi mengakibatkan tumpukan waiting tasks, serta beban database input/output (I/O) tinggi memperlambat pemrosesan data. Kementerian Kesehatan Republik Indonesia (Kemenkes RI) menetapkan indikator ideal pelayanan rumah sakit: Bed Occupancy Ratio (60-85%), Average Length of Stay (6-9 hari), Turn Over Interval (1-3 hari), Bed Turn Over (40-50 kali), Gross Death Rate (<45/1000), Net Death Rate (<25/1000) untuk mengukur pelayanan di RSUD Prof. Dr. Margono Soekarjo. Interoperabilitas data berbeda dan penyajian manual menghambat integrasi, analisis data, dan pengambilan keputusan. Penelitian ini membangun data warehouse (DW) dan mengembangkan Executive Information System (EIS) untuk data pasien rumah sakit menggunakan Microsoft Business Intelligence (MSBI). Arsitektur MSBI mengintegrasikan komponen SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), dan Power BI. Proses Extract, Transform, Load (ETL) dioptimalkan menggunakan SSIS untuk mengintegrasikan data dari sistem Online Transaction Processing (OLTP) ke Online Analytical Processing (OLAP) secara terstruktur dan efisien. SSAS digunakan untuk mendukung analisis multidimensi berbasis OLAP dengan menerapkan Model Slowly Changing Dimension (SCD) Type 2 untuk mengelola perubahan data historis pasien. Model diintegrasikan dengan Power BI untuk menyajikan visualisasi data dalam pengembangan EIS. Hasil penelitian ini berupa galaxy schema (dua tabel fakta, tujuh belas dimensi) yang mendukung analisis lintas rawat jalan rawat inap. Implementasi SCD Type 2 yang tervalidasi menjaga riwayat data pasien secara konsisten pada kedua tabel fakta. Perhitungan KPI Kemenkes RI (BOR, LOS, TOI, BTO, GDR, NDR) dengan akurasi 100% terhadap referensi SQL pada 52.321 episode rawat inap 2025, serta speedup ratio OLAP rata-rata 30,27× dibanding query SQL relasional dengan seluruh waktu respons di bawah 100 milidetik, membuktikan SCD Type 2 tidak mengorbankan performa dashboard EIS. Analisis KPI 2025 rumah sakit rujukan Tipe A menghasilkan profil dengan kapasitas BOR 89,29%, LOS 5.41 hari, TOI 0,65 hari, BTO 60,21 kali/tahun, GDR 79.15‰, NDR 62.82‰.

Traditional hospitals still rely on paper-based records or non-real-time systems, resulting in delayed result delivery, system errors, and potentially fatal risks. Modern hospitals face complex challenges in managing operational and clinical data. Key issues include limited database capacity across many applications, high transaction volumes leading to a buildup of waiting tasks, and heavy database input/output (I/O) loads that slow down data processing. The Ministry of Health of the Republic of Indonesia (Kemenkes RI) has established ideal hospital service indicators Bed Occupancy Ratio (60–85%), Average Length of Stay (6–9 days), Turn Over Interval (1–3 days), Bed Turn Over (40–50 times), Gross Death Rate (<45/1,000), and Net Death Rate (<25/1,000) to measure service quality at RSUD Prof. Dr. Margono Soekarjo. Differing data interoperability and manual reporting hinder data integration, analysis, and decision-making. This study develops a data warehouse (DW) and an Executive Information System (EIS) for hospital patient data using Microsoft Business Intelligence (MSBI). The MSBI architecture integrates SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), and Power BI. The Extract, Transform, Load (ETL) process was optimized using SSIS to integrate data from Online Transaction Processing (OLTP) systems into an Online Analytical Processing (OLAP) system in a structured and efficient manner. SSAS was employed to support OLAP-based multidimensional analysis by implementing a Slowly Changing Dimension (SCD) Type 2 model to manage historical changes in patient data. The model was integrated with Power BI to present data visualizations for EIS development. The results of this study consist of a Galaxy Schema (two fact tables and seventeen dimensions) that supports cross-analysis of outpatient and inpatient services. The validated implementation of SCD Type 2 consistently preserves patient data history across both fact tables. The calculation of Kemenkes RI KPIs (BOR, LOS, TOI, BTO, GDR, NDR) achieved 100% accuracy against the SQL reference across 52,321 inpatient episodes in 2025, while the OLAP queries achieved an average speedup ratio of 30.27× compared with relational SQL queries, with all response times under 100 milliseconds demonstrating that SCD Type 2 does not compromise EIS dashboard performance. The 2025 KPI analysis of this Type A referral hospital produced a profile with a BOR of 89.29%, an LOS of 5.41 days, a TOI of 0.65 days, a BTO of 60.21 times/year, a GDR of 79.15‰, and an NDR of 62.82‰.