Data Analytics and Reporting on the UK’s Biggest Road Construction Project
Highlights: The project data and reporting team (Mark Tootell and Jon Kirby) from the A14 (a £1.5bn road construction project) provided a talk to the London Project Data Analytics Meetup on 16 July 2018. They created the reports and the associated data architecture from scratch. Using a combination of SQL server and PowerBI they have created a portfolio of reports that cover a wide range of topics from 3 week look aheads to reports on productivity data. They are also able to track the quality of the schedules when compared to good practice outlined within DCMA planning standards. All of this was developed at a cost of ~£300k. It is maintained by a team of 2 people.
The move away from bespoke, offline spreadsheet to a centralised data respository that provides a single source of the truth has transformed how project data is reported. This in turn has enabled the management team to migrate from paper based reports to presentations on tablets, hinged around real time analysis of data. It provides the ability to provide an overview of performance, whilst also providing the capability to drill down into the raw data.
The team is also developing a range of mobile apps to ease the burden of harvesting data. Rather than conduct this work in the UK, they have a contract with a team in India to develop the apps within an agile development environment. They elected to develop the PowerBI capability themselves rather than via a consultancy because of the need to iterate and ‘learn as we go’, challenging from within at every step of the journey.
The team commented on the challenges and obstacles with the data. The key ones that stood out were:
- The need for people to move away from emailing spreadsheets towards using the central data repository. Jon and Mark recognised this as a constraint so developed a spreadsheet to enable teams to work locally in excel, but to automatically import and export centralised data rather than retain it on local hard drives.
- The need to trade off the lack of data verification and validation against the desire for access to real time data.
- The lack of competence within key members of the team in basic data analytics and the need for everyone to have a grasp of the fundamentals.
- The lack of suitably qualified and experienced people to support a data analytics driven approach. They had shortlisted 11 people for a role and none of them were sufficiently experienced in the practical application of DAX within a project environment.
The A14, A303 and Thames crossing teams are now collaborating to explore how to further develop the capability. Highways England have appointed Microsoft to advise on the potential to extract even more value from the data, moving from descriptive towards predictive analytics. This has the potential to transform how project delivery data shapes the delivery of future projects.
My own thoughts:
- The A14 team have set the bar within construction for real time reporting from a single, integrated dataset. They deliver this with 2 people which is a great achievement.
- The range of reports is tremendous and incredibly powerful. But will there become a point where managers are overwhelmed by data and charts. It makes me think of the human factor challenges associated with 35 different analog gauges and 14 digital indicators on 4 mile island. There is a major human factors component to this work and the need to translate data into insights and prompts for courses of action. This is a natural next step.
- My own work on lessons learned has illustrated that the more a process is repeated, the greater the opportunity for leveraging the experience of the past. Road construction would appear to be a prime candidate for leveraging this data set. Whether it is the average time to lay a drainage channel in clay through to the effects of weather on road surfacing. This data will enable schedules to be adapted in real time and improve certainty.
The A14 team have set the bar for others to aspire to. A great start and they have bold plans to do so much more. It has the potential to transform construction, improve confidence in delivery and provide a foundation for improving delivery productivity.
Some stats: The project started on site in 2016. 40km of new and upgraded highways. 4 lanes wide. 10million m3 of dirt to move. 600 staff. 1/3 of the way through. Due to be delivered by March 2020. Spending ~ £45m a month.
Key drivers: Support effective communication, analytics platform, single source of the truth.
Why PowerBI? They decided to pursue a Microsoft route because of familiarity with excel and sharepoint. Significant updates are released for PowerBI every month, accelerated development. Microsoft is engaging with the team to see what they have achieved.