Master of Business Analytics BUS5WB La Trobe Business School 1 BUS5WB ‐ Data Warehousing and Big Data Assignment 01: Data Warehouse Design Marks: 40% Assignment Type: Individual Due Date: 11:59PM Sunday 16th April 23rd April 2017 (LMS dropbox). The first assignment aims to test your knowledge and skills in data warehouse design. As we have discovered in lectures and from reading material, the design stage is crucial for the success of any warehouse and overall organisational BI strategy. The Case Vigour is a fictitious healthcare provider specialising in geriatrics. Its mission statement is to improve each and every client’s quality of life. This translates into two primary business objectives: promote health and wellbeing, diagnose and treat chronic disease conditions. Vigour operates on a subscription‐based model, where clients keen to obtain the health services pay an upfront membership fee and a monthly subscription. Having been in business for the past ten years, the CEO at Vigour is confident that the substantial accumulation of data in transaction systems can be capitalised to improve the effectiveness and efficiency of each business operation. He’s keen to implement an organisation‐wide data warehousing solution to explore this potential. He’s approached an analytics consultancy firm to start on this project. The business requirements analysis and feasibility study were completed on a positive note and now you have been recruited as the dimensional model specialist to complete the data warehouse design phase. Consultations Services offered by Vigour are modelled around a circle of support for each client. Clients can be individuals with a condition (or multiple conditions) or healthy individuals concerned about their health as they grow old. Potential clients are either referred by a GP or directly get in touch with Vigour. Clients keen to subscribe to Vigour services undergo an initial consultation with a general medical practitioner. Soon after, the client’s personal information is recorded in the consultation system. The general medical practitioner looks after the client during the entire consultation phase and he/she records the diagnoses, medical reports and any current medication. The first consultation will determine if a client requires further specialist consultation, medical diagnostic or treatment. In the first instance one or many consultations (depending on the number of conditions) are scheduled with relevant specialists. Specialists are under contract with the organisation and come in whenever there’s a requirement. In the case of medical diagnostic or clinical treatment a booking is made with one of the third party hospital partners to provide this treatment. Upon completing the procedure, the hospital gets back to the consultant with the diagnosis, cost and relevant reports. Based on the reports, a new specialist consultation may be scheduled. The client is offered the option to meet alternative and holistic health specialists during the consultation phase. Given the popularity of holistic medicine, client always prefer to meet with one of these specialists. Prior to exiting the consultation phase, the client meets with the general medical practitioner who reviews the entire diagnosis alongside recommended medication.Master of Business Analytics BUS5WB La Trobe Business School 2 Care plan management Soon after the consultation phase, the client is assigned to a care plan by the same general practitioner. The care plan is essentially an instrument to measure the client’s progress over time – it is similar to an electronic health record but carries specific information pertaining to the client’s illnesses and progress/recovery over time. The care plan has an estimated end date (which may change) and the management team looks after the client during this phase. Each client is assigned a carer who usually remains with the client from start to end. A client exits the care plan when the condition(s) have improved and are at a stage where they can be managed independently. The carer and the client meet at regular intervals (care plan consultations (CPC)) to review and update the care plan and also take any additional steps required. The frequency of CPCs and updates to the care plan vary depending on the client’s condition ‐ minimum weekly, monthly to bi‐monthly. At each CPC the carer records vital measurements of the client. This includes body mass index and a blood profile (blood glucose level, lipid profile, levels of vitamins etc) and condition specific markers such as eyesight, hearing and sodium levels. In addition to the measurements, the carer asks a series of questions to determine the client’s mental and emotional wellbeing. The client’s medication is also reviewed and any side‐effects, after‐effects are recorded. If the client’s condition has degraded since the last visit, the carer has to decide if it’s necessary to send the client back to consultation phase or in exceptional instances immediate dispatch to a hospital. Most often after every CPC the client meets with the resident dietician who goes through the client’s current diet plan and makes amendments if needed. All changes are noted on an electronic care plan log. Each care plan maintains overall numerical scores of the client’s progress towards achieving this goal. The carer decides when the scores can be increased or decreased. The scores are physical health (state of the illness (es)), mental health, client’s awareness of the illness(es) and client’s awareness of their diet. Pharmacy Vigour runs its own pharmacy to address the diverse types of medication required by its clientele. The pharmacy stocks medicine from different pharmaceutical companies for each type of medication. The quantity sold, selling price and cost price are recorded for each such type. The medication record is maintained separate to the care plan and looked after by the pharmacy team. This record maintains a one‐ to‐one link between the client and the medication, it does not record medical conditions. The consultation report initiates the pharmacy process and returns to be reviewed after each CPC. Billing As mentioned, clients obtain health services on a subscription basis. Hospital charges for diagnostics and clinical treatment as well as medication from the pharmacy are additional charges to the monthly subscription. Exceptional circumstances that may arise during the CPC meet‐ups are also additional expenses. The billing system maintains a record of both subscriptions and additional expenses for every client. Clients that choose to pay the subscription annually receive a 10% discount. Billing also maintains a record of the charges to each third party hospital partners for the services they provide. Vigour internal expenses, such as employee salaries and overheads are managed by Finance and not the Billing department. IT systems Each business function elaborated above has its own transaction system with a back‐end database. Snapshots of selected ER diagrams from these databases are provided below. They should aid you to understand the nature of the systems and the workflow at Vigour. It was observed that some of the database were not entirely normalised and at times use summary attributes to store a number of values (e.g. blood profile – glucose levels, lipid profile stored as a free text column).Master of Business Analytics BUS5WB La Trobe Business School 3 Consultations Care plan managementMaster of Business Analytics BUS5WB La Trobe Business School 4 Pharmacy BillingMaster of Business Analytics BUS5WB La Trobe Business School 5 A role‐play example of a client pathway within the organisation’s 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 he’s 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 he’s 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 a new project, 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. 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 client’s 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 rateMaster of Business Analytics BUS5WB La Trobe Business School 6 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 What you are required to do 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. 3. Design: Follow Kimball’s 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. Deliverables A professionally written report on the dimensional design activities undertaken for Vigour. This can be compiled as a series of topics of the abovementioned ‘to do’ list or formatted as a design document.  The report should be compiled in Microsoft Word, font size 11.  Report content should not exceed 8‐10 pages (including diagrams). Diagrams have to be in the main body as it is a design document. If the number of pages are insufficient, you may include the data dictionary as an appendix.  You are free to make any assumptions with justification. Include these in the report, where applicable.  Make realistic assumptions on any information (schema or business requirements) that may be missing in the above description.Master of Business Analytics BUS5WB La Trobe Business School 7 Rubric Criteria Pass Credit Distinction High Distinction Requirements 10 marks Identification of some decision‐ making needs and information requirements. Some effort made to determine the number of dimensional models. Identification of most decision‐ making needs and information requirements. Decent effort made to determine the number of dimensional models. Identification of all decision‐ making needs and information requirements. Good effort made to determine the number of dimensional models. A comprehensive effort in gathering decision‐making needs and information requirements. Optimal (not more not less) number of dimensional models identified. SQL 5 marks Minimal demonstration of SQL skills. Standard awareness of SQL skills. Good understanding of SQL and decision‐ making scenarios. SQL statements fulfil all expectations and decision‐making scenarios. Design 15 marks Lacking adherence to Kimball’s method. Minimal application of fact and dimension table techniques. Adhered to Kimball’s method. Fact and dimension table techniques somewhat applied. Proper adoption of Kimball’s method. Fact and dimension table techniques well applied. Extensive adoption of Kimball’s method. Correct and relevant application of fact and dimension table techniques. Demonstration 5 marks Minimal demonstration of each dimensional model and its role in effective decisions. Somewhat complete demonstration of each dimensional model and its role in effective decisions. Complete demonstration of each dimensional model and its role in effective decisions. Extensive demonstration of each dimensional model and its role in effective decisions. Novelty 5 marks No element of creativity. Some elements of creativity. Strong elements of creativity. Extensive creativity across entire design. l .