Star Schema Design Of Hospitals Assignment Sample

Star Schema Design Of Hospitals

Introduction-Star Schema Design Of Hospitals

Want the Best Assignment Help in the UK? Look to Native Assignment Help for unparalleled expertise and support. Our dedicated team of professionals goes above and beyond to ensure you receive top-quality assignments that exceed your expectations.

Hospitals are data driven organizations. Hospitals need important documents at the time of problems. Documents can clarify important situations when the reputation of the hospital is at stake. It is essential for hospitals to have proper documentation in place at the time of an important surgery. Routine operations will go smoothly if proper documentation is done on a daily basis. Hospitals will not suffer from major equipment shortages at the time of operation if they had proper documentation in place. This assessment is developed to identify four different dimension of Kimball’s model and develop a star schema model that can be implementing into hospital operational environment and reduce deaths and other routine operational problems.

1) Explanation of the assumptions

Star schema model has been introduced to produce a data warehouse for the hospital. Hospital has faced problems of bad publicity due to the deaths of numerous patients. Hospital has also faced problems with their routine operations. Star schema model has been developed to address these challenges faced by the hospital. Different sections of data have been collected to make the star schema model (Zhu et al., 2017). Following are some of the areas that have been selected for data collection

• Administrative data: Hospital administrative data is the fundamental aspect of star schema model. This model has been developed to give a brief overview about different sections of the hospital. People from different sections of the hospital have been interviewed for information. Emergency section has been prioritized for making administrative data. General ward employees were interviewed for making the administrative data. Maternity ward authorities were interviewed with effective measures for data collection procedure.
• Patient Registers: Patient registries are an area that was under special consideration while making the star schema model (Gorelik, 2019). It is essential for the hospital authority to take special precaution while making entries for the admitted patients. Interviews with different employees were taken into consideration while making the star schema model
• Insurance and claims related Information: It is essential for every hospital to have proper documentation in place to help patients with their healthcare cost coverage. Collection of patient data is essential for this purpose. Employees responsible for healthcare package documentation were interviewed for gaining insights about the requirements of the hospital.
• Different electronic data of hospitals: Hospitals use numerous electronic data to have a better understanding of patient well being. Different doctors and departmental heads have been interviewed to have a better understanding about the circulation of electronic data associated with different equipment. Feedback from various doctors have been considered while making this star schema model. This mode of data colletion was effective as the loopholes from the internal stakeholders would be integrated in the proposed model.

2) Rationale of the design

Star Schema model has been developed based on Kimball's dimensional model. Kimball’s dimensional model has four steps for data warehousing. Following are the four steps of Kimball’s model (Kimball, 1996).

Figure 1: Kimball’s Four Star Schema Steps

(Source: Kimball, 1996)

• Selecting process of business: It is essential for the organization to have a clear idea about the definite process that would be covered by the star schema model. If the hospital needs emergency related data, star schema model will provide results for emergency data.
• Grain declaration: Grain means the level of data that will be stored in the fact table of Star schema. Data stored within the fact table cannot be divided into further groups.
• Identifying different dimensions: Identification of different dimensions will include the statistics in table format. Data will be stored under various dimensions to file a clear idea about the associated fact table [Refer to Fact table and Dimension tables for each of the fact table].
• Identification of numerical facts: This section will indicate the numbers associated with each dimension. This section provides the answer searched by the user. Data collected from gain should be similar to the grain. Numerous fact tables are used to define a different set of data.

3) Star Schema diagram

Fact table and dimension tables are mentioned in detail as follows:

Fact Table

Register_document_key
Revenue_document_key
Daily_task_document_key
Administrative_key
Patient_Directory_key
Doctor_dimension_key
Diagnosis_key
Health_Insurance_key

Dimension table for Register_document_key

Partient_name_key

Issue_details_key

Registration_id_key

Guardian_contact_key

Registration_documen_key indicates presence of details of patient who tries to take admission in hospital. Presence of name, issue details and registration Id will be beneficial to provide smooth service in hospital. Further, presence of guardian contact details will help to contact in case of emergency without any barriers.

Dimension table for Revenue_document_key

Revenue_cost_document

Target_revenue_document

Target_patient_document

Revenue document key is an added measure through which total revenue can be calculated. Keys such as cost, target ad patient admission details will be helpful to smoothly maintain financial details of hospital.

Dimension table for Daily_task_document_key

Daily_task_details

Daily_task_id

Responsible_person_for_daily_task

Daily task document will be effective to track every day’s tasks to maintain smooth service in hospital. Documentation of task id and responsibility will be beneficial to eliminate bias and dilemma during service.

Dimension table for Administrative_key

Emergency_document_administration_key
Emergency_policies_document
Emergency_equipment_requirements_document_key
Maternity_ward_administrative_documentation_key
Maternity_ward_policies_document_name
General_ward_administrative_documentation_key
General_ward_policies_document_name

Administrative Department

First Administrative key has been included within the fact table. Various administrative documents will be located under the Administrative key. Information regarding health care policies will be under the Administrative key. Various shift timings will be under the various dimension tables of the administrative department. Documents regarding different policies will be under the various dimension tables of administrative fact table. This information will avoid any confusion regarding hospital policies. Administrative people will be confident while describing hospital policies. Documentation of policies in maternity ward and general words can help in maintaining effective functionality in hospitals. Further, management of documents regarding administrative details can help in elimination of documentation issue and will help to improve service ability of any hospital.

Dimension table for Patient _Directory_Key

Patient_gender_documentation_key
Male_document_id
Female_ document_id
Transgender_ document_id
Patient_medical_history_ document_key
Previous_symptoms_document
Special_attributes_document
Patient_admission_time_document

Next Patient Directory key has been considered within the fact table. Different information regarding the patient has been considered to make the dimension table of star schema model. Patient gender information will be under the dimension key. Patients' medical history with the hospital will be under dimension history of the patient's medical history key. Patient administration timings will be under the dimension key known as patient administration time key. Identification of gender of patients based on document history will be beneficial to maintain smooth services regarding registration. Further, documentation of previous symptoms of a patient and other medical history durog further treatment can be helpful to easily understand the details and treat as per requirements.

Doctor dimension key

Doctor_information_key_document
Doctor_Names_ document
Doctor_timings _document
Doctor_shifts_document
Doctor_contact_document
Doctor_information_according_to_ward_ document_Key

Above two tables is the dimension key associated with the hospital doctor information. Doctor names will be under the Doctor Information _key. Under this section doctor names will be given. Timings of different doctors will be provided in this section as well. Doctor shifts will be described under this dimension table. Doctor information according to different wards will be provided in the Doctor information according to ward Key. Doctor timings according to shifts in different wards will be provided in this part of the dimension table. Doctors assigned for different shifts will be under this dimension table.Presence of details of docturs regarding shifts and ward information will be helpful to eliminate unnecessary doubts during service. Further, it will help to conduct a proper operations especially during any emergency.

Diagnosis_Key Dimension Table

Diagnosis_history_document_Key
Patient_Requirements_ document_key
Diagnosis_timings_number_document
Diagnosis_result_ document
Date_number
Time_number

Diagnosis key dimension table will provide the necessary requirements of patient details. Different patients will require various tests according to their needs. Diagnosis key will help hospitals to keep track of different diagnosis reports of each patient (Rosita, 2021). This will reduce the unnecessary confusion for the hospital. Hospitals will be able to complete the regular operations in an effective way. Confusion regarding complex operations will reduce to the diagnosis directory of the hospital. Delivery timings of diagnosis reports will clear confusions regarding patient treatments for the hospital (Cimpoiasu et al., 2021).

Health_Insurance_key

Patient_personal_information_document_key
Patient_name_document
Patient_contact_details

Patient_Health_Insurance_History _document
Previous_coverage_document

Health Insurance key will give the patient's personal information. Patients' contact details will be provided under this dimension table. Patient’s health insurance key will help to provide hospitals the valuable information regarding patient's medical coverage. This will help hospitals to make valuable decisions in a more effective manner (Rocha, Capelo and Ciferri, 2020). Doctors will be able to provide suitable alternatives according to the medical coverage of the patient. Effective health insurance coverage will help the hospital to recover its lost glory within a short period. These details will further help insurance organisations to grab an option for renewing insurance. This documentation is further beneficial to treat patient at comparatively lower cots based o criterions of insurance policies.

Figure 2: Star Schema Diagram

(Source: Author)

4) Two examples of Star schema

Insurance clear up

Several issues were continually rising due to complications with paper work. This organisation did not have any framework that is used to resolve such paper work related issues. A major problem that this organisation was not being able to handle was separating relevant data from its unnecessary counterparts. These aspects were further aggravating problems with client detestation. A star schema model was utilised by this organisation to resolve this present issue.

Health insurance: Health_Incurance_Key

This key was used by members of that organisation to find out information related to any specific client who was being treated at this hospital. Using a Star schema model helped put in place several information that had been provided by clients (Bacry et al., 2020). Using this model simplified an otherwise complicated procedure for separating necessary facts from a combination of a massive number of resources. Entire information provided by clients was input into this star schema and only aspects of insurance that could be covered by service providers for these clients were displayed. Thus, a dimension table and its supporting facts were used and explained by this organisation to its disgruntled customers.

Solving X-Ray complications

This hospital was facing problems in solving simple issues of daily working. As a result, they were looking for a system that could solve all these problems that were causing major hiccups on the daily. Problems related to arranging all their daily functions were becoming problematic for their hospital’s ability to function. Upon introduction of this star schema model, attributes that were related to specific patients were keyed and tagged (Zhu et al., 2017). This ensured that there would be no further problems regarding miscommunication of reports.

Fact table: Diagnosis_Key

These keys were assigned by members of this organisation to pinpoint all aspects that were necessary for any specific situation. As a result, diagnosis of patients became even simpler since all information related to them was easily available

Conclusion

It is concluded that an assumption over different operational services in hospital environment have occurred due to lack of operational link-up and responsibilities taken from higher authorities. In context, research has identified facts, including administrative, doctor, patients, insurance, diagnosis, and other facts are identified. Moreover, consideration of insurance cover and x-ray diagnosis related examples are evaluated that can be implemented to address hospital issues with star schema model.

Reference

Books

Gorelik, A., 2019. The enterprise big data lake: Delivering the promise of big data and data science. O'Reilly Media.

Kimball, R., 1996. The data warehouse toolkit: practical techniques for building dimensional data warehouses. John Wiley & Sons, Inc.

Journals

Bacry, E., Gaiffas, S., Leroy, F., Morel, M., Nguyen, D.P., Sebiat, Y. and Sun, D., 2020. SCALPEL3: a scalable open-source library for healthcare claims databases. International Journal of Medical Informatics, 141, p.104203.

Cimpoiasu, M.O., Kuras, O., Wilkinson, P.B., Pridmore, T. and Mooney, S.J., 2021. Hydrodynamic characterization of soil compaction using integrated electrical resistivity and X?ray computed tomography. Vadose Zone Journal, 20(4).

Rocha, G.M., Capelo, P.L. and Ciferri, C.D., 2020, August. Healthcare decision-making over a geographic, socioeconomic, and image data warehouse. In ADBIS, TPDL and EDA 2020 Common Workshops and Doctoral Consortium (pp. 85-97). Springer, Cham.

Rosita, A., 2021. Introduction Study Of Business Intelligence Hospital Medical Recording Data. Turkish Journal of Computer and Mathematics Education (TURCOMAT), 12(11), pp.1043-1050.

Zhu, J., Potti, N., Saurabh, S. and Patel, J.M., 2017. Looking ahead makes query plans robust: Making the initial case with in-memory star schema data warehouse workloads. Proceedings of the VLDB Endowment, 10(8), pp.889-900.