Christchurch recovery efforts made possible through power of business intelligence

Stronger Christchurch Infrastructure Rebuild Team (SCIRT) is responsible for rebuilding the city's 'horizontal' infrastructure – roads, water, sewerage and stormwater – following the earthquakes of 2010 and 2011.

The organisation's remit covers more than 500 repair and construction projects identified to date, and involves expenditure of more than NZ$10 million a week.

The head contractual agreement within SCIRT is an alliance between owner participants (the Canterbury Earthquake Recovery Authority (CERA), the Christchurch City Council and the New Zealand Transport Agency) and non-owner participants (City Care, Downer, Fletcher, Fulton Hogan and McConnell Dowell).

SCIRT has a finite life-span – it will exist only for as long as it takes to rebuild the city; it has no financial targets; it does not operate a balance sheet; and it has no competitors. It also exists to provide accurate and timely information to funding bodies so they can make more informed strategic decisions at a national and regional level.

SCIRT recognised the importance of an effective business intelligence (BI) capability if it was to successfully manage the immense raft of activity it was responsible for.

Solution

SCIRT set about looking for 'best-of-breed' solutions. With guidance from Fulton Hogan, SCIRT engaged two Microsoft partners – Theta to design and develop the overall BI solution, Intergen to provide additional development and project management resource, and consulting company Clarus. Within four months they had built a comprehensive solution using Microsoft's SQL Server 2008 R2 and SharePoint technologies.

"To support SCIRT's mission to deliver 700+ civil projects within five years while keeping all stakeholders informed, a BI platform had to be up and running within three months," says Lance Kenworthy, Group Manager Information Systems, Fulton Hogan.

Data inputs come from four key systems SCIRT uses to manage its projects and track aspects of its work: a cloud-based project management tool called ProjectCentre, a project planning tool called Asta, an Enterprise Resource Planning (ERP) product called JDE, and a Geographic Information System (GIS) tracking asset management information.

The Microsoft technology stack enabled the consulting team to integrate these systems through an ETL (Extract, Transform and Load) process using both Microsoft SQL Server Integration Services and WhereScape RED.

SQL Server Analysis Services summarises the data and pre-aggregates it for fast response times. The WhereScape product confers a full data warehousing meta-data management capability on top of the SQL Server platform. It plugs into the Microsoft BI stack and provides additional visibility into the origin of any data. SharePoint 2010, including PerformancePoint Services and Excel Services, is used with SQL Server Reporting Services (SharePoint Integrated Mode) to deliver the content.

SQL Server facilitates partitioning of data across the underlying disk subsystem. Data is carved (spilt up) onto different disks; high-performance disks for high-volume workloads and slower but less expensive disks for old data or low-volume workloads. It also features a fully integrated security sub-system where users are required to log in only once and are then authenticated and monitored all the way through the system to the data-touch points in the back end.

Deployment was a seamless process, taking less than four months to deliver a working solution, and completed by 2012.

"This has been a very interesting project to work on, all the stakeholders united to work collaboratively to deliver on common goals to ensure the best outcome for the Christchurch rebuild," says Nick Draper, BI Consultant for Intergen.

Benefits

SCIRT's Microsoft-based BI solution allows it to pull a vast array of data, efficiently and accurately, into concise and easily understood reports that greatly enhance its decision-making capabilities. The single portal view and the cloud-based nature of the Microsoft solution mean staff can use the self-service BI functionality provided by SQL Server Analysis Services, SQL Server Reporting Services and the PerformancePoint, PowerPivot and Excel Services tools to undertake and share detailed data analysis quickly and easily.

Enables Essential Visibility

SCIRT staff can manage, digest and easily present the vast amounts of data collected on a weekly basis. It is easier to identify emerging trends. Things like who is spending more time than planned on a particular project, and KPI information like the number of health and safety issues being raised.

Excel 2010 gives the ability to perform ad-hoc analysis across all four key management systems from a single place, making it easier to identify and prevent factors such as wasteful workload bottle-necks and down-time.

SCIRT is able to give its funding bodies access to the cloud-based SharePoint site.

"This is of direct and tangible benefit to the funding bodies we work with. It provides direct access to our reports and data for their own planning and management purposes," said Matt Thomas, SCIRT's Business Systems Manager.

Delivers Control and Self-Service Capability

By combining the data from its four core enterprise applications into a single data warehouse, the BI system gives SCIRT a high degree of control over its organisational information, allowing it to monitor reconstruction projects with relative ease and a high degree of confidence in the interpretations.

Thomas says the Microsoft BI stack plays a crucial role in the rebuild of Christchurch by presenting data in formats that enable informed decisions around the hundreds of reconstruction projects. For example, the system was able to define the optimum number of designs needed 'in the drawer' for an initial 'bow wave' of projects once contractors started to reach their planned capacity. This helped determine additional designers were required, so an extra 40 were drafted in to ensure all the designs were available when they were needed.

"It would have taken two or three full-time staff to provide the visibility we need across all the projects we're involved with and to cope with day-to-day data integration issues," says Thomas.

"We don't have those issues now and I can run all month-end reports in an afternoon.

This control allows SCIRT to manage its projects and processes through exception-based reporting.

"The sheer scale of the work means this is the only practical way to manage activity," said Rob Hawthorne, Theta's Central Region Manager.

"The system will flag when a project parameter is being breached, allowing SCIRT to step in to put everything back on track.

The Microsoft BI solution has proved invaluable for the rebuild of Christchurch by enabling efficient management of all the projects SCIRT is in control of, allowing for early identification of potential issues and greater visibility of specific details and progress of each project. This future-proofed system will allow the organisation to successfully manage all existing and future developments until it is disestablished.