session 103: advanced hpm/hbi applications (tips & tricks) roger crook casey merickel yardley...
TRANSCRIPT
Session 103: Advanced HPM/HBI Applications (Tips & Tricks)Roger Crook
Casey Merickel
Yardley Management Solutions, Inc.
©2009 Yardley Management Solutions, Inc.This material is for the sole use of attendees of Insight 2009 Pre conference session #103. Copying or distribution to others is not permitted.
2
Contents
• Overview: Options to Extend HPM Encounter Data• Case Study: Extended HPM Data for Implant Costs• Case Study: Extended Data to Audit HPM • Case Study: HPM Events – Creative Uses• Case Study: HPM Worksheet for Net Revenue• Case Study: Custom Object for Benchmark Data• Overview: Modeling with EBB• Case Study: Loading Model Revenue Fields for
Encounter Based Modeling with EBB• Case Study: Synthetic charge codes for ABC costing• Case Study: HBI Daily Update of Patient Data• Case Study: HBI Daily Census and Productivity
3
Extending HPM Data
• UDA: User Defined Attributes, the original user-defined fields that can be activated at various levels in the encounter dataset.
• Custom Object: A user defined table that can be related to encounter data through one or more key fields
• Extended Data: Newest way to extend existing tables or encounter data type
4
Rules of Thumb
• However your HPM was built, no reason to change. All three methods are supported.
• The real question is what to do going forward when you have the need to add new data to the warehouse.
5
Pros and Cons of UDAs for new data
• UDAs are at different levels in the data model
• Some UDAs will continue to hold 3M grouping results and PMOD results
• New UDA data must be loaded with the encounter interface.
• Not viewable in Encounter Viewer
• Once created, no provision in the application to remove the UDA
• Creating a UDA requires system restart (from Extended data.pdf)
6
From Extended data.pdf:
7
Pros and Cons of Custom Objects
• Simple to build and load• Good for linking based on
encounter fields like DRG and payor,
• Good for handing benchmark data
• Cannot use UDA (like UDA DRG fields) as link/key field.
• Cannot use field in a data extension as link/key field
• Once built, lets you delete, but leaves remnants behind, including old worksheets that referenced the deleted custom object.
8
Pros and Cons of Extended Data
• Newest approach, meant to combine best of UDAs and Custom Objects
• Can extend encounters, procedures, reasons and tables• DRG tables can be extended
with benchmark data
• Can load as part of interface or independently
• Tracks what has been loaded versus what did not load
• Application supports adding and deleting extensions
• Some extensions, like service items, do not yet work
• Some extensions can only be loaded when encounter data is added/replaced.
• A little convoluted in how it is set up, until you get used to it
• Even though it will let you change field names, doing so is not recommended
• Not viewable in encounter viewer
9
Case Study: Extended Data for Implant Costs
• Background: Miscellaneous service item (6211888) was used for approximately 2.6 million of over 6 million in implant costs in 2008, with a range per item of 0 to $14,000
• Using this data as-is, HPM would develop an average cost and apply it to all patients, grossly overstating and understating individual patients.
10
There are several approaches to handling this:1) Custom interface program from McKesson to break out ranges of
charge values
– Makes new charge codes to replace old code
– Would have to reload history for these patients
– Have to process with each subsequent update
2) Change charge capture in STAR to include better detail
– Will not correct prior history
– Strongly recommend for going forward
3) Include invoice cost or a reasonable approximation for each patient
– Most control for Sample using a data extension
– Easily maintained
– You MUST include this field when reporting encounter cost
– Must remove this cost from implant to be allocated using a remap
– This is the approach we have adopted for now
Implant Costs in Data Extension - continued
11
Using an Encounter Data Extension
• Setting up the extension• To Stage or Not to Stage• Integrating staged data• Merging staged data• Using the data in Worksheet
12
Setting Up The Extension
• Data Integration/Data Model Extensibility• Choose staged or not staged (see next slide for more)• Choose field names and data types• System returns the format that the file for integration
must have• MUST got to security next and enable modify access
for your account and view access for other accounts for the extension
13
Setting up the data extension (1)
Your extension is already set up, you do not need to make a new one or modify the existing. This just shows how it was done.
14
Extensions are set up in the data model where appropriate, this one is in the encounter section.
Setting up the data extension (2)
15
Setting up the data extension (3)
When you make an extension template you get to choose if a staging area will be used. If it is, a box pops up to ask how it will link to the patient record. See more on staging on the next slide.
16
To Stage or Not To StageTwo approaches to loading an extension:1) You have the encounter key and can put it on each
record• The encounter key is complex, consisting of multiple elements,
including patient name• Using this method, you can integrate directly to the extension
2) You have the patient billing number on each record, but not the whole encounter key
• You first integrate to the staging area• Then you merge the staged data with the dataset
We used #2 because it is easier to obtain the patient billing number in the data you want to load.
17
Options here allow you to create a new extension, and once created, to “build” it, essentially publishing it in the data model.
Setting up the data extension (4)
18
One of the options on the prior screen is “Layout Report” which tells you exactly how to format the file for the data.
Setting up the data extension (5)
19
Actual file for integration
This file uses “Layout Report” specifications from prior screen. The file for 2008 is complete, unless we make a better version with invoice cost. For 2009, we should rebuild this file year-to-date with each update. You must be vigilant to make sure this data is complete as patients are added to the dataset. The next slides depict how the values were arrived at in this file.
20
Preparing the misc implant patient file (1)
Use implant.xls to determine the cost for each patient using the markup of charges tab.
21
Preparing the misc implant patient file (2)
The tab “Simple HPM Load” creates the file format needed in column e.
22
Recap on the implant extension:
• For 2008, the data has been loaded based on the markup and patient charges• Need to monitor to make sure future updates to 2008 do not
change the 2.602 million• Could make it more accurate by replacing the values with invoice
costs
• For 2009, if the charge mechanism is now splitting out several ranges of miscellaneous code, we could use those• Need to see when it began and how much it covers• Should probably continue to populate the extension with invoice
for the most accurate implant cost
23
Add access in security
You must give yourself and others access to the extension before you do anything else. You will not be able to integrate until you do.
24
When you open the process, you can see the target is the staging area for the extension we built.
Setting up the integration process
25
Merge Staging Area Data
• If you picked “Integrate to the staging area” as an option, you have one more step after integration of the data
• You need to merge the data with one or more datasets.• This makes the association to each patient record using
the linking field you specified.• Following slides depict how this is done
26
Merging Staged Area Data: Choose on menu
27
Merging Staged Data: Choose staging area and dataset(s). Okay button will detach automatically.
28
Merging Staged Data: From job menu, right click on job to read logs files
29
Merging Staged Data: From job menu, right click on job to read logs files
30
Merging Staged Data: Locating the data extension in Worksheet Editor
31
Merging Staged Data: Seeing the results in Worksheet
32
Merging Staged Data: Seeing the results in Worksheet – drill to encounter
33
Other Notes on Using The Extended Implant Data• If reporting with department drill level, qualify column for
the implant extension so that it is limited to the surgery department (6211) so that it does not pop up in others.
• DO NOT FORGET to build this element into all P&Ls and cost analyses, otherwise you are understating the cost.
34
Using the Data: Worksheets for Cost and Margin Analysis
• 0050 Encounter Component Costs by Dept• 0332 Service Line Margins Template Specialty DVI
• REMEMBER: To get full cost in a worksheet, you MUST include the extended data to pick up the full cost of implants. The extended data is NOT in any of the summarized cost components!
• If you do day-of-stay cost analysis, the extended data will not be by day of stay.
35
0050 Encounter Component Costs by Dept
36
0332 Service Line Margins Template Specialty DVI
37
Take aways on Extended Data for Implants:
• How to implement extended encounter data• Different ways to handle the problem of implant detail
38
Case Study: Extended Encounter Data for Audit Purposes• Instead of matching by merging two reports in Excel,
load the encounter level data to HPM and use Worksheet to compare
39
Note different applications:1. Straight from interface2. Audit from Trendstar3. Net revenue4. Anything transformed, retain as original source value
40
Where extended data is found in the data hierarchy – it can be used in all apps
41
This extension contains the original source values before transformations; Extremely helpful in proving that your have the same billed DRG, MDC and Payor you started with.
42
Audit to prove balancing of detail and summary data, also correct interpretation of payment and adjustments at detail and summary levels.
43
Calc to Zero
Charges – Payments – Adjustments – Balance = 0
When this result is not found, some part of the data is not loading correctly.
44
Note 37.9 mill in calc to zero in new HPM data, while original, extended data is closer to zero.
45
Various audits have proven useful.
46
Compare key encounter fields from TRENDSTAR with HPM. More often, we identify problems that existed in TRENDSTAR that were never detected.
47
Take aways of Audit Encounter Extensions
• Encounter Extensions can be added as needed• Encounter Extensions can be loaded from flat files• Audit handling of detailed transactions from patient
accounting• Audit costing results pre and post HPM • Audit pre and post HPM revenue results
48
Case Study: Encounter Events – Creative Uses • HPM encounter groupings have limitations
• No groups of groups• No use in columns – only rows• Makes it impossible to add new groupings at the end of HBI
encounter subsets
• Events do not have these limitations
49
Here you can see use of the events, both detail and summary in columns.
50
Note initial events in “Payor Group” category effectively group them.
Events in “Summary” category group the initial events.
Caveat: Events must be manually reapplied
51
Events are easily defined with encounter qualifiers
52
This is where you will find Events in the encounter data hierarchy
53
Take aways on Creative Use of Events
• It is easy to create and use Events• Events give you powerful ways to organize and use
your HPM encounter data• The downside, that they must be reapplied manually
seems to be offset by how powerful they are as an alternative to the grouping application
54
HPM Worksheet for Net Revenue
• Using worksheet to create a hybrid revenue amount for service line P&Ls• Actual payments• Patient accounting expected• PMOD expected• Estimate on charges and payor percent
• Which to use depends on payor, patient type, payments and balances
• Logic we have previously done in Access or SQL Server can be done in HPM Worksheet
• Caveats: Complexity, handling null values
55
Base data about the cases are at the start of the worksheet
56
More useful base data.
57
Making Boolean columns with zeros if false, 1 if true. If some values being tested are null, the result value here is still 1 or 0.
58
More Boolean columns
59
Default rates based on charges come from a custom object, to be used as a last resort.
60
Several columns then call for actual payment if conditions in the Boolean columns are met. In this case, higher balance cases with significant payments, the payments are used.
61
This case will use actual payments for low balance cases
62
The answer column combines the other five columns, whose answers are mutually exclusive. It is compared with answers from another method that are stored in a custom object.
63
Take aways on Net Revenue Worksheet
• HPM Worksheet can be used to help select the most logical value to use
• You must be aware of the effects of null values on calculations – it is not the same thing as zero.
• Next we’ll show you how the default rate got into the custom object.
• After that we’ll show you how to pipe the answer from worksheet to HBI and back again to load a model revenue field.
64
Custom Object for Benchmark Data
• Needed to match benchmark values to patients• Simple way to extend the data
65
Locating custom object manager
66
With authorization, you may build new custom objects. Plan carefully, as they cannot be deleted easily once built.
67
Linking fields determine how a value is “targeted” to a patient record. The link fields are not needed in the worksheet, the system is smart enough to link behind the scenes.
68
Not the header and field identifier at start of the record. This can be built in Excel using a formula like =“CODATA~&A1&”~”&A2&”~”&A3 to concatenate the contents of cells A1, A2 and A3 together with ~ as the delimiter
69
To integrate the data, you need permission to use Data Integrator
70
First you set up the source definition, which is where you tell the system to use the standard custom object layout.
71
Then you add a process definition using that source.
72
By telling it the source, it knows the target is a custom object.
73
And lets you choose a target from among the available custom objects.
74
After integrating the data, be sure to audit the integration run using this option.
75
One header record did not integrate, as expected.
76
This is where the custom object is found in Worksheet’s data hierarchy.
77
When used in a worksheet, the data for a patient is retrieved based on the keys and content of the custom object
78
Take aways from Custom Objects
• Custom objects useful for benchmark data• Easy to format the data using Excel• Use HPM to integrate your data instead of patching
things together in Excel or Access• An application to support net revenue calculations
79
Overview: Modeling with EBB
• Strategic and tactical decision making• Predict financial consequences of decisions
80
Build EBB Model from Encounter Data
• Service item cost• Model revenue field filled• Service line• Payor grouping• Physician grouping
81
82
There are many more screens that set the build options.
83
Modify Model
• After building the model, copy it • Save the copy to have a point of comparison in
reporting later• Make other copies along the way as needed
84
Service Editor Navigation
EntityPatient TypeService KeyPayorDepartment
List view shows all names underneath highlighted level in the service tree
Service Utilization view shows average service item utilization per service
Service Data view shows number of services, ALOS, charges, and reimbursement
This is an example of List view
Apply will update all of the EBB tables affected by the changes made in this editor
85
Changing Number of Services or LOS by Period
1. Select the level in the service tree at which you want to make changes (in this example Angina was selected)
4. Using either the right mouse menu, or Edit menu, select “number of services” or “average LOS”. Enter the desired value in the “Change value” dialogue.
3. Select which periods you want to make changes for using ctrl-left mouse click.
2. Select the service data view.
86
Changing Service Item Utilization
1.1. Select a Select a department department for a given for a given service / service / payor.payor.
2. Select one or more service items.
3. Select average units from the Edit menu and make your changes.
87
You may also…• Add service (product in a product line)• Add payor• Replace service items• Change service item utilization by service• Edit department costs and reallocate• Change the order of the dimensions
• E.g. Payor above service line means payor changes affect all service lines for the payor
• Perform elementary revenue modeling (do detailed models first in PMOD or 3M)
88
Reporting
• Does not use Worksheet• Has its own built in reporting tool• You choose rows, it has columns• Choice of predefined column formats• Export to Excel or Access as needed
89
Budget to Actual Variance Report
90
2 Model Variance
91
Multiple Models
92
Single Model Periodic
93
Single Model Drill
94
Service Item Detail Drill
95
EBB reports can be exported as delimited files, directly to HBI or to Excel.
96
Optional: Update Budget with Volume and Price
1. Select a Model.
5. Click on Submit
4. Optionally, qualify your data that you wish to update.
2. Select an Entity from your model.
3. Select a dataset that contains the entity you selected. This is the dataset that will be updated with the results of your model.
97
Loading Model Revenue Fields
• Encounter Based Modeling with EBB• Depends on:
• Patient encounter activity• Detailed cost accounting• Revenue per case must be in expected or model payment fields• Load from PMOD or integrate from file
98
Loading Model Revenue Fields for EBB
• Requirement of using EBB: must have patient revenue in expected or model payment fields
• Expected and model payment fields can be loaded by PMOD
• Not everyone owns PMOD• Not everyone who owns PMOD tries to try to cover 100%
of cases• Almost everyone has a report or process that derives net
revenue for each patient• Choose among payments, PA expected, PCON expected, estimated• Use Worksheet or Access• Use HBI SQL Server to format and send file for HPM integration
99
During EBB Modeling, users may choose which iteration of net revenue they wouldlike to work with during this model session.
100
In EBB reporting, the user can choose which cost field to use. Base payment comesfrom the field Expected Payment.
101
Record layout needed to insert model payment values
CAUTION: If you attempt this procedure, be sure to first test it thoroughly on a copy of your dataset to ensure no other changes are made unintentionally.
You may want to consider expert assistance on this aspect.
102
Tools Needed to Format Records for HPM
• Access:• Concatenate fields using & to construct the different records• UNION query to stack the records in order• Export text file
• SQL Server:• Concatenate fields using + to construct the different records• UNION ALL to stack the records• A view can be used to hold the code• A DTS package can call the view• Once set up, the DTS can be called by a shortcut or it can be
scheduled• The output can be sent to HPM’s source file if the drive is mapped
103
Code Example in Access – shows union query to stack records, how to place a header record at the top
104
To integrate the data you need access to Data Integrator, located here.
105
You will need a process definition referencing the source file and the target dataset.
106
The source step identified the file name and type of interface. Here we define the target dataset.
107
Data integrator provides this screen to give extra options and allow you to override the source file used.
108
Take aways from Loading Model Revenue Fields• Possible to load these fields in existing records• Use caution to make sure you are not changing any
data• Extremely useful if you want to use EBB for modeling
109
Synthetic Service Items for ABC
• Activity Based Costing seeks better match of cost and activity
• Example: Giving cost of the cardiology program to only cardiology patients – treat like a direct instead of indirect
• Service items based on charges fail us because they are revenue center driven
• Example: Use patient days or adjusted patient days to absorb cardiology cost for patients who were in the cardiology service line
• Problem: There is no charge code that can track days for a patient in the service line – the patients were on many nursing units
110
Solutions
• Create a set of zero-price charge codes on the CDM and use them to record each day a patient was in a given service line. Not a popular solution for a variety of reasons.
• Create service items in HPM based on the patient service line and patient days or charges or cost. The service items do not have to be charge codes (Use service item types nursing codes or OR codes for example). Still extra work, but when you really need to ABC done right, this is one way.
111
Ways to create service items
• Create service items in the interface• Service line depends on your definition, usually related to DRG or
diagnosis and procedure• Duplicating this logic in the interface would be challenging• For many hospitals, the DRG grouping is done after HPM data is
loaded
• Create service items after encounter data is loaded• Allows patients to be grouped to DRGs and service lines• More control over the qualifiers to determine service items• Orthopedic patients with heart issues could get service items for
both service lines
112
Steps in the process
• Encounter grouping definition and apply• Worksheet to extract flat file by patient• Process in Access create records for integrating to
patient records• Add service items and departments as appropriate• Integrate new service item data to encounters• Create price and volume and continue second pass at
costing
113
Worksheet to extract basis for service items
The HPM worksheet must contain fields needed to identify the encounter to integrate to, and statistics for each service line to create service items’ volume from. Note how charges divided by 100 is used to create a unit value for the new service item. Service items can have fractional units.
114
Access application
• Takes in the extract file• Reformatting many fields, especially dates• Creates the record types needed, inserting just the data
needed:• ENCHDR• ENCTR• ENCSIHDR• ENCSI
• Sort the records
115
File for integration to encounter data
116
Ongoing maintenance
• In ongoing updates, some of these patient records will be replaced
• When the extract Worksheet is generated, you must be careful that the selection criteria includes only cases that are new or have been replaced since the last time you generated the extract. Otherwise, you will insert duplicate service item records in some patients.
117
Take aways of synthetic service items
• It is possible to insert service items just for costing• Desirable approach if you want to undertake true
Activity Based Costing (ABC)• Use caution
118
HBI Daily Update of Patient Data
• Daily discharge data along with last year’s data helps us understand how we are doing• Volume of encounters• Payor mix• DRG for intensity• Length of stay adjusted for DRG• Activity by attending physician
• Problem: Takes a lot of processing• Prior year data is pretty static• Process it weekly or monthly and combine with the daily
119
HBI Daily Update of Patient Data
Dailysubset
YTDsubset
PatDatUnchanged.txt
PXHBI1.tsv15th, 2:07 pmoverwrite
AMHBI1.tsvDaily, 6:01 pmoverwrite
DTS Daily6:12 pm
DTS Daily6:20 pmOverwrite
DTSDaily, 6:35 pmNo overwrite
1
2
3
4
5
View selects data
120
We build the view first, which compares the cases we loaded before with the cases that are new or changed, and puts out just the cases that have are not changed.
121
It does this by choosing only the cases that find no match in the new data
122
We use three DTS packages to update, since it is really several updates in one day from different files. Right click on a package to schedule it.
123
We use the standard DTS templates supplied by McKesson, specifying the sources file…
124
…and the target table to load.
125
If you have not used these templates, they are found in this directory on the HBI server. You need rights to Enterprise Manager (SQL Server 2003 or earlier) or SQL Design Studio (Server 2005)
126
This is where jobs are managed after they are scheduled.
127
Finished highlight builds everyday with more discharged and coded patients, in this case.
128
HBI Daily Census and Productivity
• Daily census subset by nurse unit• Daily hours subset from Kronos, using a pull from HBI• Each with own highlights and automation• SQL Server View on HBI to summarize and join them
for a third productivity subset and highlights
129
Uses a linked server to issue the query for data from HBI to the Kronos report server.
130
The SQL logic is stored in the DTS package which pulls back data each night as a text file to the WTFiles directory.
131
This is where the logic is located. You can see it graphically by taking the logic into a view.
132
The view shows the tables, some on Kronos and some on HBI that are joined. The query is too complex to be saved in the view.
133
The Kronos data is very useful
134
The census data is also useful and is a much simpler data feed, by department and day.
135
A series of views summarize the data from payroll and census to the day and department.
136
That view becomes the basis for the daily productivity highlight.
137
Take aways from HBI Productivity
• Obtain daily updates of key data• Link it up in SQL Server on HBI• How a linked server works• Replace expensive productivity systems
138
Questions?
• Roger: [email protected]• 215-321-9197• Casey: [email protected]• (646) 221-7560
Thank you for attending, and enjoy the conference!