Data Warehousing and Business Intelligence for Vigour Healthcare

This report presents a data warehousing solution designed for Vigour Healthcare, a geriatrics provider aiming to leverage its data for improved business operations. The report begins by outlining the current and future decision-making needs of the organization, focusing on areas such as patient r...Read More

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:.................................................................................................................................13

2DATA WAREHOUSING
1. Requirements:
Current and Future Decision Making Needs:
i. Providing benefits to the general physicians refers most of the patients. The
decision will be made by management and will have many conditions.
ii. The side effects of the medications, provided to the patients. The organization
may want to identify the date of the medicine started from. This will provide an
idea to the organization about the negative impacts of the medications on the
patients.
iii. The medical condition of a client is a crucial information. This will provide an
insight of the client condition. The patient care system can provide suggestion for
improving patient care.
iv. The organization will be analysing the orders made in a single day. The
information of orders provides insight of business core activities. The
management of Vigour will access the reports of medications expenses and make
various decisions.
v. The business depends on the clients. The system will create report on which
patient has paid how much for the service they have taken. This will allow the
organization to improve business infrastructure by providing better offers to loyal
customers.
The information requirements are as following.
i. Patient Information: patient information is crucial for business. The organization
will be promoting various offers based on the client they want to attract. The

3DATA WAREHOUSING
phone number and email id will be used for communicating with the clients and
promoting business.
ii. Treatment Information: The information of the patient’s medical conditions and
the improvement in their conditions are important for care management. The
organization will be providing treatment to the patients based on their medical
condition.
iii. Medication and Side Effects: The doctors will be proscribed various medications
to the patients. All the information will be stored in the database. The medication
and side effects of the information is crucial as the doctor will change the
medication if the side effects are severe.
iv. Bill: The payment accepted from the patients are the main source of business
profit. The information of payment is crucial for business decision making.
v. Employee Details: The information of the employees is also an important part of
the business. The organization will need to be in touch of the employee at all
times. The information will provide better understanding of an employee. This
will offer an opportunity to Vigour for improving the working atmosphere.
vi. Consultation: The information of the consultation for each of the patient will be
recorded and used later. The outcome of each consultation is provided to the
management in the form of report. The date of consultation, consultant name,
patient name, duration, outcome and many more information is presented through
that report.

4DATA WAREHOUSING
2. SQL
SQL Query 1: Select C.Name, E.Name, CO.ConsultationDate From Client as C inner
join Consultation as CO on C.ClientID = CO.ClientID inner join Employee as E on
CO.EmployeeID = E.EmployeeID Order By ConsultationDate;
SQL Query 2: Select C.Name, M.SideEffects, M.Dosage, CA.StartDate From Client as C
inner join CarePlan as CA on C.ClientID = CA.ClientID inner join CarePlanEvent as CE on
CA.CarePlanID = CE.CarePlanID Inner Join MedicationReview as M On CE.CarePlanEventID
= M.CarePlanEventID Order By ConsultationDate;
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, MO.OrderDate, MOL.Quantity,
MOL.MedicationSummary, M.SupplierName From Client as C inner join MedicationOrder as
MO on C.ClientID = MO.ClientID inner join MedicationOrderLine as MOL on MO.OrderID =
MOL.OrderID inner join Medication as M on MOL. MedicationID = M.MedicationID inner join
Employee as E on MO.EmployeeID = E.EmployeeID Where MO.EmployeeID = (Select
EMP.EmployeeID From Employee as EMP Where Gender = `Female` AND StartDate = `01-01-
2017`) Order By Order Date;
SQL Query 5: 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;

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

6DATA WAREHOUSING
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
Attribute Description Data Type Constraint Purpose

7DATA WAREHOUSING
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
or period
Outcome The outcome of Varchar (200) N/A TO analyse the

8DATA WAREHOUSING
the consultation
is stored in brief
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
critical or
normal

9DATA WAREHOUSING
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
the organization
Date N/A Poetize the
employees
Dimension Table: Invoice

10DATA WAREHOUSING
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
4. Demonstration and Dimension Table Justification:
Client Dimension Model: The client demotion model will provide information of the
user. This model can be sued for gathering personal information of the patient. At the time of
patient registration, all the patient personal data will be stored in this table. This dimension table

11DATA WAREHOUSING
can be used for identifying which client has registered at Vigour at which date. This way the
management can identify in which month most of patients have registered and in which month
lowest amount of patients have registered. All the patient medical and medication record will be
stored against this dimension model.
Invoice: The invoice will be generated for every payment a client will make. The invoice
will hold the amount of payment made against it. These amounts will be used to identify the
business benefits for a certain amount of time. All the invoice related information will be used
for auditing. Finance department of Vigour will be accessing all the invoice related information
for defining new strategies. Through analysing the invoices, the management can categorize
clients into different levels. The most loyal customers will be provided best offers and so on.
Employee: The employee dimension provides the information on the employee. The
employees are crucial part of business. Each of employee data are required to store for having
better idea about the business operations. The employees will be providing all their personal
information during joining. The information of the employees is also an important part of the
business. The organization will need to be in touch of the employee at all times. The information
will provide better understanding of an employee. This will offer an opportunity to Vigour for
improving the working atmosphere.
Condition: Getting constant update on the client condition is a crucial part of the care
management plan. This dimension will be providing information if the patient condition is
improving or not. The impact of the medication on patient condition.
Medication: Medication dimension provides all the information of the medicines. The
side effects of the medicine is required to know before prescribing it to a patient.

12DATA WAREHOUSING

13DATA WAREHOUSING
Bibliography:
Andersen, O., Thomsen, C. and Torp, K., 2018. SimpleETL: ETL Processing by Simple
Specifications.
Andersen, O., Thomsen, C. and Torp, K., 2018. SimpleETL: ETL Processing by Simple
Specifications.
Chapman, K. and Green, J.R., 2017. Analysts' Influence on Managers' Guidance. The
Accounting Review, 93(1), pp.45-69.
Chevalier, M., El Malki, M., Kopliku, A., Teste, O. and 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.
Dehdouh, K., Bentayeb, F., Boussaid, O. and 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).
Salama, A.A., El-Henawy, I. and Bondok, M.S., 2016. New Structure of Data Warehouse via
Neutrosophic Techniques. Neutrosophic Sets and Systems, 13.