Data Warehousing for Vigour: Requirements, SQL, Design and Demonstration

Verified

Added on  2023/06/13

|13
|1465
|487
AI Summary
This article discusses the requirements, SQL, design and demonstration of data warehousing for Vigour, an organization that requires accurate data to attract new patients, gather resources, and increase patient treatment quality. The article includes a table of contents, data dictionary, and SQL queries for various dimensions. The dimension models include client, invoice, employee, condition, and medication. The article also includes a demonstration of how the dimension models can be used for decision making.
tabler-icon-diamond-filled.svg

Contribute Materials

Your contribution can guide someone’s learning journey. Share your documents today.
Document Page
Running head: DATA WAREHOUSING
Data Warehousing
Name of the Student
Name of the University
tabler-icon-diamond-filled.svg

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
1DATA WAREHOUSING
Table of Contents
1. Requirements:..............................................................................................................................2
2. SQL..............................................................................................................................................3
3. Design:.........................................................................................................................................5
4. Demonstration:..........................................................................................................................11
Bibliography:.................................................................................................................................12
Document Page
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
Document Page
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;
tabler-icon-diamond-filled.svg

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
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;
Document Page
5DATA WAREHOUSING
3. Design:
Figure 1: Fact and Dimension Table of Vigour Warehouse
(Source: Created by Author)
Document Page
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
tabler-icon-diamond-filled.svg

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
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
Document Page
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
Document Page
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
tabler-icon-diamond-filled.svg

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
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
Document Page
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.
Document Page
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.
chevron_up_icon
1 out of 13
circle_padding
hide_on_mobile
zoom_out_icon
logo.png

Your All-in-One AI-Powered Toolkit for Academic Success.

Available 24*7 on WhatsApp / Email

[object Object]