Star Schema Design Assignment Sample

Star Schema Design Assignment

Get free written samples by our Top-Notch subject experts and Assignment Help team.

Introduction

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 insurance coverage. Collection of patient data is essential for this purpose. Employees responsible for insurance 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.

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).

• 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 five a clear idea about the associated 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

Administrative_key
Patient_Directory_key
Doctor_key
Diagnosis_key
Health_Insurance_key

Dimension table for Administrative_key

Emergency_administration_key
Emergency_policies and time_frames_number
Emergency equipment requirements_key
Maternity_ward_administrative_documentation_key
Maternity_ward_administrative_time_frames_number
Maternity_ward_policies_name
General ward administrative documentation_key
General_ward_shift_timings _name
General_ward_policies_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.

Dimension table for Patient _Directory_Key

Patient gender_key
Male_id
Female_id
Transgender_id
Patient medical history key
Previous_symptoms_name
Special_attributes_key

Patient admission Time_key
Date_number
Time_number

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.

Doctor dimension key

Doctor Information_key
Doctor_Names
Doctor_timings
Doctor_shifts
Doctor_contact
Doctor_information_according_to_ward_Key
Doctor_timings_in_different_wards_key
Doctor_assigned_in_different_shifts_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.

Diagnosis_Key Dimension Table

Diagnosis_Key
Patient_Requirements_key
Diagnosis_timings_number
Diagnosis_result_delivery_id
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 _key
Patient_name
Patient_contactDetails

Patient_Health_Insurance_History _Key
Previous_coverage_number
Whether_applicable_for_Hospital_policy_number

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.

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.