BUS5WB Data Warehousing: Design for Vigour Healthcare Provider

This report presents a comprehensive data warehouse design solution for Vigour, a fictitious healthcare provider specializing in geriatrics. The design addresses Vigour's need to capitalize on its accumulated data to improve business operations. The report includes a detailed explanation of the requirements, focusing on patient data, progress data, medicine information, and payment details. Six dimension models are proposed: Client, Invoice, Employee, Condition, and Medication. The report also provides SQL queries for data retrieval and manipulation, along with a data dictionary outlining the attributes of each dimension table. A fact and dimension table diagram illustrates the overall warehouse structure. Finally, the report demonstrates how each dimension model supports decision-making, such as client-related decisions, payment analysis, employee management, patient condition monitoring, and medication management. This assignment showcases a practical application of data warehousing principles in the healthcare sector, available for study on Desklib.

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

1DATA WAREHOUSING
Table of Contents
1. Requirements:..............................................................................................................................2
2. SQL..............................................................................................................................................3
3. Design:.........................................................................................................................................5
4. Demonstration:..........................................................................................................................11
Bibliography:.................................................................................................................................12

2DATA WAREHOUSING
1. Requirements:
Current and Future Decision Making Needs:
i. Vigour’s main business is depended on the patients. Attracting new patients
requires proper planning. The organization would need details of the patients so
that they can identify which type of patients is more valuable.
ii. In order to gather resources as per the business need, the organization would
require accurate data. The management will identify the amount of services
provided in a specific time period. Based on the identified data resources will be
forecasted.
iii. In order to increase patient treatment quality, the management would require all
the data regarding treatment. The treatment data will be presented to the
management as report.
The information requirements are as following.
i. Patient Personal Data: It is essential to store the personal data of the patient.
These data are very important for management decisions. The management make
promotions, policies and create other business related decisions based on patient
personal data.
ii. Progress Data: In order to provide proper treatment to patients, the doctors and
other staffs must have information of patient condition. After each consultancy,
the medical condition of the patient will be recorded. This information will allow
the doctors to determine whether the patient is recovering or not. This data can be

3DATA WAREHOUSING
used for identifying which medications and treatments are better for a specific
diesis.
iii. Medicine Name and Side effect: The name of the medicines will allow the
doctors to identify the medicines. The side effects are very dangerous. The
doctors will save the side effects of the medicines on the patients to improve
treatment process.
iv. Payment: The payment related information is a vital information. The amount of
capital a patient pay toVigour is the only way of profit.
Total six dimension models are required to store all the data. The dimension models are
as following.
i. Client Dimension Model: This model will be stored in the client dimension table.
ii. Invoice Dimension Model: The payment a patient make will be stored in this
dimension table.
iii. Employee Dimension Model: The employees’ data will be stored in the
dimension table.
iv. Condition Dimension Model: This dimension table will store the conditions of
the patient.
v. Medication Dimension Model: Medication will hold all the details of the
medicines.
2. SQL
SQL Query 1: Select * from Client Inner Join ClientCondition on Client.ClientID=
ClientCondition.ConditionID Order By Client.Name;

4DATA WAREHOUSING
SQL Query 2: Select Name, Total From Client Inner Join MedicationOrder on
Client.ClientID= MedicationOrder.ClientID Group By Name;
SQL Query 3: Select Client.Name, ConsultationDate, CurrentMedication, Type From
Client Inner Join ClientCondition on Client.ClientID= ClientCondition.ClientID Inner Join
MedicalCondition on ClientCondition.ConditionID= MedicalCondition.ConditionID Order By
Client.Name;
SQL Query 4: Client.Name, CarePlan.StartDate, CarePlanEvent.EventDate,
CarePlanEvent.MentalState From Client Inner Join CarePlan on Client.ClientID=
CarePlan.ClientID Inner Join CarePlanEvent on CarePlan.CarePlanID =
CarePlanEvent.CarePlanID;
SQL Query 5: Client.Name, Sum(SubscriptionRecord.Total) As ‘Total Payment’ From
Client.ClientID= SubscriptionRecord.ClientID Group By Client.Name Order by Client.Name;

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

6DATA WAREHOUSING
Data Dictionary:
Dimension Table: Client
Attribute Description Data Type Constraint Purpose
Client_Code This is the
primary key of
the table
Varchar (40) Primary To identify each
row of the
individually
Name The name of the
client/patient
Varchar (40) N/A To store the
name of the
client
Date_Of_Birth Date of birth of
the patient
Date N/A To collect the
birth date of
client
Gender Whether the
client is male or
female
Varchar (10) N/A To determine the
sex of the client
Occupation What the client
does for living
Varchar (150) N/A Store the jib
description of
the client
Dimension Table: Medication

7DATA WAREHOUSING
Attribute Description Data Type Constraint Purpose
Medication_Code This is the
primary key of
the table
Varchar (40) Primary To identify each
row of the
individually
Supplier_name The name of the
medicine
supplier
Varchar (40) 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: Consultation
Attribute Description Data Type Constraint Purpose
Consultation_Code This is the
primary key of
the table
Varchar (40) Primary To identify each
row of the
individually
Date The date stores
on which day
consolation was
done
Date N/A To identify the
consultations
that are done in
a specific date

8DATA WAREHOUSING
or period
Outcome The outcome of
the consultation
is stored in brief
Varchar (200) N/A TO analyse the
result of the
consultation for
future
Dimension Table: Condition
Attribute Description Data Type Constraint Purpose
Condition_Code This is the
primary key of
the table
Varchar (40) Primary To identify each
row of the
individually
Name The name of the
condition
Varchar (40) N/A To store the
name of the
condition
Type In which
category the
condition reside
Varchar (10) N/A To identify the
type of the
condition and
process
treatment
Symptoms The symptoms
of the condition
Varchar (200) N/A To identify if the
condition is

9DATA WAREHOUSING
critical or
normal
Dimension Table: Employee
Attribute Description Data Type Constraint Purpose
Empployee_Code This is the
primary key of
the table
Varchar (40) Primary To identify each
row of the
individually
Name The name of the
employee
Varchar (40) 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
Date N/A Poetize the
employees

10DATA WAREHOUSING
the organization
Dimension Table: Invoice
Attribute Description Data Type Constraint Purpose
Invoice_Code This is the
primary key of
the table
Varchar (40) Primary To identify each
row of the
individually
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

11DATA WAREHOUSING
4. Demonstration:
Client Dimension Model: The client dimension will be used when the data will be
needed for making client related decisions.
Invoice: The payment data will be presented to the management through a report. This
dimension table will store the total amount, discount on payment, date of payment and many
more information.
Employee: Employee information is essential for business internal decision making such
as working environment enhancement.
Condition: The condition of each patient is vital to monitor. This dimension table will
hold the condition name symptoms and side effects.
Medication: Medication dimension provides all the information of the medicines. The
side effects of the medicine are required to know before prescribing it to a patient.

12DATA WAREHOUSING
Bibliography:
Andersen, O., Thomsen, C. and Torp, K., 2018. SimpleETL: ETL Processing by Simple
Specifications.
George, J., Kumar, V. and Kumar, S., 2015. Data Warehouse Design Considerations for a
Healthcare Business Intelligence System. In World Congress on Engineering.
Kraus, C. and Valverde, R., 2014. A data warehouse design for the detection of fraud in the
supply chain by using the benford’s law. American Journal of Applied Sciences, 11(9), pp.1507-
1518.
Mireku Kwakye, M., 2017. Modelling and Design of Generic Semantic Trajectory Data
Warehouse. Science.
Sehgal, S. and Ranga, K.K., 2016. Translation of Entity Relational Model to Dimensional
Model.