Comparing Healthcare Data Warehouse Approaches A Deepdive Evaluation

Comparing Healthcare Data Warehouse Approaches: A Deep-dive Evaluation of the Three Major Methodologies February 2014 Creative Commons Copyright

A Personal Experience with Healthcare • • Dear mother… A trip to the doctor… 22 © 2013 Health Catalyst | www. healthcatalyst. com

Healthcare Analytics Goal Why have an EDW? ● It is a means to a greater end ● It exists to improve: 1. The effectiveness of care delivery (and safety) 2. The efficiency of care delivery (e. g. workflow) 3. Reduce Mean Time To Improvement (MTTI) 3

Three Systems of Care Delivery Analytic System Deployment System Content System 4 Creative Commons Copyright

Population Health Management Mean # of Cases 1 box = 100 cases in a year Excellent Outcomes # of Cases Poor Outcomes Excellent Outcomes Poor Outcomes Focus On Inliers (“Tighten the Curve and Shift It to the Left”) • Strategy. Identify best practices through research and analytics and develop guidelines and protocols to reduce inlier variation • Result. Shifting the cases which lie above the mean (47+%) toward the excellent end of the spectrum produces a much more significant impact than focusing on the adverse outlier tail (2. 5%) © 2013 Health Catalyst | www. healthcatalyst. com 5

Healthcare Analytics Adoption Model Level 8 Personalized Medicine & Prescriptive Analytics Tailoring patient care based on population outcomes and genetic data. Fee-for-quality rewards health maintenance. Level 7 Clinical Risk Intervention & Predictive Analytics Organizational processes for intervention are supported with predictive risk models. Fee-for-quality includes fixed per capita payment. Level 6 Population Health Management & Suggestive Analytics Tailoring patient care based upon population metrics. Feefor-quality includes bundled per case payment. Level 5 Waste & Care Variability Reduction Reducing variability in care processes. Focusing on internal optimization and waste reduction. Level 4 Automated External Reporting Efficient, consistent production of reports & adaptability to changing requirements. Level 3 Automated Internal Reporting Efficient, consistent production of reports & widespread availability in the organization. Level 2 Standardized Vocabulary & Patient Registries Relating and organizing the core data content. Level 1 Enterprise Data Warehouse Collecting and integrating the core data content. Level 0 Fragmented Point Solutions Inefficient, inconsistent versions of the truth. Cumbersome internal and external reporting.

Polling Question What level would you to the healthcare analytic solutions with which you are most familiar? (levels 1 – 8)

An Analyst’s Time Too much time spent hunting for and gathering data rather than understanding and interpreting data Analyst’s or Clinician's Time Understanding the need Hunting for the data Gathering or compiling (including waiting for IT to run report or query) Waste Value-add Interpreting data Distribution of data 8 © 2013 Health Catalyst | www. healthcatalyst. com

HR – Desired State Authors Typical User Distribution Drillers • Authors or knowledge workers are scarce and in high demand – few users have both clinical knowledge AND access to tools and data • Large backlogs of analytic/report requests exist since underlying systems are too complex for the average user (users make analytic requests vs. self-service) Viewers • Create more knowledge workers by doing the following: • Expand data access (audit access vs. control access) • Simplify data structures (relational vs. dimensional) • Continue use of naming standards (intuitive vs. cryptic) • Providing better tools (metadata, ad hoc, etc. ) • Promote shift in culture by rewarding process knowledge discovery rather than punishing outliers 9 Authors or Knowledge Workers Drillers Ideal User Distribution for Continuous Improvement Viewers © 2013 Health Catalyst | www. healthcatalyst. com

® Comparison of prevailing approaches © 2013 Health Catalyst | www. healthcatalyst. com

Enterprise Data Model EDW FINANCIAL SOURCES (e. g. EPSi, Lawson, People. Soft) DEPARTMENTAL SOURCES (e. g. Apollo) Patient Provider Bad Debt Provider Survey Encounter Cost ADMINISTRATIVE SOURCES (e. g. API Time Tracking) Charge ENTERPRISE DATA MODEL Census Facility House Keeping Diagnosis Procedure Employee Time Keeping EMR SOURCE (e. g. Cerner) More Transformation Less Transformation Catha Lab PATIENT SATISFACTION SOURCES (e. g. NRC Picker) Enforced Referential Integrity © 2013 Health Catalyst | www. healthcatalyst. com 11

Enterprise Data Model – Still need Subject Area Marts EDW FINANCIAL SOURCES (e. g. EPSi, Lawson, People. Soft) DEPARTMENTAL SOURCES (e. g. Apollo) Patient Provider Bad Debt Provider Readmissions ADMINISTRATIVE SOURCES (e. g. API Time Tracking) Survey Encounter Cost Diabetes Charge ENTERPRISE DATA MODEL Census Facility Sepsis House Keeping Diagnosis Procedure Employee Time Keeping EMR SOURCE (e. g. Cerner) More Transformation Less Transformation Catha Lab PATIENT SATISFACTION SOURCES (e. g. NRC Picker) Enforced Referential Integrity © 2013 Health Catalyst | www. healthcatalyst. com 12

Bill of Materials Conceptual Model Product Supplier Order Customer Typical Analyses • Counts • Simple aggregations • By various dimensions © 2013 Health Catalyst | www. healthcatalyst. com 13

Star Schema Conceptual Model Dimension 1 Dimension 4 (Product) (Location) Typical Analyses • Transaction counts • Simple aggregations • By various dimensions Fact (Transaction) Dimension 3 Dimension 2 (Purchaser) (Date) © 2013 Health Catalyst | www. healthcatalyst. com 14

Vertical Summary Data Marts FINANCIAL SOURCES (e. g. EPSi, Lawson, People. Soft) DEPARTMENTAL SOURCES (e. g. Apollo) Regulatory Labor Productivity ADMINISTRATIVE SOURCES (e. g. API Time Tracking) Pregnancy Oncology Heart Failure Asthma Redundant Data Extracts Diabetes Census PATIENT SATISFACTION SOURCES (e. g. NRC Picker) EMR SOURCE (e. g. Cerner) More Transformation Dimensional Data Model Revenue Cycle Less Transformation © 2013 Health Catalyst | www. healthcatalyst. com 15

Adaptive Data Warehouse Metadata: EDW Atlas Security and Auditing Common, Linkable Vocabulary FINANCIAL SOURCES (e. g. EPSi, Peoplesoft, Lawson) Financial Source Marts DEPARTMENTAL SOURCES (e. g. Apollo) Departmental Source Marts Readmissions ADMINISTRATIVE SOURCES (e. g. API Time Tracking) Administrative Source Marts Patient Source Marts PATIENT SATISFACTION SOURCES (e. g. NRC Picker, Press Ganey) Sepsis EMR Source Marts EMR SOURCE (e. g. Cerner) Diabetes HR Source Mart Human Resources (e. g. People. Soft) More Transformation © 2013 Health Catalyst Less Transformation | www. healthcatalyst. com

Classic Star Schema Deficiencies • Resolution of many-to-many relationships • Not as much about counts of transactions • More about: • • • Events States of change over time Related states (e. g. co-morbidities, attribution) 17 © 2013 Health Catalyst | www. healthcatalyst. com

Sample Diabetes Registry Data Model Procedure Code Diagnosis History Procedure History Office Visit Vital Signs History Diabetes Patient Exam History Current Lab Result History Exam Type Lab Type Typical Analyses • How many diabetes patients do I have? • When was there last HA 1 C, LDL, Foot Exam, Eye Exam? • What was the value for each instance for the last 2 years? • What are all the medications they are on? • How long have they been taking each medication? • What was done at each of their visits for the last 2 years? • Which doctors have seen these patients and why? • List of all admissions and reason for admission? • What co-morbid conditions do these patient have? • Which interventions (diet, exercise, medications) are having the biggest impact on LDL, HA 1 C scores? © 2013 Health Catalyst | www. healthcatalyst. com 18

® Measurement System Exercise Webinar 19 © 2012 2013 Health Catalyst | www. healthcatalyst. com

The Enterprise Shopping Model Your Shopping List Enterprise Shopping Model Produce __ Apples __ Pears __ Tomatoes __ Carrots Meat __ Beef __ Ham __ Chicken __ Pork Dairy __ Celery __ Banana __ Melon __ Grapes __ Turkey __ Sausage __ Lamb __ Bacon __ Milk __ Eggs __ Cheese __ Cream Dry Goods __ Pasta __ Flour __ Sugar __ Soup __ 2% Milk __ Half & Half __ Yogurt __ Margarine Apples Sugar Yogurt Tomato Soup Beans Flour Hot dogs Milk Banana Turkey Noodles Lettuce __ Baking soda __ Rice __ Beans __ B. Sugar Additional purchases Eggs Flowers Tires Dry cleaning © 2013 Health Catalyst | www. healthcatalyst. com

Enterprise Data Model (Technology Vendors) EDW FINANCIAL SOURCES (e. g. EPSi, Lawson, People. Soft) DEPARTMENTAL SOURCES (e. g. Apollo) Patient Provider Bad Debt Provider Survey Encounter Cost ADMINISTRATIVE SOURCES (e. g. API Time Tracking) ENTERPRISE DATA MODEL Charge Census Facility House Keeping Diagnosis Procedure Employee Time Keeping EMR SOURCE (e. g. Cerner) More Transformation Less Transformation Catha Lab PATIENT SATISFACTION SOURCES (e. g. NRC Picker) Enforced Referential Integrity © 2013 Health Catalyst | www. healthcatalyst. com 21

Using a dimensional model in Healthcare is kind of like shopping for data like this … 22 © 2013 Health Catalyst | www. healthcatalyst. com

23 © 2013 Health Catalyst | www. healthcatalyst. com

The Dimensional Shopping Model Trip #1 to the Store Trip #2 to the Store Dimensional Shopping Model - Cookies Dairy __ 4 eggs __ 2 c shortening Dimensional Shopping Model - Cake Dry Goods Dairy Dry Goods __ ½ cup of butter __ ½ cup milk __ 2 eggs __ 1 c sugar __ 2 c brown sugar __ 2 t baking soda __ 2 t vanilla __ 1 t salt __ 4 -5 c all-purpose flour __ 4 cups chocolate chips __ 1 cup white sugar __ 1 ½ cups all-purpose flour __ 2 teaspoons vanilla extract __ 1 ¾ teaspoon baking powder How many recipes to do you need to make? 24 © 2013 Health Catalyst | www. healthcatalyst. com

Dimensional Data Model (Healthcare Point Solutions) FINANCIAL SOURCES (e. g. EPSi, Lawson, People. Soft) DEPARTMENTAL SOURCES (e. g. Apollo) Regulatory Labor Productivity ADMINISTRATIVE SOURCES (e. g. API Time Tracking) Pregnancy Revenue Cycle Oncology Heart Failure Asthma Redundant Data Extracts Diabetes Census PATIENT SATISFACTION SOURCES (e. g. NRC Picker) EMR SOURCE (e. g. Cerner) More Transformation Dimensional Data Model Less Transformation © 2013 Health Catalyst | www. healthcatalyst. com 25

The Adaptive Shopping Model Additional Apples Tomato Soup Flour Milk Turkey Lettuce Sugar Beans Hot dogs Banana Noodles Yogurt Get eggs Buy flowers Get tires rotated Pick up dry cleaning • • Store: _______________ __ ______________ __ ______________ Initial List • __ ______________ __ ______________ • • • And Even More • • • 26 Buy a Christmas tree Baking Powder Baking Soda Buy a new couch Get oil change Chocolate Chips Buy paint and painting supplies Buy yarn and knitting supplies Vanilla extract Buy a set of pots and pans © 2013 Health Catalyst | www. healthcatalyst. com

Shopping List Revisited Initial List Additional Apples Tomato Soup Flour Milk Turkey Lettuce Sugar Beans Hot dogs Banana Noodles Yogurt Get eggs Buy flowers Get tires rotated Pick up dry cleaning • • • Once you are home can you make these recipes? And Even More • • • Buy a Christmas tree Baking Powder Baking Soda Buy a new couch Get oil change Chocolate Chips Buy paint and painting supplies Buy yarn and knitting supplies Vanilla extract Buy a set of pots and pans 27 Cake: 1 cup white sugar 1 ½ cups all-purpose flour 2 teaspoons vanilla extract 1 ¾ teaspoon baking powder ½ cup of butter ½ cup milk Cookies: 2 eggs 1 cup (2 sticks) butter, softened 2 large eggs 3/4 cup white sugar 2 1/4 cups all-purpose flour 1 teaspoon vanilla extract 1 teaspoon salt 1 teaspoon baking soda 2 cups chocolate chips © 2013 Health Catalyst | www. healthcatalyst. com

Adaptive Data Warehouse Metadata: EDW Atlas Security and Auditing Common, Linkable Vocabulary FINANCIAL SOURCES (e. g. EPSi, Peoplesoft, Lawson) Financial Source Marts DEPARTMENTAL SOURCES (e. g. Apollo) Departmental Source Marts Readmissions Administrative Source Marts Diabetes Patient Source Marts ADMINISTRATIVE SOURCES (e. g. API Time Tracking) PATIENT SATISFACTION SOURCES (e. g. NRC Picker, Press Ganey) Sepsis EMR Source Marts EMR SOURCE (e. g. Cerner) HR Source Mart Human Resources (e. g. People. Soft) More Transformation © 2013 Health Catalyst Less Transformation | www. healthcatalyst. com

® Late-binding™ Deeper Dive 29 © 2012 2013 Health Catalyst | www. healthcatalyst. com

Data Modeling Approaches Corporate Information Model Early Binding Popularized by Bill Inmon and Claudia Imhoff I 2 B 2 Popularized by Academic Medicine Star Schema Popularized by Ralph Kimball Data Bus Popularized by Dale Sanders File Structure Association Popularized by IBM mainframes in 1960 s Reappearing in Hadoop & No. SQL Late Binding © 2013 Health Catalyst | www. healthcatalyst. com 30

Origins of Early vs Late Binding • Early days of software engineering ● Tightly coupled code, early binding of software at compile ● ● time Hundreds of thousands of lines of code in one module, thousands of function points Single compile, all functions linked at compile time If one thing breaks, all things break Little or no flexibility and agility of the software to accommodate new use cases © 2013 Health Catalyst | www. healthcatalyst. com 31

Origins of Early vs Late Binding • 1980 s: Object Oriented Programming ● Alan Kay, Universities of Colorado & Utah, Xerox/PARC ● Small objects of code, reflecting the real world ● Compiled individually, linked at runtime, only as needed ● Agility and adaptability to address new use cases • Steve Jobs: Ne. XT Computing ● Commercial, large-scale adoption of Kay’s concepts ● Late binding – or as late as practical – becomes the norm ● Maybe Jobs’ largest contribution to computer science © 2013 Health Catalyst | www. healthcatalyst. com 32

Data Binding in Analytics ● Atomic data can be “bound” to business rules about that data and to vocabularies related to that data ● Vocabulary binding in healthcare – – Unique patient and provider identifiers Standard facility, department, and revenue center codes Standard definitions for sex, race, ethnicity ICD, CPT, SNOMED, LOINC, Rx. Norm, RADLEX, etc. ● Binding data to business rules – – – Length of stay Patient attribution to a provider Revenue and expense allocation and projections to a department Data definitions of general disease states and patient registries Patient exclusion criteria from population management Patient admission/discharge/transfer rules © 2013 Health Catalyst | www. healthcatalyst. com 33

Analytic Relations The key is to relate data, not model data Core Data Elements Charge Code CPT Code Date & Time DRG code Drug code Employee ID Employer ID Encounter ID Sex Diagnosis Code Procedure Code Department ID Facility ID Lab code Patient type Patient / member ID Payer / carrier ID Postal code Provider ID High Value Attributes About 20 data attributes account for 90% of healthcare analytic use cases Vocab in Source System 1 Vocab in Source System 2 Vocab in Source System 3 Highest value area for standardizing vocabulary © 2013 Health Catalyst | www. healthcatalyst. com 34

® Six Points to Bind Data Analysis Internal Source Data Content Source System Analytics Clinical Financial Supplies HR HR Others Customized Data Marts Visualization Disease Registries Qlik. View, Tableau Materials Management Microsoft Access Compliance Measures Web Applications Clinical Events Excel Operational Events SAS, SPSS Research Registries et al. 5 6 External State Academic 1 2 3 4 Business Rule and Vocabulary Binding Points Low volatility = Early binding High volatility = Late binding © 2013 Health Catalyst | www. healthcatalyst. com 35

® Binding Principles & Strategy 1. Delay Binding as long as possible…until a clear analytic use case requires it 2. Earlier binding is appropriate for business rules or vocabularies that change infrequently or that the organization wants to “lock down” for consistent analytics 3. Late binding in the visualization layer is appropriate for “what if” scenario analysis 4. Retain a record of the bindings from the source system in the data warehouse 5. Retain a record of the changes to vocabulary and rules bindings in the data models of the data warehouse © 2013 Health Catalyst | www. healthcatalyst. com 36

® Thank you! 37 © 2012 2013 Health Catalyst | www. healthcatalyst. com