BUS5WB Data Warehouse Design: Vigour Healthcare Provider Report

This report presents a data warehouse design for Vigour, a healthcare provider specializing in geriatrics. The design addresses Vigour's need to leverage its accumulated data to improve business operations. It outlines the current and future decision-making needs, focusing on client data, medical condition progress, medication effects, and billing information. The report includes SQL queries to extract relevant data and details the design of six dimension models: Client, Invoice, Employee, Condition, Medication, Date and Consultation. A data dictionary is provided for each dimension table, describing the attributes, data types, constraints, and purpose of each field. Finally, the demonstration section explains how each dimension table fulfills specific information needs for decision-making, such as customer relationship management, financial analysis, and treatment optimization.

Running head: DATA WAREHOUSING
Data Warehousing
Name of the Student
Name of the University

1DATA WAREHOUSING
Table of Contents
1. Requirements:.............................................................................................................................................................2
2. SQL.............................................................................................................................................................................2
3. Design:........................................................................................................................................................................3
4. Demonstration:............................................................................................................................................................7
Bibliography:..................................................................................................................................................................8

2DATA WAREHOUSING
1. Requirements:
Current and Future Decision Making Needs:
i. 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
ii. 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.
i. 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.
ii. 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.
iii. 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.
iv. 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.
i. Client Dimension Model: This dimension model will hold personal details of the client.
ii. Invoice Dimension Model: This dimension model will hold all the payment related data.
iii. Employee Dimension Model: This dimension model will hold all the details of the employee.
iv. Condition Dimension Model: This dimension model will hold the information of condition
against every patient.
v. Medication Dimension Model: This dimension model holds the information of all the medicines
Vigour purchases and prescribes.
vi. Date Dimension Model: Will store every process against a proper timeline.
vii. Consultation Dimension Model: The consultation provided to every patient will be needed for
better decision making.
2. SQL
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;

3DATA WAREHOUSING
3. Design:
Figure 1: Fact and Dimension Table of Vigour Date Warehouse
(Source: Created by Author)

4DATA WAREHOUSING
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

5DATA WAREHOUSING
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 Varchar (200) N/A This outcome is

6DATA WAREHOUSING
outcome of the
consultation
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 Varchar (50) N/A To store the name

7DATA WAREHOUSING
employee 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
4. Demonstration:
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.

8DATA WAREHOUSING
Bibliography:
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).