The Key to Effective BI is Asking Questions

The key to effective BI is asking questions that if answered correctly will make a difference, in this case, in people’s lives and safety.

One of my first BI projects was for the Butler County Engineers Office (BCEO). It was classic case of taking common sense to a new level with technology. Among other things, county engineers in Ohio determine how to apply the state highway funds to improve the state and county roads within their local jurisdiction. The idea was to plot the location of each auto accident and use BI technology to determine which road sections and intersections were the most dangerous. Once we identified the dangerous segments, further analysis would show the improvements that would make them safer.

When the project began, copies of the accident reports required for analysis were not available on line but only on paper. We built a Windows application get the key accident data into a database, and then we could tackle the real challenges of extracting the BI necessary to make decisions.

The first challenge was to resolve the different ways that the accident locations were reported; the reports recorded the accident locations as the distance to the nearest address or intersection. The data had to be converted to the distance in feet from the beginning of the road to match the way the state identified highway locations.  This was a continuing challenge until GPS technology enabled the absolute positioning of the accident and road locations.

The first approach to analysis was to count the accidents within a certain range of an intersection, or within a certain range on a highway. Some of the road sections with multiple accidents turned out to be among the best safety engineered highways and intersections in the county. Data doesn’t lie, but it might be misinterpreted. The high accident sections that were well engineered also turned out to have the highest traffic volume.

A new dimension needed to be considered, traffic volume, and with it came another complication. An intersection with thousands of cars passing through it has several accidents a year. Does that fact make it dangerous?  Should we expect an even higher accident rate?  Probabilities based on very small samples are its own area of study.  The Engineers went to nearby Miami University to consult with one of the professors who came up with the science to enable us to compare the number of accidents on high traffic urban sections with road sections in remote rural areas and determine which sections were the actually the most dangerous and required additional analysis.

The BI application identified one dangerous side road intersecting a highway with some interesting facts. The vast majority of accidents occurred during the day, with very few after dark. This brings us back to the importance of common sense. When the engineers went out to physically inspect the situation they could see that a rise in the road would hide the stop sign until it was too late to stop. The driver could avoid a crash with the high speed cross traffic if they knew the area and had begun to slow down before reaching the rise. But that did not explain the lack of accidents at night. One of the engineers returned that evening and as he approached the still hidden stop sign, he could see the headlights of the through traffic glowing behind the rise. He slowed down and stopped while the cross traffic cleared. Mystery solved.

BCEO began this BI project in the 1980s which would make it one of the early BI efforts. My role several years ago was to migrate the app from a legacy DBMS to a MS SQL Server application with a web based user interface. From its beginning this accident analysis software has had all of the elements of today’s BI applications. Identify the information that is needed to make a decision, assemble the data required to support the analysis, apply the analytics to the data, and finally report the results.  Greg Wilkins, the current Butler County Engineer continues to use this BI application to guide the decisions on how millions of dollars in road improvements will be spent to keep Butler County roads as safe as possible.