Utah finance sites get up and running on a slim budget, using open-source tools
- By Edmund X. DeJesus
- Oct 12, 2009
In March 2008, the mandate from the Utah State Legislature was clear: Replace the paper-based process for requesting public financial information, and make the finances of Utah government transparent to its citizens. The challenge was also clear: Acquire the necessary government financial data, stage the data for access, and create a Web site interface to enable that access, all on a slender budget. This was the mission for the Utah Public Finance Web site project.
“We created several different proposals to meet the objectives,” said John Reidhead, director of the Division of Finance. The proposals ranged from using outside developers and hosted solutions to developing the site completely in-house. Unfortunately, all of the proposals exceeded the project budget. That’s when they started to get creative.
“We were committed to the success of this project, so we looked for places to save money elsewhere,” Reidhead said. One source of savings came from the choice of software. Rather than using commercial software, with the accompanying licensing costs, the project decided to use open-source software, including the MySQL database, the GlassFish application server, and the Hibernate persistence framework.
Open Approach: Director John Reidhead, third from left, and the team at the UtahDivision of Finance delivered a transparent site on a small budget.
The project team also leveraged the existing skills of the participants. “The Division of Finance has a lot of experience designing, creating, and using data warehouses,” said Robert Woolley, chief technical architect for Utah's Department of Technology Services (DTS). Rather than hiring new developers, they used existing staff for the project. Utah’s prime technology partner, Utah Interactive, had expertise in designing and creating Web sites for Utah government
, and the DTS is an award-winning provider of cross-agency services. The collaboration of these and other Utah agencies made for a winning combination.
A major aspect of the Utah Public Finance Web site project was designing data structures that were generic and flexible enough to accommodate all kinds of data. This required significant input from the Utah Transparency Advisory Board and several levels of government. “We knew that many agencies would be using this solution, and we had to anticipate that,” Reidhead said. The resulting data structures allow entities to define their data appropriately and describe it meaningfully for users.
After completing a prototype in October 2008, demonstrations of the proposed Web site were held for the Utah Transparency Advisory Board, which was overseeing the project, and the entities that would be participating in it. The demonstrations yielded suggestions to improve the interface for non-expert users, as well as enhance back-end performance. “This cooperation was essential, or it wouldn’t have worked,” Reidhead said.
The resulting site, transparent.utah.gov, offers a simple but powerful interface. Users can select a level of government, specific entity, transaction type, and time period from drop-down menus. Data mining techniques let users filter the results by fund, organization, category, and vendor. At any point, the user can drill down to more detailed information by simply clicking on links, and even view the results along a different dimension. As users drill down, the site maintains the previous search window so that users can keep track of where they are, backtrack if necessary, and try new data paths.
Online videos show new users how to use the site effectively. Both simple and advanced searches for specific information are provided. Database queries are cached, using the Hibernate persistence framework, to provide rapid response to iterative searches. Users are able to download information as simple text or Excel-compatible spreadsheets, enabling off-line analysis. The size of downloads is currently limited to avoid overloading the servers with the results of large queries.
The initial data offered on the site was mainly state revenue and expenditure information for the current year. However, the legislation makes it clear that the choices will be increasing dramatically. Regulations require that state agencies, institutions of higher education, school districts, local and municipal governments, and even special district agencies (such as water and power boards) must either participate in the Utah Public Finance Web site project, or create their own equivalent, within specific deadlines. In addition, more kinds of data are becoming available. “We’re now adding payroll information,” said Brenda Lee, project manager for the Division of Finance. There is also the possibility that historic data could be staged for access.
To facilitate the participation of entities, Utah Interactive has created special tools for managing information and moving it to the Web site for display. Entities can automatically upload data in a standardized file to a self-service administration portal. These tools are simple for agencies to use but also offer password protection for security. The generic, flexible data structures enable relatively simple mapping of data from different sources and formats. Help is available for smaller government entities that might not have the personnel or expertise to comply with the requirements otherwise.
The project has not been without its challenges. For example, concerns about the privacy of personal information has led to some critical decisions. “The budget does not permit staging personal information securely, so we exclude personal information and certain data fields before pulling the data into the database,” Lee said. In addition, citizens often want more than mere numbers -- they want to know why money was spent the way it was. The Utah Public Finance Web site project accepts such questions and suggestions, which will also influence future developments.
The resulting database is proving to be very popular. “Success is not only the number of users but whether people are getting the information that they want,” Woolley said. The addition of new sources and types of information should ensure that the site continues to be valuable to residents. “The future is where the power is going to be with this,” Reidhead said.