If you work in the world of infrastructure management, you will have a good understanding of what happens behind the scenes when it comes to fixing road defects. The process involves more than just patching up potholes. It's a meticulous dance between data collection, analysis, and action.
Welcome to the Iamdata Solutions Asset Management Newsletter - April 2024
I would like to take a deep dive into the world of road defect management, where Power BI can help us transform raw data into actionable insights.😊
Every day, countless vehicles traverse our roads, encountering various defects that can impact safety and comfort. Our road maintenance crews work tirelessly to address these issues, but how do we prioritise our efforts? How do we bring the data together to help us analyse the data for us to make informed data-driven decisions about our assets?
This is where Power BI can help! Power BI is a powerful tool that allows us to visualise and understand road defect data much easier than the tools we've used before.
The Journey Begins
Let’s begin at the beginning. How do we know when there’s a problem with the road surface? Sometimes, Council is notified via a Customer Action Request (CRM). This triggers the Council to conduct a road defect inspection to determine whether or not it is a defect and if it is, to log it in the system against the asset. It is more efficient if Councils work proactively rather than reactively and they will perform regular road defect inspections to pick up defects and deal with them before any member of the public is aware.
Data Collection
So, our journey really starts with the collection of defect data in the field. Armed with tablets or smartphones, inspectors capture crucial information such as Defect Type, Road Name, Road Segment, X, Y, Defect Location, Defect Intrusion % (how much of the road surface is impacted by the defect), etc. This data provides the foundation for our analysis and decision-making process.
From Raw Data to Insights
With access to the dataset, I can begin to build the Road Defects Power BI Report. I like to build a summary page first that provides a snapshot of all the important key information in a quick summary format.
NOTE: If you're reading this on your computer, you can click the screen shot below and see the Road Defects Report for yourself.
Power BI Report - Summary Page
1. The first stop on our visualisation journey is a simple and informative bar chart showcasing the number of defects in each suburb.
2. Next, I have included a smart narrative box that relays what the data is telling us in human language.
Time is of the Essence
3. One crucial aspect of road defect management is ensuring a timely resolution. To address this, I have built a line chart that tracks the time allocated for defect resolution versus actual completion time.
4. A dynamic KPI visualisation provides a clear indication of whether defects are being fixed within the allotted time-frame. Answering questions like, are we meeting our service level targets, or is there room for improvement? The data tells the story.
5. We can delve deeper into the data with a map visualisation. By plotting defect locations on a map, we gain spatial insights into problem areas. Are certain suburbs or roads more prone to defects? Are there patterns emerging in defect distribution? These are the questions we seek to answer.
6. A simple table shows the Defect Type, the number of defects collected, and whether the defects have been resolved within the allotted time.
Visualising Success
7. As defect statuses are updated upon completion, the visualisations come alive with color-coded indicators of resolved defects.
A heatmap overlay reveals hotspots of successful repairs, painting a picture of progress across the road network.
Power BI Connecting the Data
One of the most powerful features of Power BI is its ability to connect with a wide variety of data sources, ranging from traditional databases to cloud-based services and more. This connectivity is made possible through Power BI's data connectors and integration capabilities.
Let’s see how Power BI achieves this and how it enables users to build reports once and refresh them with new and updated data seamlessly.
Data Connectors
Power BI provides a vast library of data connectors, allowing users to easily connect to different data sources without needing to write complex code or scripts. These connectors include:
Database connectors (SQL Server, MySQL, PostgreSQL, etc.)
Cloud service connectors (Azure, AWS, Google Cloud, etc.)
Online service connectors (Salesforce, Dynamics 365, Google Analytics, etc.)
File connectors (Excel, CSV, XML, JSON, etc.)
Custom connectors (allowing users to connect to proprietary or custom data sources)
Data Refresh
Once a report is built, users can publish it to the Power BI service, where it can be shared with others in your organisation.
You can set up automated data refresh in Power BI, allowing you to schedule regular refreshes of the datasets.
During a refresh, Power BI retrieves the latest data from the connected data sources and updates the report accordingly. This ensures that users always have access to the most up-to-date information without needing to manually intervene.
Power BI's ability to connect with a wide range of data sources, coupled with its data transformation, modelling, and reporting capabilities, enables users to build dynamic and insightful reports that automatically update with new and updated data.
This empowers you to make data-driven decisions about your Road Defects based on the latest information available.
You can check out my Road Defects Power BI Report, along with many others here: ➡️www.iamdata.solutions/interactive-power-bi-reports-for-you-to-explore
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 the 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.