Watchdog Group Brings Freedom of Information Data to the Public

Imagine studying the financial books of a rural county government and discovering that a retired judge, who had also served as a states attorney and police officer, was taking not one but THREE pensions, costing local taxpayers thousands of dollars a year. Or finding the data to prove that city plumbers in New York were making as much as $500K in bonuses every year.

This is the power of data – and it’s a power that this nonprofit transparency group wanted to put into the hands of the public. To do so effectively, they opted for a powerful, flexible and fast data warehouse framework and PowerBI system built by SPR.

The mission of the not-for-profit political watchdog group is to keep an eye on government spending. The organization collects publicly available data in several categories from the federal government, all 50 states, and the vast majority of state and local governments in the United States. To provide interested parties with transparent access to the expenditures made by each jurisdiction, the group files an ongoing stream of Open Records and Freedom of Information (FOI) requests every year. The result is a massive amount of local, state, and federal data on salaries, checkbooks, and pensions that is gathered and collated much faster than any individual or group could on their own.

While the watchdog group had a public web site and mobile application that allowed users to manually search for certain expenditures; the process was slow and could take many hours for one report. They were interested in creating a data-as-a-service business model that would give users the tools to dig deeper, search more broadly, and run real-time reports on the data in the database. By monetizing the service, subscribing customers would enjoy greater freedom, faster speeds, and 24/7 access to the data; at the same time, they would begin to make their mission self-supporting.

While the leadership at the watchdog group had the technological experience to write and manage their own code, they looked to SPR to help them create a framework that would take their efforts to the next level, helping them reduce costs and get to market faster.

SPR was asked to help envision and build a world-class online experience for analysts, journalists, and watchdogs. Working through an agile inception process, SPR and the group’s leaders identified the full set of business features the application needed and created a master story list of potential uses for the data – such as news articles, campaign intel, and even criminal investigations. They held workshops to help define the scope of the software to be built. This enabled all parties to agree on a shared vision for the portal.

Most of SPR’s work focused on warehousing: capturing massive amounts of external data, housing it consistently, and making it accessible for exploratory research – while also protecting it from unauthorized use. To increase efficiency and scalability, SPR helped rearrange their backend and move it to a SQL server online. The data warehouse holds 3 years of data – billions and billions of records – that have all been normalized so that they can easily be blended with other data. They created this new repository, process, and System Admin set-up so that data could be imported at a speed that met the watchdog group’s requirements. To test and optimize performance given the large amounts of data behind the scenes, SPR and the group also set-up PowerBI reporting capabilities for the site.

“If it weren't for the framework created by SPR, we wouldn’t have what we have today,” says the group’s chief operating officer. The resulting portal runs like “a well-oiled machine. It's shocking how fast it operates now: a report that used to take us hours to create manually can now be rendered in all its glory in less than 45 seconds. We haven’t created a report by hand in over a year.“

Today, the watchdog group’s easy-to-navigate, flexible and robust portal makes 3 years’ worth of state and federal records available to the public for research and analysis – and has a growing list of subscription users as well – providing interested parties with the tools to hold our elected officials accountable.