This case study explores how Campbelltown City Council (NSW) partnered with Iamdata Solutions Pty Ltd to develop a Power BI Report and suite of dashboards that optimised the asset management of council’s playground assets. By integrating data from council’s Conquest Asset Management system, these reports provided in-depth visualisations and insights into asset conditions, valuations, and renewal strategies. This solution has empowered council to make informed, data-driven decisions, ensuring the efficient maintenance and strategic planning of council playgrounds.
Welcome to the Iamdata Solutions Asset Management Newsletter - September 2024
Client Overview
Campbelltown City Council (NSW) sought a comprehensive solution for managing and analysing data related to their playground assets. They required a tool that could provide in-depth insights into the condition, valuation, and future planning of playgrounds within their council area. Iamdata Solutions Pty Ltd developed a series of Power BI reports tailored to meet these needs, delivering a data-driven approach to asset management.
Project Overview
The Power BI Playground Report were designed to assist Campbelltown Council in effectively managing their playground assets. The report aggregate data from various sources, provide detailed dashboards and visualisations, and offer actionable insights on playground conditions, asset valuations, replacement costs, and renewal planning. The dashboards are user-friendly, enabling council staff to interact with the data and make informed decisions.
The Implementation Process
The implementation process began with the establishment of a direct connection to Council's cloud-based Conquest Asset Management system. Conquest Solutions provided a customised Power BI connector, which would ensure real-time data updates.
Data Preparation
The next phase involved extensive data preparation. Iamdata Solutions developed various SQL queries to select relevant data from the Conquest Asset Management system and created specific views within the system tailored for Power BI consumption. This optimisation of data retrieval and processing laid the groundwork for the report development.
Designing the Power BI Report
The Power BI report was designed and built with multiple pages, each focusing on different aspects of playground asset management.
Key Features
Data Integration and Source Tables
The report consolidate data from both the Conquest Asset Management system and Power BI Embedded, enabling a comprehensive view of playground assets. Key data sourced from council's Conquest Asset Management system includes data relating to Playground Reserves, Playground Assets, Playground Master Plan, and Playground Hierarchy, among others. The data provides detailed information on the inventory, condition, and planning of playground assets, helping to map out the current status and future needs.
From Power BI Embedded, additional data like Latitude Longitude, Reserve Name, and Location are integrated, which enhance the visual and geographic representation of the playground assets. The inclusion of latitude and longitude coordinates, along with reserve names and specific locations, allows for precise mapping and spatial analysis within the Power BI reports.
This integration of data sources ensures that the reports offer a well-rounded and actionable perspective on playground asset management, combining detailed asset data with geographic insights to support strategic decision-making.
Calculated Columns and Measures
To enhance the functionality and analytical capabilities of the reports, custom measures and calculated columns were developed. These additions were tailored to meet the specific needs of the council, ensuring that the reports provided not only visual insights but also precise calculations that support strategic decision-making.
Calculated Columns
Calculated columns were particularly useful in displaying Asset IDs within map visualisations. By leveraging the unique asset identifiers, the calculated columns allowed for easy reference and tracking within the geographic context of the maps. This capability enabled users to visually pinpoint and differentiate between various playground assets, enhancing the usability of the map visualisations for asset management and planning purposes.
Measures
Custom measures were developed to calculate critical financial metrics related to playground assets.
Together, these custom measures and calculated columns enrich the reports by providing both detailed visual insights and essential financial metrics. This enables Campbelltown City Council to make more informed decisions regarding the maintenance, replacement, and upgrade of their playground assets, ensuring that both current and future needs are met efficiently and cost-effectively.
Power BI Report Dashboards
The reports are organised into multiple dashboards, with each dashboard dedicated to a specific aspect of playground management. This structured approach ensures that users can easily navigate through the various dimensions of asset management and access the precise information they need without being overwhelmed by data.
Summary Page Dashboard
The Summary Page offers a comprehensive dashboard that serves as a central hub for monitoring key metrics related to playground management. This dashboard is designed to provide at-a-glance insights into critical aspects of playground assets, empowering council members, planners, and maintenance teams to make informed decisions quickly and efficiently.
Playground Assets and Locations Dashboard
The Playground Assets and Locations dashboard offers a detailed and comprehensive view of all playground assets managed by the council. This dashboard is designed to provide users with essential information about each playground, enabling them to assess the current state of the assets, understand their geographical distribution, and evaluate the financial implications of maintaining or replacing these assets.
Playground Condition Information Dashboard
The Playground Condition Information dashboard is dedicated to providing an in-depth analysis of the condition of playground reserves and the playground equipment within each playground reserve. This dashboard uses a variety of visual tools, such as tables, doughnut charts and bar charts, to present the data in an easily digestible format, allowing council members and maintenance teams to quickly assess the state of their playground assets and prioritise necessary actions.
Playground Asset Valuations Dashboard
The Playground Asset Valuations dashboard is a critical section of the report that provides an in-depth financial assessment of the council’s playground assets. This dashboard focuses on the current replacement cost of each playground and its individual playground equipment components, helping council to understand the current financial standing of their assets and plan for future expenditures. Key elements of this page include detailed asset valuations, current replacement costs, accumulated depreciation, written down value, annual depreciation, last valuation date, etc - all of which are presented in a clear and accessible format.
Playground Assets Past Useful Lives Information Dashboard
This dashboard identifies playground assets that have surpassed their estimated useful life. These are typically pieces of equipment or infrastructure that were installed some years ago and have since worn out due to regular use, weathering, and age. The identification process uses data on the original installation date, expected lifespan, and condition at inspection date to flag assets that are overdue for replacement. This information is presented in a table format, listing each asset alongside its condition, expected useful life, and the number of years it has been in service beyond that period.
While the dashboard highlights assets that have exceeded their useful life, it's important to recognise that some of these assets may still be in good condition and continue to serve the community effectively. To make informed decisions, the council can use this information to develop a targeted inspection plan focused on visually assessing the condition of these aged assets.
Prioritising Inspections
Council can prioritise inspections for assets that are flagged as being beyond their useful life, particularly those that have exceeded their lifespan by several years. By systematically assessing the condition of these assets, council can determine whether they still meet safety standards and user needs or if they are in immediate need of replacement.
Condition Assessment Process
During the inspection, the condition of each asset is carefully evaluated. If an asset is found to be in good condition despite being beyond its expected useful life, it may still have a significant amount of serviceable life left.
Updating the Asset Management System
After the inspections, the new condition information is used to update council’s Asset Management System. This includes recording the inspection date, detailed findings, and the inspected condition. The Conquest Asset Management System then recalculates the asset’s remaining useful life and depreciation and generates a revised Expiry Date based on the new inspection data.
Integration with Capital Works Program
If the inspection reveals that an asset has indeed reached the end of its useful life and requires replacement, it can be flagged for inclusion in council’s next year's Capital Works Program. By identifying and prioritising these assets well in advance, council can allocate the necessary budget and resources to ensure that replacements are carried out in a timely manner.
This proactive approach not only enhances safety and functionality but also helps to avoid the higher costs associated with emergency replacements or reactive maintenance.
Playground Hierarchy and Features Dashboard
The Playground Hierarchy & Features Power BI dashboard provides a comprehensive overview of playgrounds within a specific region of the council area. The playgrounds are visualised on the map displayed at the bottom of the dashboard which allows the user to quickly see where the playgrounds are located.
The dashboard serves as a valuable tool for council as it provides a clear and organised way to analyse the distribution, hierarchy, and features of playgrounds, potentially guiding future developments and improvements in recreational spaces.
The dashboard is divided into primarily two sections, each providing valuable insights into the distribution, hierarchy, and features of these playgrounds.
Playground Hierarchy
Playgrounds are categorised into different hierarchy levels, including Pocket, Local, District, and Regional. The bar chart visually represents the distribution of playgrounds across these categories.
Playground Features
Interactivity and User Experience
The Power BI report is designed with a strong emphasis on interactivity and user experience, ensuring that users can engage deeply with the data and derive meaningful insights with ease. These interactive features not only make the report and dashboards more intuitive and accessible but also empower users to explore the data in a dynamic and personalised way, tailoring the experience to their specific needs and interests.
Dynamic Filtering and Drill-Down Capabilities
One of the key interactive features of the report is the ability to apply dynamic filters and drill down into specific data points. Users can filter the data by various criteria such as location, asset type, condition, or time-frame, allowing them to focus on the most relevant information. For example, a user might start with a high-level overview of all playground assets and then drill down into specific playgrounds or equipment types to examine detailed conditions or financial metrics.
Interactive Filters
The interactive filters built into each dashboard allows users to adjust parameters on the fly, such as selecting a specific geographic area or filtering assets based on their condition rating. The dashboards update in real-time, reflecting the user’s selections instantly.
Drill-Down Features
Users can click on specific data points in charts or tables to reveal more granular information. For example, clicking on the Playground Location in the table exposes the user to more details about the individual assets within the Playground Reserve.
Responsive Visualisations
The Power BI report incorporates responsive visualisations that adjust and reconfigure based on user interactions. As users apply filters or drill down into data, charts, graphs, and maps, the visualisations dynamically update to display the most pertinent information. For example, clicking on a specific playground location in the example above also filters the map on the right to display the location of the playground on the map. This responsiveness ensures that users can see the impact of their actions immediately, making the exploration of data both engaging and insightful.
Interactive Maps
Maps within the reports are fully interactive, allowing users to zoom in on specific areas, click on individual playgrounds to view asset details that highlight key metrics such as asset age, condition, or replacement costs.
Real-Time Data Interaction
To ensure that users are always working with the most current data, Campbelltown Council has published the Power BI report and set up daily scheduled data refreshes. This regular update cycle ensures that the information presented in the report is always accurate and reflective of the latest changes in playground asset management.
Scheduled Data Refreshes
Council has implemented a daily data refresh schedule, which automatically pulls the latest data from the Conquest Asset Management system and any other integrated data sources. These scheduled refreshes occur at consistent times, ensuring that the reports are up-to-date at the start of each day. This is particularly valuable for teams who rely on the most current data for planning and decision-making.
Data Viewing and Exporting
Users can view data as tables, export to Excel or CSV, and use slicers for data filtering.
Impact and Benefits
The implementation of the Power BI Playground Reports provided Campbelltown Council with a powerful tool to manage playground assets efficiently. The visualisations and insights enabled the council to:
Make data-driven decisions regarding asset maintenance and renewal.
Forecast budgetary requirements for future playground upgrades and replacements.
Ensure compliance with safety and quality standards by closely monitoring the condition of playground equipment.
Council Engagement
Throughout the project, Iamdata Solutions maintained close communication with Campbelltown City Council to ensure timely delivery, budget adherence, and alignment with the Council's needs. The process began with in-depth meetings with key stakeholders to understand specific needs and expectations, followed by collaboration with the Council's asset management team to identify critical data points and desired visualisations.
Regular weekly update meetings were scheduled with the Council to provide progress reports detailing completed tasks, upcoming milestones, and any potential issues.
One of the standout aspects of this project was the exceptional level of professionalism and collaboration demonstrated by the team at Campbelltown City Council. The Council displayed a deep understanding of their needs and current systems, providing valuable insights throughout the project. Their dedication to improving their processes and embracing new technologies was evident in their active engagement and thoughtful feedback.
Council staff maintained clear, prompt, and constructive communication channels, facilitating smooth information exchange and decision-making. Their willingness to engage in open dialogues about challenges and opportunities fostered a collaborative problem-solving environment. The team showed remarkable adaptability in adjusting to new workflows and technologies introduced by the Power BI solution, and their innovative mindset led to several enhancements in the final product.
Campbelltown City Council Reflections and Insights
Tanjeeb Huq MIEAust
Asset Renewal Planner at Campbelltown City Council, NSW
“As an Asset Renewal Planner at Campbelltown City Council, I have played a key role in enhancing our playground asset management through the use of innovative, data-driven tools. By collaborating with Iamdata Solutions Pty Ltd, we developed Power BI reports that have significantly improved our ability to manage and plan for playground renewals. These reports provide detailed insights into asset conditions, valuations, and future needs, allowing us to make informed decisions that address our community’s growing demands.
Integrating data from our Conquest Asset Management system into these reports has been transformative. We can now visualize asset conditions, assess valuations, and plan renewals with greater accuracy. The real-time data and interactive features of Power BI have enabled our team to respond proactively to the needs of our community.
This project’s success is a result of the collaborative efforts between our team at Campbelltown City Council and Iamdata Solutions. Together, we have created a solution that meets our current needs and prepares us for future challenges, ensuring our playgrounds remain safe and sustainable for our community.
This initiative reflects our commitment to leveraging technology to improve asset management practices, ensuring we deliver high-quality services to our residents while optimising our resources. The insights gained from these reports will guide our asset renewal strategies, keeping our playgrounds vibrant and well-maintained for years to come.”
Thank you for your insights Tanjeeb!
Campbelltown City Council Testimony
Shakil Mim
Asset Systems & Strategy Engineer at Campbelltown City Council, NSW
Rate: Excellent
What was the project?: Power BI Dashboard which summarises Council Playground Data and provides key insights to senior management about future renewal programs, lifecycle costs, predictions, current condition of playgrounds in the LGA and upcoming master plans.
How to improve: No improvements needed, Jill has been super supportive throughout the whole process of developing the Power BI Dashboards.
Anything to add: It would be good to work with Iamdata Solutions in the future as they were able to easily understand Council requirements and provide options in how to present the data to senior management.
Conclusion
The Power BI Playground Reports developed by Iamdata Solutions Pty Ltd exemplify the effective use of business intelligence tools in local government asset management.
The Playgrounds Power BI report developed for Campbelltown Council offers a range of practical benefits that significantly enhance their day-to-day asset management activities.
Asset Management Plans
The visualisations within the report can be directly used to populate their Asset Management Plans, ensuring that all data is up-to-date and accurately reflects the current state of playground assets.
The report also provides critical financial information, such as asset valuations and depreciation details, which can be efficiently shared with the Finance Department to support budgeting and financial planning.
The condition data in the report allows Council to plan and execute condition and defect inspections more effectively, targeting specific assets that may require attention. This proactive approach helps in maintaining the quality and safety of playgrounds.
Useful Lives / Unit Rates Development
The report facilitates easy access to current asset types, including their useful lives and unit rates, which is invaluable when developing new unit rates and assessing the lifespan of assets. This data can be easily shared with consultants, aiding in the development of updated useful lives and unit rates. Keeping the Conquest Types IDs visible when developing any new rates or lives simplifies the process of updating the Conquest Asset Management system with this updated data.
By leveraging the power of data visualisation and analytics, Campbelltown City Council can now manage their playground assets with greater precision and foresight, ensuring the safety and enjoyment of their community's play spaces.
I have worked on many different projects with my Local Government clients, from designing and developing Power BI Reports, to building SQL Server databases for spatial data, to managing and maintaining GIS and the Asset Management systems. If you'd like to discuss how we might work together, then please email Jill at ➡️ jill.singleton@iamdata.solutions
If you would like to receive the latest Newsletter Blog straight to your inbox, please subscribe here: ➡️ https://www.iamdata.solutions/subscribe
You can read all our Newsletters and Blogs here:➡️ https://www.iamdata.solutions/blog
You may also be interested in our Projects Page:➡️ https://www.iamdata.solutions/past-projects
Check out what our clients say about us here:➡️ https://www.iamdata.solutions/reviews
If you would like to see a particular topic covered in these newsletters, then please let me know about it. The chances are other people will be interested and would like to hear about it too! Please email me at: ➡️ jill.singleton@iamdata.solutions with your suggestions.
תגובות