Chief Data Officer, City of San Diego
Data to Decisions
The City of San Diego is the government agency that runs and maintains San Diego, including 11,000 employees and 35 separate departments. The City of San Diego is responsible for parks and recreation, neighborhood and street maintenance, and establishing incentives and programs that help grow and expand local businesses, provide support for homeowners and ensure public safety. The city produces, tracks and stores enormous amounts of data. Some of that data is systematically shared inter and intra departmentally, while other data is used to report to state and federal agencies.
The city’s chief data officer, Maksim Pecherskiy, joined in November of 2014 to implement its open data initiative called for by Mayor Kevin L. Faulconer's One San Diego Transition Advisory Committee.
In order for Maksim to implement the city’s open data initiative, he needed the tools to help put into practice the principles of open, accessible, efficient and transparent government.
For example, the Street Division of the city looked to use data to provide a safe city street system through effective and efficient maintenance. Maksim needed a way to enable analysts to run and create their own queries on the data.
The Pavement Management System (PVM) is an app built on top of SQL server using queries and stored procedures. On a monthly basis, the team updates IMCAT, a system the city uses to coordinate street work across departments. The app creates efficiencies, ensuring that the same segment isn’t being dug-up multiple times and will allow citizens to report on the conditions of their streets.
He had to help the team pull a report from the PVM generated by a query and a stored procedure, and find a way to create consensus for categories in the 319 tables that held the street data.
Maksim realized that to enable the programs of the open data initiative, the city would need a solution that could document all of its data sources and allow different teams to easily find, understand and trust data. The city invested in Alation to be able to maintain data catalogs and keep documentation on data sources to ensure accuracy and allow analysts to find, understand and trust the data they need to run open data initiatives.
The city’s first end-to-end open data project was the Pavement Management System (PVM), which provides maps so citizens can see when their street was last paved as well as an index for the streets’ conditions. Alation brought continuity to the fields within 319 tables that contained PVM data, ensuring accuracy of the data and the open data for developers, who might use it in an application and to power the map that lets citizens learn about the condition of their streets and when they will next be paved.
Implementing Alation has begun to enable the City of San Diego’s analysts to find, understand and trust the data they need to fuel the open data initiative. Data is critical to improving the City’s democratic processes through the sharing of information with the public.
With Alation, the City of San Diego is able to better document the data it has and keep its data users better informed. The data it uploads monthly to its IMCAT system has to be in a particular format with certain columns, so the team could not directly trace the dataset to the query running against the PVM application. As the PVM has 319 tables, they could not simply guess the query and match the result either.
The team built a robust query to continuously pull data, using Alation to trace the query the PVM was running. However, they had no visibility into the stored procedure, so they bypassed this by combining information from using the query, the streets team and Alation’s query logs.
In this way, Alation helped bring transparency, consistency and accuracy to the City’s first end-to-end open data initiative project. Alation is a key component of the City’s effective data discovery and management, allowing staff and citizens to access accurate and up-to-date information.
- Brought down a manual, time-consuming and error-prone process from 10-20 hours monthly to under 3 seconds every night.
- This let the streets engineers do what they do best, while giving them the most up-to-date data.
- Having a better understanding of the data has allowed the City to provide more assistance to the streets team on their data needs.
- Releasing streets data on the open data portal contributed to the data team’s KPI for releasing 46% of identified high value data sets by June 30, 2017.
- Streets data is also published on PerformSD, the City’s first performance dashboard where residents and decision-makers can check in on progress on a variety of indicators.
- StreetsSD, the interface for paving information, broke new ground as a web app recognized by an award from the American Public Works Association, due to its relationship to helping residents engage better with infrastructure issues (this type of award is typically reserved for brick-and-mortar infrastructure projects).
The City of San Diego leveraged Alation, the first data catalog built for collaboration to search, query and collaborate around data and achieve faster, more accurate customer loyalty insights. Alation is generated and updated using machine learning and improved through human collaboration. On top of the data catalog, Alation gives analysts easy-to-use interfaces that surface the knowledge stored in the catalog through recommendations that increase analyst productivity by 20-50%.
- Non-technical people are now able to explore databases.
- The City received huge gains in time spent trying to figure out queries.
- Allows the City to keep documentation and notes with caveats of certain fields.
- Very well executed – the team spun up a server, gave Alation VPN and they take it from there.
Improving the democratic process through sharing information is a top commitment for the City of San Diego. Creating an open government requires effective data discovery and management. Alation was the key component in its data-driven strategy, enabling constituent visibility tools like the StreetsSD initiative, and allowing it to provide accurate, up-to-date information. StreetsSD also received a 2017 Honor Award from the American Public Works Association for its innovation.