One live data warehouse for the DC Department of Public Works

Context

The DC Department of Public Works (DPW) knows a great deal about the city: which streets were swept, where the plows went, how many tons crossed the scale, and which routes changed this season. Almost none of that information was usable. It sat in a dozen systems, bought separately over two decades from vendors with no reason to share data.

The usual fix is one interface at a time. System A needs data from system B, so the department pays a vendor to connect them. Then B needs C, and A needs C. Each connection is a separate purchase, only as good as whoever built it, with its own documentation. Each one also ties the department more closely to a system it can no longer afford to replace.

The result was spreadsheets: Excel files, and comma-separated and tab-separated text files. Some vendors handed over data, and others sold a reporting module instead. The department had no live view of its own operation.

Meanwhile, the department's daily question stayed the same: which streets have been done so far today?

What we built

An enterprise service bus, which is one integration layer that every system connects to once. The bus translates each vendor's data into a common set of fields and events. No system needs to understand another system's format, and replacing a vendor does not mean rewriting everything connected to it.

Behind the bus, a data warehouse: one database with one data model. It answers an analytical question in seconds, and staff reach it from the tools the department already had. Parking and towing records arrive as enforcements. Fleet vehicles arrive as assets, alongside asset management. Service requests keep their own fields, separate from the 311 system's.

One rule did a lot of the work: every DPW system reaches the 311 system through the bus, and none connect to it directly. The department needs a few licenses on the 311 side instead of one per connection, and when the 311 vendor changes, only one connection changes.

We also built live operations tracking. Crews enter work from the field, service areas are recalculated when routes change, and data flows to the department's own dashboards. The people who run the operation and the people who report on it see the same numbers. Events can also start work automatically: boot and tow alerts run this way today.

Other parts of the system calculate street sweeping inspector areas, police service areas, and trash and recycling routes in real time. The warehouse connects to ArcGIS Online and ArcGIS Pro for mapping. Field crews enter mowing, leaf collection and street cleaning work on mobile devices, and truck weights are linked back to routes through vehicle assignments.

Why it was hard

The hardest part was commercial. Vendors want you to come to their system to get your own data, and several had never been asked for an application programming interface (API). Each of those was a negotiation, and the ones that went badly went badly early. Part of our job became advising the department on contract language, so that the next contract required an interface.

The rest was about meaning. Each source system had its own idea of what a route was, what counted as complete, and how fresh the data needed to be. Reconciling those definitions is slow, detailed work, and it is most of the job.

Time was the third difficulty. Since 2016, the work has continued through several chief information officers (CIOs), a pandemic, and a full turnover of the people who first specified the system. A system that depends on people remembering how it works does not survive that. Documentation, design documents for every interface, and training for department staff were all contract deliverables.

Results

Reports that arrived weekly as spreadsheets now update within minutes. Parking enforcement updates in one to two seconds. Tows and vehicle locations update every five minutes. Service requests and work orders, the slowest of the five main feeds, update every 15 minutes.

By 2019 the warehouse held 4.5 million parking tickets, with about four thousand added each day. It also held 37,000 tows, 426,000 service requests, 160,697 fleet work orders, 26 million location points from the vehicle tracking system, and 4,200 vehicles tracked as assets. The totals have grown since.

Executives use its dashboards during snow events and inaugurations. The performance team uses it for analysis. Other agencies use it too: the Office of the Chief Technology Officer (OCTO) for maps and open data, the Homeland Security and Emergency Management Agency (HSEMA), the District Department of Transportation (DDOT), and the department's own snow team. The warehouse has outlived most of the systems that feed it, and it is still in use.

What we learned

Start the vendor conversations on the first day. They are the biggest obstacle, and the obstacle is in the contracts. No amount of engineering gets you data that a vendor has decided to keep.

Long projects depend on documentation. The contract keeps renewing because, when a new chief information officer arrives, everything is written down and the department's own staff can run the system.

Agree early on how fresh the data must be. We spent much of the first years persuading the department to expect vendor data in real time instead of weekly. Everything useful that came later depended on that agreement.

The harder half is cultural. When real-time data arrives, someone has to change how they work to benefit from it, and that does not happen by itself. One approach helped: treat each step in a business process as a separate block that can be replaced. That meant fewer duplicate systems bought from vendors, and less retraining each time a system changed.