Data Warehouse User Group July 26, 2012

1. Data Warehouse User GroupJuly 26, 2012 • Combined PB Transaction cube – DRAFT • Apex PB Transaction cube – DRAFT

2. Combined PB Transaction cube • APEX data will only be available to query from the Cognos 8 series tools (web-based), NOT Cognos 7 • Cube will combine Pro-fee transactions from IDX with transactions from Apex’ Resolute PB application • Will contain data beginning Fiscal Year 2012, by Post Date • Will be populated through FY 2013, or at least while IDX is still posting (IDX to be decommissioned in April 2013)

3. Combined PB Transaction cube • Attempts an apples-to-apples comparison across the two billing systems • Only contains those dimensions that both systems shared, or that we were able to derive cleanly and consistently

4. Combined PB Transaction cube – mapping strategy

5. Combined PB Transaction cube – common dimensions Dimensions will be very familiar to users already querying cubes from Cognos 8.4 Billing Systems are designated as different sources We tried to maintain the same measures as well

6. Combined PB Transaction cube – decisions made • Since the data elements from IDX and Apex don’t match exactly, we were forced to make several assumptions and decisions regarding the data • Billing Agent • All McKesson Bar Groups rolled up into one for IDX data • There is no Research Billing Agent in APEX, but there is a Research Financial Class. This financial class was used to create an artificial Research Billing Agent rollup for the Apex data

7. Combined PB Transaction cube – decisions made • Plan Category • In IDX, all FSCs rolled up to high level FSC Categories • In Apex, most plans are mapped to one of our old categories, with a couple of exceptions: • Charity Plans – these didn’t exist in IDX. Bad Debt was written off using specific Charity-related pay codes, and the associated FSC type on the Invoice was always Self Pay. Charity Plans in APEX don’t have a grouper 6, so we’ve grouped these under Self Pay in this cube • “Unlisted” Plans – still figuring out how to group these. They currently appear under the Unknown category • Unknown and Blank categories are also related to Undistributed Transactions (more on this later)

8. Combined PB Transaction cube – decisions made • POS: Medicare Place of Service • In IDX, this was populated for every transaction (charges, payments, contractual adjustments, bad debt, etc.) Every transaction for a given invoice shared the same POS as the charge line(s) in that Invoice. • In APEX, POS is really only meaningful when looking at Charge lines. Payments and other adjustments have Places of Service like “SBO”; i.e. where the payment was made or posted. Sometimes these make sense, most times the don’t. • Wherever we were able to match a payment/debit/credit to a charge, the POS for that charge was inherited. • For Undistributed transaction in APEX (Payments, Debits, Payment Reversals, Credit), the POS will be NULL or Unknown in our cube *until* that transaction is matched to a charge. This is true for the combined PB as well as the APEX-only PB cube.

9. Outstanding Issue #1: UndistributedPayments and Other Transactions • Undistributed Payments and Other Transactions • All New Transactions, except for Charges, come in as Undistributed – this applies to both Pre-payments, as well as Copayments • The only way to know what a payment is actually paying for is to match the payment to a charge. This can happen quickly, but the matching can also occur much later • Epic’s posting methodology impacts us both financially and data-wise • 1) Financial Implications: when do we call a payment a payment? • What happens if a DEP recognizes a $100K payment in July, and then that payment is matched/distributed to a different DEP in August (we have already seen examples of this…smaller dollar amounts, but same concept). This would lead to a -$100K the next month. In this scenario, it might make sense to wait to recognize the revenue/payment until it’s been matched to services rendered • Calculation of the monthly PSA reconciliation would be directly affected. • This is still under discussion, and would impact some departments more than others • We are also currently researching how Apex incorporates this into its AR Calculations.

10. Outstanding Issue #1: UndistributedPayments and Other Transactions (cont.) • 2) Data Implications: Lots of Holes • Undistributed Payments (and credits, debits, etc.) aren’t associated with a DOS, Billing Provider, Service Provider, Location where service was provided and many other charge-related pieces of info…it’s like they are free-floating until applied/matched • Epic’s explanation is that one payment can be distributed to many different charges for a given Guarantor Account • These transactions where no matching to a charge has occurred will have blank/NULL/Unknown values for all the items mentioned above • We’re not used to seeing BLANKS in our cubes and tables; remember, in IDX, every transaction inherited Provider, Bill Area, Location, Original FSC, etc. from the Invoice Header record

11. Outstanding Issue #1: UndistributedPayments and Other Transactions (cont.) • 2) Data Implications (continued) • Biggest Problem = Attribution • New payments are automatically associated to a DEP, and some payments even identify a provider (not many), but when matched, both of those could change! • Do we give a Department, Provider, Cost Center, or Location “credit” for something that actually doesn’t belong to them? • Proposed Solution in the warehouse: Unless the transaction has been matched to charge, we report those charge-related elements as UNKNOWN. The only exception to this is the DEP, since we have to associate these with something known. • Drawbacks to this approach: • Some Transactions will never be matched to a charge: credits and debits that are posted to a Guarantor Account don’t have to be matched to specific services • Data Mismatch: reports run out of Hyperspace/Clarity may group the data differently than our cubes do.

12. Outstanding Issue #2: Unknown Insurance Plans • Hierarchy for Insurance in Apex is • Financial Class • 19 categories currently • Ex: Aetna, Blue Cross, Medicare • Payors • 320+ with activity in Apex to date • Ex: Anthem BX/BS, CCS-CA, Cigna • Plans • 1050+ with activity in Apex to date • Ex: AETNA MCARE OPEN PLAN PFFS MCARE (12621) , B&T HLTHNET B&G ALT (25267) , HILL PHYS MED GRP/SF HLTHNET ALT (25089) • Apex reports group financial data by Financial Class and/or Payor, but not Plan • We care about Plan (the lowest level of detail) because only Plans roll up to the main “FSC categories” that we’re used to; i.e. Commercial, Contract, Medicare, Medi-Cal, etc.

13. Outstanding Issue #2: Unknown Insurance Plans (cont.) • Assumption: we want to keep reporting by these main Payer Categories (Capitation, Contract, Medicare, Medi-Cal etc.) • Big Problem: Payments (as well as debit adjustments, credit adjustments, reversals, etc.) get posted to our system with a Payor, but not a Plan. • Knowing the Payor allows us to roll up to Financial Class, but not to FSC/Payer Category! • Charges are the only transaction detail type that explicitly contain Plan information • Data Warehouse solution: *Derive* a Plan for every new payment, adjustment, reversal, etc. • Sound easy, but has proved to be really difficult in practice; the payor on an invoice won’t necessarily match the payor on the charge! • When a charge payor is different from the payment payor, we are looking to the patient’s Coverage to help us calculate the most likely Plan to associate with that payment • Sometimes a patient’s coverage doesn’t include the payor from the payment invoice at all! • Drawbacks to this approach? • Until a Payment (or Credit or Debit or Reversal, etc.) is matched back to a charge, the Plan is UNKNOWN to us • If the patient’s coverage doesn’t include the insurance payor, the Plan information is forever UKNOWN to us

14. Combined PB Transaction cube – some things to note • Purpose of this cube is to provide continuous reporting, especially important for FY2012, but at a general level • Only one cube will be built each month; no mini-cubes per Division • Querying this cube should be fast since this cube has few dimensions and very few levels within dimensions • The cube will NOT have drill-thru capability • Totals in this cube will tie back, by source, to IDX-only cubes (currently being refreshed each month), as well as to our future APEX-only cube • More detailed analysis should be done in the existing Transaction cube (for IDX), or the Future Apex Transaction cube • Both the “old” as well as the “new” Transaction cubes will always have drill-thru capability • You’ll have access to the detail that make up these Transaction cubes via a drill-thru from Analysis Studio. You’ll also be able to query the data independently via Query Studio. • There will also be a companion Combined HB cube that will function much in the same way

15. Where are the other cubes – what’s the holdup? • Tackling one subject area at time: financial first • Validation – just finding any sources for balancing back to Apex has been challenging • Only yesterday we identified a Clarity report that seems to be a good candidate (REP0018607) • Medical Center Finance will be tying back to it every month as well • We also need to tie back to our existing, IDX-only cubes • Outstanding issues (previous slides) • How to report Undistributed Payments • How to work with limited Plan-level information

16. Preparing for Apex cubes and packages…parting thoughts • We will be offering a larger “orientation” session (probably in a lecture hall) to familiarize users with these new cube formats. However… • Be sure to have some PB training. I cannot even pretend to “teach you Apex PB” in one Cognos training class, much less one demo session • All users who sign up for Cognos training will be expected to know at least the basic concepts in Apex; e.g. Master Files, Coverages, Guarantor Accounts, etc. • Many online classes and documents are available through the learning center: http://learningcenter.ucsfmedicalcenter.org/

17. Preparing for Apex cubes and packages…online training 2) From there, you’ll be shown all classes related to your choices 1) After logging in, menus will guide you to find courses based on area and function

18. What’s next? • By the end of August, we will likely release • Combined PB Transaction cube • Apex PB Transaction cube • Definite Maybe’s: • Combined HB Transaction cube (SMS + Apex HB) • Apex Utilization cube • Apex Appointments cube • A finalized Framework Package for Query Studio won’t be ready until September • i.e. the detailed reporting environment you currently have for IDX, where elements are organized neatly into logical folders (dates, patient info, etc.) • Prior to the August release of our first cubes, I propose some additional working sessions with this group • Gives us a chance to look more closely as transaction detail types in Apex • Sample exercises and handouts from Candice and my Clarity training in Wisconsin • Opportunity to get your input about things like: • What dimensions and levels should be contained in the cubes • How should the cubes/packages/folders be named and organized?