intelli HEALTH ONTARIO : The NEW and IMPROVED Access to the PHPDB*

1. intelliHEALTH ONTARIO:The NEW and IMPROVED Access to the PHPDB* 2007 APHEO Conference JoAnn Heale (joann.heale@ontario.ca) Health Data & Decision Support Unit, MOHLTC *Provincial Health Planning Database

2. Recommendations from PHPDB User Survey • Assist external PHPDB users to overcome VPN problems • Develop additional functionality and products to cater to users with different levels of expertise and needs • Improve the format of the PHPDB code tables to make them more easily accessible • Modify the website to make it more user-friendly and up-to-date

3. Requirements for Future PHPDB Access • Maintain current Hummingbird BI functionality • Customize access to datasets and/or data fields • Assure security/privacy guidelines are met • Provide ‘thin client’ access to the PHPDB • Add enhanced reporting functionality

4. Web-based Access – no VPN required!

5. Web link – restricted access

6. Directory of Information Maps (Table[s])

7. Red + Grey Tables in PHPDB Red Table in PHPDB Maintain Functionality of Old Access • All tables and almost all data items (attributes) from the PHPDB are available in WRS • Example: Inpatient Discharge IMs

8. Meet Privacy/Confidentiality Guidelines • No personal identifiers • Chart number • Birth Date • Health number • Unique Record Keys Used as Measures Only • E.g. CIHI Key is not available as a data item but is used as the measure # inpatient discharges • E.g. Encrypted HN is not available as a data item but is used as the measure # patients (HN)

9. Enhanced functionality for all levels of user…

11. Enhanced Functionality – Measures Are Created for Users What the User Sees in WRS: D = Distinct Measure HN = patient with health number

12. How the Measures were Calculated • # Ext Causes • Count on hlth_serv_type field (always IP) from External Cause table • There can be multiple (or no) external causes per discharge • # Ext C Dschg (D) • Count distinct on cihi_key from External Cause table • Distinct measures cannot be totaled across cells and can only be used in list reports • # Dschg (D) • Count distinct on cihi_key from Inpatient Discharge (red) table • Requires outer join between IP Discharge & External Cause • # Pts with Ext C (D-HN) • Count distinct on identification_num (HN) from External Cause table – where identification_cd = ‘H’ Note: WRS allows multiple distinct measures in one report (list only) – Hummingbird allowed only one per query

13. Enhanced functionality – no need to link to code tables…

14. Enhanced functionality – distinct measures can only be used in lists – no totals

16. Enhanced functionality – formatted crosstab reports

17. All measures in all information maps have been tested (by Ian Taylor) against the PHPDB

18. New Products • Summary Information Maps • Based on cubes built from 6-7 most-frequently queried dimensions • Very fast query time, e.g. medical services query takes seconds-minutes • Drill down or expand dimensions • Standard reports built from each Summary IM • Special Reports • SAS datasets created to join record level and aggregate datasets • Available for pilot group – live birth rates, death rates, death rankings by leading cause • In creating rates datasets, some records have to be excluded (e.g., unknown age, sex, public health unit)

19. Example of Summary Information Map

20. Example of Special Reports Developed Death Rates Report Leading Causes of Death Report

21. Death Rates Report: Nine Sections

22. Sample Report View for Death Rates Report

23. User Can Select and View Pages

24. Most Report Sections Include Graphs

25. You can quickly sort, e.g. by death rate

26. … or by number of deaths

27. You can filter on any data item, e.g. age group

28. … and make comparisons between PHUs

29. Footers include detailed information on source data

30. Leading Cause of Death Report: Six Sections

31. Sample: Lead Cause Rank Under 20 Yr

32. User can select from prompted items

34. User can refine report to explore other issues

36. User can sort/rank report on any data item

37. Other Features of Reporting Tool(available for users of the Special Reports) • Customize for agency’s needs and save to ‘My Folders’ • Add ‘conditional highlighting’ to any report • E.g., any cell with fewer than 5 displays as ‘NR’ • Create additional custom measures • E.g., Deaths < 20 Yr, Rank < 20 Yr in Leading Cause of Death Report • Rank and display any data item, • E.g. display leading causes of death that account for 75% of all deaths • Filter any data item • Use available filters or create custom filters

46. Next Steps • Pilot group (20 users) trained week of Oct. 22 2007 • PHPDB Advisory Group reinstated • Pilot group tests Web Report Studio for one month • Pilot group & Advisory Group provide feedback to development team • Product revisions in December-January 2008 • Roll-out to existing users February-April 2008 • Hummingbird BI phased out to coincide with roll-out

47. Questions?

48. User-friendly graphing options

49. User friendly prompt lists have been created

50. Sample output- can edit report to improve formatting