Supercharged renewals management

In today’s article, I am recreating my recent talk at the RIMS Forum 2023, all about supercharged renewals management!

The talk was about an infrastructure asset renewals management solution, designed and managed directly in the RAMM software by Thinkproject. This innovative solution supercharges the renewal management process, saves time for those involved with the management and delivery of the programme, creates a single source of truth, and has been used on the Auckland Transport network for the past eight years.

This is an adaptation of the talk I gave. I have used my presentation notes and put some commentary under each slide to try and make it (almost) read like a transcript of how the talk was delivered on the day.

So grab a coffee and enjoy.

On to the presentation…

In 2016, I presented here at RIMS Forum about a renewals management solution built for Auckland Transport (AT). This system changed the game for the management of renewals activities.

And in today's talk, we will take a retrospective look at the last eight years since go-live to look at the system's origins, how it has evolved over time, and some of the many ways it has changed the game.

Before we go too far, though, let’s provide some context for what ‘renewals activities’ mean. These are things like resurfacing a road, replacing a retaining wall, or rebuilding a footpath.

And these activities that we are planning to do are typically put into programmes spanning multiple financial years. Programmes that detail what work needs to be done this year, plus what is coming up on the horizon.

So before 2015, what was the data management problem in this space?

How were things done in the before days?

This screen right here. The RAMM FWP Grid or NOMAD was used to manage the pavement renewals programme. However, at the time, this was found to have a few limitations for what AT needed to get from it;

• It was a static snapshot of the programme with no real active connection to the delivery world.

• It was not particularly flexible for assets other than pavements.

• And the dataset didn't support everything needed for the business.

 So, from the perspectives of

• data traceability,

• communication,

• creating a systemised connection between the planning and delivery worlds,

• plus a real need to establish a trusted single source of current truth

 It was clear that things needed to change.

So in 2015, the RFWP system was created and launched. This went live for all pavement renewal activities across all road maintenance contracts on the AT Network.

The RFWP system is designed and built on a framework of RAMM user-defined tables and SQL code.

And it operates wholly within Thinkproject’s RAMM software. So no extra software is needed. And no new systems to procure and integrate. This gives the solution a strong underlying element of Back to Basics (the theme for this year’s RIMS Forum) by leveraging things AT already has in place - the ecosystem of capabilities, data and processes built in and around RAMM.

And it changed the game in many ways, so let’s look at some of the key features…

Each activity in the system has a status that gives you immediate insights into its overall progression. So, at a quick glance or an easy filter away, you can see what activities are programmed, dispatched, or completed just from a single attribute.

Intelligence that is beautifully simple and really easy to digest.

From day one, there was capability built into the system to support asset classes beyond just pavements. Today, this includes assets like transport structures and non-carriageway assets like footpaths and surface water channels.

You can store a traditional snapshot version of the programme for those asset classes where needed. So while these snapshots don’t get automated live updates, you benefit from having all relevant renewals programmes available in a single uniform dataset.

But there are also some exciting live capabilities in the programme, leveraged for some asset classes, which we will explore in the upcoming sides.

But first. Each activity starts with a Works record, which creates a unique works_id for that activity. This identifier stays with the activity through the entire renewal lifecycle.

This works record also creates the bucket or container to capture the history of the activity going forward.

For each decision or event tracked for an activity, a new record is added to the linked Programming table. And using the asset hierarchy in RAMM HTML, this layered history of decisions and events can be easily stepped through.

So you might be thinking… what is a decision or event?

Well this is everything from;

• the activity being added to the programme

• to when it has been delivered

 and all sorts of other things along the way like

• deferrals

• cost and activity changes

• and when the activity was dispatched.

 All things recorded and tracked directly in the dataset.

Who will do the doing when it comes to all these renewals?

There is an attribute to assign the maintenance or renewals contract that will be responsible for delivering a given activity. Things are flexible, so this can be set manually if needed. But it can also be automated via a SQL script that dynamically assigns contracts based on the location of the renewals activity itself.

So where there are multiple roading contracts across the network, there is no need to manually reconcile activity locations to contract boundaries.

The renewal dispatches are programmatically generated based on the contract assignment we just talked about. This is a fancy way of saying that with a single script, we create renewal dispatches that are pushed directly into the relevant contracts in RAMM in a matter of seconds.

So there is no need to email a spreadsheet to someone to ask them to sit down and create a bunch of dispatches for the jobs. These are immediately actionable dispatches, which from running a single script, are:

• consistently and accurately defined.

• created directly in RAMM.

• and ready for delivery.

But pushing these dispatches into contracts is only part of the equation. The RFWP system maintains an active link between the activity and the renewal’s dispatch to keep things in sync automatically. And these updates are scheduled to happen every single night. So if the dispatch is completed, the activity in the programme is also automatically marked as completed.

But this automation is not limited to just status changes.

If, for some reason, the dispatch was changed from an AC to a chip seal, the activity in the programme is updated to match. Again, automatically. Or say the dispatch was extended a further 10m, this too, will be synced back to the activity in the programme.

This live connection automates the traditional reconciliation process to determine what things from the programme have been delivered on the ground.

And you don’t need to wait until the end of the financial year to get that intel. The SQL framework behind the system does all the heavy lifting for you, so you get up-to-date insights every single day.

 The RFWP dataset creates a comprehensive picture of each renewal activity in the programme.

For activities managed in the RFWP and automatically updated with the live connection, they get the added capabilities and insights of live status tracking, programmatic dispatch creation and delivery cost reporting.

 For those activities which are managed only as traditional snapshots, you will still find the location, dimension, activity details and planning estimates about them.

Unlike the original FWP grid, with its treatment length-centric approach, pavement activities are not constrained by treatment lengths in the RFWP.

This enables an activity spanning only part of treatment lengths, or across multiple adjacent treatment lengths, to be managed and delivered as a single project. And also unlike the original FWP grid, you don’t need to define percentages for partial lengths.

The flexibility helps mitigate issues around questions like when should a treatment length actually be recognised as a treatment length?

To support updates coming into the system via the annual programme development cycle, a load module provides a staging area for all bulk updates.

And this staging area has a series of QA checks to test all manner of things from clash detection, to invalid attribution, to letting you know that you are trying to update an activity today - that was actually just dispatched for delivery yesterday.

This makes the tools and intel available right at the front line of making updates to the programme.

With usability front of mind, there is a robust read-only table to communicate the published programme in a single flat dataset.

This isn’t a view in the true SQL Server sense of the word. But it follows an underlying concept of a read-only derived dataset by leveraging available capabilities in the RAMM system. And importantly, it presents the table in a familiar and consistent way for end users.

So you can think of this as an extra helpful dataset containing your year zero + years 1 to 3 of the programme. This data is automatically updated every night, pulling together the latest activity and dispatch information.

Plus, it adds other attributes from elsewhere in the database - all the elements people want to report on or see, like management areas, local boards, traffic volumes and heavy vehicles.

Other business processes are also embedded directly into the system. For example, the AT Reseal Guidelines provide guidance around making resealing decisions on the network.

These outcomes are baked directly into the dataset, allowing programme managers to specify how treatment decisions have been made in alignment with the document.

So that gives some insights into the key features and functions of the system. Now let's take a look at some actual real-world examples in the data.

This first activity has a relatively straightforward path through the renewals lifecycle.

On the left, you can see the layers of records in the Programming table, which are all the decisions and events tracked for this activity.

So if you were to click on the first entry, it will show you the activity was added to the programme on December 6th 2021.

Drilling through those other records reveals key milestone events like the activity being dispatched on December 7th, the physical works being completed on January 28th and the as-builts being accepted on June 27th 2022.

Information at your fingertips within the system itself, without checking any spreadsheets or chasing things up by an email.

Now let’s consider another scenario through the lens of the old way of managing the programme. Here we are looking at the same road in static datasets at two different points in time… June 2015 and February 2021.

 What has happened here?

• Is this the same activity, and the requirements have continued evolving over time?

• Was the TAC completed in 2-17/18 and perhaps has failed earlier than expected?

• Is this some sort of second-coat scenario?

• Is this a location clash or a double-up resulting from a data issue?

All possible scenarios and all questions that can be answered. But under the old way of programme management, it could take a fair bit of digging to figure out what’s happening.

With the information in the RFWP dataset, we can see the full story for this renewals activity without needing to track down and dive into various spreadsheets.

We can see dimension and cost estimate changes.

We can see activity type changes.

We can see multiple deferrals for reasons like budget and an external project.

All this history and information sitting right there in the programme, linked to the activity, ready to be analysed and stepped through. And it removes the guesswork about what is happening at this location.

The system has evolved over the past 8 years so let’s look at a few examples of where and how this has happened.

The transport structures asset class includes assets like bridges, sea walls and retaining walls. And this transport Structures asset class has now started to leverage the full programmatic dispatching of activities and live updates.

The original system was designed and built in the very early days of RAMM user defined tables (UDTs).

In the time since the RFWP launched, Thinkproject has continued to enhance UDT functionality. And some of these enhancements have been retroactively incorporated into the system.

One example is that dispatch fault codes can now easily be linked to renewals activity types via a lookup table. Originally this was not possible, and the relationship needed to be defined in the SQL framework behind the RFWP system. This upgrade makes it easier to manage and maintain these relationships without touching any underlying SQL code.

As we reflect back on the successes of this system, three key factors that jump out as having helped with its extensive and continued use over the last 8 years.

Making usability a key driving force has been crucial to delivering a positive user experience.

Take for example, automatically generating all the pavement renewals dispatches from a script. It saves time on data entry, no activities are accidentally left behind, and there are no entry or translation errors with things like location or activity types.

When users have access to the right tools, they can perform the tasks they need to get done more efficiently and effectively.

The data QA framework in the load module staging area is an example of this. Users have access to a robust set of quality tests to help make sure the updates are valid, complete and are of high quality. And this all happens before any data integrates into the live programme.

Systems that are not flexible run the risk of becoming outdated, inefficient, and difficult to use and manage.

Being open to change has allowed the RFWP system to evolve and improve over time, respond more effectively to changing business and data needs, and leverage improvements in the underlying RAMM software.

In the spirit of my original presentation back in 2016, I want to conclude things by throwing around a few numbers to help demonstrate the scale and size of the system.

To date, there are over 142,000 individual renewal activities in the dataset.

This includes the history of previously completed programmes, programmes loaded in as snapshots, and those actively managed with dispatches and automated status tracking today.

There are 394,000 layers of history that have been captured about these activities. These records represent the decisions and events which have been captured and tracked directly in RAMM.

For the current 2022/23 financial year, over 1,500 activities have been programmatically dispatched from the dataset for delivery.

And you can jump into the system and easily see for each of the 1,582 activities what still needs to be dispatched, what’s in progress, and what has been completed, all through the simple and powerful works status attribute.

So that’s a wrap on this retrospective look at the Auckland Transport Renewals Forward Works Programme (RFWP) system and how it has changed the renewals management game for Auckland Transport.

I want to pass on a massive thanks to the project team at Auckland Transport, with special mention to Alison and Allie, who are doing an absolutely brilliant job leading the way today.

I really enjoy working with this incredible team of people and the environment we have to share fantastic ideas, innovate and drive improvements. So a big thanks to the team.

My name is Scott McIntyre and I am from The Datastack which is an asset management, RAMM and digital solutions consultancy.

If you need to supercharge your data, I am going to help you do it.  

 It’s important to me that The Datastack is not only just about the day-to-day business stuff.  So, I try to make a difference through three key sustainability pillars of;

• being climate-friendly.

• giving back to the community.

• and helping the infrastructure asset managementsector with free resources.

And speaking of free resources, if you want to learn SQL and how to use it in the new RAMM SQL Management application, you need to go here now to sign up for SQL Basics for RAMM.

This is a free and independent digital boot camp from The Datastack to help kickstart your SQL journey today. It’s designed for beginners, and it is accessible and as jargon-free as possible.

Thanks for checking out this adaptation of my talk. It is an exciting project to be involved with, and it leverages many aspects of the RAMM system, including SQL Management, RAMM HTML, and RAMM User Defined Tables.