Olympus Data Warehouse Design

Olympus Data Warehouse Design

The
assignment aims to test your knowledge of dimensional modelling and
skills in data warehouse design. As we have discovered in lectures
and related readings, the design stage is crucial for the success of
any warehouse and overall organisational BI strategy.

The
Case

Olympus
is a professional
sport services provider, on a mission to enhance every client’s
performance potential, and contribute positively to their respective
sporting careers. This translates into three business objectives:
increase sporting performance, reduce risk of injury and improve
overall fitness.

Olympus
operates on a subscription-based model, where an athlete (or
sportsperson) interested to obtain sport management services pay an
upfront membership fee and a monthly subscription. Having been in
business for the past ten years, the CEO at Olympus is confident that
the substantial accumulation of data in operational systems can be
capitalised to improve the effectiveness and efficiency of each
business operation. She is keen to implement an organisation-wide
data warehousing solution to explore this potential. She has
approached an analytics consultancy firm to start on this project.
The business requirements analysis and feasibility study were
completed on a positive note.

You
have been recruited as the dimensional model specialist to complete
the data warehouse design phase.

Consultations

Services
offered by Olympus
are modelled around
the concept of a sphere of sport services for each client.

Potential
athletes are either referred by a sporting body, a coach, or directly
get in touch with Olympus.
Clients interested to subscribe to Olympus
services undergo an
initial consultation with a sport performance specialist. Soon
afterwards, the client’s personal information is recorded in the
consultation system. The specialist looks after the client during the
entire consultation phase and he/she records current performance
benchmarks, past training, nutrition plans, intended performance
outcomes, and any current injuries/medication. The first consultation
will determine if a client requires further consultation on
nutrition, injury or medication. 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 is a requirement.

In
the case of injury or medical imaging requirement a booking is made
with one of the third party hospital partners to provide this
diagnostic. Upon completing the procedure, the hospital reverts to
the consultant with the diagnosis, cost and relevant reports. Based
on the reports, a new specialist consultation may be scheduled. The
client is also offered the option to meet allied health and holistic
health specialists during the consultation phase. Given the
popularity of allied and holistic medicine, clients always prefer to
meet with them. Prior to exiting the consultation phase, the client
meets with the same sport performance specialist who reviews the
entire diagnosis alongside recommended actions.

Performance
plan management

Following
the consultation phase, the client is assigned a performance plan by
the specialist. The performance plan is essentially an instrument to
measure the client’s progress over time – pertaining to sporting
performance, risk of injury, strength and fitness, and nutrition as
well as progress/recovery over time. The performance plan has an
estimated end date (which may change) and the core services team
looks after the client during this phase. Each client is assigned a
trainer/coach who remains with the client from start to end. A client
exits the performance plan when performance outcomes are met, or
benchmarks have improved and/or risk of injury has decreased and are
at a stage where they can be managed independently. The coach and
client meet at regular intervals (performance plan consultations
(PPC)) to review and update the plan and also account for any
additional steps. The frequency of PPCs and updates vary depending on
the client’s progress – minimum weekly, monthly to bi-monthly.

At
each PPC the trainer records vital measurements of the client. This
includes physiological measures, clinical metrics (blood glucose
level, lipid profile, levels of vitamins etc) and more importantly
performance markers of the corresponding sport, status of injuries
(if any) and scores of several strength and fitness tests. 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. Most often after every PPC the client meets with the
resident dietician for a walk-through of current nutrition plans and
makes amendments if needed. All changes are noted on an electronic
PPC log. Each performance plan maintains overall numerical scores of
the client’s progress towards achieving corresponding goals. The
trainer determines increase or decrease to each score. The scores are
performance benchmark/s, risk of injury (or injuries), strength and
fitness, and levels of nutrition.

Wellness
centre

Olympus
runs its own wellness centre to provide premium wellness services for
its clientele. Sport injury rehabilitation, physiotherapy, massage,
spa and sauna, yoga retreats, meditation retreats and leadership
workshops are the primary wellness services offered in-house.
Non-listed services can also be requested, and for these Olympus
contracts third party providers.

For
each premium service requested, basic transactional information as
well as customer feedback and comments are recorded. This premium
service record is maintained separate to the PPC and looked after by
the Wellness centre. It maintains a one-to-one link between the
client and the premium services requested.

Billing

As
mentioned, clients obtain sport management services on a subscription
basis. Hospital charges for diagnostics and clinical treatment as
well as services from the Wellness centre are additional charges to
the monthly subscription. Exceptional circumstances that may arise
during the PPC meet-ups are further 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 partners for the services
they provide. Olympus 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. These should help you to
understand the nature of the systems and the workflow at Olympus. It
was observed that some of the database were not entirely normalised
and at times use summary attributes to store a number of values.

Consultations

Performance
plan management

Wellness
centre

Billing

A
role-play example of a client pathway within the organisation’s
health services is given below.

Serena
is a professional tennis player who has subscribed to Olympus sport
services. She has two sport-related injuries, on her wrist and ankle.
Serena’s first visit to Olympus she meets a sport performance
specialist who goes through her sporting track record, injury record,
level of fitness and current nutrition and medication. She recommends
Serena undergo several medical diagnostics at a nearby hospital
facility and also schedules consultations with a holistic orthopedic
clinic. After couple of weeks for examinations and consultations she
returns for a subsequent meeting with the sport performance
specialist. Again, the specialist goes through consultant
recommendations, reviews her records and places her in a performance
plan, based on her expected goals and benchmarks in tennis.

A
week later, Serena meets with her assigned trainer/coach who checks
and records performance, injury and fitness levels. The trainer
completes a further assessment and generates a training schedule for
both on-site and off-site training routines. She is also introduced
to the rest of the training team who will support her throughout the
performance plan at Olympus. Serena is introduced to a sports
nutritionist and they sit together to discuss the nutrition plan. The
next performance plan consultation is scheduled for two weeks.

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 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 performance expectations and those who have achieved similar
goals. Some of the features he mentioned were a social network, blog
posts and discussion forums.

2)
Introducing wearable devices to continuously monitor each client’s
performance and fitness.

Consultation


Number
of clients by sport and playing position


Demographic
distribution and types of injuries of clients who do not return after
the first consultation


Demographic
distribution of clients who express interest to meet allied health
practitioners


Number
of clients with multiple injuries that require for diagnostics


Most
reliable hospital partners


Most
cost-effective hospital partners


Performance
specialists and trainers demonstrating consistently high performance

Performance
plan management


Successful
and unsuccessful performance plan figures by sport for each client


Demographic
distribution of clients completing the performance plan on-time


Progress
measures by client details compared by year


Correlation
between performance, fitness, nutrition – over time


Types
of nutrition plans closely associated with successful performance
plans

Wellness
centre


Revenue
by wellness service


Top
performing employees at wellness centre

• Top
ten wellness services requested


Most frequently outsources wellness
service

Billing


Revenue by year by sport


Distribution of subscriptions over time
by client demographics

•Types
of subscriptions that require additional charges


Demographics of customers paying annual
upfront fee


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
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 Olympus. This can be compiled as a series of topics of
the abovementioned ‘to do’ list or formatted as a design
document.