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

Implant Costs in Data Extension - continued

There are several approaches to handling this: 1) 2) 3) 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 Change charge capture in STAR to include better detail – Will not correct prior history – Strongly recommend for going forward 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

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

Setting up the data extension (1)

Your extension is already set up, you do not need to make a new

Setting up the data extension (2)

Extensions are set up in the data model where appropriate, this one

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 Stage

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

Setting up the data extension (4)

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 (5)

One of the options on the prior screen is “Layout Report” which

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

Preparing the misc implant patient file (1)

20 Use implant.xls to determine the cost for each patient using the markup of charges tab.

Preparing the misc implant patient file (2)

21 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

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.

23

Setting up the integration process

24 When you open the process, you can see the target is the staging area for the extension we built.

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

0332 Service Line Margins Template Specialty DVI

36

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 interface 2. Audit from Trendstar 3. Net revenue 4. Anything transformed, retain as original source value

40 Where extended data is found in the data hierarchy – it can be used in all apps

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.

41

Audit to prove balancing of detail and summary data, also correct interpretation of payment and adjustments at detail and summary levels.

42

43

Calc to Zero

Charges – Payments – Adjustments – Balance = 0 When this result is not found, some part of the data is not loading correctly.

Note 37.9 mill in calc to zero in new HPM data, while original, extended data is closer to zero.

44

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.

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 68

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

Service Editor Navigation

List view shows all names underneath highlighted level in the service tree Service Data view shows number of services, ALOS, charges, and reimbursement Entity Patient Type Service Key Payor Department This is an example of List view Service Utilization view shows average service item utilization per service Apply will update all of the EBB tables affected by the changes made in this editor 84

1.

Select the level in the service tree at which you want to make changes (in this example Angina was selected) Changing Number of Services or LOS by Period 2.

Select the service data view.

85

3.

Select which periods you want to make changes for using ctrl-left mouse click.

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.

1.

Select a department for a given service / payor.

86

Changing Service Item Utilization 3.

Select average units from the Edit menu and make your changes.

2.

Select one or more service items.

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.

Optional: Update Budget with Volume and Price 1.

Select a Model.

2.

Select an Entity from your model.

4. Optionally, qualify your data that you wish to update.

96

3.

Select a dataset that contains the entity you selected. This is the dataset that will be updated with the results of your model.

5. Click on Submit

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

During EBB Modeling, users may choose which iteration of net revenue they would like to work with during this model session.

99

In EBB reporting, the user can choose which cost field to use. Base payment comes from the field Expected Payment.

100

Record layout needed to insert model payment values

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

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 102

Code Example in Access – shows union query to stack records, how to place a header record at the top

103

104 To integrate the data you need access to Data Integrator, located here.

You will need a process definition referencing the source file and the target dataset.

105

The source step identified the file name and type of interface. Here we define the target dataset.

106

Data integrator provides this screen to give extra options and allow you to override the source file used.

107

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

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.

113

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

1

HBI Daily Update of Patient Data

2 AMHBI1.tsv

Daily, 6:01 pm overwrite Daily subset PXHBI1.tsv

15 th , 2:07 pm overwrite View selects data PatDatUnchanged.txt

YTD subset 3 DTS Daily 6:12 pm 4 119 5 DTS Daily, 6:35 pm No overwrite DTS Daily 6:20 pm Overwrite

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.

120

It does this by choosing only the cases that find no match in the new data 121

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.

122

We use the standard DTS templates supplied by McKesson, specifying the sources file… 123

…and the target table to load.

124

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) 125

This is where jobs are managed after they are scheduled.

126

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!