LGPI - Research Project Transformation

Development of an open database financial analysis tool. We worked with a leading Canadian think tank to transform their widely respected research project - the Local Government Performance Index (LGPI). Working with the President and a Senior Policy Analyst, we took the 100 page paper report format, and transformed it into an online interactive financial analysis tool.

Business Outcomes

• On time and on budget. The project was completed before the agreed deadline, within the budget allocated to the project.
• Thought leadership. We provided the Drupal based technology that enabled Frontier to become the first think tank in the world to open their raw research data to public scrutiny and contribution.
• Unleashing public policy insights. The financial analysis tool allows users to compare cities by a single data point, compare cities and regions side by side, and see data presented as tables, pie chart breakdowns or bar charts.
• Engaging public policy stakeholders. Soon after launch, the city of Regina, Canada, became the first city to load their annual reports into the system.
• Improves productivity. Manual collation of research data into a paper format used to take policy analysts weeks, it is now instant and 100% automated.
• Low cost. By leveraging existing Drupal modules, we were able to save development time and cost.

Services Delivered

• Concept development
• Strategic advisory
• Project management
• Graphic design and Drupal theming
• Copywriting Custom
• Drupal module development
• Drupal performance optimization
• Drupal optimized cloud powered website hosting
• Ongoing maintenance and enhancement

Project Summary

The Frontier Centre for Public Policy (FCPP) approached Themesandmodules.com with an idea. David Seymour, Senior Policy Analyst, wanted to open FCPP’s research project up and bring public policy stakeholders together, around the objective of improving the reporting standards of Canadian municipalities.

We worked with David and Peter Holle, President, to formulate a strategy to bring the project online and open the data up.

The project is a world first. The Frontier Centre is the first think tank to open a research project up to this extent. Anyone - particularly members of the public, civil servants and political activists, can contribute data, and challenge the accuracy of any data already in the system. This openness results in better data, and ultimately, better public policy decisions.

The front end of the website is a financial analysis tool that provides two ways to view the database.

A user can view all cities for a single datapoint - for instance, the total tax revenue for all cities in Canada.

Or, a user can view all data points for a single city - for instance, a complete table of financial reports for Toronto, with pie chart breakdowns of expenditure and revenue, and bar chart comparisons of Toronto against regional and national averages.

Whilst browsing data, a user can click on any data point, log in, and change it if they believe it to be incorrect. This utilizes Drupal’s inbuilt revisioning system to keep a complete audit trail of changes.

Technical Highlights

This was a very complex project, and throughout the project, we were presented with many technical challenges.

The first challenge was the number of datapoints - each city record has over 600 different data fields. This pushed CCK module to its limits. Building a completely custom module in place of CCK to go direct to the database would have eliminated some issues, but it also would have dramatically pushed the cost of the project up. By pushing some processing back to the database, using some custom code a doing some performance tuning, we managed to deploy the system using standard Drupal modules.

Drupal tends to promote data processing in the programming language layer because this is appropriate for content orientated websites. But for a data orientated financial analysis application, with hundreds of records and over 600 data fields, the standard practice wasn’t going to work. During the performance optimization phase, we developed a technique to keep most of the data processing in the database to achieve a performance improvement of over 500%.

Data migration was also challenging, and some customizations were made to the Table Wizard and Migrate modules in order to automate the mapping of hundreds of fields.

The LGPI uses pie charts to break down budget categories, and bar charts to provide quick comparison views. The Drupal Fusion Charts module was uses as the basis for all charts in the system. It was heavily extended with an additional custom module, in order to meet the functional requirements of the system.

A custom module was built to manage the presentation of financial report tables. This used standard Drupal table theming functions as a base, and extend them to handle multiple column levels and improve performance.

About The Frontier Centre

Ideas for a better tomorrow.

The Frontier Centre for Public Policy (FCPP) is an independent, western Canada based public policy think tank with offices in Winnipeg, Regina and Calgary. Described as a “global go to” think tank by Foreign Policy Magazine, it is one of the top 25 think tanks in North America.

FCPP policy notes are read by over 10,000 opinion leaders and broadcast weekly on 13 Canadian radio stations. It regularly publishes commentary in the major Canadian daily newspapers and reaches an estimated 58 million people on a regular basis (2009).