Semester 1, 2017 BUS5WB Data Warehousing and Big Data Guide to Assignment 01Context • 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. • CEO at Vigour wants to improve the effectiveness and efficiency of each business operation. • He’s keen to implement an organisation-wide BI 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 brought on-board as the dimensional model specialist to complete the data warehouse design phase. 2Information • Business functions • Consultations • Care plan management • Pharmacy • Billing • IT systems • ER Diagrams (entities, attributes and relationships) • An example scenario • Information the CEO is interested in • By business function 3Requirements • 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. Design: Follow Kimball’s dimension design process to create these 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). 3. 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. 4Requirements • What you are required to do 1. Requirements: Identify current and future decision-making needs and information requirements of the organisation. Understand the CEO’s information needs Focus on business value Synthesise and aggregate (business function vs business process) Determine the number of dimensional models required (with justification). Will there be a fixed number? (business function vs business process vs info needs) 5Requirements 2. Design: Follow Kimball’s dimension design process to create these 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). 6Data dictionary – examples 7 Column Data type Description DateKey Integer Surrogate primary key Date Date Full date Day Integer Number of the day in the month (e.g. 1..31) Month Integer Number of the month in the year (e.g. 1..12) Year Integer Number of the year (e.g. 2014) Week Integer Number of the week in the year (e.g. 1..52) Quarter Integer Calendar quarter (e.g. 1..4) Column Data type Description ClientKey Integer Surrogate primary key ClientID Integer Transaction system ID ClientName Text Client’s full name (e.g. John D. Smith) ClientDateOfBirth Date Client’s date of birth ClientGender Varchar(1) Client’s gender (e.g. Male, Female) ClientMaritalStatus Varchar(8) Client’s marital status (e.g. Single, Married, Unknown)Requirements • Demonstration: • Demonstrate how each dimensional model can be used to respond to current and future business questions and decision-making scenarios. • Bring part 1 and part 2 together. • Articulate how the dimensional model can lead to effective decisions. • Two or more sample scenarios of using the warehouse for CEO information needs. • Any additional strategic information that can be extracted? (analytical thinking) 8Structure • Executive Summary • Introduction • Requirements • Design and Demonstration • All Dimensional models and outcomes from techniques • Data Dictionary • References 9