Customer Solution Case Study
/ Washington State Posts Spending Online as a Web-Based Money-Tracking Map
Overview
Country or Region:United States
Industry:Government—Regional/state
Customer Profile
The Washington State Department of Commerce provides economic and community development opportunities to citizens, businesses, and governments throughout the state. It is based in Olympia and has about 300 employees.
Business Situation
The department receives many requests from legislators, businesses, and others about money it disburses throughout the state. Responding to these requests quickly and accurately was a challenge.
Solution
The department used business intelligence software from Microsoft to build a data warehouse for expenditure tracking and the Bing Maps Platform to create a map-based tool for people to browse online.
Benefits
- Better accountability and transparency
- Improved usability and performance
- Less development time, more innovation
Randy Ayers, Applications and Database Manager, Washington State Department of Commerce
The Washington State Department of Commerce disburses millions of dollars annually for community and economic development projects. Each year it fields many queries for information about its disbursements, from legislators, federal agencies, businesses, and the public. To respond faster and more accurately to these requests, the department created a data warehouse for collecting spending data and an online money-tracking tool to present the data as interactive maps, charts, and graphs. The department used business intelligence software from Microsoft to create the data warehouse and the Bing Maps Platform to create Commerce Money Tracker, and today can provide better expenditure accountability and transparency than ever before. With the flexibility of Bing Maps, IT staff can turn out enhancements faster and spend more time creating innovative strategies for the department.
Situation
The Washington State Department of Commerce is the state’s business champion, tasked with charting a statewide economic and community development strategy to “Grow and Improve Jobs.” It disburses millions of dollars annually through grants and loans for housing programs, aid to vulnerable populations, public works assistance, energy efficiency and renewable energy development, and infrastructure projects that help keep communities growing. The department headquarters are in the state capital of Olympia.
The Washington State Department of Commerce frequently receives requests from the business community, the state legislature, federal agencies, community organizations, and others for information about its expenditures across the state. However, responding to these requests often took days to weeks. The department had to consult more than 30 independent data-tracking systems and hundreds of Microsoft Office Excel spreadsheets, and often found itself with multiple versions of the truth.
When the U.S. Congress passed the American Recovery and Reinvestment Act (ARRA) in February 2009, the department’s reporting challenges intensified. While ARRA provides more than U.S.$300 billion in funding for government organizations and government-related programs, the legislation places a heavy reporting burden on state and federal officials to track allocated funds and report on projects.
“Even before ARRA was passed, we knew that we needed to have a better way to track and report on disbursements, to provide better accountability and transparency to our constituents,” says Randy Ayers, Applications and Database Manager for the Washington State Department of Commerce.
Solution
In 2007, the department received legislative funding to develop a data warehouse, which would simplify disbursement reporting by enabling the IT staff to funnel data from multiple systems into one central repository. Because the Department of Commerce was already a heavy user of Microsoft software and had an Enterprise Client Access License Suite and Microsoft server software licenses, it decided to build the data warehouse on business intelligence software from Microsoft. It used Microsoft SQL Server 2005 data management software as the data store, Microsoft SQL Server Analysis Services for data analytics, Microsoft Office PerformancePoint Server 2007 for dashboard and charting features, Microsoft Office SharePoint Server 2007 as the reporting portal, and Windows Server 2003 as the underlying operating system.
Because the primary audience for the spending data would be nontechnical users, the department knew that a mapping adjunct to the data warehouse would be critical in presenting expenditure data to legislators, business people, and the public, both visually and interactively. The department wanted to pull data from the data warehouse and overlay it on a state map that users could explore interactively. The team called the map-based money-tracking component of the reporting solution the Commerce Money Tracker.
Mapping Performance Improvement
The Department of Commerce IT staff worked on the reporting solution throughout 2008, introducing the data warehouse and Money Tracker in July 2009. To reduce costs, the development team developed the original Money Tracker using a GIS solution that the department had already licensed. However, with the initial project requirements met, the development team immediately began looking to improve the reporting solution. “As people started using the application, we found that the mapping engine was slow,” says Reza Khoshbin, GIS Applications Developer at the Washington State Department of Commerce.
Part of the problem was that the GIS solution provided detailed geospatial data representations with far more detail than the Money Tracker application needed, and the map redraws simply took too long for interactive data exploration. In late 2008, the development team brought in Google Maps to augment its GIS solution with surface-layer mapping. Still, performance was not as snappy as the IT team wanted, and the increased number of software solutions in Money Tracker complicated maintenance.
Around mid-2009, the team decided to investigate the Bing Maps Platform from Microsoft. “We saw major improvements in Bing Maps, including broad geographic coverage and support for polygon creation,” Khoshbin says. “We didn’t need really granular displays such as rivers or pipelines, which were the existing solution’s strong points. Plus, if we could replace Google Maps, it meant that we could eliminate more third-party, non-Microsoft programs from our solution mix.”
Khoshbin and his colleagues found abundant information and code samples on the Microsoft website and used Microsoft development tools to integrate Bing Maps into Money Tracker. “We had already built several iterations of Money Tracker, so we knew exactly what we were looking for in Bing Maps,” Khoshbin says. “It took one person two months to replace Google Maps and our old application with Bing Maps.”
At the same time that it introduced Bing Maps to Money Tracker, the IT team incorporated the Microsoft Silverlight 3 browser plug-in into the part of Money Tracker that displays federal stimulus funding expenditure. “With Silverlight, the user experience is much better and faster,” Khoshbin says. “It feels like you are running a desktop application instead of a web application.”
Map-Based Money Tracker
The Washington State Department of Commerce Money Tracker provides businesses and citizens with detailed visual data about all contracts executed by the Department of Commerce since July 1, 2003—more than 13,000 contracts. Contracts are categorized by their primary purpose within each division of the agency. Citizens are able to view simple charts and maps, some with drill-down capabilities. The map views show where contracts have been executed by county or legislative district. The Money Tracker also tracks ARRA recovery stimulus funding received by Washington State.
A citizen wanting to know how much money King County received in housing funding would start with a state map that is divided into counties. The person clicks on King County, and a popup window appears that shows the total number of contracts and their value, with breakdowns by area—community services, housing, economic development, and so forth. When the person clicks on “housing,” a pie chart appears, showing all the housing contracts in King County. The person can then drill down further into the pie chart to see a list of all the actual contracts.
Benefits
As a result of its new data warehouse and Money Tracker tool, the Washington State Department of Commerce can provide better expenditure accountability and transparency than ever before. Not only does it have a single expenditure repository but it can present the data in a visual, user-friendly way that enables many users to self-service their own information needs. Because the warehouse is built on Microsoft software, the Department of Commerce can turn out enhancements faster and spend more time creating innovative strategies.
Better Accountability and Transparency
“In this age of intense government oversight, it’s incredibly important to provide fast, accurate answers to legislators and the public about state spending,” Ayers says. “With Money Tracker, the Department of Commerce is providing a level of transparency that it’s never had before. We can also report on ARRA stimulus funding with this tool, so we don’t have to develop a separate reporting engine for that.”
Now, when the Department of Commerce receives questions from legislative staff, nongovernmental organizations, or others, it simply sends them a link to the Money Tracker tool and lets them explore the data for themselves. “If users really don’t feel comfortable using the online tool, we will fulfill the information requests for them, but we can now do so in minutes, rather than the days or weeks that it took in the past,” Ayers adds.
Improved accuracy is just as important as speed of response. Because reports are pulled from a common data warehouse, all represent the same “version of the truth,” which the department struggled with previously when composing reports from multiple data sources.
Improved Usability and Performance
Ayers notes that data warehouses are often not used because of their complexity. “A data warehouse for the public is especially tricky, because you can’t train your users,” he says. “However, with Bing Maps, we can give access to a broader set of people, because Bing Maps is so easy to use and has very snappy performance. People really like using the Money Tracker. We have received several compliments from users saying how nice it is. There is a lot of complex data there, and people find it very easy to use.”
Khoshbin adds, “The usability of Bing Maps is so much better, and the performance is incredible. When users would click on the previous tool map, it took 30 seconds to load; with Bing Maps, it’s only a couple of seconds. The user experience is smoother and faster, with no full-screen repaints. A great user experience is critical in drawing people back to the site.”
Reduced Development Time, More Innovation
Also, by using Bing Maps, the Department of Commerce IT staff can turn out enhancements much faster. “Reza had to write a whole new GIS [geographical information system] in migrating to Bing Maps but did it in only two months compared to the six months it took with the previous mapping software,” Ayers says. “Plus, the resulting site looks far better than it did before.”
Khoshbin adds that for the Money Tracker application, the team did not need all the horsepower of a full GIS application; it only needed a visualization tool with light GIS modeling. “Bing Maps is perfect for this, without the processing burden of a heavy-duty back-end,” he says.
The Department of Commerce recently underwent a staff and program reorganization, which will test the flexibility of the data warehouse. Some programs that received funding will merge and others will disappear altogether. “We will have to retool our data warehouse to match our organizational changes,” Ayers says. “But with our flexible foundation of Microsoft software, we believe that we can make the changes easily and still keep the public apprised of what they want to know.”
Bing Maps Platform
Build immersive mapping experiences with the Bing Maps Platform. Thousands of organizations and developers worldwide depend on Bing Maps for rich imagery, innovative APIs, and leading-edge technology.
From engaging customer interactions to insightful business analysis, Bing Maps provides the mapping platform to help developers and organizations realize their vision.
For more information about Bing maps, go to: