Vigour: Data Warehouse Design For Healthcare Provider Specializing In Geriatrics

Consultations

Current and Future Decision Making Needs: 

1. In order to promote business, Vigour will evaluate clients’ personal details and generate various information. This information will be based on the treatment each of the patient will receive. Therefore, the main decision that Vigour will make for promoting business is clients will be receiving messages of discussants and new features
2. The second need is payment related information. The organization will be evaluating the payments a client makes. This information will be processed and financial knowledge will be generated. The organization will categorize patients based on payments they make and few other things.

The information requirements are as following.

1. Client Data:The personal details of the client are essential for business operations. The management will make various crucial decisions based on the client details. The policies, promotions, treatment and many more factors process the client data for generating knowledge.
2. Medical Condition Progress Data: This data is most vital of all. The system will be storing the medical condition of each patient after consultation and diagnostic. This data will be processed for generating treatment related information. Based on the medical condition progress data, the treatment procedure is modified.

• Medicine and Effects:Medicines are prescribed to the patients. The employees will store the name of the patient, medicine, side effect of it and after of medicine. These data will be processes later to identify which medicine suits which clients. Most of the decisions related to medication will be made based on the mentioned findings.

1. Billing:The bills are generated as a prof of service provided to client. The invoice, on the other hand, serves as a proof that client made payment to Vigour. Payment information is crucial for financial decision making.  

Total six dimension models are required to store all the data. The dimension models are as following.

1. Client Dimension Model:This dimension model will hold personal details of the client.
2. Invoice Dimension Model: This dimension model will hold all the payment related data.

• Employee Dimension Model:This dimension model will hold all the details of the employee.

1. Condition Dimension Model:This dimension model will hold the information of condition against every patient.
2. Medication Dimension Model:This dimension model holds the information of all the medicines Vigour purchases and prescribes.
3. Date Dimension Model:Will store every process against a proper timeline.

• Consultation Dimension Model:The consultation provided to every patient will be needed for better decision making.

SQL Query 1: Select C.Name, CC.CurrentMedication, MC.Symptoms From Client C inner join ClientCondition CC on C.ClientID=CC.ClientID inner join MedicalCondition MC on CC.ConditionID=MC.ConditionID Where C.Gender=Female Order By C.Name;

SQL Query 2: Select Name, Total From Client Inner Join MedicationOrder on Client.ClientID= MedicationOrder.ClientID Group By Name;

SQL Query 3: Select C.Name, MC.*, From Client as C inner join ClientCondition as CC on C.ClientID = CC.ClientID inner join MedicalCondition as MC on CC.ConditionID  = MC.ConditionID Order By ConsultationDate;

SQL Query 4: Select C.Name, I.Date, I.Discount, I.Total From Client as C inner join Invoice as I on C.ClientID = I.ClientID Where SubTotal>200 AND Discount <5 Order by I.Date ASC;

Figure 1: Fact and Dimension Table of Vigour Date Warehouse

(Source: Created by Author)

Data Dictionary:

Dimension Table: Dim_Client
Attribute	Description	Data Type	Constraint	Purpose
Client_ID	It separates all the row	Varchar (50)	Primary	Identify each table uniquely
Name	The given and last name of the client	Varchar (50)	N/A	Identify the name of the client
Date_Of_Birth	The date on which client was born	Date	N/A	The birth date of the client assist in various decision making
Gender	Identify to which sex the client belongs to	Varchar (6)	N/A	The gender assist in identifying what kind of consultation to be offered
MartialStatus	It stores the martial status of the client	Varchar (50)	N/A	Marital status is required for various decision making needs
HomeAddress	The home address will be default address	Varchar (50)	N/A	Delivering the bill to the default address
WorkAddress	The optional address will be work address	Varchar (50)	N/A	In case delivery fails, bill will be sent to the optional address
Occupation	The current job of the client	Varchar (150)	N/A	Consultation is done based on the working nature of the client

Dimension Table: Dim_Medication
Attribute	Description	Data Type	Constraint	Purpose
Medication_ID	It separates all the row	Varchar (50)	Primary	Identify each table uniquely
Supplier_name	The name of the medicine supplier	Varchar (50)	N/A	Supplier name is crucial for logistics
Unit_Cost	Cost for each unit of medicine	Decimal (12,2)	N/A	To store the price of medicine

Dimension Table: Dim_Date
Attribute	Description	Data Type	Constraint	Purpose
Date_ID	It separates all the row	Varchar (50)	Primary	Identify each table uniquely
Date	The date is stored	Daye	N/A	To assign a process a particular date
Day	The name of the day for a specific date	Varchar (10)	N/A	To find the name of the day on which a process was done or an outcome was generated
Day_of_Week	The number of day on a week	int	N/A	To find the number of the day
Month	The numerical or alphabetical representation of a month	Month	N/A	The date will be assigned for individual months
Month_Name	The name of the mont	Varchar (10)	N/A	The name of the month like January
Quarter_Name	The quarter like first, second and more	Varchar (10)	N/A	To search data or create report for a large period of time
Year	The year is stored	Year	N/A	All the dates, months and quarters will be separated by the year attribute

Dimension Table: Dim_Consultation
Attribute	Description	Data Type	Constraint	Purpose
Consultation_ID	It separates all the row	Varchar (50)	Primary	Identify each table uniquely
Consultation_Outcome	The result of consultation	Varchar (200)	N/A	To identify the outcome of the consultation
Diagnostic_Outcome	The result of diagnostic	Varchar (200)	N/A	To see the impact of the diagnostic on the client
Specialist_Outcome	The result a specialist input against each consultation	Varchar (200)	N/A	The specialist outcome servers additional information for treatment
Consultation_Event_Outcome	The final outcome of the consultation	Varchar (200)	N/A	This outcome is very essential

Dimension Table: Dim_Condition
Attribute	Description	Data Type	Constraint	Purpose
Condition_ID	It separates all the row	Varchar (50)	Primary	Identify each table uniquely
CurrentState1	Initial present state of the client	Varchar (200)	N/A	To analyse the initial client state during treatment
CurrentState2	Second storage of client state	Varchar (200)	N/A	To identify and evaluate the second entry on client medical state
CurrentState3	Final information on client state	Varchar (200)	N/A	To identify and evaluate the final entry on client medical state
CurrentMedication	Present medication prescribed to client	Varchar (200)	N/A	To identify and evaluate all the medicines that are prescribed to the client
SignificantEvents	If any special situation occured	Varchar (200)	N/A	To evaluate the events that are special
MedicalConditionName	The name of the condition	Varchar (50)	N/A	To store the name of the condition
MedicalConditionType	In which category the condition reside	Varchar (10)	N/A	To identify the type of the condition and process treatment
MedicalConditionSymptoms	The symptoms of the condition	Varchar (200)	N/A	To identify if the condition is critical or normal

Dimension Table: Dim_Employee
Attribute	Description	Data Type	Constraint	Purpose
Empployee_ID	It separates all the row	Varchar (50)	Primary	Identify each table uniquely
Name	The name of the employee	Varchar (50)	N/A	To store the name of the employee
Date_Of_Birth	Date of birth of the employee	Date	N/A	To collect the birth date of employee
Gender	Whether the employee is male or female	Varchar (10)	N/A	To determine the sex of the employee
Date_of_Joining	The date on which the employee joined the organization	Date	N/A	Poetize the employees

Dimension Table: Dim_Invoice
Attribute	Description	Data Type	Constraint	Purpose
Invoice_ID	It separates all the row	Varchar (50)	Primary	Identify each table uniquely
Date	The date on which invoice is generated	Date	N/A	To identify the business profits
Sub_Total	Total expenses of the treatment	Decimal (12,2)	N/A	To identify the business profits
Discount	Discount a client gets	INT	N/A	To make the client loyal
Total	Total amount after discount	Decimal (12,2)	N/A	The amount individual clients pay against treatment

Dim_Client: This dimension table will fulfil the information needs of customer relationship management decision making.

Dim_Invoice: This dimension table will fulfil the information needs of financial decision making.

Dim_Employee: This dimension table will fulfil the information needs of work place related decision making.

Dim_Condition: This dimension table will fulfil the information needs of treatment related decision making.

Dim_Medication: This dimension table will fulfil the information needs of treatment related decision making.

Dim_Condition: This dimension table will fulfil the information needs of treatment related decision making.

Dim_Date: This dimension table will fulfil the information needs of all the decision making.

Andersen, O., Thomsen, C., & Torp, K. (2018). SimpleETL: ETL Processing by Simple Specifications.

Arifin, S. M., Madey, G. R., Vyushkov, A., Raybaud, B., Burkot, T. R., & Collins, F. H. (2017). An online analytical processing multi-dimensional data warehouse for malaria data. Database, 2017.

Arunachalam, S., Page, T., & Thorsteinsson, G. (2016). Healthcare Data Warehousing. i-Manager’s Journal on Computer Science, 4(4), 1.

Bernard, S. K., Tra, G. B., Marcelin, B. K., & Oumtanaga, S. (2016). Determination of Child Vulnerability Level from a Decision-Making System based on a Probabilistic Model. INTERNATIONAL JOURNAL OF ADVANCED COMPUTER SCIENCE AND APPLICATIONS, 7(11), 379-384.

Berrahou, L., Lalande, N., Serrano, E., Molla, G., Berti-Équille, L., Bimonte, S., … & Le Ber, F. (2015). A quality-aware spatial data warehouse for querying hydroecological data. Computers & Geosciences, 85, 126-135.

Bouadi, T., Cordier, M. O., Moreau, P., Quiniou, R., Salmon-Monviola, J., & Gascuel-Odoux, C. (2017). A data warehouse to explore multidimensional simulated data from a spatially distributed agro-hydrological model to improve catchment nitrogen management. Environmental Modelling & Software, 97, 229-242.

Chevalier, M., El Malki, M., Kopliku, A., Teste, O., & Tournier, R. (2015, April). How can we implement a Multidimensional Data Warehouse using NoSQL?. In International Conference on Enterprise Information Systems (pp. 108-130). Springer, Cham.

Chevalier, M., El Malki, M., Kopliku, A., Teste, O., & Tournier, R. (2016). Document-oriented models for data warehouses.

Corral, K., Schuff, D., Schymik, G., & St Louis, R. (2015). Enabling Self-Service BI through a Dimensional Model Management Warehouse.

Dehdouh, K., Bentayeb, F., Boussaid, O., & Kabachi, N. (2015, January). Using the column oriented NoSQL model for implementing big data warehouses. In Proceedings of the International Conference on Parallel and Distributed Processing Techniques and Applications (PDPTA) (p. 469). The Steering Committee of The World Congress in Computer Science, Computer Engineering and Applied Computing (WorldComp).

George, J., Kumar, V., & Kumar, S. (2015). Data Warehouse Design Considerations for a Healthcare Business Intelligence System. In World Congress on Engineering.

Hart, R. (2017). Extending dimensional modeling through the abstraction of data relationships and development of the semantic data warehouse (Doctoral dissertation).

Kannan, V., Fish, J. S., Mutz, J. M., Carrington, A. R., Lai, K., Davis, L. S., … & Bhat, D. G. (2017). Rapid Development of Specialty Population Registries and Quality Measures from Electronic Health Record Data: An Agile Framework. Methods of information in medicine, 56(99), e74.

Liu, X., Lei, G., Ren, F., & Ma, H. (2015). Data Warehouse Architecture for Metal Mine Enterprise with UML AND CWM.

Mansmann, S., Rehman, N. U., Weiler, A., & Scholl, M. H. (2014). Discovering OLAP dimensions in semi-structured data. Information Systems, 44, 120-133.

McGlothlin, J. P., Madugula, A., & Stojic, I. (2017). The Virtual Enterprise Data Warehouse for Healthcare. In HEALTHINF (pp. 469-476).