Business intelligence based model proposal in traffic accident analysis

Traffic accidents are one of the most important problems of the society in our country as in the whole world. The analysis of road traffic accidents gains importance day by day due to reasons such as the fact that the majority of freight and passenger transportation in our country is carried out on highways, our roads do not have the infrastructure to handle this transportation density, and our traffic culture is not at the level of developed countries. At this point, if an integrated system is offered to the institutions or organizations that will carry out the analyzes, the real causes of the accidents can be determined correctly and new policies can be developed by taking preventive measures. In this study, the stakeholders in road traffic safety partner institutions in Turkey can benefit a business intelligence system built on dynamic multi-dimensional analysis and reporting system is designed to demonstrate the necessity and feasibility of the establishment. In order to realize the proposed system in this model, OLAP technology based on a multi-dimensional model based data warehouse structure was used. With Microsoft Sql Server Integration Services (SSIS), operational data passed through the pull-transform-load process, transformed into a structure to be stored in the data warehouse and cleared of existing errors. Thus, quality data was transferred to the data warehouse. In this study, due to lack of technical team and time constraints, how to create multidimensional cubes with Microsoft SQL Server Analysis Services (SSAS) tool and how to model the data are explained in a limited way. In the analysis section, Microsoft Power BI Desktop which is a business intelligence tool is used for data modeling at the system developed as a prototype. In the analysis part, the road traffic accident data with fatalities and injuries that occurred between 2013 and 2019 in the Sakarya province police region were analyzed according to environmental factors, road geometric characteristics and driver characteristics through the prototype system. Detailed analyzes prepared according to the types of vehicles involved in the accident (heavy vehicle, commercial vehicle, automobile, motorcycle and bicycle) were visualized with the Microsoft Power BI Desktop application. In addition, three or more repeating points of the same accident occurrence type were determined on the digital map in the same year and what could be done at these points was mentioned.