Identify current and future decision-making needs and information requirements of the organisation. Determine the number of dimensional models required (with justification).
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 |
|
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 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; |
Figure 1: Fact and Dimension Table of Vigour Date Warehouse
(Source: Created by Author)
|
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 will allow the database to separate each of the rows from one another. |
Varchar (80) |
Primary |
Give each row of 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 |
||||
|
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 diagnostic |
Varchar (200) |
NO |
To see the impact 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 gets |
INT |
NO |
To make the Patient loyal |
|
Total |
Total amount after discount |
Decimal (12,2) |
NO |
The amount individual Patients pay against treatment |
|
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. |
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).
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Contact Essay is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.
Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Essay Writing Service Works
First, you will need to complete an order form. It's not difficult but, in case there is anything you find not to be clear, you may always call us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order form
Once we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignment
As soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download