A BI Framework for Traffic Alerts: Improving the Understanding of Traffic Alerts and its relationship with Air Quality

Rising pressure on urban mobility due to limited availability and inefficiency of public transport systems, has led to an increase in private vehicle circulation and, consequently, increasing traffic congestion and alerts. This also leads to elevated levels of air pollution. Existing literature explores machine learning models and analytical tools for traffic prediction and monitoring; however, these approaches remain fragmented, focusing either on prediction or on the visualisation of metrics. As a result, there is the need of a solution that ensures the integrated processing of traffic alerts data, enables consistent generation of insights, and supports thorough analytical exploration. This gap reinforces that there remains a lack of a systematic, end-to-end data analysis framework that describes a scalable and replicable Business Intelligence (BI) solution across cities. This study addresses these gaps by proposing a framework that supports comprehensive traffic alerts monitoring, with Lisboa and Porto, in Portugal, as case studies. This solution follows the Kimbal Lifecycle methodology, implemented in Microsoft Fabric, using a centralised Data Warehouse (DW) and automated extract, transform, load (ETL) processes. Waze traffic alerts data, as well as air quality data obtained from Lisboa Aberta and Porto Digital, were leveraged to develop a semantic model and derive key indicators, enabling temporal and spatial analysis of traffic alerts and air quality. The outcomes of this study include the centralised DW, the underlying data architecture, and The Waze Dashboard – an interactive and narrative-driven analytical tool designed to support in depth exploration of traffic alerts behaviour, jam analysis and its relationship with air quality. The analytical tool can be replicated across cities, ensuring durability and applicability through time, aiding optimal and data driven decision-making for stakeholders, promoting sustainable development.