Teaching a Pig to Sing

1
Teaching a Pig to Sing

• (Consolidated Reporting from Multiple Patient
  Costing Sites)

Bill Stomfay Geoff Luke Casemix, Costing
Allocation Team, Finance Branch, Queensland Health
2
Overview

• Background
• Clinical Costing Methodology
• IT Architecture
• Reporting Infrastructure
• Human Resources and Governance
• Integration with Other Systems

3
Background

• QH implemented Sunrise Decision Support Manager
  (SDSM), formerly known as Transition II between
  1997 and 2000 still going strong
• 19 sites, covering more than 80 of statewide
  inpatient activity
• Corporate implementation with centralised data
  repository and reporting
• Basis for most QH NHCDC centralised processing
  and reporting, as well as Casemix Funding Model
  prices

4
SDSM Conceptual Structure
5
Functionality (Why Bother?)

• Patient costing
• aggregation of costed products provided to an
  individual patient
• basis for detailed, patient centric reporting and
  benchmarking
• Application of QH funding model
• analysis of variance between funding and actual
  cost
• Departmental variance analysis
• differences due to volume, input prices and
  product mix
• Activity Based budgeting and modelling
• what if analysis of service profile changes
• budget setting based on current and projected
  service delivery

6
Financial Level

• Cost types, cost categories and GL account codes
• Cost centre mappings to Departments
• FTE and payroll data

7
SDSM Cost Types

• Fixed within system
• Combinations of fixed/variable, direct/indirect,
  labour/supplies/equipment
• Different behaviour when analysing variances and
  modelling

8
SDSM Cost Types
9
SDSM Cost Categories

• User defined
• Differentiates within cost types for reporting
  and NHCDC compliance
• eg VDL used for medical, nursing and allied
  health salaries and wages

10
SDSM Cost Categories
11
Account Code Master
12
JOB PAY TYPES
13
JOB CODES
14
Department Level

• Departments
• Direct/indirect
• Operational areas, eg Imaging, Pathology, Wards 1
  2, Clinical Units A B
• Departmental products (actual and budget)
• Imaging chest xrays, ultrasound, CT scans
• Pathology FBCs, Troponin
• Relative Value Units
• Cost types and categories

15
(No Transcript)
16
Patient Level

• Encounters
• Distinct inpatient and outpatient episodes of
  care
• Utilisation
• Services provided to patients, date/time stamped

17
Feeder Systems and Encounter Matching

• Details of what services are provided (eg
  pathology tests, drugs, ward bed days)
• Departmental products
• Costed using RVUs within cost types and cost
  categories
• Must be matched to patient encounters

18
Ledger Expenditure
Transition II
FAMMIS
Labour Hours and
PAYMAN
Normalised Data Extracts
Demographics
TALONS
HBCIS
Create Encounters
Utilisation
Match utilisation to Encounters
Dept Info Systems
FTP Server
Feeder Extracts
19
Encounter Matching Business Rules

• Defined within Talons
• Requires understanding of clinical process
• Best guess and business decision re activity
  allocation
• Not all utilisation can be linked to an encounter
• No feeder system
• Virtual Patient Generator (aka Dummy Spitter)

20
TALONS Business Rules
Admit Time
DischTime
Midnight DOD (Pharm)
Inpatient
Admitted Inpatient
UR.Ep
Midnight DOP
Midnight DOP 1
Emergency
Emergency Presentation
UR.YYMM.Date
Outpatient
Clinic Attendance
UR.Clinic Code.Date
Midnight Day Of Clinic -14
Midnight Day Of Clinic 14
21
IT Architecture

• Source data from GL, payroll, HBCIS and third
  party systems
• Encounter matching and formatting through Talons
• SDSM runs on central AS400/DB2
• PC client green screen IBM terminal emulation
• Reporting via Crystal Enterprise (Citrix) and
  Crystal reports

22
(No Transcript)
23
COR

• Not an SDSM database
• Collection of DB2 Tables containing SDSM data
• Over 300GB (more than 20 of all data stored)
• Rebuilt weekly
• Subset of information in each site database
• Reference tables to facilitate reporting
• Some replicated back to site databases
• Homegrown utilities
• Summaries for reporting
• NHCDC extract

24
Reporting Infrastructure

• Crystal Enterprise (QHERS)
• Browser based (Citrix)
• Crystal Reports
• Citrix or desktop
• Business Views
• user friendly metadata
• Online utilities

25
QHERS

• Users may be
• Viewers (only access populated instances of
  reports)
• Schedulers (able to repopulate reports and change
  record selection parameters)
• Report Developers (report content designers using
  business views or ODBC connection)
• Publishers (Business Area Administrators)
• Reports may be run
• On demand (Run Now)
• As background jobs (Scheduled as resources
  permit)
• Scheduled as recurring instances

26
Business Views

• User friendly metadata with fixed logical table
  joins
• Can be built from native tables, database views,
  or a mixture of both
• Control which fields are presented to the Report
  Developer
• Provide controlled access (read only) to source
  data
• Support for Row and Column Level Security
• Users can only see the data they are authorised
  to see, even though its all in the same table

27
Business Views

• Several major views, Encounter being the most
  complex
• 78 table objects
• One object built from 14 separate tables
• 1,900 reportable data fields
• Linking logic critical to prevent duplicate rows
  being returned
• Critical intellectual property
• Currently developing web based glossary to
  support content developers
• GPS navigation for Crystal

28
Online Utilities

• R_COR_CTC
• Extracts data for NHCDC processing
• By Encounter, Department, cost type and cost
  category
• Millions of records per site
• Mapping to NHCDC cost items and areas
• COR QUALSETS
• Build a list of Encounters to facilitate record
  retrieval
• Can utilise complex joins and logical
  relationships to create the list
• SUMMARY TABLES
• Tables of summarised data (like OLAP cubes) to
  facilitate reporting
• Size matters

29
Human Resources and Governance

• Sites
• Each site has their own database and a site
  manager
• Corporate Office
• Casemix, Costing Allocation Team (CCAT)
• Transition System Support Service
• Enterprise Reporting Systems Centre (ERSC)
• Business Objects licencing and support
• External service providers
• Systems Advisory Services, NZ (DBA services and
  Operations Management)
• Australia-Pacific SDSM agent for Eclipsys
  Corporation
• CSH Consultants (Talons)
• Corporate Standards

30
(No Transcript)
31
(No Transcript)
32
Integration with Other Systems

• SDSM not statewide
• Most Districts incorporate satellite sites to
  provide District wide coverage
• Statewide morbidity reporting from HBCIS through
  Data Collections Unit
• Application of Casemix Funding Model by CCAT
• Reporting through DSS Panorama
• Browser based OLAP
• Common data source (HBCIS) but results may vary
  .
• Please explain!!

33
(No Transcript)
34
But WHY??

• Extract timing and selection criteria
• Monthly cycle for DCU, weekly for SDSM
• DCU extraction discharged inpatients only
• Dummy SDSM patients
• re-extraction triggers
• Need for reconciliation
• Unit record level
• Missing records
• Different funding model results
• Automated

35
QH Approach

• Exchange unit record data between systems
• SQL Server to AS400 COR
• AS400 COR view to SQL Server and Panorama
• Also allows reporting of TII actual cost in
  Panorama
• Very few differences
• Recurring Crystal reports to highlight
  differences
• Analysis and follow-up of systematic differences
• Posthumous organ harvesting
• Patients remaining in
• Dummy patients generated by the Dummy Spitter

36
Summary

• Its not simple or easy
• Long term strategic commitment
• Serious IT investment
• Needs expert in-house resources
• Effective working relationships with service
  providers and sites
• All of the above are necessary but do not
  guarantee success

37
Questions?
Never try to teach a pig to sing. It wastes your
time, and it annoys the pig. The Notebook of
Lazarus Long, Robert Heinlein
38
Further Information

• Bill Stomfay bill_stomfay_at_health.qld.gov.au
  (07 - 323 41366)
• Geoff Luke geoff_luke_at_health.qld.gov.au
  (07 - 323 90474)