Using data engineering and visualization to improve road safety for a large municipality in Canada.
For this Large Regional Municipality, ensuring that the 1 million plus residents can walk and drive safely is top of mind. Spanning 2,000 km^2, the Municipality’s employees are tasked with ensuring residents and visitors are not exposed to unnecessary risks on the roads. The key to this is knowing where the trouble areas are and what is happening at these locations to cause the issues.
Of the Municipality groups who had an interest in learning more about collisions within the region, only one had access to the data they needed. The remaining groups were forced to either rely on an annual Excel report or go through multiple channels to request the one-off report they needed. For them, just getting access to this data was a new and exciting concept, let alone a self-serve dashboard that would simplify the process.
Through requirements gathering, it became clear that even though the stakeholders were involved in different areas of business, they all had the same top questions:
Seemingly simple questions, but without timely access to the right data, the answers remained a mystery to the people who had the power to solve the problems.
With winter weather fast approaching, the team had only two months to complete the database and dashboard work needed to help Municipality employees address critical safety issues on the roads.
The project was implemented with the use of the Microsoft SQL Server, SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), and Power BI Report Server.’
Within the short timeframe, the team was able to:
The team delivered the dashboard in under 8 weeks, empowering Municipality employees to better understand important safety factors, such as:
The Power BI dashboard was immediately adopted and lauded by end-users, garnering rave reviews from the Director of Transportation and the Commissioner of the Region. Stakeholders at all levels were thrilled to finally have the ability to access and analyze road safety data to make better decisions.
“Great work everyone! You delivered a lot of functionality in a short period of time. We have been interested in digging into these metrics and now we have the ability. Looking forward to the continued momentum!”
– Feedback from Municipality employees
Work continues with the Municipality, with several exciting projects in the pipeline, including a series of dashboards and reports pertaining to winter road maintenance.