Geographic Data Visualization from the DR

1. Geographic Data Visualization from the DR Presented By: Thomas Harlan, SQL Technical Team Lead, Data Repository, Report Writing Services Thomas.Harlan@iatric.com

2. The ChallengeHow to integrate geographic visualization tools with the DR? Deploying a geographic visualization tool like Office Power Map or CartoDB can reveal patterns in your patient data of great use for: Community Health Infection Control Marketing for Elective Procedures Business Development (Clinic planning) But this is not without obstacles…

3. Immediate Obstacles • Regardless of the tool you use to visualize the data… you need to: • Get the data from the DR • Address data quality • Geocode the data to some level • Respect Patient Privacy • Deploy the maps to your customers

4. Data Sources in the DR Depending on your version of Meditech and your own workflows, your best-quality geographic information may live in different places: MT 6.1 - fdb.dbo.HimRec_Address MT 6.0 - fdb.dbo.HimRec_Address C/S – ndb.dbo.AdmVisits or BarVisits Magic – mdb.dbo.AdmVisits or BarVisits And so on…

5. Getting Geocodable Data from the DR Once you’ve identified possible locations for address, state, city, nation data … you need to set up a query to get at it. Unlike regular reporting or extract work, where we would use a stored procedure, the optimal method for fetching address data from the DR is to set up one or more Views with: Address, City, Zip/Post Code, County, State, and possibly Country

6. Getting Good Data from the DR The geographical data you pull will have a lot of bad or missing components. The mapping tools will try and adjust for that, but you need to refine your view to: Exclude invalid zip/post codes Exclude placeholder patients Exclude addresses used for the homeless This is an excellent time for an address verification project!

7. Geocode Your Data Each visualization tool has an import routine which converts addresses into geocoded locations, so they can be displayed on the map. Office365 Power Map uses the Bing web-service CartoDB uses a secured web-service In both cases, be aware that your address data is being transmitted over the Internet.

8. Geocoding Levels The finest level of detail we’re likely to get from the DR is a specific house address if we get a Zip+4 valued or a discrete address match. But you do not have to send at that level of detail: You could send just zip/post code and map from that.

9. Patient Privacy One map does not fit all. To respect HIPAA and patient privacy, you need to provide customer-tailored maps, like: Marketing gets maps to zip code only, with service-level filters, not DRG, with opt-out patients removed. Community Health and Infection Control get maps to address, with DRG and/or MDC Business Development gets maps to zipcode with selected Service information (like Urgent Care / Emergency)

10. Questions?

11. Installing Office 365 Power Map Depending on your version of Office you may have Power Map already installed via the Insert > Tours > Map button. It may also be installed, but not enabled: File > Options > Add-Ins > Manage > COM Add-ins > Go > Add-ins Available > Power Map > OK Or it may not be available at all. 8-(

12. Formatting Data for PowerMap PowerMap can geocode to the City, Country, State, Street or Zipcode level. After inserting a PowerMap, select the view data from the other tab; then map the imported elements to the Power Map fields via the wizard. The Bing-based mapping process will then plot your imported locations and also generate a list of errors for non-plottable data rows.

13. Visualization and Filtering in PowerMap Now you (or your end user) is within the mapping application inside of Excel and can: Filter Change Visualization Modes Navigate through the Map Zoom in/Zoom out Add annotations Add additional layers

14. Securing Power Map Once you’ve imported data into an Excel workbook and used Power Map to visualize it, everything is in one .xlsx based package. Securing it means securing the Excel workbook on a shared drive, just as with any other Office document containing PHI.

15. Delivering Power Map The Excel workbook containing the Power Map can be delivered to your end-users via: Shared drive Export to PDF Emailing the Excel The workbook should be locked, to prevent editing by end-users, like any Excel.

16. What Does That Look Like? Stacked Column - 35K Visit Records over six years

17. Clustered Column in PowerMap

18. Bubble Map in PowerMap

19. Region Map by ZipCode in Power Map

20. Questions?

21. Accessing CartoDB Unlike Power Map, CartoDB is an entirely web-based solution. Accessing it can be as simple as going to the web-site and signing up: http://cartodb.com/ Then you can upload data in a variety of formats and begin configuring visualizations. Note that we can upload multiple discrete datasets.

22. Visualization and Filtering in CartoDB CartoDB allows you to: Upload multiple datasets per map Filter and choose amongst the datasets Apply more than one dataset to a given map Apply multiple visualization styles Add elements (titles, text, annotations, images)

23. Securing CartoDB CartoDB maps are delivered via the cloud through their web-interface. The data behind a map is stored in PostresSQL instances. You can secure a CartoDB map three ways: The map and data can be completely private The data can be public or only via assignment. The map can be public or only via assignment.

24. CartoDB Delivery Once you’ve loaded data and assigned it to a map (or you have multiple datasets, each a layer on the map) and chosen visualizations, you can deliver it via: Web to any web-capable device Export to PDF Export to various GIS formats The web-app is dynamic, PDF is static.

25. What does that look like?

26. Questions?

27. Lessons Learned! If you’re starting from raw address data, converting it into geocoded locations is expensive (either in PowerMap performance, or CartoDB geocoding credits). Compress your data into an aggregate per address, reducing the total number of rows: Address or Zip Code Service Count of Visits (*)

28. Lessons Learned, Part II Power Map is resource intensive: 64-bit Office recommended 32 GB of RAM recommended Visualizing Region or Heatmaps over time crashed Excel on the development machine (16GB of RAM) unless all other apps were closed.

29. Lessons Learned III CartoDB is hamstringed (for demo purposes) by a limited number of GeoCoding credits in Trial mode. Performance of the maps on the web is good. Data options, including custom SQL queries are good. Direct linkage to the DR is not readily available.

30. Pros and Cons Pro – Office PowerMap You may already license the add-in. Uses a very familiar tool, Excel. The map itself lives within your network. Auto-refresh data connections to your DR. Cons – PowerMap Visualization options are limited. Performance is slow with real data volumes. May need to buy licenses per user. PowerMap can crash when overtaxed.

31. Pros and Cons Pro – CartoDB Delivery to many devices from one source. Many visualizations for the data Multiple datasets per map. Excellent user interface Con – CartoDB Geocoding costs! Most useful with a paid license. Data is in the cloud. No dynamic connection to the DR.

32. Conclusions There are many mapping options available, these are only two. For initial proof of concept, Power Map is the way to go. If you exceed the capabilities of Excel/PowerMap then level up to something like CartoDb, once you’ve made a business case to pay for a proper enterprise license. Refine your dataset outside of either tool; that will save time and frustration.

33. Geocoding Options Moving beyond Bing-based mapping, getting your addresses geocoded is a barrier, but there are options: https://www.geocod.io offers 2,500 lookups per day, or 0.0005 cent per lookup after that for US addresses only. As does Google via their Geocoding API, which is global.

34. Geocoding Implementation Developing an SSIS-based process to batch geocode your addresses is entirely possible: Deploy two new tables in zcus: SourceAddresses, MappedAddresses Schedule a daily SSIS job to batch geocode in blocks of 2500 (for free) or more (paid) Example site had 670,000+ unique addresses from 10 years of data = $335 at Geocod.io

35. Address Quality Process When addresses are geocoded, you get useful info back: Post Office approved address. If it could not be coded, some indication of why. This gets returned by the geocoding system and can be stored in the batch processing tables, and used in a data quality feedback loop.

36. Questions?

37. Wrap-up Copies of the PowerPoint and supporting documentation are available on our web-site at: http://new.iatric.com/muse-info Or send Thomas an email at: Thomas.Harlan@iatric.com Or give him a call at: (978) 674-8330 (PST)