The prevalence, spatiotemporal distribution, and category incidence of medical emergencies are rapidly changing worldwide. The current pandemic context and emerging trends in public health create the need for self-adapting Emergency Medical Services (EMS). Emergency occurrences and responses are intricately dependent on contextual factors, including weather, epidemic context, urban trafic, large-scale events, and demographics. In this context, monitoring emergency occurrences, medical responses, and their situational context is essential to optimize EMS eficiency and eficacy. In this work, we implement best practices in multidimensional database modelling to consolidate emergency event data with public sources of situational context for context-aware data analysis. The resulting design is able to address challenges pertaining to the massive, incomplete, and spatiotemporal nature of emergency event data and the heterogeneity of context sources and their varying spatiotemporal footprints. We present a study case on real-world medical emergency data from Portugal. The results show the eficient retrieval of data structures conducive to spatiotemporal data mining tasks.
data with publicly available sources of relevant situaMany societies around the world have nationwide ser- tional context – including weather, epidemics, largevices to respond to emergencies, including individual scale events, urban trafic, demographics or zoning inhealth emergencies, large-scale disasters, trafic acci- formation – is essential to better assess causality factors dents, amongst others. Emergency Medical Services underlying the spatiotemporal prevalence of emergen(EMS) generally provide initial triage, the necessary care cies and response ineficiencies. Still, several challenges on-scene, and eficient transportation to health facilities found at the data consolidation levels limit the applicafor the subsequent full care delivery [1]. bility of context-aware emergency data analysis. First,
Despite the pivotal worldwide role of EMS, they are in- there are challenges on the heterogeneity and real-time creasingly pressured to meet higher service levels due to monitoring of situational context data and the varying the rising number of emergencies in major urban centers. geographical-and-temporal footprint of meaningful conThey also need to adapt to the ongoing changes in pub- text occurrences, such as festivities, sports matches, traflic health caused by shifts in demographics and disease ifc jams, or abnormal weather conditions. Second, the prevalence [2]. This has led to various studies to improve inherent complexity and massive size of spatiotemporal EMS by looking at diferent situational contexts [ 3, 4, 5, 6]. emergency event data, characterized by a multiplicity of Furthermore, EMS face additional challenges in the cur- stages (call, triage, activation, vehicle dispatch, and so on), rent pandemic context where ambulance waiting times arbitrarily-high degree of missing values, the abundance near hospitals can be considerably high, and emergency of relevant categories (diagnostics, assistance provided, requests occur at later complication stages [7]. Finally, a and type of dispatched vehicles). Third, the need to efisignificant number of situational factors that can predis- ciently handle data analytics with spatial, temporal and pose emergency prevalence are commonly neglected. emergency-specific drill-down, roll-up, slicing and dicing criteria.
In this work, we apply best practices in multidimensional data modelling to address the above challenges, using real-world EMS data in Portugal1 as a study case.
In particular, we consolidate emergency event data with available sources of situational context for efective and eficient subsequent spatiotemporal data analysis. The result is an integrated data warehouse that supports the
Data2Help, an initiative that aims at developing a set of computational tools to optimize the operations of EMS. eficiently retrieval of relevant multi-source information to assist in various computational tasks. Gathered results from using the data warehouse suggest its relevance in boosting retrieval eficiency, promoting querying eficacy, and deriving data structures conducive to subsequent data mining tasks.
The paper is structured as follows. Section 2 provides background on EMS and introduces the study case along with its major challenges. Section 3 describes the implementation of the multidimensional data modelling solution. Section 4 presents the performance of the data warehouse-based system in terms of eficiency and potential in context-aware data analysis. Section 5 discusses some related work. Finally, concluding remarks and future work are drawn in Section 6.
Emergency medical services in mainland Portugal are coordinated by Instituto Nacional de Emergência Médica (INEM)2. In most cases, medical emergencies are reported via the 112 number, where specialized medical staf classifies the emergency and dispatches the proper vehicles (ambulance, helicopter, life-support vehicle, among others) along with the medical staf. Each vehicle is equipped to deal with diferent situations, from light injuries to life-support. In 2019, INEM answered (dispatched) nearly 1.3 million calls (1.2 million vehicles).
INEM stores the data associated with all medical emergencies in a relational database. The database maintains procedural-based views of medical emergencies, including their spatiotemporal frame, as well as care-based views comprising emergency diagnoses, provided assistance, and outcomes. Despite the ongoing eforts, as not all dispatched vehicles and staf are from INEM, abundant records associated with the emergency response are missing, particularly those pertaining to the timestamps of site arrival, departure, and hospital redirection.
Until now, INEM has not considered the role of situational context in shaping emergency prevalence and response. Moreover, the assignment of medical emergency vehicles in a preventive way is rarely done, except for special events for which, by law, the event’s organization needs to ensure nearby emergency resources (concerts, sports matches, etc.). Figs. 1 and 2 illustrate the efect that festivals and weather factors can bear on the prevalence of specific emergencies. Part of the work reported in this paper is to easily incorporate these situational contexts into medical emergency data analysis. The context-aware predictive modelling of emergency events is essential to support resource allocation and assist in vehicle allocation at large gatherings.
tion 1 and 2, we integrate available sources of emergency and situational context data using a multidimensional schema (Fig. 3) with four major properties of interest.
First, spatial and temporal dimensions with multiple calendric and territorial hierarchies are included to support the specification of spatiotemporal queries. These dimensions capture location and time information and are linked to the diferent stages of an emergency process (e.g., dispatch, arrival, return) to support process-related queries. The location dimension captures the geographical coordinates as well as the district and municipality that encompasses the location, while the time dimension captures the relevant time components such as the year, month, and minute.
Second, an arbitrarily high number of context-specific dimensions and facts are introduced to capture one-tomany relationships between emergencies and situational context sources in accordance with the spatiotemporal footprint of the monitored large-scale events and sensor measurements. In the emergency response database, we implement dimensions capturing weather, festivity, and sports event information.
Third, multiple fact tables are further instantiated to diferentiate between complete and incomplete emergency occurrences, thus supporting the eficiency of stage-specific queries. In this context, we preserve the stance of facts as materializations of the linked dimensions.
Finally, complementary dimensions are further considered to hierarchically describe the typology (diagnostic) and severity of emergencies and abundant information ments can be autonomously inferred from their category on the dispatched vehicles and provided assistance. For and duration. Thus, the dynamic retrieval of context data instance, the unit dimension contains information about and corresponding upload can be done in an automated the responding vehicles, while the emergency dimen- fashion [8]. In this context, emergency event associasion contains information such as the type and sever- tions are precomputed once, alleviating the subsequent ity of the emergency. The introduced spatial, temporal, computational complexity of context-aware analytics. emergency categorization and contextual dimensions offer hierarchical content to support the incorporation of drill-down, roll-up, slicing and dicing operations within 4. Preliminary Results queries. These operations are essential to retrieve data structures, such as multivariate time series, conducive to This section gathers preliminary results from the prosubsequent descriptive or predictive tasks. posed data warehousing approach for spatiotemporal
Despite the complexity of context-enriched emergency emergency data analysis, discussing eficiency gains (secdata, the proposed schema is intrinsically simple. Com- tion 4.1) and aspects of usefulness and interpretability plementarily to expressive OLAP querying, a service (section 4.2). Experiments were run using SQL Server on layer is also provided to support parametric queries for Intel Xeon CPU E3-1230 v6 @ 3.50GHz with 16GB RAM. advanced context-enriched spatiotemporal analytics.
Online context data sourcing. Ministries, munic- 4.1. Eficiency ipalities and weather institutes generally have well- Considering the Portuguese study case introduced in secestablished eforts to gather and publicly provide relevant tion 2, relevant data retrieval operations were applied on context data. Hence, periodic routines can be placed to both the INEM relational database and the proposed muldynamically acquire situational context from structured tidimensional database. The selected operations cover or/and semi-structured data sources via portals and APIs spatiotemporal queries typically involved in data analyprovided by national-wide initiatives, city Councils, in- sis tasks. Each query was executed 10 times to account stitutes and other entities. Illustrating, the Lisbon city for variability between runs. A two-tailed paired t-test Council stores semi-structured representations of large- was performed to assess the statistical significance of the scale public events and urban trafic at Lisboa Aberta por- observed diferences. Table 1 shows the results. The full tal3. The spatiotemporal footprint of events and measure- list of queries that we used are listed in Appendix A.
Table 1 various types of data retrieval operations associated with Comparison of execution times (in seconds) of queries exe- spatiotemporal data analysis. Fig. 4 provides visualizacuted over the original and the multidimensional databases. tions of the outputs of context-enriched data queries. Fig. 4a-b relies on individual emergencies’ retrieval Query A B C D E F G H and grouping for a given spatial criteria. These simple Original 0.28 58.90 0.47 71.36 73.77 59.67 72.61 73.01 queries can be structured into four parts: selecting the Multidim. 0.20 0.68 0.24 0.24 1.11 0.17 0.36 0.10 desirable fact table, joining the dimensions with relevant Execution times on the multidimensional database con- information to query, specifying the conditions to filter sistently outperformed those on the relational database results, and specifying the grouping conditions, if any. with statistical significance ( < 0.01). A small improve- This query structure is easy to construct and can easment could be observed on Query A, involving the re- ily be modified to accommodate various combinations trieval of individual emergencies parameterized by date, of parameters. The grouping of related variables in didue to a proper organization of temporal criteria. Ef- mensions further makes retrieval of desired information ifciency improvements are observed for queries C, D, more intuitive. Retrieval of information can easily be and E, which involved aggregating the number of emer- done by joining the appropriate dimension rather than gencies according to specific criteria of interest, such as going through a list of all possible columns. municipality and emergency type. Queries D and E in- Fig. 4c shows the retrieval of time series data by groupvolved large numbers of groups, magnifying eficiency ing and aggregating desired emergencies into fixed time diferences. Queries B, G, and H involved the aggre- intervals. To this end, the individual timestamps in gation of emergency response times. For the original the time dimension are mapped into columns containdatabase, subtraction operations had to be performed ing abundant calendric information. This allows usable between timestamps for these queries. This was not queries in place of computationally expensive SQL funcnecessary on the multidimensional database as the difer- tions (such as datepart) to extract individual date compoences are pre-computed. Overall, the data warehousing nents, supporting the specification of the desirable time process on the context-enriched database improved data series granularity. The precomputation of values of inretrieval eficiency, supporting demanding analytics and terest, such as durations along emergency stages, further visualization requests. simplifies the data retrieval process. The integration of contextual information from ex4.2. Context-aware data analysis support ternal sources for context-aware emergency analysis is further illustrated in Fig. 4d. In this example, aggresWe show the potential of the data warehousing system sion events occurring in the spatiotemporal footprint of in context-aware data analysis support by performing sports matches are retrieved. This can be easily accom(a) all conditions
(b) intoxication, body pain, trauma, altered consciousness states
Numerous multidimensional modelling principles have been proposed for heterogeneous data source consolidation with spatiotemporal content [10]. However, in contrast to our work, the association of events based on their spatiotemporal footprint and the handling of missing data is pushed towards the analytical processing stage, hampering query expressivity and eficiency. Classic multidimensional databases have been extended to cater for specific scenarios and use cases. For example, Papadias et al. [11] introduced a framework for indexing spatiotemporal data for the eficient execution of ad-hoc group-bys using a combined spatial and temporal dimension. In the presence of data sources with undefined or partially deFigure 6: Interesting trend discovery allows us to discover ifned structures, approaches have been proposed to autotrends that deviate in a particular context. I this case, the num- matically discover dimensions. In the work of Mansmann ber of emergency occurrences in the district of Faro behaves et al. [12], a data enrichment layer was added to detect diferently (decreases) compared to those of other districts. structural elements in user-generated Twitter data with data mining techniques. Similarly, Gutiérrez-Batista et plished by identifying the desirable context dimensions al. [13] used hierarchical clustering techniques to extract and querying the fact entries holding a foreign key to the multidimensional structure from textual data. Alterthe target sports match. The addition of context-specific natively, spatiotemporal ontologies can be used to this fact tables not only ensures integrity by preventing for- end [14]. Thalhamer et al. [15] introduced the concept eign keys to be null; it also improves interpretability by of active data warehouses, able to autonomously extract explicitly stating which dimensions can be accessed and rules on behalf of the data analyst. The use of multidiguaranteeing that the traced occurrences fall within the mensional database structures has been documented in time and radius of a context event. various domains such as management [16], education
Finally, the multidimensional data warehouse supports [17], agriculture [18] and epidemiology [19]. In urban the development of more complex data analysis processes. data, the handling of large amounts of spatiotemporal In the Portuguese study case, we present two examples: i) content is largely notable [20], and comprises identical detection of anomalies in which unusual levels of occur- challenges that can be addressed by a multidimensional rences of certain types of emergencies are extracted from approach, as demonstrated in our work. the data (Figure 5) [9], and ii) discovery of interesting trends, where diverging patterns on the rise or drop in the number of cases within localities or emergency types 6. Concluding Remarks are automatically extracted (Figure 6). These applications show the relevance of the data warehousing system in eficiently supporting convenient data analysis facilities.
sional database modelling to consolidate emergency events and public sources of situational context to support and boost context-aware emergency data analysis. emergency medical service system: a population-based, The solution addressed challenges pertaining to the mas- descriptive study in osaka, japan, Acute medicine & sive, incomplete, and spatiotemporal nature of emer- surgery 7 (2020) e534. gency event data, as well as challenges related to the [8] S. Cerqueira, R. Henriques, E. Arsénio, Integrative analysourcing, heterogeneity and varying spatiotemporal foot- sis of trafic and situational context data to support urban print of context sources. We further introduced a study mobility planning, in: European Transport Conference 2020, 2020. case where the data warehousing system yielded sta- [9] L. Silva, H. Galhardas, V. Manquinho, R. Henriques, Unitistically significant performance improvements. This ano: robust and eficient anomaly consensus in time work opens up the possibility to support and scale up series sensitive to cross-correlated anomaly profiles, in: spatiotemporal data analysis and data mining tasks with Proceedings of the 2021 SIAM International Conference heavy context-enriched data retrieval demands, whether on Data Mining (SDM), SIAM, 2021, pp. 324–332. of descriptive or predictive nature. Amongst diverse ends, [10] O. Romero, A. Abelló, A survey of multidimensional this possibility can be used to assist real-time monitoring modeling methodologies, International Journal of Data and decision tasks in favour of self-adapting EMS able to Warehousing and Mining (IJDWM) 5 (2009) 1–23. timely detect and respond to emerging changes. The con- [11] D. Papadias, Y. Tao, P. Kanis, J. Zhang, Indexing spatiotributions of this work extend beyond medical emergency temporal data warehouses, in: Proceedings 18th Internadomains, as the principles apply to similar spatiotempo- 1ti6o6n–a1l7C5o.nference on Data Engineering, IEEE, 2002, pp. ral domains with dependencies on situational factors like [12] S. Mansmann, N. U. Rehman, A. Weiler, M. H. Scholl, trafic and utility supply data. Discovering olap dimensions in semi-structured data, Information Systems 44 (2014) 120–133. [13] K. Gutiérrez-Batista, J. R. Campaña, M.-A. Vila, M. J.
Acknowledgements Martin-Bautista, Building a contextual dimension for olap using textual data from social networks, Expert This work is supported by Portuguese national funds through Systems with Applications 93 (2018) 118–133. FCT, Fundação para a Ciência e Tecnologia (FCT), un- [14] A. Salguero, F. Araque, C. Delgado, Spatio-temporal onder projects UIDB/50021/2020, DSAIPA/AI/0044/2018 and tology based model for data warehousing, in: WSEAS DSAIPA/AI/0111/2018. International Conference. Proceedings. Mathematics and Computers in Science and Engineering, 7, World ScienReferences tific and Engineering Academy and Society, 2008. [15] T. Thalhammer, M. Schrefl, M. Mohania, Active data [1] B. S. Roudsari, A. B. Nathens, C. Arreola-Risa, warehouses: complementing olap with analysis rules, P. Cameron, I. Civil, G. Grigoriou, R. L. Gruen, T. D. Data & Knowledge Engineering 39 (2001) 241–269. Koepsell, F. E. Lecky, R. L. Lefering, et al., Emergency [16] K.-W. Chau, Y. Cao, M. Anson, J. Zhang, Application medical service (ems) systems in developed and develop- of data warehouse and decision support system in coning countries, Injury 38 (2007) 1001–1013. struction management, Automation in construction 12 [2] R. Aringhieri, M. E. Bruni, S. Khodaparasti, J. T. van Es- (2003) 213–224.
sen, Emergency medical services and beyond: Address- [17] E. M. A. Butt, S. Quadri, E. M. Zaman, Star schema iming new challenges through a wide literature review, plementation for automation of examination records, in: Computers & Operations Research 78 (2017) 349–368. Proceedings of the International Conference on Frontiers [3] L. Aboueljinane, E. Sahin, Z. Jemai, J. Marty, A simula- in Education: Computer Science and Computer Engition study to improve the performance of an emergency neering (FECS), The Steering Committee of The World medical service: application to the french val-de-marne Congress in Computer Science, 2012, p. 1. department, Simulation modelling practice and theory [18] A. K. Gupta, B. D. Mazumdar, Multidimensional schema 47 (2014) 46–59. for agricultural data warehouse, International Journal of [4] H. J. Kam, J. O. Sung, R. W. Park, Prediction of daily Research in Engineering and Technology 2 (2013) 245– patient numbers for a regional emergency medical cen- 253. ter using time series analysis, Healthcare informatics [19] B. Amin, H. Djamila, Towards a spatiotemporal data research 16 (2010) 158. warehouse for epidemiological surveillance, Instrumen[5] M. A. Mahmood, J. E. Thornes, F. D. Pope, P. A. Fisher, tation Mesure Metrologie 18 (2019) 1–7.
S. Vardoulakis, Impact of air temperature on london am- [20] E. Zim’nyi, Spatio-temporal data warehouses and mobilbulance call-out incidents and response times, Climate 5 ity data: Current status and research issues, in: 2012 19th (2017) 61. International Symposium on Temporal Representation [6] T. Janchar, C. Samaddar, D. Milzman, The mosh pit and Reasoning, IEEE, 2012, pp. 6–9. experience: Emergency medical care for concert injuries, The American journal of emergency medicine 18 (2000) 62–63. [7] Y. Katayama, K. Kiyohara, T. Kitamura, S. Hayashida,
T. Shimazu, Influence of the covid-19 pandemic on an
Original Database A: Get emergency dates, types and priority level for a given day. SELECT time, type, priority FROM emergencies WHERE CAST(time AS date) = <some date>
SELECT arrivaltime-time, priority FROM emergencies WHERE arrivaltime IS NOT NULL and time IS NOT NULL
SELECT priority, COUNT(*) AS count FROM emergencies GROUP BY priority ORDER BY priority
SELECT type, COUNT(*) AS count FROM emergencies GROUP BY type ORDER BY type
SELECT municipality, COUNT(*) AS count FROM emergencies GROUP BY municipality ORDER BY municipality SELECT time, type, priority FROM all.facttable AS a INNER JOIN emergency.dimension AS b ON a.emergency = b.id WHERE time BETWEEN <some dates> SELECT arrivalseconds, priority FROM completeresponse.facttable AS a INNER JOIN emergency.dimension AS b ON a.emergency = b.id SELECT priority, COUNT(*) FROM all.facttable AS a INNER JOIN emergency.dimension AS b ON a.emergency = b.id GROUP BY priority ORDER BY priority SELECT type, COUNT(*) FROM all.facttable AS a INNER JOIN emergency.dimension AS b ON a.emergency = b.id GROUP BY type ORDER BY type SELECT municipality, COUNT(*) FROM all.facttable AS a INNER JOIN emergency.dimension AS b ON a.emergency = b.id GROUP BY municipality ORDER BY municipality
SELECT year, month, COUNT(*) SELECT DATEPART(year, CAST(time AS date)), FROM all.facttable AS a DATEPART(month, CAST(time AS date)), COUNT(*) INNER JOIN time.dimension AS b FROM emergencies ON a.time = b.id WHERE type = ’DROWNING’ INNER JOIN emergency.dimension AS c GROUP BY DATEPART(year, CAST(time AS date)) ON a.emergency = b.id DATEPART(month, CAST(time AS date)) WHERE type = ’DROWNING’ ORDER BY DATEPART(year, CAST(time AS date)) GROUP BY year, month DATEPART(month, CAST(time AS date)) ORDER BY year, month G: For occurrences with units, get the average time to activation of the response unit.
SELECT AVG(dispatchtime-time) FROM emergencies SELECT AVG(dispatchseconds) WHERE dispatchtime IS NOT NULL and time IS NOT NULL FROM withunits.facttable AND dispatchtime-time > 0 WHERE dispatchseconds > 0 H: For occurrences that are complete, get the maximum time to destination.
SELECT MAX(destinationtime-time) FROM emergencies WHERE destinationtime IS NOT NULL and time IS NOT NULL AND destinationtime-time > 0 SELECT MAX(desintationseconds) FROM completeresponse.facttable WHERE desintationseconds > 0
INNER JOIN location.dimension AS b ON a.locationid = b.id WHERE district=’Lisboa’ AND time BETWEEN <some time> B. Get the number of diving / drowning emergencies per district
SELECT district, COUNT(id) AS count FROM all.facttable AS a INNER JOIN location.dimension AS b ON a.locationid = b.id INNER JOIN emergency.dimension AS c ON a.emergencyid = c.id WHERE type = ’DROWNING’ GROUP BY district
SELECT year, month, COUNT(id) AS count FROM all.facttable AS a INNER JOIN ftbl.dimension AS b ON a.ftbl = b.id INNER JOIN emergency.dimension AS c ON a.emergencyid = c.id WHERE type = ’AGRESSION’ INNER JOIN time.dimension AS b ON a.time = b.id INNER JOIN emergency.dimension AS c ON a.emergencyid = c.id WHERE type = ’AGRESSION’
GROUP BY team1, team2
Add desired dimensions.
Note: Names of tables and columns have been altered for security purposes.