Past Analytics Excellence Awards

JulieH · ‎04-28-2016

Author: Shelley Browning, Data Analyst

Company: Intermountain Healthcare

Awards Category: Most Time Saved

Describe the problem you needed to solve

Intermountain Healthcare is a not-for-profit health system based in Salt Lake City, Utah, with 22 hospitals, a broad range of clinics and services, about 1,400 employed primary care and secondary care physicians at more than 185 clinics in the Intermountain Medical Group, and health insurance plans from SelectHealth. The entire system has over 30,000 employees. This project was proposed and completed by members of the Enterprise HR Employee Analytics team who provide analytic services to the various entities within the organization.

The initial goal was to create a data product utilizing data visualization software. The Workforce Optimization Dashboard and Scorecard is to be used throughout the organization by employees with direct reports. The dashboard provides a view of over 100 human resource metrics on activities related to attracting, engaging, and retaining employees at all levels of the organization. Some of the features in the dashboard include: drilldown to various levels of the organization, key performance indicators (KPI) to show change, options for various time periods, benchmark comparison with third party data, and links to additional resources such as detail reports. Prior to completion of this project, the data was available to limited users in at least 14 different reports and dashboards making it difficult and time consuming to get a complete view of workforce metrics.

During initial design and prototyping it was discovered that in order to meet the design requirements and maintain performance within the final visualization it would be necessary for all the data to be in a single data set. The data for human resources is stored in 17 different tables in an Oracle data warehouse. The benchmark data is provided by a third party. At the time of development the visualization software did not support UNION or UNION ALL in the custom SQL function. During development the iterative process of writing SQL, creating an extract file, and creating and modifying calculations in the visualization was very laborious. Much iteration was necessary to determine the correct format of data for the visualization.

Other challenges occurred, such as when it was discovered that the visualization software does not support dynamic field formatting. The data values are reported in formats of percent, currency, decimal and numeric all within the same data column. While the dashboard was in final review it was determined that a summary of the KPI indicators would be another useful visualization on the dashboard. The KPI indicators, red and green arrows, were using table calculations. It is not possible to create additional calculations based on the results of table calculations in the visualization software. The business users also requested another cross tabular view of the same data showing multiple time periods.

Describe the working solution

Alteryx was instrumental in the designing and development of the visualization for the workforce dashboard. Without Alteryx the time to complete this project would have easily doubled. By using Alteryx, a single analyst was able to iterate through design and development of both the data set and the dashboard.

The final dashboard includes both tabular and graphic visualizations all displayed from the same data set. The Alteryx workflow uses 19 individual Input Data tools to retrieve data from the 17 tables in Oracle and unions this data into the single data set. Excel spreadsheets are the source for joining the third party benchmark data to the existing data. The extract is output from Alteryx directly to a Tableau Server. By utilizing a single set of data, filtering and rendering in visualization are very performant on 11 million rows of data. (Development included testing data sets of over 100 million rows with acceptable but slower performance. The project was scaled back until such a time as Alteryx Server is available for use.)

Describe the benefits you have achieved

The initial reason for using Alteryx was the ability to perform a UNION ALL on the 19 input queries. By selecting the option to cache queries, output directly to tde files, and work iteratively to determine the best format for the data in order to meet design requirements and provide for the best performance for filtering and rendering in the visualization, months of development time was saved. The 19 data inputs contain over 7000 lines of SQL code combined. Storing this code in Alteryx provides for improved reproducibility and documentation. During the later stages of the project it was fairly straight forward to use the various tools in Alteryx to transform the data to support the additional request for a cross tab view and also to recreate the table calculations to mimic the calculations the visualization. Without Alteryx it would have taken a significant amount of time to recreate these calculations in SQL and re-write the initial input queries.

Our customers are now able to view their Workforce Optimization metrics in a single location. They can now visualize a scenario in which their premium pay has been increasing the last few pay periods and see that this may be attributed to higher turnover rates with longer times to fill for open positions, all within a single visualization. With just a few clicks our leaders can compare their workforce optimization metrics with other hospitals in our organization or against national benchmarks. Reporting this combination of metrics had not been attempted prior to this time and would not have been possible at this cost without the use of Alteryx.

Costs saving are estimated at $25,000 to-date with additional savings expected in future development and enhancements.

JulieH · ‎08-20-2017

PATH logo.png

Author: Jeff Bernson, Senior Director, Results, Management, Measurement and Learning

Company: PATH

Team Members: Mike Hainsworth, Allan Walker, Doug Morris, Anya A’Hearn, Philip Riggs, Chris DeMartini, Jonathan Drummey, Jeff Bernson, Daniel Bridges, Joe Mako, Ken Black

Awards Category: Best 'Alteryx for Good' Story

PATH is the leader in global health innovation. We are driven by an unshakeable commitment to health equity and a bold belief in the power of innovation to improve health, save lives, and transform the future, especially for women and children. Working with partners across the public and private sectors, our trusted experts bring together the right tools and people at the right time and place to accelerate innovative solutions to the health challenges facing vulnerable women, children, and communities worldwide. By tenaciously supporting solutions all the way from early concept to large-scale use, PATH’s work translates smart ideas into tangible change for millions of people.

Describe the problem you needed to solve

In Africa, malaria is the leading cause of death in young children. For the past decade, the Zambian National Ministry of Health and the National Malaria Elimination Centre (NMEC) with support from PATH have used data to fight malaria—saving thousands of lives. In 2015, PATH teamed up with Alteryx and six other technology partners to launch Visualize No Malaria—a campaign focused on integrating new tools and systems for data use to support Zambia’s Ministry of Health in their effort to eliminate malaria by 2021.

The PATH-supported malaria laboratory in Lusaka, Zambia, located at the NMEC is implementing a range of molecular tools in the quest to achieve elimination. Every month, thousands of blood samples from around the country are processed to answer key questions around issues such as diagnostic efficacy, drug resistance, and transmission intensity.

Prior to our partnership with Alteryx, the lab was ill equipped to deal with these data. At the time, our laboratory information system was costly, cumbersome and necessitated building a custom database for each study. We needed a flexible, agile tool that could grow with our needs, namely, a system that would provide a simple mechanism for reporting data, aggregating it, and producing summary outputs.

Describe the working solution

Alteryx software has transformed how we access data. As we experimented with Alteryx, it quickly became apparent that we could almost instantly curate, process, and archive large amounts of data to inform our field activities and to better understand what’s really happening with malaria transmission patterns and dynamics in near real-time.

Our partnership will empower frontline health workers with the critical tools to prevent, track, and treat malaria cases to help eliminate this deadly disease. We are improving data accuracy and making real-time critical data-informed decisions about how and where to tackle outbreaks. We are also building the skills of district and facility health teams to combat the disease at community level. If successful, this model could serve to inform global efforts to end malaria for good.

PATH and its data science partners are using Alteryx in a number of ways to help lead the charge toward elimination including:

1. Automating workflows that help us process lab results and notify health workers of positive malaria cases faster in targeted study sites. Prior to the introduction of Alteryx, we essentially were unable to provide feedback on results in any meaningful way. Now instead of waiting months, we can process results on a daily basis with automated outputs sent to the end-user for dissemination and to inform decisions.

PATH Workflow 1.png

2. Responding to survey data during field data collection. Survey data is usually evaluated at the end of the process, which is fine if everything has gone well, but potentially catastrophic if errors were introduced. Through Alteryx, we have been able to access survey data as it is collected through a cloud API and then validate and summarize data to ensure that we are on track. The speed of this process meant that we were able to not only identify and resolve issues during the survey, but could rapidly and iteratively interrogate the data to ensure high data quality.

PATH Workflow 2.png

3. Developing a workflow to enable automated prediction and forecasting of malaria cases in the Southern Province of Zambia that can be batched on a weekly schedule.

4. Leveraging Alteryx for geospatial data processing and analysis to create modeled boundaries such as facility catchment areas, entity relationship, and resource optimization diagrams, such as health worker to Facility network diagrams, and input layers for other geographic processing and rendering tools, such as Mapbox.

PATH Mapbox.png

5. Pre-processing and merging of geospatial and epidemiological data for ease of use in front end rendering and analytic tools, such as Tableau.

PATH Tableau Viz.png

Describe the benefits you have achieved

The net result from this work has been a more efficient workflow process, more insightful analyses, and the development of a more holistic approach to understanding the complete malaria picture in Southern Province. Alteryx put the power in the hands of the user so we can build exactly what we need when we need it.

Since deployment of these tools, along with a number of strategies to further accelerate the gains against malaria, since 2014 there has been a 90 percent decrease in malaria cases in the Southern Province. As transmission rates decrease and malaria cases become increasingly localized, combining the existing interventions with capabilities of data analytics tools like Alteryx will play an essential role in combating the disease at the community level and bringing the number of cases to zero.

PATH Benefits 1.png

Past Analytics Excellence Awards

Excellence Awards 2016: Shelley Browning - Most Time Saved

Excellence Awards 2017: PATH - Best 'Alteryx For Good' Story