Data Warehouse User Group June 24, 2010

1. Data Warehouse User GroupJune 24, 2010 • Upcoming Changes to the Cubes • Additions to the MEIS Catalog

2. Removing Redundancy • Many of the cubes contain the same information and we’d like to consolidate these into fewer cubes • Fewer cubes means more efficiency • faster cube builds • fewer cubes to have to search through when creating a report • fewer .IMRs to have to reconcile to each other

3. Candidates for Removal • Referral Tracking Transaction and Referral Tracking Utilization • Instead of new, separate cubes, a new Referring Physician dimension will be added to the existing Transaction and Utilization cubes • Why? Apart from the Referring Provider dimension, both cubes have exactly the same information as the original Transaction and Utilization cubes

4. Candidates for Removal (cont.) • SMS Charges Why? • Combined_Utilization has all the same dimensions and categories, though folder names may be different • By filtering the Combined_Utilization cube on SMS data only, you’ll get the same data as the SMS Charges cube • Scheduling_Original Why? • The regular Scheduling cube (which is actually the newer cube) contains all the same information with the exception of the Referring Provider (Refer Prov) dimension • We would add this dimension to the Scheduling cube. While we’re at it, we can structure it the same way as in the new Utilization and Transaction cubes; i.e. “Refer Prov Group/Refer Prov.” This would be a future addition to the MEIS Catalog

5. Candidates for Removal (cont.) • Open AR – time for Retirement? • Because it’s only updated once a month, the information in the cube quickly becomes obsolete • Drill-thru from cube to Impromptu often doesn’t match, as the database is refreshed twice a week. Additional payments/credits/adjustments, etc. are received that won’t be captured until the following month’s refresh • Can’t really function as work-file (as originally hoped). It’s more like a snapshot • A month-end snapshot of open AR is already being captured in the AR Trends cube • For these reasons, we’d like to retire this not-so-useful cube. Please let us know if you have any active reports based on the cube. We will likely help you recreate the report using AR Trends. • Any other cubes not being used?

6. Changes to Existing cubes • In addition to removing several cubes, we also plan to alter several of the existing cubes: • Transaction and Utilization • Addition of Referring Provider dimension (discussed earlier). New folder will appear below existing dimensions/folders. • Change in the current calculation of Referring Physician, presented earlier this year. Our goal is to identify, wherever possible, the original doctor who referred the patient to UCSF.

7. Changes to Existing cubes • Transaction and Utilization (cont.) • After some experimentation, we liked this strategy: • 1)  See if the visit was scheduled.  If so, and the Referring MD is valid*, use the Referring Phys from the SCHED visit. • 2)  If the visit was not scheduled, look at the ADT visit.  If the Referring Phys here is valid*, use the Referring Phys from the ADT VISIT. • 3) Otherwise, default to the Referring MD on the Invoice. *NOTE: A valid value is not null and not one of the 9999X providers (NOT,AVAILABLE, etc.) • For those of you who only want to see the Referring Provider listed on the Invoice, remember that the Marketing cube has this as a dimension.

8. Changes to Existing cubes (cont.) • AR Trends and Lipay Rejects • Addition of a stand-alone PSA dimension. Folder will appear below existing dimensions. • Invoice Lag • Addition of a stand-alone PSA dimension. Folder will appear below existing dimensions. • New calculation of Charge Lag (maybe) • Currenly, entry lag is defined at the Invoice level, using the DOS of the first charge line posted to the Invoice • New calculation would use every charge line posted to an invoice, not just the first line. • Results will likely not change very much, but will be more accurate and more in-line with MGBS metrics for charge lag. • This change will NOT be made in time for the June cube refresh

9. Changes to Existing cubes (cont.) • Combined Utilization and Combined Revenue • Addition of a new Inpatient/Outpatient flag (IPOP) • In SMS, “Patient Type” will determine IP vs. OP category. This is passed to us by the medical center via the monthly files we receive. We assume it comes from the Visit record. • In IDX, “Patient Type” will actually be determined using the Locations dictionary (DN100) and assigning IP or OP based on the Medicare Place of Service (POS) code. • map to Outpatient (OP): 2, 11, 12, 22, 23, 24, 31, 32, 33, 34, 41, 42, 54, 62, 65, O • map to Inpatient (IP): 21, I • Q: Why not use the same algorithm for IDX as for SMS? • A: IDX Patient Type is often NULL/missing.

10. When will the Changes happen? • Cubes for month ending June 2010, refreshed and published in early July, will be the first to display the new dimensions. • Adding new dimensions to an existing cube should not cause problems for existing reports…at least not in theory. • If an existing report of yours does return some unexpected results: • Send it to me to investigate and fix. I will make these a priority and get them back to you a.s.a.p. • “Old” versions of the Transaction and Utilization cubes will be available for one month. Your existing report can always be run against these cubes in the meantime.

11. Upcoming Changes to the MEIS Catalog • The release of a new catalog will coincide with the June cube refresh. Watch your Inbox for a message and instructions from Candice in early July. • New MEIS Catalog will also be posted to the website • The following additions will then be visible to all users: • Pds Inv | Inv Ref Provider • Pdx Txn | Txn Ref Provider • Pds Li Pay | Dictionaries | Lipay MCare Fees 2009 • Pds Li Pay | Dictionaries | Lipay MCare Fees 2010 • **NOTE** for the drill-thru function to work, you will also need new IMR’s for both Utilization and Transaction. These will be distributed via email, as well as posted to the website.

12. Catalog Changes Specific to MGBS • The following additions will then be visible to to users at MGBS: • These will allow drill-thru ability for the Denial Diagnostic cubes • Data inside the folders • is supplied by Denial Diagnostics Inc., not the Medical Group • all appears at the same level (no subfolders) • can’t be altered in any way

13. Questions/Comments/Parting Thoughts • Personally, I… • am incredibly happy to be back! • obviously haven’t taken any PowerPoint classes since I’ve been away. • have the same email address as before: ranallik@ucsfmg.ucsf.edu • have a different phone number: 415-476-4076 • look forward to working with you again!