To give citizens information about state and local government employee salaries, the California State Controller’s Office engaged SymSoft Solutions to help deploy a business intelligence (BI) solution based on the Microsoft platform. With it,
the public can create custom BI using 15 gigabytes of data and 5 million records. The solution also facilitates activism, speeds efficiency, and delivers a scalable foundation to support future BI projects.
The California State Controller’s Office manages the State’s $100 billion budget, which equates to the world’s ninth-largest economy. Before 2010, the only insight that taxpayers had into state expenditures, including employee salaries, came from the media.
After a high-profile controversy over excessive compensation, the State Controller decided to increase government transparency and reduce waste and fraud by giving citizens easy insight into public state and local government payroll data.
In 2010, IT staff designed and deployed an initial reporting solution in just three months based on the Microsoft platform. Known as the Government Compensation in California (GCC) web site, it listed the salaries for some positions in large tables. “The
previous solution displayed about 500 rows of information per page,” says Steve Champeau, Data Processing Manager at the California State Controller’s Office. “If you wanted to find out who was the highest paid county employee in the state, you would have
had to sort and merge the data from each of the 58 county screens.” In addition, to create reports or visualizations, people had to manually cut, paste, and format data using applications such as Microsoft Excel.
To better meet its citizens’ needs, the Controller’s Office decided to create a new BI solution that improved insight into payroll information and provided greater scalability to support future requirements.
||Our 38 million citizens … can use our Microsoft BI solution to view reports and create custom BI about how taxes are spent on 1.5 million employee salaries.
| Kumar Sah
Data Processing Manager
California State Controller’s Office
After evaluating its options, the Controller’s Office engaged SymSoft Solutions to help design and deploy GCC 2.0 with the Microsoft platform, including Microsoft SQL Server 2008 R2 Enterprise software and the Windows Server 2008 R2 Enterprise operating
system. “Some of our developers were familiar with Microsoft tools and there is extensive support and training for the platform,” says Champeau. “The Controller’s Office chose SymSoft based on its track record of successfully managing IT projects and delivering
high-quality, web-based systems with BI capabilities. SymSoft has a first-rate staff and was well-recommended by previous clients. Its contract bid was highly competitive for delivering this project on time and within budget.”
In April 2012, developers from SymSoft and the Controller’s Office began work on a new website that the public could use to access GCC 2.0 (athttp://publicpay.ca.gov). Built
with Microsoft Visual Studio 2010 Premium development system, Microsoft ASP.NET 4, and Internet Information Services 7.5, the site runs on a virtual machine on an HP ProLiant G6 server computer. The team set up another server to manage BI with Microsoft SQL
Server 2008 R2 Reporting Services. It satisfies web requests for reports by obtaining and formatting information from a new data warehouse or, when maps are needed, from Google Maps. The reporting server and data warehouse run on separate HP ProLiant G7 servers.
The warehouse currently holds 5 million records and 15 gigabytes of data in tables—the largest with 4 million rows. The team also designed an online analytical processing (OLAP) cube with SQL Server 2008 R2 Analysis Services to enhance data analysis.
To update the warehouse each day with new information from more than 3,400 government entities, engineers used the Microsoft .NET Framework 4 to develop the GCC Tracking and Validation System. It uses SQL Server 2008 R2 Integration Services and SQL Server
Reporting Services to collect and validate up to 2 million records per year. To help streamline development, control source code, and track bugs, the team used Microsoft Visual Studio Team Foundation Server 2010. “We liked Team Foundation Server so well, we
are migrating 20 of our existing Microsoft .NET applications to it,” Champeau explains.
The new BI-driven GCC solution was made available to the public in November 2012.
With GCC 2.0, California provides more insight into costs, encourages activism, boost efficiency, and facilitates greater BI.
Gives Citizens Instant Insight into Data
Today, anyone can go to the Controller’s website and use GCC 2.0. Kumar Sah, Data Processing Manager at the California State Controller’s Office, says, “Our 38 million citizens—and anyone else who is interested—can use our Microsoft BI solution to view reports
and create custom BI about how taxes are spent on 1.5 million employee salaries.” Static reports show information such as the State’s top 10 wage earners. People can also create their own BI using data filters such as state, county, city, special districts,
and higher education—and visualization tools including charts, maps, and lists.
Encourages an Active Democracy
Successful democracies require engaged citizens. By providing a public-facing BI tool, the Controller’s Office is stimulating discussion and more active constituent participation. Sah explains, “If the public is happy, we’re happy. However, if the public
is unhappy because they learn, for example, that a state psychologist is earning $800,000 a year, then we are still happy. It is our job to provide the information that citizens need in order to ask tough questions, voice their opinions, and help make decisions.”
Champeau adds, “We do continue to receive very good feedback about GCC. Even though it is not clustered, the solution continues to meet all of our expectations for performance and availability.” Designed to support 600 concurrent users, the BI system maintains
99.69 percent availability.
Speeds Staff and Public Efficiency
The Controller’s Office has boosted productivity by improving and automating business workflow processes. “One of the unsung benefits of our Microsoft BI solution is that our business users can now quickly and easily validate data,” says Champeau. “As a
result, employees’ lives are infinitely easier.” Robert Mar, Developer at the California State Controller’s Office, adds, “We can also accommodate more data today with GCC 2.0 because staff now have better tools to track, review, and validate all of it.”
Delivers a Scalable Foundation
The Controller’s Office plans to take advantage of GCC 2.0 to provide more insight into other expenses. “We built a really powerful and scalable architecture with technologies from Microsoft and HP, and we have also developed considerable technical knowledge,”
says Champeau. “As a result, we are well positioned to easily add other types of data, more reports, and new visualization capabilities to further increase government transparency and empower the public.”
This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.