SELECT a.admission_type,
i.first_careunit
FROM admissions a
JOIN icustays i
ON a.hadm_id = i.hadm_id
WHERE a.admission_type IN ("EMERGENCY", "URGENT", "ELECTIVE")Analysis Report Two - Data, Data Everywhere
Executive Summary
Most healthcare providers today produce enormous volumes of data from various sources, including patient admissions, intensive care unit records, patient demographics, and pharmacy records.(Ristevski and Chen 2018) While the data collected may be informational or logistically useful, this usefulness diminishes when data continues to exist in isolation from each other across departments within the organization. This report is intended to describe how Big Data can be of assistance within the healthcare industry and illustrates how the combination of data sets from different areas of the hospital will lead to much more comprehensive information for the administrators of healthcare facilities. Using the MIMIC-III database as a source for the analyses, we will utilize both two-table joins and three-table joins in order to reach our conclusions. Ultimately, we find that the integration of data from multiple departments within the healthcare organization will provide better staffing decisions, more accurate resource planning, more efficient medication monitoring, and improved operational decision-making.
Introduction
Today, we often hear healthcare organizations described as being “data-rich” but “information-poor.” Hospitals collect tons of information via various systems: electronic health records, admission systems, pharmacy systems, laboratory systems, billing platforms, and intensive care documentation, for example. But just collecting information alone does not mean that it will improve clinical care or decision-making. The real value of using big data comes from having the ability to connect multiple pieces of data together from a variety of locations and use them to make decisions based on knowledge created through the process of analyzing that data. This week’s readings emphasize that for healthcare data to achieve their full potential, the data must be organized, integrated, and interpreted correctly. When data are collected in an isolated manner within a particular area of a healthcare organization, it can limit the ability of the administrators to obtain an accurate and complete picture of the entire organization. For instance, although an admission report will tell an administrator how many patients were admitted into the hospital, it will not tell him/her how many resources were used by those patients in the ICU. However, by joining tables from various departments within the hospital, healthcare leaders can have a much clearer representation of how patients flow throughout the organization, utilize resources, and operate clinically. By helping an organization identify patterns of data that may not otherwise be visible when looking at them in isolation, big data can help organizations make better strategic decisions about operations. However, at the same time, big data also introduces challenges around privacy of data, security of data, quality of data, and overwhelming amounts of data. Therefore, healthcare leaders must understand the importance of balancing the benefits of integration technology with the responsibility of protecting confidential patient information and maintaining the reliability of their data systems.
The Healthcare Context
Technological advancements have created many connections between different areas of healthcare organizations. Electronic health records, pharmacy databases, admission systems, and intensive care documentation systems all store critical patient data. Through the integration of these elements within an organization, executives can gain a better understanding of how patients travel through the organization and how resources are used. The benefits of integrating these systems include: first, improving operational planning by allowing leaders to predict future demand for services such as ICU beds, nursing staff, and medication support. Second, supporting quality improvement through the identification of patterns in care delivery. Third, enabling healthcare organizations to use data-derived “evidence” to make decisions rather than rely strictly on individual reports or assumptions. Integrated healthcare data presents risks and challenges as well. Large data systems can be complex to manage due to incomplete and inconsistent information; the use of multiple platforms; and a shortage of healthcare personnel with data governance expertise. (Kruse et al. 2016) Data governance becomes more critical when working with health care data, as inaccurate or poorly governed data can produce bad decisions and inaccurate results. Therefore, healthcare providers need to invest in adequate governance, appropriate training, and competent information systems. An example of connecting data from different hospital sites can be seen with the MIMIC-III database. The combination of admissions data with ICU data can assist administrators in gaining a better understanding of critical care demand. The combination of patient data, admission data, and prescription data will provide healthcare leaders with an examination of medication utilization by patient cohort. These two examples provide a demonstration of how big data can create valuable insights through the careful combination of data from separate sites in a health care system.(Adler-Milstein and Jha 2017)
Data Visualizations
This visualization uses a two-table join between the admissions and icustays tables. The purpose of this analysis is to examine ICU care units by admission type. This helps show how patients from different admission categories use ICU resources.
ggplot(data = myquery1,
aes(x = first_careunit,
fill = admission_type)) +
geom_bar(position = "dodge") +
theme_minimal()+
labs(
title = "ICU Care Units by Admission Type",
subtitle = "Two-table join using ADMISSIONS and ICUSTAYS",
x = "ICU Care Unit",
y = "Number of ICU Stays",
fill = "Admission Type"
)
This graphic shows how linking the admission data and the ICU data will help us to generate operational insights. The only thing the hospital used to know was how many patients they would be admitting, but using the admissions data will give the hospital a better understanding of what types of admissions are associated with the different types of ICU care. Why is this important? Because ICU beds, nurses, and equipment are all limited resources for healthcare administrators and need to be managed very well. From my graphic, I could tell that the emergency and MICU were the most representative areas. This means that hospital leaders can use integrated data to know what the demand is for the ICU and how to effectively staff and allocate other resources.
This visualization uses a three-table join between the patients, admissions, and prescriptions tables. The purpose of this analysis is to examine medication orders by admission type and gender. This shows how combining demographic, admission, and pharmacy information can provide a broader view of hospital operations.
SELECT p.gender,
a.admission_type,
COUNT(*) AS medication_orders
FROM patients p
JOIN admissions a
ON p.subject_id = a.subject_id
JOIN prescriptions pr
ON a.hadm_id = pr.hadm_id
WHERE a.admission_type IN ("EMERGENCY", "URGENT", "ELECTIVE")
GROUP BY p.gender, a.admission_typeggplot(data = myquery2,
aes(x = admission_type,
y = medication_orders,
fill = gender)) +
geom_col(position = "dodge") +
theme_minimal() +
labs(
title = "Medication Orders by Admission Type and Gender",
subtitle = "Three-table join using PATIENTS, ADMISSIONS, and PRESCRIPTIONS",
x = "Admission Type",
y = "Number of Medication Orders",
fill = "Gender"
)
Displaying the merged data from numerous hospitals through this visualization will assist in understanding how prescribed medications are being utilized. The demographics of a patient’s profile can be found in the patients table. The type of admission associated with a visit to the hospital can be found in the admissions table. The pharmacy can be found in the prescriptions table. By merging these three elements together, we can create a clearer view of how medications are being prescribed to the various demographics of patients. My visualization highlights the number of medication orders for emergency admissions within the three datasets. The insight found in this finding may be useful for healthcare organizations due to medications playing a critical role in pharmacy workload, patient safety, staffing levels, and overall healthcare costs. Additionally, by integrating the pharmacy, admissions, and demographics, hospital administrators can gain a better understanding of how patients receive treatment within the health system and, therefore, may be able to identify areas that will need additional monitoring.
Warning
Remember, the practice covers certain specific concepts. Your grade is based on how well you show mastery of these concepts.
Your queries can be loosly based on Practice queries, but they must extend or adapt the practice in interesting ways.
Visualization One - Two Table Join
SELECT a.admission_type,
i.first_careunit
FROM admissions a
JOIN icustays i
ON a.hadm_id = i.hadm_id
WHERE a.admission_type IN ("EMERGENCY", "URGENT", "ELECTIVE")ggplot(data = myquery1,
aes(x = first_careunit,
fill = admission_type)) +
geom_bar(position = "dodge") +
theme_minimal() +
labs(
title = "ICU Care Units by Admission Type",
subtitle = "Two-table join using ADMISSIONS and ICUSTAYS",
x = "ICU Care Unit",
y = "Number of ICU Stays",
fill = "Admission Type"
)
Visualization Two - Three Table Join
Visualization Two - Three Table Join
SELECT p.gender,
a.admission_type,
COUNT(*) AS medication_orders
FROM patients p
JOIN admissions a
ON p.subject_id = a.subject_id
JOIN prescriptions pr
ON a.hadm_id = pr.hadm_id
WHERE a.admission_type IN ("EMERGENCY", "URGENT", "ELECTIVE")
GROUP BY p.gender, a.admission_typeggplot(data = myquery2,
aes(x = admission_type,
y = medication_orders,
fill = gender)) +
geom_col(position = "dodge") +
theme_minimal() +
labs(
title = "Medication Orders by Admission Type and Gender",
subtitle = "Three-table join using PATIENTS, ADMISSIONS, and PRESCRIPTIONS",
x = "Admission Type",
y = "Number of Medication Orders",
fill = "Gender"
)
```
##Recommendations for Industry Healthcare administrators should employ the use of integrated data systems in their operational planning. By examining data from both ICU and critical care facilities, it can be noted that the value of admission data is enhanced when it is combined with critical care information. Therefore, to make the appropriate staffing decisions regarding your ICU, you need to monitor regularly not only the types of admissions you are receiving, but also how much of the ICU is being utilized. This facilitates your ability to manage your beds and adequately plan for your surge capacity. In addition, healthcare organizations will benefit from the use of integrated pharmacy data regarding medication utilization. Combining three different tables to find out how to use the three areas together can assist an administrator in making more informed decisions regarding pharmacy staffing, medication safety, and resource planning. For example, by combining data regarding patient demographics, admission data, and information about prescriptions, administrators can determine patterns in the use of medications. Another suggestion for healthcare organizations would be to improve interoperability between their various departments, so admissions, intensive care units (ICU), pharmacy and demographic information can be seen together. This is necessary if healthcare leaders are to get an overview of their organizations. Improved interoperability will also aid organizations towards a more complete and thorough use of data in their decision-making. Lastly, strong data governance is vital for leaders of all healthcare organizations. By using Big Data, healthcare organizations can improve their quality of service; however, in order for their use to be effective, organizations need to have accurate and secure data that is interpreted correctly. Thus, organizations need to continue to invest in maintaining privacy and cyber security so that their Big Data can be used successfully. (Adler-Milstein and Jha 2017)
##Conclusion This project has indicated that the use of big data in health care will be most effective when it connects and utilizes the results of multiple areas within a hospital. MIMIC-III data has shown how combining the admission data and ICU data can be used to help plan for resource needs, while also demonstrating how the combination of patient data, admission data, and prescription data can assist in understanding the use of medications. These examples indicate that health care organizations need to not only gather large amounts of data but also develop the capability to utilize this data in combination and meaningful ways. When combined responsibly, the integrated data can support health care leaders by improving operational efficiencies, supporting improved quality of care, and making stronger overall strategic plans.
References
Adler-Milstein, Julia, and Ashish K Jha. 2017. “Electronic Health Record Adoption and Interoperability Among US Hospitals.” Health Affairs 36 (8): 1416–23.
Kruse, Clemens Scott, Christine Kristof, Bradley Jones, Erica Mitchell, and Amanda Martinez. 2016. “The Benefits and Drawbacks of Electronic Health Record Systems.” Risk Management and Healthcare Policy 9: 47–55.
Ristevski, Blagoj, and Ming Da Chen. 2018. “Big Data Analytics in Medicine and Healthcare.” Journal of Integrative Bioinformatics 15 (3).