Comparing Healthcare Data Warehouse Approaches: A Deep-dive Evaluation of the Three Major Methodologies

1. Comparing Healthcare Data Warehouse Approaches:A Deep-dive Evaluation of the Three Major Methodologies February 2014

2. A Personal Experience with Healthcare Dear mother… A trip to the doctor…

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

4. Three Systems of Care Delivery

5. Population Health Management Mean 1 box = 100 cases in a year # of Cases # of Cases Poor Outcomes Excellent 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%) 5

6. Healthcare Analytics Adoption Model

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

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 Waste Gathering or compiling (including waiting for IT to run report or query) Value-add Interpreting data Distribution of data

9. Authors HR – Desired State • Authors or knowledge workers are scarce and in high demand – few users have bothclinical 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) Drillers Typical User Distribution Viewers Authors or Knowledge Workers • 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 Ideal User Distribution for Continuous Improvement Drillers Viewers

10. Comparison of prevailing approaches

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

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

13. Bill of Materials Conceptual Model • Typical Analyses • Counts • Simple aggregations • By various dimensions Product Supplier Order Customer

14. Star Schema Conceptual Model Dimension 1 (Product) Dimension 4 (Location) Fact (Transaction) • Typical Analyses • Transaction counts • Simple aggregations • By various dimensions Dimension 3 (Purchaser) Dimension 2 (Date)

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

16. Adaptive Data Warehouse Metadata: EDW AtlasSecurity and Auditing FINANCIAL SOURCES (e.g. EPSi, Peoplesoft, Lawson) DEPARTMENTAL SOURCES (e.g. Apollo) Common, Linkable Vocabulary FinancialSource Marts DepartmentalSource Marts Readmissions AdministrativeSource Marts PatientSource Marts PATIENT SATISFACTION SOURCES (e.g. NRC Picker, Press Ganey) ADMINISTRATIVE SOURCES (e.g. API Time Tracking) Diabetes Sepsis EMR Source Marts HRSource Mart EMR SOURCE (e.g. Cerner) Human Resources (e.g. PeopleSoft) More Transformation Less Transformation

17. Classic Star Schema Deficiencies • Resolution of many 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)

18. Sample Diabetes Registry Data Model Procedure Code Diagnosis Code • Typical Analyses • How many diabetes patients do I have? • When was there last HA1C, 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, HA1C scores? Diagnosis History Procedure History Office Visit Diabetes Patient Vital Signs History Exam History Current Lab Result Lab Result History Exam Type Lab Type

19. Measurement System ExerciseWebinar

20. The Enterprise Shopping Model Your Shopping List Apples Tomato Soup Flour Milk Turkey Lettuce Sugar Beans Hot dogs Banana Noodles Yogurt Additional purchases E n t e r p r i s e S h o p p i n g M o d e l Produce Dairy Eggs Flowers Tires Dry cleaning __ Milk __ Eggs __ Cheese __ Cream __ 2% Milk __ Half & Half __ Yogurt __ Margarine __ Apples __ Pears __ Tomatoes __ Carrots __ Celery __ Banana __ Melon __ Grapes Meat DryGoods __ Turkey __ Sausage __ Lamb __ Bacon __ Beef __ Ham __ Chicken __ Pork __ Bakingsoda __ Rice __ Beans __ B. Sugar __ Pasta __ Flour __ Sugar __ Soup

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

22. Using a dimensional model in Healthcareis kind of like shopping for data like this …

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

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

26. The Adaptive Shopping Model 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 A d a p t i v e S h o p p i n g M o d e l Store: _____________________________ 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. Shopping List Revisited Initial List Additional Once you are home can you make these recipes? • 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 Cake: 1 cup white sugar 1 ½ cups all-purpose flour 2 teaspoons vanilla extract 1 ¾ teaspoon baking powder ½ cup of butter ½ cup milk 2 eggs 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 Cookies: 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

28. Adaptive Data Warehouse Metadata: EDW AtlasSecurity and Auditing FINANCIAL SOURCES (e.g. EPSi, Peoplesoft, Lawson) DEPARTMENTAL SOURCES (e.g. Apollo) Common, Linkable Vocabulary FinancialSource Marts DepartmentalSource Marts Readmissions AdministrativeSource Marts PatientSource Marts PATIENT SATISFACTION SOURCES (e.g. NRC Picker, Press Ganey) ADMINISTRATIVE SOURCES (e.g. API Time Tracking) Diabetes Sepsis EMR Source Marts HRSource Mart EMR SOURCE (e.g. Cerner) Human Resources (e.g. PeopleSoft) More Transformation Less Transformation

29. Late-binding Deeper Dive

30. Data Modeling Approaches Corporate Information Model Popularized by Bill Inmon and Claudia Imhoff Early Binding I2B2 Popularized by Academic Medicine Star Schema Popularized by Ralph Kimball Data Bus Popularized by Dale Sanders File Structure Association Popularized by IBM mainframes in 1960s Reappearing in Hadoop & NoSQL Late Binding

31. 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

32. Origins of Early vs Late Binding • 1980s: 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: NeXT 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

33. 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, RxNorm, 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

34. Analytic Relations The key is to relate data, not model data High Value Attributes Core Data Elements About 20 data attributes account for 90% of healthcare analytic use cases 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 Vocab in Source System 1 Vocab in Source System 2 Vocab in Source System 3 Highest value area for standardizing vocabulary

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

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

37. Thank you!