Consultations
Current and Future Decision Making Needs:
- 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
- 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.
Don't use plagiarized sources. Get Your Custom Essay on
Vigour: Data Warehouse Design For Healthcare Provider Specializing In Geriatrics
Just from $13/Page
- 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.
- 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.
- 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.
- Client Dimension Model:This dimension model will hold personal details of the client.
- 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.
- Condition Dimension Model:This dimension model will hold the information of condition against every patient.
- Medication Dimension Model:This dimension model holds the information of all the medicines Vigour purchases and prescribes.
- 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).
