Local Government Asset Management Consultants
ABN 33 621 952 323 ACN: 621 952 323
Contact: jill.singleton@iamdata.solution | 0423 240 439
INFRASTRUCTURE ASSET MANAGEMENT
EXAMPLES OF PROJECTS
Asset Management System Review
Iamdata Solutions carried out a complete review of Council’s Asset Management System and data to identify gaps in these key areas:
​
Data Validation and Verification
Identify areas for improvement including:
​
-
Incomplete data
-
Incorrect data
-
Out of date data
-
Obsolete data
​
Various analysis techniques were run on the data to identify these areas and provide Council with a road map on how to address these data and data management issues.
​
Systems Structure and Configuration
​
The work done for the data analysis can also help to identify where the system may not be configured correctly. For example, if the Knowledge base tables have not been set up with the correct base data, such as unit rates or useful lives for an asset class, or the asset class’s depreciation, expiry date is set up based on condition, but no condition component has been set up in the system, and similarly where a system has been set up for condition based calculations in the knowledgebase tables but the attribute data only has information relating to age.
Each asset class requires a different set up in the system and it is important that the system is set up accordingly. Iamdata Solutions corrected these issues where they were identified.
​
Road Map
​
We provide concise documentation of our analysis findings and present the client with a detailed Road Map to fix identified issues with the data and configuration.
This provides our clients with the opportunity to work on fixing the issues in-house if they have the resources available. If our clients do not have the capacity in-house, then Iamdata Solutions will undertake the work and the detailed Road Map report provided by Iamdata Solutions to the Client provides in-depth details of the work that is being undertaken.
​
​
If you'd like to discuss how we might work together on projects like an Asset Management system review, please email Jill at jill.singleton@iamdata.solutions
​
Capital Works updates and End of Year Financial Reporting
At the end of each financial year, Councils must have updated their Asset Registers and Financial information to reflect the work that has been carried out on their Infrastructure Assets.
​
Legislative Requirements
​
It’s worth taking a minute to consider what’s involved when we talk about End of Year from an Asset Management / Financial point of view.
Local Councils must meet many legislative requirements including Australian, and State legislation, and State regulations. There are many other legislative requirements Councils must adhere to that relates to specific asset classes, but the main legislative requirements include:
Local Government Act
Sets out role, purpose, responsibilities, and powers of local governments including the preparation of a long-term financial plan supported by asset management plans for sustainable service delivery.
Local Government Act-Annual Reporting Section 428(2)(d)
(d) A report of the condition of the public works, under the control of Council as at the end of that year together with:
(i) An estimate (at current values) of the amount of money required to bring the works up to a satisfactory standard: and
(iii) Council’s programme for maintenance for that year in respect of the works.
Australian Accounting Standards
Set out the financial reporting standards relating to, inter alia, the (re)valuation and depreciation of infrastructure assets.
Local Government (Financial Management and Rating) Amendment Act 2005
Impetus for the development of a Strategic Management Plan, comprising an (Infrastructure) Asset Management Plan and Long-Term Financial Plan.
The ISO 55000 series comprises three standards:
ISO 55000 provides an overview, principles, and terminology - standard terms and definitions.
ISO 55001 is the requirements specification for an integrated, effective management system for asset management.
ISO 55002 provides guidance for the implementation of such a management system.
We are governed by these (and other) Acts and Standards to ensure we manage our Assets effectively and efficiently and that we can report in an open and transparent way about how we did it. This is a good thing for us as it’s much easier to manage our assets when we have all this information at our fingertips.
​
Our Role
​
We understand that Councils will have their own processes in place to plan capital renewal and replacement projects to meet their levels of service objectives. It will make life a lot easier if you’re using the Capital Works programs that have been developed using up-to-date, verified, and validated information extracted from your Asset Management system. The AMS should be the one point of truth and the single source of all Asset information. However, there are many Councils who are planning Capital Works Projects without an Asset Management system. This makes is more difficult to manage the Infrastructure Asset data, but it is important that this information is kept up to date, correct and complete, whether it’s being managed within a spreadsheet, or a sophisticated Asset Management system.
​
We provide help to Councils during this busy end of financial year period by updating their Infrastructure Assets with the various Capital Works Projects that the Council has undertaken over the financial year period. We work closely with Council to ensure we have all the correct information we need to make the updates in the Asset Management systems. This may include updating spatial data as well as attribute data.
The capital works updates may require further development of the Asset Management system’s core tables. For example, where a new Asset Class is being brought into the Asset Management system, or if a new material type has been used in the Capital Project on an existing Asset Class. In this case, a new material type with its own useful lives, unit rates, and sometimes a new set of attributes may be required.
Once the capital projects have been updated in the Asset Management system, a series of validation and verification checks are run to ensure the systems are producing the expected results of the updates.
When everything looks correct, we run a suite of End of Year Depreciation Reports for the Finance Department. These reports are very useful to the Asset Mangers themselves and we can provide detailed documentation of which assets are due for renewal in future years and provide a list of assets that the Asset Manager may wish to inspect to see if they are potential candidates for the following years capital projects list.
​
If you'd like to discuss how we can work together to update your Capital Works and EoY Reporting, please email Jill at: jill.singleton@iamdata.solutions
​
We have provided these services to many Councils. We offer a discount for contracts that span 3+ years.
​
Power BI Report Development
Power BI is a great tool to analyse and visualise your data. It makes it possible for you to understand thousands of rows of data by visualising your data in various charts, graphs, and tables. Data Analysis and Data Analytics are very important components in the management of your Infrastructure Assets. With good information, you can manage your Assets efficiently and cost effectively.
​
View data in real-time
​
When Power BI is connected to live data via one of the many built-in data connections, consumers of the reports will be able to view live dashboards with real time updates. As data gets processed via the many different corporate databases, such as Finance & Property, Asset Management System, Customer Request Management system etc, and Power BI is connected to that data source, the reports and dashboards will reflect those updates and changes after each refresh. This is a great timesaver, and the consumer of the data can be confident that the data is current.
NOTE: Power BI works perfectly with static data sources such as Excel, but we must remember that any dashboards or reports created from a static data source will just be a snapshot in time, not a living document.
Power BI dashboards are dynamic, I can set the report up so that if I click on an element in one visual, then the other graphs also focus on that element, providing me with information across the graphs, charts, and tables for the element that has been selected.
Power BI Reports are great for sharing information across your organisation. Once the report is published, you can share the report with anyone within your organisation and allocate specific viewing or editing rights.
​
Our Role
​
We have developed many Power BI Reports for Councils across Australia. There are many connection types available in Power BI, which means that we can connect the reports directly to Council’s databases ensuring that the data displayed in the various reports are up to date. Some of the reports we’ve developed for our clients include:
​
-
Asset Financial Information
​
​
​
​
​
2. Maintenance Information
3. Data Integrity Checks
If you'd like to discuss how we can work together on developing customised Power BI Reports for your organisation, please email Jill at jill.singleton@iamdata.solutions
GIS and Asset Data Cleansing
You could have the best IT System in the world, but if the data that is powering it is incomplete, out of date, or incorrect, the system becomes worthless. Your organisation must have confidence in your data, otherwise how can you trust any analysis or reporting that comes from it and how can it be correctly informing your organisation?
The Asset management systems (and any other corporate systems) can only provide you with insights to help you make well informed decisions about the assets, if the data is in good order.
You’ve probably seen this cartoon dozens of times across the internet – but, it still makes me smile, although somewhat ironically. Of course, we all want our data to be in pristine condition, but it can seem like such a big and overwhelming task that you may not know where to begin.
Our Role - Example 1
​
Data cleansing is an essential part of managing and maintaining your Asset Management systems and GIS. We have been able to help our clients by identifying areas of concern in many different types of datasets.
​
We look at the construction, arrangement, formatting, spellings, duplication, extra spaces, as well as issues with the actual data, such as out of date data or missing data. We undertake many different validation and verification steps using an assortment of different tools, depending on the types of data we are cleansing, whether it be attribute data or spatial data.
For example, you would want to extract the data from the system and immediately begin building your analysis / reporting / visualisation. However, when the data is bad you could spend up to 80% of the time cleansing and manipulating the data when the actual analysis only takes 20% of your time. And, of course, while this is happening time and time again, the issues in the core data within the system are not being addressed or corrected.
Another example of where data issues may arise is where a client has two datasets from two separate sources that needs to be consolidate into one dataset. Data can become disparate with missing, incorrect, and out of data information as the result of this consolidation and sometimes these types of errors are not identified and do not get fixed at the time of consolidation.
Our Role - Example 2
​
We recommend that spatial data be managed within a database. Some Geographical Information Systems do have their own geodatabase, but there are still some GIS that do not. For example, MapInfo. We have helped many Councils move from TAB files to building a GIS database utilising Microsoft SQL Server.
What is Spatial Data in SQL Server?
​
Spatial data relates to the points, lines, and polygons that is a geographic representation of a feature on the Earth. In our examples, we have chosen Line (roads, footpaths, etc), Polygon (property, buffers, etc) and Point data (defects, etc). Spatial data is stored as coordinates and topology which allows the data to be mapped. SQL Server supports geometry data and geography data. We recommend using the geometry configuration. The difference being:
-
Geometry – Stores the X and Y coordinates that represent lines, points, or polygons.
-
Geography – Stores the latitude and longitude coordinates that represent lines, points, or polygons.
​
Process
​
Organisations like Councils collect, manage, and share enormous amounts of data and usually the data is being held within bespoke systems, such as Property & Rating, Customer Request System, Asset Management System – all of which are usually Microsoft SQL Server databases. Every operation that a Council is engaged in doing has a spatial element and if the spatial data is also being managed and maintained within a SQL database, it is relatively easy to incorporate spatial data into the mix to provide the additional benefit of location intelligence.
Microsoft SQL Server handles spatial data just as easily as any other data type. The benefits of using SQL Server to manage your spatial data includes:
All Spatial Data are stored in the one point of truth, safe and secure within a database
Dataflow Scripting provides the ability to perform complex data transformations very simply
Dataflow Tasks to easily identify the data-source and the data-destination, SQL scripts to replace current MapBasic scripts, set-up data viewer to view the data flowing through the SQL SSIS scripts – helps with checking and de-bugging.
Package Configuration – ensure production quality packages with good deployment practices.
Control Flow Tasks – helps to define workflows
Web-service Tasks – e.g. ability for the organisation to consume publicly available data e.g. demographics data to enable us to perform spatial analysis with our corporate data e.g. determining the best area to build a playground, determining where Council requires wider footpaths to accommodate wheelchairs use, etc.
Ability to set up Checkpoints in each step of the SSIS Workflow to help maintain consistency of the data, especially useful when managing large quantities of data.
Logging – Error Monitoring – able to record and gather information about the error to enable us to easily identify the problem and fix it.
​
Other Benefits to the Organisation
​
Other benefits that will be gained through implementing this model will include the on-going improvements to the attribute and spatial data. It will become easier to identify areas where attribute data and spatial data are not matching. A SQL script providing the relevant error information is the first step to being able to fix any issue. Automated scripts can be set-up to create email alerts that will inform us immediately if a package or transformation has failed.
It is also true, that when the organisation starts using the same ‘one-source of truth’, any errors identified can be rectified at the source, and the correction will automatically flow through to other applications. By definition, this model promotes more accurate data.
​
If you would like to discuss how we can work together on projects like Asset Attribute and GIS data cleansing, then please email Jill at jill.singleton@iamdata.solutions
Develop Infrastructure Spatial Data
Our Role
​
Create a digitised spatial layer of Council's Traffic Control Devices from Aerial Photography utilising on-screen digitising method of tracing geographic features from aerial photography directly on the computer screen. The spatial dataset was then merged with Council’s Traffic Control Devices Excel spreadsheet to create a complete Traffic Control Devices spatial dataset.
Create a digitised spatial Layer from aerial photography containing Traffic Control Devices located within the Council area.
​
Methodology
​
Utilising on-screen digitising method of tracing geographic features from aerial photography directly on the computer screen. The spatial dataset was then merged with Council’s Traffic Control Devices Excel spreadsheet to create a complete Traffic Control Devices spatial dataset.
​
If you'd like to discuss how we can work together on projects like these, please email Jill at jill.singleton@iamdata.solutions
​
​