Special Issue: Conference Proceeding of i-CON-2016

Global Journal on Advancement in Engineering and Science (GJAES) Vol. 2, Issue 1 : March-2016, ISSN (Print): 2395-1001 Original Research Work Data Warehouse System Architecture for a Typical Health Care Organization Rajib Dutta1, Vicky Mondal2 Department of Computer Science & Engineering, Global Institute of Management & Technology, Krishnanagar, Nadia, West Bengal. Email: [email protected], [email protected]

Abstract: In current scenario large enterprises depends on database systems to manage their huge data and information. These huge data and information are very useful for daily business transactions. The tough competition in the business market has most used the concept of data mining in which data are analyzed to derive effective business strategies and discover better ways in carrying out business by through the Decision Support System. To perform data mining, we have to convert regular databases into what so called informational databases also known as data warehouse. Shifting of healthcare data in database management system is not a proper solution in now a day, so huge volumes of data have been accumulated in organizations related to medical are stored in data warehouse. This paper presents a design of system architecture for building data warehouse of a typical health care organization system. Keywords: Health care data warehouse, Data Mining, Extract Transform and Load (ETL), Multidimensional databases (MDDBs) and Decision Support System (DSS).

I. Introduction A Data Warehouse (DW) is defined as “a subject-oriented, integrated, time-variant, non-volatile collection of data in support of management’s decision-making process” [1]. The process of developing a data warehouse starts with identifying and gathering requirements, designing the dimensional model followed by testing and maintenance. The design phase is the most important activity in the successful building of a data warehouse [2]. In current scenario the world fast growing, largest and most information’s are available in the health care industry. The stored data in health care organization, data may be recorded as patient’s details, patient’s hobbies details, diseases record, individual patient pathology report, physician’s details, physician’s order entry, physician’s decision support system, medicine, billing section. Most of the health organization is still stand along, they are not communicating with other health organization, and they don’t share their documents like patient’s details, diseases record, individual patient pathology record, their previous treatment history with others. To overcome this stand aloneness problem we proposed a health care system architecture which works universally, means different health organization can share needful documents with others. Also it is useful to the patient’s, they can find such a health care organization where best doctor’s list, best treatment for them and lowest cost treatment. II. Background Operational Database: Operational Database is the database-of-record, consisting of system-specific reference data and event data belonging to a transaction-update system. It may also contain system control data such as indicators, flags, and counters. The operational database is the source of data for the data warehouse. Informational Database: An informational database is a special type of database that is designed to support decision making based on historical point-in-time and prediction data for complex queries and data mining applications. A data warehouse is an example of informational database. [5] Data Warehouse: a data warehouse (DW or DWH), also known as an enterprise data warehouse (EDW), is a system used for reporting and data analysis. DWs are central repositories of integrated data from one or more disparate sources. They store current and historical data and are used for creating analytical reports for knowledge workers throughout the enterprise. Examples of reports could range from annual and quarterly comparisons and trends to detailed daily sales analysis. Data Mining: It is a knowledge discovery process that uses a blend of statistical, machine learning, and artificial intelligence techniques to detect trends and patterns from large data-sets, often represented as data warehouse. The purpose of data mining is to discover news facts about data helpful for decision makers [6].

S. Mandal (Editor), GJAES 2016 © GJAES

Page 94

R. Dutta et al., Data Warehouse System Architecture for a Typical Health Care Organization, Global Journal on Advancement in Engineering and Science, 2(1), March 2016, pp. 94-99

III. Operational Database Design Essentially, the operational database used to derive the data warehouse later on, encompasses fourteen distinct relations or tables associated together by means of relationships. It is a relational model database implemented under MS Access [8]. This database represents the business inside a typical health care organization. It includes a front end registration system for handling patient’ registration processes where all details of , patient’s hobbies details, diseases record, individual patient pathology report, an accounting system for managing patient’ payments, a departmental management system for managing patient’s according to their diseases and assigning them particular pathology, physician, and an assets system for distributing items such as medicine, equipment, and bed allocation over different departments. Figure 1 depicts the conceptual schema of the operational database.

Figure 1: Conceptual Schema of the Operation Database

IV. System Architecture Design Now a day’s both effective health care and to financial survival is the most important things for any health care organizations. Data about the accuracy of diagnoses, effectiveness of treatments, efficient doctors, and proper cost is the crucial things about a health care centre. Different health care centre has different cost for same type treatments. The health care industry is unique in that it needs to bring together efforts to improve the quality of individuals’ health with the effort to cut costs to employers and governments. Recent in India there are several types of health care organization are there. It is quite different from other industry to build the health care centre data warehouse for diagnoses. But just like software development project, data warehouse maintain the stage by stage procedure. Our main aim is to make some data warehouse architecture where a patient's get proper treatment with low cost. Two basic stages for building a health care data warehouse for a healthcare organization is describe in this paper as following. A. Business Analysis. B. System Architecture Design. C. Data Architecture Design. A. Business Analysis A data warehouse is an information delivery system for business intelligence. Business intelligence (BI) is a technology-driven process for analyzing data and presenting actionable information to help corporate executives, business managers and other end users make more informed business decisions. It is solving users’ problems and providing strategic information to the user. In the phase of defining requirements, need to concentrate on what information the users need. With the purely top-down approach, the data warehouse will be developed based on the third normal form relational data model. This relational database will form the data warehouse. The business analysis stages consist of business process analysis and business requirement analysis. A. 1. Business Process Analysis In that process four actors are Patient, Doctors, Pathologist, and executive manager. Here all patients are coming to the health care centre for treatment. According to the symptoms of the patient, the doctors send them to the pathology for some medical test. As per their medical test report, doctors decide the type of diseases and then started the treatment. S. Mandal (Editor), GJAES 2016 © GJAES

Page 95

R. Dutta et al., Data Warehouse System Architecture for a Typical Health Care Organization, Global Journal on Advancement in Engineering and Science, 2(1), March 2016, pp. 94-99

Case 1: Seek Consultation Patients go to doctor when certain symptoms are noticed by the patient. According to their diseases symptom the heath care organization send them to the doctors. Case 2: Perform Diagnosis The doctors and the pathologist will together perform a series of test like Blood test (white blood cell differential), Chest x-ray, Auscultation (to detect abnormal breath sounds), Nasopharyngeal culture to determine the type of diseases he/she has. Case 3: Propose Treatment According to their pathological report doctors start their treatment. In their treatment need some consult, they just sheared their medical report to other doctor and then started their treatment. A. 2. Business Requirement Analysis Here some important requirements for health care data warehouse to support different diseases and treatment recommended by the doctors. The propose requirements are: Minimum level of dimensional nature of business data about the patient required where patient details is stored. In the record included portion are: Full Name, Date of Birth, Gender, Age, Marital Status, Address, Contact Number, Occupation, Disease Details, Treatment under which Doctors and etc. All patient record details must be recognized by unique it may be using ID number which may be declaring as a primary key. Thus may prevent data duplication, and easy to search. The medical diagnosing function requires to updates patient medical history report, symptoms, drug interaction before and after the patient treatments. The system must be able to display at both summary and details levels, by which the use (doctors) may get specific idea about the disease and analyze the result. B. System Architecture Design for Health Care Organization Figure 2 show that total proposed architecture for the health care organization data warehouse. Architecture of health care organization data warehouse system builds with Source Data components in the left side where multiple data are comes from different data source and transform into the Data Staging area before integrated. The Data Staging component present at the next building block. Those two blocks is under Data Acquisition Area. In the middle Data Storage component that manages the data warehouse data. This component also with Metadata, that also keep track of the data and also with Data Marts. Last component of this architecture is Information Delivery component that shows all the different ways of making the information from the data warehouse available to the user for further analysis.

Figure 2: Universal Data Warehouse Architecture for Health Care Organization

B. 1. Data Acquisition This portion data are mainly medical files which are store in Microsoft Access database. Medical files such as patient medical reports, blood tests result, x-ray results, auscultation test results, Nasopharyngeal culture reports, etc. Those data are coming from multiple sources. In data extraction, select data from those source data and moving all the extracted data to the staging area. Now in the Data Transformation portion, map extracted data for the data of the data warehouse. Combining pieces of data from different data source is a part of data transformation. When data transformation function ends then the collection of integrated data that is cleaned, standardized and summarized. In this stage there are a set of functions and services such as:

S. Mandal (Editor), GJAES 2016 © GJAES

Page 96

R. Dutta et al., Data Warehouse System Architecture for a Typical Health Care Organization, Global Journal on Advancement in Engineering and Science, 2(1), March 2016, pp. 94-99

Data Extraction Select data from Medical files and determine the types of filters to be applied to individual sources. Generate automatic extract files from operational systems using replication and other techniques. Create intermediary files to store selected data to be merged later. Transport extracted files from multiple platforms. Provide automated job control services for creating extract files. Generate common application codes for data extraction. Resolve inconsistencies for common data elements from multiple sources. Data Transformation Map input data to data for data warehouse repository. Clean data, deduplicate, and merge/purge. Denormalize extracted data structures as required by the dimensional model of the different disease data warehouse. Convert data types, Calculate and derive attribute values, Check for referential integrity, Aggregate data as needed. Resolve missing values, Consolidate and integrate data.

Figure 3: Data acquisition: Health Care Organization

B. 2. Data Storage This portion, the data from the staging area load into the data warehouse repository. Medical Files data and Microsoft Access Data are loaded in to the data warehouse in the day-to-day basic. Data repositories contain the data structure in highly normalize form for fast and efficient processing. Large amount of historical data of the patients are needed in data warehouse for analysis. The data storage in data warehouse is kept separate for quick retrieval of individual pieces of information. Data warehouse are read-only data repositories.

Figure 4: Data acquisition: Health Care Organization

In this there are a set of function and services such as: Load health care data for full refreshes into data warehouse tables. Perform incremental loads at regular prescribed intervals. Loading details and summarized levels of patient’s data into multiple tables. Optimize the data loading process. B. 3. Information Delivery In this stage the Doctors collect information from data warehouse. To collect the information from data warehouse, information delivery components is use to make it easy to access and decision making to access the information directly from the health care data warehouse. There are different information delivery methods for different user. Ad hoc reports are predefined reports primarily meant for novice and casual user i.e. staffs of the health care. Provision for complex queries, multidimensional analysis and statistical analysis cater to the needs of the business analysts and power users i.e. Doctors are this type of user. Information fed into Executive Information Systems is mint for senior executive and high level managers. The primary data warehouse feeds data to proprietary multidimensional databases (MDDBs) where summarized data is kept as multidimensional

S. Mandal (Editor), GJAES 2016 © GJAES

Page 97

R. Dutta et al., Data Warehouse System Architecture for a Typical Health Care Organization, Global Journal on Advancement in Engineering and Science, 2(1), March 2016, pp. 94-99

cubes of information. Based on fact table and multiple dimensions table, the star model is adopted. Then from dimension table create hierarchy which is useful to create reports. These stages there are a set of functions and services such as: It allows the doctors and other decision maker to brows disease data warehouse content. It also provides security for unknown user to access data. It totally hides the complexities of data storage from the user and allows them simply access the data. Reformat the queries automatically for optimal execution. Provide self-service report generation for users, consisting of a variety of flexible options to create, schedule, and run reports. Queries and reports result set are store for future use. Provide event triggers to monitor data loading and multiple levels of data granularity.

Figure 5: Information Delivery: World Health Care Organization Architecture

C. Data Architecture Design The star schema demonstrates the data layer architecture of the health care data warehouse which is shown in Figure 6. The design star schema of health care data warehouse uses a de-normalize schema which contain denormalize or redundant data. Business Intelligence techniques and data mining may uses facilitate of denormalized data.

Figure 6: Health Care Data Warehouse Star Diagram

The fact table that describe all about medical report is named Medical Fact. The table consists of Patient_ID, Date_of_Entry, Habit_ID, Disease_ID, Risk_Factor_ID, Treatment, Symptom_ID, Diagnostic_status etc. The symptoms of a patient diagnosed condition and result are stored in Symptom table. The dimension table that store all details of each entity of every table. In Medical_Fact table the entity are Patient_Dimension, Treatment_Dimension, Symptom_Dimension, Disease_Type_Dimension.

S. Mandal (Editor), GJAES 2016 © GJAES

Page 98

R. Dutta et al., Data Warehouse System Architecture for a Typical Health Care Organization, Global Journal on Advancement in Engineering and Science, 2(1), March 2016, pp. 94-99

Patient_Dimension: A table that stores patient information, such as patient name, sex, age, address, disease, test report, blood group etc. This data are use for find out which type of disease. Treatment_Dimension: A table that is used to stores all possible treatment option. Symptom_Dimension: A table that is used to stores the entire symptom, the normal condition values and abnormal condition values. Disease_Type_Dimension: A table that is used to stores all the diseases and the types of the diseases. In this case only all types of diseases related data are stores.

Figure 7: Data in Patient Dimension table

V. Conclusion Our aim is to design such type of data warehouse architecture for health care organization that gives best treatment with low cost. Developing a typical health care data warehouses, places data quality high on the agenda. Health care related data warehouses is challenging because definitions for individual items must be clear and unambiguous throughout the organization while in practice shared data elements have alternative definitions, owing to a range of different users with a variety of different information needs. This health care industry is the fast developing, most data sufficient industry. For taking that advantage, build a typical health care organization oriented data warehouse. In world health care organization related data warehouse, integrate between medical files and operational data. Then analysis on patient’s medical report data make easy by using OLAP cubes. By using those multilevel viewing data, anyone can analysis the diseases, cost of the treatment, date rate of specific type of diseases and impact of particular drug. The proposed classifier has been implemented using JAVA (JDK 1.6_16), Microsoft Sql Server 2008, Microsoft Office Access 2007, Microsoft Sql Server Integration Service 2008, Microsoft Sql Server Reporting Service 2008, Microsoft Sql Server Analysis Service 2008. VI.

References

[1]

Inmon, W.H., Hackathorn, and R.D (1994) Using the data warehouse. Wiley-QED Publishing, Somerset, NJ, USA..

[2]

Rajni Jindal and Shweta Taneja, COMPARATIVE STUDY OF DATA WAREHOUSE DESIGN APPROACHES: A SURVEY, International Journal of Database Management Systems ( IJDMS ) Vol.4, No.1, February 2012

[3]

William Inmon, “Building the Operational Data Store”, 2nd ed., John Wiley & Sons, 1999.

[4]

Matteo Golfarelli & Stefano Rizzi, “Data Warehouse Design: Modern Principles and Methodologies”, McGraw-Hill, Osborne Media, 2009.

[5]

Youssef Bassil, “A Data Warehouse Design for A Typical University Information System”, ournal of Computer Science & Research (JCSCR) - ISSN 2227-328X, Vol. 1, No. 6, Pages. 12-17, December 2012

[6]

Pang-Ning Tan, Michael Steinbach, Vipin Kumar, “Introduction to Data Mining”, Addison Wesley, 2005

[7]

Robert Laberge, “The Data Warehouse Mentor: Practical Data Warehouse and Business Intelligence Insights”, McGraw-Hill Osborne Media, 2011..

[8]

Vineetha Appidi, Dr Syed Umar, Sushma Vallamkonda “Development of a Data Warehouse for Cancer Diagnosis andTreatment Decision Support” International Journal Engg Techsci Vol 5(3) 2014, 22 – 26

[9]

Paulraj Ponniah “Data Warehousing Fundamentals A Comprehensive Guide for IT Professionals”,Wiley India Pvt.Ltd, ISBN: 978-81265- 0919-5

S. Mandal (Editor), GJAES 2016 © GJAES

Page 99