A large city in the Western US has been working to build a more data-informed government. The City has built an open data portal to share data with the public and released performance metrics to measure how effective government programs are. Additionally, there are a number of dashboards shared internally that are used for decision-making processes. The City has been a part of the What Works Cities program and has a goal of achieving certification. One of its key current projects includes getting data from various datasets and sources, compiling the information, and using it to inform a neighborhood early warning system so departments can proactively resolve issues that are likely to arise.
The City faces challenges that many other cities do as well: they are interested in ways to more efficiently get access to, transform, and make sense of data that lives in a variety of systems hosted both by the City and hosted as a service by other vendors. Additionally, the City is interested in migrating away from some of its legacy on-premises data warehouse tools and is interested in using cloud data technologies.
While the City has a wide range of goals for its data and analytics practice, for this first project phase, it wanted to understand the capabilities of cloud data storage and processing. With a small but talented team of data engineers, analysts, and GIS professionals, they had a goal of understanding the workflow for ingesting, storing, shaping, and ultimately analyzing data all in Azure. Up to this point, they had not done much work in Azure.
An initial use case they were interested in exploring was metrics around Fire Department response times. Previously, they had done work with the Fire Department and had a good understanding of the data, but setting up consistent data pipelines that could combine data from a variety of sources in the cloud was a challenge they wanted to tackle.
After leading an Architectural Design workshop with the data team, BlueGranite was able to draw up a solution architecture leveraging Azure Data Services powered by BlueGranite Catalyst Framework with Data Lake Hydration.
Within the first week of the engagement, BlueGranite stood up the Data Lake Hydrator Framework in the client’s Azure environment and successfully landed most of the data required for the modern solution in Azure.
To address the need to ingest data from both on-premises sources and external sources, BlueGranite was able to use the Data Lake Hydrator to bring in data from on-premises SQL Server and Oracle databases and then also ingest data via an API from ServiceNow (a workflow management tool) as well as data from the US Census Bureau.
"While each of these data sources was important in building out this phase of the data product, showing how data from this variety of sources could all be brought together in one data lake in the same format for easy analysis later was incredibly impactful," recalls BlueGranite Senior Consultant, Sam Edelstein.
To host the Data Warehouse, BlueGranite used Azure Synapse Analytics, leveraging its serverless capability. This decision came in part because of the ability to efficiently transform data, leverage the Synapse workspace notebooks, and provide the ability to scale to more use cases in the future. Most importantly, the SQL functionality was familiar to the client’s work process already.
One additional requirement for the data warehouse was to query an API hosted and maintained by the City’s GIS team. With a given geographic coordinate, the API would return which City Council Ward the coordinate was in. To add this information about Wards to each row of fire response data in the data warehouse, BlueGranite used Synapse Workspaces. BlueGranite created a notebook with code written using PySpark that looped through each row of data and assigned the respective Ward to that row of data. To productionalize this process, BlueGranite added this notebook functionality into the Data Factory build in the Synapse Workspace.
With data ingested from the source, stored in the data lake, and then transformed in Synapse, BlueGranite delivered powerful business intelligence through Power BI, giving the team access to pertinent data about fire incident response – from when calls came in, broken down by station or time, to response times to properties that called 911 frequently.
The client will use this information to look at opportunities to improve fire response while also using the process to build out more use cases for city service delivery in the future.
An overview of the tools used in this solution are below:
Setting up resources and moving data from source systems and REST APIs used to be challenging and would take days or weeks. With BlueGranite’s Data Lake Hydrator, it took hours.
The Data Lake centralized data from a variety of systems and freed up source system capacity. Previously, the client would build analyses and run queries from its production systems, saying that it could take minutes or sometimes hours to get a result.
Synapse set this City up with a scalable data warehouse solution that could be managed using SQL. In addition, using the serverless capability, there were no servers to worry about turning on or off and the cost was within their budget.
Using Power BI, this City could measure its effectiveness in responding to fire incidents and 911 calls, helping to bring more public safety to the community.
Due to the rapid deployment resulting in shortened development cycles and the economies of scale of Microsoft’s Platform as a Service cloud technology, the client has a clear path to a strong return on investment from the solution.
The use of BlueGranite’s Data Lake Hydrator cut down development time by months and will continue to reduce the cost of administration, support, and continued development for years to come.
Finally, the City will be able to use this initial phase of work to better gauge and inform how to respond to fire incident calls more efficiently going forward, with an automated data pipeline that ingests, stores, transforms, and makes available data on a daily basis.
Interested in learning more about how BlueGranite can help accelerate your modern analytics solution? Contact us today.