Proof of Automation

Utilizing Microsoft Power Automate (formally Flows) we built an ETL process that extracts data from a SharePoint list for the Blighted Property Actions Teams Property list into a CSV every hour, can be scaled up or down or run manually. This CSV file can then be consumed by ArcGIS Online from which an Operations Dashboard was created. The only operation that the user has to do is click an Update Button in ArcGIS Online to update the Operations Dashboard. 

We generate a monthly report for departmental leadership on paid overtime. This was previously a manual process and could take a few hours to complete, as we had to pair employee names with division and subdivision. Using Power BI, I improved three things.

• Created a psuedo database using Get Data type 'Folder' where I can drop new payable time files into.
• Created a psuedo database using Get Data type 'Folder' where I drop new PCRs (excel files with employee departments, etc.)
• Connected the two with a relationship through Power Bi to be able to present both overtime and departments.

This let me automate the report and turn a few hour process into a 5 minute update.

Prior to developing this automation, all of our property maintenance letters had to be run by an individual in crystal reports and exported to a pdf daily.  They then had to run another report and manually verify that each letter that should have been included in one of the 7 letter reports was included in that report.  They then had to highlight the missing letters for another staff member to manually fix.  I identified existing but unused functionality in an existing software system (infoview) that allowed us to schedule reports to automatically run and email nightly.  This saved several minutes each day as staff no longer had to wait for each report to run they could just download the pdf from their email in the morning.  I also developed a way to automate the highlighting of the missing letters in the verification report, which saved a staff member a couple hours of time each morning identifying the missing letters and significantly reduced the number of errors that were made in identifying missing letters.  To further reduce staff time spent fixing the errors and running the missing letters individually another report was developed and sent out at the end of each day to all of our property maintenance inspectors letting them know about inspections that were missing the close log stop time which was one of the primary drivers of missing letters.  This automated report allowed the records to be corrected same day and prior to the letter reports running further reducing the number of errors and the time taken to identify and correct our letters daily

     The Department of Public health and Wellness needed a way to automate gathering and management of data related to the Childhood Lead Poisoning Prevention Program.  The need was to track information through the process of Blood Lead Testing, Follow Up Testing, referrals to other professionals, Home Visits, and more.  In addition they needed reports that would display Blood Lead tests inputted into the system the day before, their activities, and the status of individuals moving through the evaluation and treatment process.
     I created a Microsoft Access front end and SQL Server back end database application to do the job.  In addition I used Microsoft SQL Server reporting Services to meet their reporting needs.
     This database application is now used as a reference by the Commonwealth of Kentucky.  

There is a map that is used in PDS to display short term rental registrations which needs to be periodically updated.  I wrote a short Arcpy script that pulls the new data, geocodes it, and updates the source shapefiles.  I realize that this is kindergarten-level coding but I knew zero Python when I started and I am happy with how it turned out.  It will save the user who maintains the map some tedium.

Prior to our four (4) year engagement with Tolemi (formerly known as Opportunity Space), our division had very little insight on how to monitor the performance of our programs. The lack of efficient data and an organized platform to analyze this resulted in mediocre property sales for several years.  Through our engagement with Tolemi, we were able to utilize Open Data and track information such as property sales, foreclosures, demolitions and transfer that information onto their web-based GIS platform.  I worked with Tolemi as a divisional Sharepoint Administrator and GIS Analyst to ensure the accuracy of the information.  We have since automated all of the data sets aforementioned and have create programming in response to information discovered through its analysis.  Last year our sales jumped up 203% through the creation of a vacant home sales program called Last Look.