Data Warehousing: Requirements, SQL, Design and Demonstration

This article discusses the requirements, SQL, design and demonstration of data warehousing for Vigour, an online library for study material with solved assignments, essays, dissertation etc. It covers the patient data, invoices, medication, dimension models, SQL queries, and data dictionary. The article also explains how the selected dimension models will fulfil the information requirement of different decision-making needs.

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:........................................................................................................................................................................4
4. Demonstration:............................................................................................................................................................9
Bibliography:................................................................................................................................................................10

2DATA WAREHOUSING
1. Requirements:
Current and Future Decision Making Needs: The biggest decision making need is the Patient purchase
related information. The organization will identify which Patient has done which treatment. Moreover, how much
they have paid for the treatment. Vigour will be using that information to make decisions of discounts. In order to
promote business, the organization must have an idea about which customer need what treatment and how much
they can pay. The purchase history of customers is essential for financial decisions. Furthermore, this information is
also extremely vital for the marketing department.
Information Description
Patient Data The patient data are very essential for executing the business operations. The
organization may send letters or invoices to client through post to the registered
address. Vogiur can use the registered email or phone number to contact. It is useless
to say how important the name of the client is. All the bills and invoices will be
generated with the name of the client.
Patient Condition Data The organization will look forward to improve their medical environment and
treatments. In order to that a vast amount of real life data is required to be evaluated.
The patient condition data will be the biggest asset in improving treatment
procedures.
Invoices The invoices related data will be used for creating various reports and audits. The
organization will be calculating their profit using the invoice data. The invoice data
are also very important for financial department.
Medication The system will record all the medicine data prescribed to the patient. The name of
the medicine and supplier name is essential. The system will record the Sid effects of
the medicine so that precautions can be taken in crucial situation.
Dimension Model
Name
Justification
Patient This dimension model will hold personal details of the Patient.
Invoice This dimension model will hold all the payment related data
Employee This dimension model will hold all the details of the employee
Condition This dimension model will hold the information of condition against every patient.
Medication This dimension model holds the information of all the medicines Vigour purchases
and prescribes.
Consultation The consultation provided to every patient will be needed for better decision making.
Date Will store every process against a proper timeline
2. SQL:
Query No 1 SQL Query
1 Select C.Name from Client C Where C.DateOfBirth>1980
2 Select H.Name, Sum(HB.Total) From Hospital H Inner Join HospitalBill HB on

3DATA WAREHOUSING
H.HospitalID=HB.HospitalID Group By H.Name;
3 Select C.Name, MC.*, From Patient as C inner join PatientCondition as CC on
C.PatientID = CC.PatientID inner join MedicalCondition as MC on CC.ConditionID
= MC.ConditionID Order By ConsultationDate
4 Select C.Name, CC.CurrentMedication, MC.Symptoms From Patient C inner join
PatientCondition CC on C.PatientID=CC.PatientID inner join MedicalCondition MC
on CC.ConditionID=MC.ConditionID Where C.Gender=Female Order By C.Name;

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

5DATA WAREHOUSING
Data Dictionary:
Dimension Table: Dim_Condition
Attribute Description Data Type Constraint Purpose
Condition_ID The primary key
will allow the
database to
separate each of
the rows from one
another.
Varchar (80) Primary Give each row of
the table unique
identity
CurrentState1 Initial present
state of the Patient
Varchar (200) NO To analyse the
initial Patient
state during
treatment
CurrentState2 Second storage of
Patient state
Varchar (200) NO To identify and
evaluate the
second entry on
Patient medical
state
CurrentState3 Final information
on Patient state
Varchar (200) NO To identify and
evaluate the final
entry on Patient
medical state
CurrentMedication Present
medication
prescribed to
Patient
Varchar (200) NO To identify and
evaluate all the
medicines that are
prescribed to the
Patient
SignificantEvents If any special
situation occured
Varchar (200) NO To evaluate the
events that are
special
MedicalConditionName The name of the
condition
Varchar (80) NO To store the name
of the condition
MedicalConditionType In which category
the condition
reside
Varchar (10) NO To identify the
type of the
condition and
process treatment
MedicalConditionSymptoms The symptoms of
the condition
Varchar (200) NO To identify if the
condition is
critical or normal
Dimension Table: Dim_Medication
Attribute Description Data Type Constraint Purpose
Medication_ID The primary key Varchar (80) Primary Give each row of

6DATA WAREHOUSING
will allow the
database to separate
each of the rows
from one another.
the table unique
identity
Supplier_name The name of the
medicine supplier
Varchar (80) NO Supplier name is
crucial for logistics
Unit_Cost Cost for each unit
of medicine
Decimal (12,2) NO To store the price of
medicine
Dimension Table: Dim_Date
Attribute Description Data Type Constraint Purpose
Date_ID The primary key
will allow the
database to separate
each of the rows
from one another.
Varchar (80) Primary Give each row of
the table unique
identity
Date The date is stored Daye NO To assign a process
a particular date
Day The name of the day
for a specific date
Varchar (10) NO 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 NO To find the number
of the day
Month The numerical or
alphabetical
representation of a
month
Month NO The date will be
assigned for
individual months
Month_Name The name of the
mont
Varchar (10) NO The name of the
month like January
Quarter_Name The quarter like
first, second and
more
Varchar (10) NO To search data or
create report for a
large period of time
Year The year is stored Year NO All the dates,
months and quarters
will be separated by
the year attribute
Dimension Table: Dim_Patient

7DATA WAREHOUSING
Attribute Description Data Type Constraint Purpose
Patient_ID The primary key
will allow the
database to separate
each of the rows
from one another.
Varchar (80) Primary Give each row of
the table unique
identity
Name The given and last
name of the Patient
Varchar (80) NO Identify the name of
the Patient
Date_Of_Birth The date on which
Patient was born
Date NO The birth date of the
Patient assist in
various decision
making
Gender Identify to which
sex the Patient
belongs to
Varchar (6) NO The gender assist in
identifying what
kind of consultation
to be offered
MartialStatus It stores the martial
status of the Patient
Varchar (80) NO Marital status is
required for various
decision making
needs
HomeAddress The home address
will be default
address
Varchar (80) NO Delivering the bill
to the default
address
WorkAddress The optional
address will be
work address
Varchar (80) NO In case delivery
fails, bill will be
sent to the optional
address
Occupation The current job of
the Patient
Varchar (150) NO Consultation is done
based on the
working nature of
the Patient
Dimension Table: Dim_Consultation
Attribute Description Data Type Constraint Purpose
Consultation_ID The primary key
will allow the
database to
separate each of
the rows from
one another.
Varchar (80) Primary Give each row of
the table unique
identity
Consultation_Outcome The result of
consultation
Varchar (200) NO To identify the
outcome of the
consultation
Diagnostic_Outcome The result of Varchar (200) NO To see the impact

8DATA WAREHOUSING
diagnostic of the diagnostic
on the Patient
Specialist_Outcome The result a
specialist input
against each
consultation
Varchar (200) NO The specialist
outcome servers
additional
information for
treatment
Consultation_Event_Outcome The final
outcome of the
consultation
Varchar (200) NO This outcome is
very essential
Dimension Table: Dim_Employee
Attribute Description Data Type Constraint Purpose
Empployee_ID The primary key
will allow the
database to separate
each of the rows
from one another.
Varchar (80) Primary Give each row of
the table unique
identity
Name The name of the
employee
Varchar (80) NO To store the name
of the employee
Date_Of_Birth Date of birth of the
employee
Date NO To collect the birth
date of employee
Gender Whether the
employee is male or
female
Varchar (10) NO To determine the
sex of the employee
Date_of_Joining The date on which
the employee joined
the organization
Date NO Poetize the
employees
Dimension Table: Dim_Invoice
Attribute Description Data Type Constraint Purpose
Invoice_ID The primary key
will allow the
database to separate
each of the rows
from one another.
Varchar (80) Primary Give each row of
the table unique
identity
Date The date on which
invoice is generated
Date NO To identify the
business profits
Sub_Total Total expenses of
the treatment
Decimal (12,2) NO To identify the
business profits
Discount Discount a Patient INT NO To make the Patient

9DATA WAREHOUSING
gets loyal
Total Total amount after
discount
Decimal (12,2) NO The amount
individual Patients
pay against
treatment
4. Demonstration:
Dimension Model Name How decision making need will be fulfilled
Dim_Patient The selected dimension model will be able to fulfil the information requirement
of customer relationship management decision making.
Dim_Invoice The selected dimension model will be able to fulfil the information requirement
of financial decision making.
Dim_Employee The selected dimension model will be able to fulfil the information requirement
of work place related decision making.
Dim_Condition The selected dimension model will be able to fulfil the information requirement
of treatment related decision making.
Dim_Medication The selected dimension model will be able to fulfil the information requirement
of treatment related decision making.
Dim_Condition The selected dimension model will be able to fulfil the information requirement
of treatment related decision making.
Dim_Date The selected dimension model will be able to fulfil the information requirement
of all the decision making.

10DATA 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).