Part A
Summary of the project, product, framework
Melton City Council (MCC) is one of the fastest growing regions in Australia. With a growth rate at almost four times the state’s average, Melton City Council needs efficient and automated processes in order to effectively on-board Council assets.
The Asset Data Workflow project has seen a review and implementation of a number solutions across six phases of the workflow.
Figure 1 – Asset Data Handover Process
These solutions involved collaboration with external software developers, Council working groups, internal development of skills to improve automation through SQL scripting and the design of business intelligence dashboards to create a live snapshot of the state of Council’s assets.
Part B
Description of the project or framework addressing the assessment criteria
Melton City Council in recent years has witnessed a boom in the rising number of subdivisions and capital projects being completed. The Asset Management and GIS team undertook a review of the Asset Management Data Workflow in order to stay ahead of the curve.
The main objective of the review was to improve a number of pain points highlighted within existing processes. Outcomes highlighted by the Asset Management & GIS team included a validation portal, Power BI dashboards and a direct asset register importing tool from GIS.
Figure 2- Melton City Council Asset Snapshot 2019/20
Project Completion
Process
For subdivision assets, Melton City Council receive ASPEC standard data as a requirement for awarding Practical Completion (Civil) or Statement of Compliance (Landscape). For constructed assets, Capital Projects, Operations and Recreation staff complete an Asset Handover template which details all of the new\disposed assets included in a project.
Pain Point
Attributing financials of constructed assets from Capital and Operational projects.
Improvement Solution
This was a great example of a cross-Council working group getting together to workshop a solution. The adopted solution now includes an intermediate step in the process. Instead of the Asset Management & GIS team taking long amounts of time try to match expenditures with ledgers, the process now outlines that the Asset Handover form must be approved by Council’s Asset Accountant within the Finance Department first. This added step enables the Finance team to be all across the expenditure for each of the Capital and Operational ledgers.
Figure 3 – Capital Assets Handover Procedure & Form
Asset Handover
Process
Supply of digital ASPEC standard data to the Asset Management & GIS team.
Pain Point
Time taken to review and digitise assets from projects provided by Developers and Council Officers.
“It is critical we continue to look at both technical and procedural efficiencies relating to Asset Handover”
Luke Shannon,
GM- Planning and Development
Improvement Solution
ACDC (As Constructed Design Certification) portal has been successfully implemented to automate and simplify the process of receiving new asset data. The ACDC portal allow developers to upload asset data directly from as-built drawings within their AutoCAD environment. The portal automatically enforces data validation rules to ensure the asset data adheres to ASPEC standards. Once the data is approved, Melton City Council receive an email notifying us of the submission. The ACDC portal is able export out the asset data as a GIS layer. It is then stored in a staging database on the GIS server before being merged into the whole asset layer. This solution ensures that assets are accurately captured and reduces the time taken to proof provided as-built drawings and digital ASPEC data.
Figure 4 – ACDC Portal within the AutoCAD environment
Data Review for Import
Process
Asset attributes specific to Melton City Council are associated with each Asset Category. These include; Asset Name, Classifications, Hierarchies, Capacity, Asset Identification & Location
Pain Point
Time taken to add data classifications accurately against new assets
Improvement Solution
The solution for this issue was threefold;
- Review of Asset Attributes – A comprehensive review of classifications was undertaken to ensure assets attributes included the necessary information for searches, reporting and maintenance\renewal planning.
- Create a SQL script to auto populate attributes – Where possible asset attributes were added through a SQL script. Using provided ASPEC data, spatial joins and geometries a lot of the added classification could be populated. Where the APSEC data was lacking, manual data entry still occurs.
- PowerBI dashboard to check attributes – To ensure that all assets use the same naming conventions, have accurately populated classifications and construction dates, a Power BI dashboard has been set up to assist reviewing the data before import.
Figure 5- SQL script to populate attributes
Data Import
Process
Importing assets, components, network measure and valuation into Assetic Cloud (Asset Register)
Pain Point
Time taken to import new assets. Each quarter, over 100 manual imports to be undertaken by Officers.
Improvement Solution
With collaboration from Assetic, an XML solution to import assets directly from Council’s QGIS software was designed. The importing tool enables the direct import of assets, components and network measures into Council’s Production environment of Assetic Cloud. The solution utilising existing software, now enables Council to import assets more frequently into the Asset Register. This results in a more live & accurate representation of assets we have captured. Valuation importing would still need to undertaken manually using the Data Exchange as it could not be configured in the solution.
Figure 6- Assetic import tool within QGIS
Post Import Data Checking
Process
Comparing ASPEC GIS layers and data in Assetic to align databases
Pain Point
Very manual process to check synergies between initial ASPEC working datasets and asset data currently in Assetic
Improvement Solution
A stored procedure was developed in SQL Server to create both an Excluded (in ASPEC, not Assetic) and Missing GIS (in Assetic, not ASPEC) layer within the database. This stored procedure is refreshed on a weekly basis. This allows for regular checking to ensure that the databases are aligned.
Figure 7- Tables created to find discrepancies in databases
Visualising Asset Data
Process
To enable end-users to visualise asset datasets and provide information on the state of the assets, tables and layers have been created for GIS and Business Intelligence tools.
Council already had done significant work wholly aligning both the Asset Management and GIS databases. The layers made visible through Council GIS (IntraMaps) are a combination of asset data direct from Assetic Cloud (AMS) and the geometry from the ASPEC GIS layer. This means only assets common to both sets of data are made visible on GIS.
Pain Point
No easy way to provide State of the Assets report.
Improvement Solution
Even though the assets are visible in GIS, there was no solution to easily display what the state of the assets were. PowerBI was determined as the best platform to build a solution. After some external training, Council Staff were able to build a State of the Assets Dashboard with their newly acquired skills. The dashboards help assist Council Officers\Councillors grasp knowledge of both the total and recently added network lengths\quantities, replacement values and condition of assets
.
Figure 8- State of the Assets Dashboard in PowerBI
Part C
Contribution made by the AM/GIS team
This project has been initiated by Melton City Council’s Asset Management & GIS team, who continually drive improvements with a focus on efficiencies in the Asset Data Workflow.
The team’s ambition is to continue to find new and innovative ways to manage the ever growing amount of asset data that enters Council’s Asset Management Register each year.
Part D
General Comments
Melton City Council is unique in the way it integrates Asset Management and GIS in the same business unit. This integration allows a smooth flow of information from when Council’s first receive ASPEC standard data from developers through to visualising the asset data on Council’s GIS platform.
Melton City Council is one of the fastest growing municipalities in Australia, with an infrastructure asset portfolio currently valued at over $2.5B with a planned annual increase of $200M through new build. The time and money saved, through the implementation of this project has not only been significant, but it is what is needed for efficient data capture going forward. The Asset Data Workflow will constantly be reviewed and refined to further gain efficiencies in the process.