A role-play example of a client pathway within the organisations health services is given below. Ray is a 60 year old man keen to subscribe to Vigour health services. He has several medical conditions, diabetes, hypertension and glaucoma. In his first visit to Vigour he meets a general practitioner who goes through his condition history and current medication. He recommends Ray undergo several medical diagnostics (eye test, glucose profile and ECG) at a nearby hospital and also schedules consultations with a general physician and cardiologist. After couple of weeks for examinations and consolations hes back to meet the general practitioner. Again, the general practitioner goes through consultant recommendations, reviews his medication and places him in a care plan. After a week, Ray meets with his assigned carer who checks and records his biomarkers (blood profile, BMI). She also reviews his medication and notes down responses to a set of questions. She also records care plan measures for Ray. Next Ray is introduced to a dietician and they sit together to discuss the diet plan. The next care plan consultation is scheduled in two weeks time. The CEO has provided several examples of the types of information hes interested to extract from the warehouse. Try not to restrict your design to these examples, keep in mind the overall motivation of increased effectiveness and efficiency (and single version of truth) when designing the model. He also spoke at length of two new projects. 1) An online collaboration platform, conceptualised by the IT department. This online platform, still in its infancy, will facilitate communication and information exchange among clients with similar conditions. Some of the features he mentioned were a social network, blog posts and discussion forums. 2) Introducing wearable devices to continuously monitor each clients health and wellbeing. Every client will be given a wearable device with several sensors that track physiological measures, motion and other factors. Consultation -Number of clients by number of medical conditions (single-morbidity, multi-morbidity)

-Demographic distribution and types of medical conditions of clients who do not return after the first consultation

-Demographic distribution of clients who express interest to meet holistic medicine practitioners

-Number of clients with multiple conditions that require for clinical procedures

-Most reliable hospital partners

-Most cost-effective hospital partners

-Most dedicated general medical practitioners and medical specialists (with consistent performance) Care plan management -Successful and unsuccessful care plan figures by number of medical conditions of each client

-Demographic distribution of clients completing the care plan on-time

-Progress measures by client details compared by year

-Correlation between blood-gas measures and clients progress measures over time ?

-Types of medication closely associated with successful care plans

-Profile of clients whose health degraded to exceptional circumstances (hospitalisation, diagnostics) during a care plan period

-Most dedicated carers and their success rate

Pharmacy

Revenue by medication type by supplier

Quantity sold by customer demographics by medication type

Top ten medicines and suppliers

Most prescribed medicine by medical condition

Cost breakdown of the same medication by different suppliers

Top ten consistently in-demand medicine Billing -Revenue by year by medical condition

-Distribution of subscriptions over time by client demographics

-Types of subscriptions that require additional charges

-Demographics of customers paying annual

-Most active/ least active period of business during the year

Tasks 1. Requirements: Identify current and future decision-making needs and information requirements of the organisation. Determine the number of dimensional models required (with justification).

2. SQL: Compile SQL statements, based on given information and ER diagrams, to address five decision making needs identified above. Use of advanced SQL syntax is required as this task aims to assess your knowledge of SQL.

3. Design: Follow Kimballs dimension design process to create the identified models. Revise each model by applying the fact table and dimension table techniques. Provide a data dictionary (a description of each table in each model outlining the purpose, attributes and data types).

4. Demonstration: Demonstrate how each dimensional model can be used to respond to current and future business questions and decision-making scenarios. Articulate how the dimensional model can lead to effective decisions.

Please help me with this assignment, need any additional info please comment. Thank you.