dimensional modeling basics for healthcare

8/4/2019 Dimensional Modeling Basics for Healthcare

1/27

Buckets or Dimensions

Dimensional modeling basics

for healthcare


2/27

2

Measurements & their context

What is a Measurement?: A procedure for assigning a number to an

object or an event. 1

Data can then be viewed in two categories:

1. Measurements = Metrics (or Facts) = the number itself

2. Context for any given measurement = Dimensions or Buckets

= things we know to be true about themeasurement

1 http://www.ojp.usdoj.gov/BJA/evaluation/glossary/glossary_m.htm


3/27

3

Limited # of major dimensions to conform

Major dimensions match how managers and

analysts think about & classify activities

Kiplings six servants:

I keep six honest serving men

(They taught me all I knew);

Their names are What and Why and When

And How and Where and Who


4/27

4

Limited # of major dimensions

What? What services and products do we provide?

(Treatment/Procedure, Visit Type)

Why? Why do we perform our services? (Diagnosis)

When?

When do we perform our services? (Over what Daterange? During what Time-Of-Day?)


5/27

5


Where? Where do we perform our services? (Service Location)

Where do our patients come from? (Patient Address)

our referrals? (Referring Provider Address)

Who? Who performs our services? (Provider, Employee) Who receives our services? (Patient)

Who pays for our services? (Payer, Grantor)

Who refers patients for our services? (Referring Provider)

How? How do we organize to perform our services (Clinical

Organization, Academic Organization)

How do we expend resources and earn income? (ExpenseCategory, Income Category)


6/27

6

Measures and possible contexts


7/27

Measurement Subject Area:

Appointment Scheduling

ApptSchedulingMeasures

By Date

By Timeof Day

ByProvider

ByService

Location

ByPatient

ByReferring

Provider

By ApptType

By Appt

Status

ByPrimary

Diagnosis

By OrgUnit

Measures: Appt Scheduled Qty

Appt Arrived Qty

Appt No-Show Qty

Appt Scheduling Lag

Appt Cycle Time

Checkout Lag Copay Due

Copay Collected


8/27

Measurement Subject Area:

Medication Alerts

MedicationAlert

Measures

By Date

ByProvider

By ServiceLocation

By Patient

By DrugBy Alert

Type

ByInteraction

Severity

ByOverrideReason

By OrgUnit

Measures Alert Qty

Override Reason Qty


9/27

Measure vs. Bucket matrix

"Buckets" or Dimensions

Subject

Area Sample Measures ByDate

ByTimeofDay

ByProvider

ByServiceLo

cation

ByPatient

ByOrgUnit

ByReferring

r

i

r

ByApptType

ByApptStatu

s

ByPrimary

Diagnosis

ByDrug

ByInteraction

S

rit

ByAlertOverride

eason

Appointment

Scheduling

Appt Arrived Qty, Cycle Time, %

No-Show Rate, % New Pt Appts

X X X X X X X X X X

Medication

AlertsAlert Qty, % Alerts Overriden X X X X X X X X


10/27

11

Re-use of dimensions:

Measures-vs.-Buckets (dimensions) matrix

Measures (Facts) Dimensions


11/27

12

Design of an Integrated DSS:

DW Bus ArchitectureData Warehouse Bus Matrix - Conceptual draft only

Last updated: 3/6/2003 Dimensions

Business Process

Fact Source

System "Grain" of Data Date

Time

Location

Organiza-tional

Unit

Patient

FacultyMember

Employee

ReferralSource

Diagnosis

ProcedureType

Procedure

Results

Insurer

Destination

PhoneNumber

Billing and Reimbursement** IDX (BAR) Health Care Bill Line item X X X X X X X X X X

Clinic Visits**IDX (Sched)

&/or EpicClinic Encounter X X X X X X X X X X

Patient Telephone Messages** Epic Patient Message/Call X X X X X X X X X X

Telephone Answering** ACD system Incoming Telephone Call X X X X X X

Test Ordering & Resulting Epic Test Order X X X X X X X X X X X

Lab/Rad Test Results OACIS Test Result X X X X X X X X X X

Patient ComplaintsProposed

DatabasePatient Complaint X X X X X X X X X X X X

Accounting (G/L & subledgers)

Accounting

System

One accounting transaction

(or balance) X X X X X X

Cardiology Procedures CIMSCompleted CV Procedure

ReportX X X X X X X X X X X

Surgical Procedures SNIPSCompleted Surgical

Procedure RecordX X X X X X X X X X X

Grants ManagementGrants

Database

Research Account subledger

(?)X X X X X X X

Publishing Research Results ISIRecord of Publication or

CitationX X X X X


12/27

Why is this important?

Dashboard filters

Executive Summary Reports

Combining data for: Quality analytics

Planning and forecasting

Ratios of measures fromdifferent domains

Data mining and statisticalanalysis

Role-specific informationportals


13/27

Implications

Need for shared conformed dimensions, and

a group to review/approve

Need for careful source system dictionarysetup and maintenance

Dictionary data stewardship

Crucial part of system implementation Ongoing dictionary maintenance (additions and

changes to entries)


14/27


15/27


16/27

17

Measurements & their context

What is a Measurement?: A procedure for assigning a number to an

object or an event. 1

Data can then be viewed in two categories:1. Measurements

= Facts = the number itself

2. Context for any given measurement = Dimensions = things we know to be true about the

measurement

1 http://www.ojp.usdoj.gov/BJA/evaluation/glossary/glossary_m.htm


17/27

18

Conceptual star-schemas map directly to

SQL star-schemas

PhoneCall

Measures

ByDate

ByClinic

ByTime

of Day

ByEmployee

ByPhoneLine

FACT_TelephoneCalls

FK1 DateKeyFK2 ClinicKeyFK3 TimeOfDayKeyFK4 EmployeeKeyFK5 PhoneLineKey

Calls_Recvd_QtyCalls_Abandoned_QtyCalls_Answered_QtyCalls_Ans_In_30_Sec_QtyTotal_Answer_Time

DIM_Date

PK DateKey

DateSQL_DateDay_Of_WeekWeek_Ending

Calendar_MoCalendar_QtrCalendar_YearHoliday_FlagWeekend_Flag

DIM_Clinic

PK ClinicKey

{Clinic attributes}

DIM_TimeOfDay

PK TimeOfDayKey

Time_Of_DayHour

AM_vs_PM_FlagMilitary_Time

DIM_Employee

PK EmployeeKey

{Employee attributes}

DIM_PhoneLine

PK PhoneLineKey

{Phone Line attributes}

*

*

*

*

*


18/27

19

Major Conformed Dimensions for a Data

Warehouse at an Academic Medical Center

Provider Academic Organizational Unit

Clinical Organizational Unit

Campus Location

Payer

Appointment Type

Treatment

Diagnosis

Pt Demographics

Patient Employee

Student

G/L Account

Grant

Journal

Vendor

Course

Benefactor


19/27

20

Dimensional Model:

Appointment Scheduling

Business Process: Appointment Scheduling

Grain: 1 row per appointment version

Facts: Appt Scheduled Qty

Appt Arrived Qty Appt No-Show Qty

Appt Scheduling Lag

Appt Cycle Time

Rooming Time

Scheduled Duration Time

Checkout Lag Time

Copay Due

Copay Collected

Total Collected

Dimensions: Date (Appt Made, Appt Cancelled,

Date of Service)

Time-of-Day (Arrived, Scheduled, In-Room, Checked-out)

Provider

Org Unit Campus Location

Diagnosis

Patient

Referring Provider

Appt Type/Category

Appt Status Appt Cancel Reason

Copay Category

Employee (Scheduled, Checked-In,Closed Encounter)

...

Source Tables: Epic = PAT_ENC, IDX SCHED


20/27

21

Dimensional Model:

Medication Alerts

Business Process

Medication Prescribing

(interaction alert

checking)

Grain

1 row per Medication

Alert

Facts: Alert Qty

Override Reason Qty

Dimensions:

Date Rx Written

Alert Description

Alert Type

Interaction Severity

Drug

Provider

Org Unit

Override Reason

Source Tables: Epic (Clarity) = ORDER_MED_ALERT_INF, ZC_ALLERGY_LEVEL,ZA_DG_INT_SVRTY


21/27

22

Dimensional Model: CPT Charges

Business Process

Professional Billing

Grain

1 row per CPT line-item charge(Accumulating

Snapshot)

Facts:

Chg Amt, Pmt Amt,RVUs, Chg EntryLag,

Dimensions: Date, Provider, Org

Unit, Location, Dx,Tx, Patient, Payer,Referring Provider,


22/27

23


What? What services and products do we provide?

(Treatment/Procedure, Visit Type)

What characteristics of our services are important to our

customers? (Patient Satisfaction Survey Categories, CoreMeasure Categories)

Why?

Why do we perform our services? (Diagnosis)

When?

When do we perform them? (Over what Date range?

During what Time-Of-Day?)


23/27

24


How? How do we organize to perform our services (Clinical

Organization, Academic Organization)

How do we expend resources and earn income? (ExpenseCategory, Income Category)

Where? Where do we perform our services? (Service Location)

Where do our patients come from? (Patient Address)

our referrals? (Referring Provider Address)

Who? Who performs our services? (Provider, Employee)

Who receives our services? (Patient Demographics)

Who pays for our services? (Payer, Grantor)


24/27

Dashboard example


25/27

Report example


26/27

27

Benefits of Dimensionally-Modeling Data End-user benefits:

Data is understandable

Querying data is fast On-line Analytic Processing (OLAP) for direct data exploration by

end-users

Organizational benefits:

Minimum number of data extractions Lowest total cost to organization

Reduction in time to bring new data online

Highest degree of analytic data interoperability Expanded range of analyses possible

Marked decrease in time/cost to reconcile data

Modular design iterative, incremental development Delivery of useful features early, reliably, often


27/27

28

What value is delivered by integrating

clinical, financial and external data?

Balanced Scorecards & Strategy Maps Financial

Customer (patient, referring provider satisfaction)

Internal process (operational)

Learning and growth

Role-specific information portals via single tool

Useful ratios of data from different domains

Data mining

dimensional modeling basics for healthcare

Documents