Cleaning Up Government Data and Freeing Up Time and Resources
Author: John Matyasovsky Jr (@JohnMaty) - Database Manager and Systems Analyst
Team Members: Mike Svidron, Cynthia Zwerge
Company: Fifth Judicial District of PA
Business Partner: eMoksha LLC – Vivek Mehta
Originally Published: 2017 Excellence Awards Entry
Use Case Overview:
We are government. To put precisely, we are local government. Budgets are always tight and we are always looking for something we can use that will be effective, flexible and an excellent ROI. We are a data hub to all other departments and organizations within government. We share data with everyone. This data is critical in providing services to the people of Allegheny County.
We work closely with Vivek Mehta from eMoksha LLC, who first introduced us to Tableau. It was an eye-opening mess. The tool exposed our urgent and clear need to standardize and clean data. To complement Tableau, Vivek then presented us with Alteryx. The effect was immediate. We downloaded the free-trial and BEFORE THE FREE TRIAL EXPIRED, we were able to create and deploy an XML parsing process that would have traditionally taken months to complete. Not only was it completed in record time, but it was completed by someone who is not a traditional data guru. Normally we would need to allocate developer resources to this project, but due to Alteryx’s friendly interface, I was able to complete the project. It was estimated that the free-trial alone paid for the license investment. That was two years ago. We have not looked back since!
Describe the problem you needed to solve:
We are responsible for sharing data with multiple departments and agencies within the county. These data need to be near real time and incredibly accurate. Judges, police officers, Children, Youth and Family workers, and probation offers all use these data in the field and on the bench. We are dealing with real lives and freedoms. Being government however, we are constrained by certain rules and requirements. In certain divisions, we are required to use mandated systems to which we have limited back-end data access. Some of the most critical data we need comes from a mandated system and is available in xml format. We needed to consume, parse, and load these data elements into multiple different systems. Alteryx allows us to do just that.
Describe the working solution:
We created several alteryx workflows and scheduled them to run continuously. Currently we have 14 workflows performing a scheduled ETL on a myriad of data. Additionally we have 117 workflows that are used ad-hoc. (Keep in mind we have been using Alteryx for only 20 months. That’s over 2 developments a week, all by only 2 people!) These data are consumed by the target systems, mostly in SQL server, and made available to the end users through our own proprietary systems and through Tableau Server. The response has been incredible. We have presented our progress at National and State events, specifically the National Association of Court Managers (NACM), and the Pennsylvania Association of Court Management (PACM). No other court in the state is doing what we are doing!
Describe the benefits you have achieved:
As stated above, we are now able to allocate resources differently within our department. Duties that used to be reserved only for developers can now be handled by differently specialized employees, thus freeing up critical resources. Alteryx alone through reallocation of resources has created 1x FTE. Data sharing development times have been halved. We are now creating interactive dashboards that are near-real time and are a boon to the organization. Data sharing has increased tremendously between organizations. I have included samples of what we do on a daily, weekly, and monthly basis within our organization. As you can see, we are invested in Alteryx!
Figure 1 - Case Creation Process for Adult ProbationFigure 1 - Case Creation Process for Adult Probation
Figure 2 - Partial - Case Creation from xml for Adult ProbationFigure 2 - Partial - Case Creation from xml for Adult Probation
Figure 3 - Warrant NotificationsFigure 3 - Warrant Notifications
Figure 4 - Case Lifecycle for Statistical ReportingFigure 4 - Case Lifecycle for Statistical Reporting
Figure 5 - Reporting for PA Department of Probation and ParoleFigure 5 - Reporting for PA Department of Probation and Parole