Business Intelligence Solution for UnivesitatsKlinikum AACHEN.

Presented by:

Fernanda Castillo Almeida

Harika Janagama

Yakup Arslan

Business Consulting Masters 2011

Agenda

1. Introduction / Overview

2. Business Case

3. KPIs

4. Roadmap / Project Management.

5. Reports.  

6. Lessons Learned (Conclusion) 

• Health is a complex area and is influenced by many factors outside of the provision of health services.

• Hospitals must deliver high-quality care, comply with regulatory requirements and enhance patient satisfaction while trying to reduce costs and enhance efficiencies.

• Hospitals looking to improve their clinical, operational and financial operations need to understand previous, present and future opportunities.

• Business Intelligence provides this integrated view and enables decision makers to drill down and understand improvement opportunities within the hospital.

1. Introduction.

2. Business Case. • The Klinikum Aachen, full German name "Universitätsklinikum Aachen" (University Hospital Aachen), abbreviated UKA, formerly known as "Neues Klinikum", is a very large hospital in Aachen(Germany). It is part of the RWTH Aachen and contains its whole medical faculty

• The Klinikum Aachen contains many specialized clinics, theoretical and clinical institutes and other research facilities, lecture halls, schools for jobs in the medical field, and all facilities necessary for a hospital like a laundry and central sterilization

• Health is a complex area and is influenced by many factors outside of the provision of health services

• Hospitals looking to improve their clinical, operational and financial operations need to understand previous, present and future opportunities.

• Gaining this level of understanding requires information about the hospital. Business Intelligence provides this integrated view and enables decision makers to drill down and understand improvement opportunities within the hospital.

2. Business Case.

Universitätsklinikum Aachen

 Year 2010

annual impatients 48,000

annual outpatients 31,000professors 67scientific staff 1200non-scientific staff 5000clinics 33institutes 21Rooms 6600

2. 1. Objetives.

• To integrate data previously dispersed in clinical, financial and operational systems

• To provide users with more accurate insights into key areas such as service-line spending, quality of care and organizational performance.

• Developed a Business Intelligence solution for a

Healthcare management through useful KPI’s, resulting in improved Patient satisfaction and better care by reducing costs for the Management

3. KPIs.

Type Key KPIs Drill down KPI

CLINICAL  Diseases

Medical Treatment

1. Total of Patients per Disease.2. Most Common Diseases

  Death Rate 1. Total death rate per disease2. Total death rate per medical

procedure.OPERATIONAL Health Insurance 1. Number of patients that doesn’t

have a health insurance coverage .2. Number of Patient per Health

Insurance .

4. Road Map.

DimensionsDimensions

Patient ID

Status ID

Insurance ID

Disease ID

Medical Proceedures ID

Section ID

Rooms ID

4.1. Dimensions.

4.2. Start Schema.

Disease_Id

Time_IDPatient_IDType_IDInsurance_IDSection_IDDiseases_IDRooms_ID

Patiente NumberDuration Time

Time_IDPatient_IDType_IDInsurance_IDSection_IDDiseases_IDRooms_ID

Patiente NumberDuration Time

Fact Table

Time_ID

Section_Id

Patient_ID

Insurance_ID

Type_ID

Patient_IDNameCountryCityGenderDOB

Patient_IDNameCountryCityGenderDOB

Insurance_IDInsurance_Name

Insurance_IDInsurance_Name

Type_IDStatus

Section_IDSection_nameSection_IDSection_name

Time_IDDateHour

Medical_Procedures_Id

Mprocedures_IdMprocedures_Name

Disease_IdDisease_Name

Rooms_IDRoom_NumberDescription

Rooms_IDRoom_NumberDescription

Rooms_Id

4.3. Key Figures.

Key Figure Explanation

Discharge Time Time that patient is discharged from hospital.

Admission Time Time that patient is admitted in the hospital/ registration time

Duration Time Time that patient stays in hospital

Patient Number Number of patients per register.

4.4. 1. InfoObjects (KF).

4.4. 2. InfoObjects (CH).

4.5. Uploading Master Data from Excel Sheet .

4. 6. InfoCube.

4.7. Transformation.

4.8. Formula.

4.9. Data Flow.

4.10. Master and Transactional Data.

5. Reports. General ReportHCReport

Status  

Cal. year / month  

Calendar year  

Calendar day  

Key Figures  

Disease  

Medical Procedure  

Section  

Room  

Insurance  

Disease Medical Procedure Section Room Insurance Duration Time Patients Number

1 1 25 38 2 6 1

        Result 6 1

      Result   6 1

    Result     6 1

  11 2 14 7 13 1

        Result 13 1

      Result   13 1

    Result     13 1

  13 21 2 7 4 1

        Result 4 1

      Result   4 1

    29 35 7 13 1

        Result 13 1

      Result   13 1

    Result     13 2

  18 4 46 2 8 1

        Result 8 1

      Result   8 1

    Result     8 1

  38 13 28 7 1 1

        Result 1 1

      Result   1 1

    Result     1 1

5.2. Patient’s Health Insurance.

HC-Insurance

Key Figures  

Insurance  

Insurance Z33 KF Patients Numb

None 123

AOK 124

BKK 129

DEBEKA 116

TKK 142

IKK 112

Mawista 139

Barmenia 115

Overall Result 1,000

5.3. Patient’s Death Rate per Disease.

HCReport

Status  

Cal. year / month  

Calendar year  

Calendar day  

Key Figures  

Disease  

Medical Procedure  

Section  

Room  

Insurance  

Disease Patients Number

Neurology 39

Senior Health 39

Dictionary 36

Healthy Kids 36

Digestion 35

Cancer 33

Chronic Pain 33

Hearing & Ear 33

Skin 33

Arthritis 31

5.4. Patient’s most common Diseases.

HC Death Rate

Status 3

Cal. year / month  

Calendar year  

Calendar day  

Key Figures Patients Number

Disease  

Medical Procedure  

Section  

Room  

Insurance  

Disease Patients Number

Skin 13

Allergies 12

Arthritis 12

Blood Pressure 12

Dictionary 12

Hearing & Ear 11

Medications 11

Mental Health 11

Senior Health 11

Chronic Pain 10

5.4. Patient’s most common Diseases.

HCReport

Status  

Cal. year / month  

Calendar year  

Calendar day  

Key Figures  

Disease  

Medical Procedure  

Section  

Room  

Insurance  

Disease Medical Procedure Duration Time Patients Number

1 1 6 1

  11 13 1

  13 13 2

  18 8 1

  38 1 1

  42 4 1

  49 7 1

  53 3 1

  67 3 1

  71 6 1

  72 14 1

  73 3 1

  78 1 1

  86 9 1

  90 11 1

  92 1 1

  94 13 2

  95 15 1

  97 9 1

  104 0 1

  106 12 2

  108 4 1

  111 6 1

  Result 7.043 26

5.5. Average of Hospitalization time per Medical Procedure and Disease.

HCReport

Status  Cal. year / month  Calendar year  Calendar day  Key Figures  Disease  Medical Procedure  Section  Room  Insurance  

Disease Medical Procedure Duration Time Patients Number

Allergies Abdominoplasty (Tummy Tuck ( Abdominoplasty)) 6 1

 Adenosine Stress Test For Heart Disease (Coronary Artery Disease Screening Tests (CAD))

13 1

  Alexander Technique for Childbirth (Childbirth Class Options) 13 2

  ALT Test (Liver Blood Tests) 8 1

  Apgar Score 1 1

  Arthrocentesis (Joint Aspiration) 4 1

 Auditory Brainstem Response (Newborn Infant Hearing Screening)

7 1

  Baclofen Pump Therapy 3 1

  Biopsy Of Cervix (Colposcopy) 3 1

  Birth Control Patch (Hormonal Methods of Birth Control) 6 1

  Birth Control Pills (Hormonal Methods of Birth Control) 14 1

  Birthing Classes (Pregnancy: Birthing and Parenting Classes) 3 1

  Blood Liver Enzymes (Liver Blood Tests) 1 1

  Blood, Electrolytes (Electrolytes) 9 1

  Blood, Potassium (Electrolytes) 11 1

  Blood, Sodium (Electrolytes) 1 1

  Bone Density Scan 13 2

  Bone Marrow 15 1

  Bone Marrow Transplantation for Breast Cancer 9 1

  Breast Augmentation 0 1

  Breast Cancer Follow-Up Self-Exam 12 2

  Breast Implants (Breast Augmentation) 4 1

  Breast Reduction 6 1

  Result 7.043 26

5.6. Occupancy of Rooms.

HCReport

Status  Cal. year / month

01.2010; 02.2010; 03.2010; 04.2010; 05.2010; 06.2010

Calendar year  

Calendar day  

Key Figures Patients Number

Disease  Medical Procedure

 

Section  

Room  

Insurance  

Room Patients Number

10 17

48 16

45 15

50 15

39 14

11 13

17 13

30 13

37 13

38 13

6. Lessons Learnt.

• First understand the Dimensions and its Attributes carefully

• Don‘t forget to activate all the Objects immediately after creating

• Take the datas which are needed, according to the KPI‘s

• You can‘t do the right thing without doing the wrong.

Questions.