Santiago Dominguez, deputy chief information officer, Mercedes Pérez, project manager, and Federico Sáez, development area director, were key to the IT infrastructure upgrade at Dirección General de Tráfico.
Features
Case Study
Driving results
Spain’s Dirección General de Tráfico keeps traffic moving with a data warehouse.
by Brad Hauber
A subsidiary of Spain’s Department of Interior, Dirección General de Tráfico (DGT) is a public organization responsible for safety on the roads. It oversees traffic safety and control on the nation’s extensive road network through six traffic management centers strategically located throughout the country.
Plus, it manages traffic infractions, driver education and the administrative functions required for 33 million registered vehicles and 25 million drivers. It even supports a police force of 10,000 officers dedicated to improving safety on the country’s roads.
The main goals of the public organization are traffic management to improve the safety of traffic flow and decrease the number of crashes, and modernization of the procedures that citizens use to obtain driver’s licenses, matriculation of vehicles, fine payments, etc.
Those main goals are met through the following strategies:
- Road safety. Reducing the number of accidents and improving mobility.
- Vehicles. Managing the database of vehicles—matriculations, discharges, transferences, circulation permissions, expeditions, etc.
- Drivers. Overseeing the database of drivers—exams, renovations, driver’s license expeditions, etc.
- Infraction procedures.
These strategies are built upon statistics—delivered through reports, analysis and dashboards—and modernization of IT procedures.
The most important objective of DGT is to reduce mortality rates on the roads. Fatal traffic accidents have been declining systematically in recent years. From 2008 to 2009, the rate dropped 12.89%.
Likewise, the reduction was 20% in 2008, 9% in 2007, 9% in 2006, 5% in 2005 and 12% in 2004. These trends are even more impressive considering that the number of vehicles in Spain has increased significantly over the same time period.
A new way to operate
To meet its goals, DGT recognized the need to upgrade its IT infrastructure. Doing so would better leverage the data generated from its operational systems to meet the growing demand for accurate, near real-time information. DGT had been producing reports using various operational systems, which made it difficult to correlate data across them. Generating reports also placed a heavy burden on the systems, leading to poor performance. DGT analysts wanted capabilities that the legacy systems simply could not deliver.
So in 2007, DGT determined that an enterprise data warehouse (EDW) was the solution to provide a single, consistent view of the business to meet growing analytical requirements while accommodating future needs. The organization wanted a platform that offered ease of data access, high performance for fast responses and flexibility.
After considering its options, the national traffic authority selected the Teradata Database and a Teradata Active Enterprise Data Warehouse. DGT formulated a phased, incremental approach to implementation. The first phase involved the development of a corporate logical data model, integrating data sources and re-engineering old reports and dashboards. Subsequent phases have involved:
- Data cleansing
- Adding more data sources
- Developing new reports and dashboards
- Building metadata and an alert system
The EDW integrates data sources from the mainframe and an Oracle database, storing such information as vehicle and driver registration, exam data, taxes, data from driving schools, traffic tickets, accidents, automated radar devices, and meteorological data.
The user base has grown from only a handful at the end of 2007 to more than 120. The number of analytical queries executed has nearly tripled, from an average of 550 per month in early 2009 to more than 1,500 in June 2009. Daily, more than 3 billion records are extracted from operational systems, transformed and loaded into the data warehouse.
"The organization saw immediate benefits with analytical capabilities that were previously impossible."
—Santiago Dominguez, DGT
Into high gear
With a single platform in place that consolidates data from many sources, the organization saw immediate benefits with analytical capabilities that were previously impossible, says Santiago Dominguez, deputy chief information officer at DGT. The EDW can quickly link data from any of the sources to provide users with fast access to valuable information.
“We are now delivering timely, consistent data to the right people at the right time,” says Dominguez. “Plus, the system has the scalability to grow as our requirements and number of users continue to grow. We can develop new services with minimal resources and do it quickly to meet information demand.”
Data in EDW tables feeds cubes or views, which are then accessed by end users who utilize analysis and reporting tools, dashboards, parameterized queries or SQL queries. The result is a significant improvement in information management with many new analytical capabilities. The agency can also better meet its goals of:
- Improving driver education and licensing
- Tracking vehicle registration
- Enforcing traffic laws and fines
- Predicting and improving areas prone to accidents
Empowered by the data warehouse, DGT manages more than 4.7 million vehicle registrations and transfers, 33 million active vehicles (cars and motorcycles), and nearly 3.96 million driving exams per year.
On the street
With integrated, up-to-date data in a single repository, DGT improved analysis on a variety of fronts, all of which help improve traffic safety. For the police force, the EDW provides valuable information for accident investigations. Analysts can now provide more information to investigators. This includes information such as driver data, vehicle data and details about previous accidents at a particular location.
Dominguez notes that system performance is essential to investigations. Analysts have to generate queries with a short response time to quickly obtain reports. In many instances, response time has been reduced from hours to seconds.
For example, analysts frequently must locate vehicles with certain characteristics. Before implementing the EDW from Teradata, response time for these queries was an hour, and they couldn’t be run until the CPU’s production level was low.
Now analysts receive responses to such queries in seconds. “This is a powerful tool for investigators that investigate how to improve traffic security to ensure citizen safety,” adds Dominguez.
The road ahead
The data warehouse continues to evolve to enhance its value for a broader user base. In 2010, the priority is to develop an active data warehouse environment for greater availability of near real-time information. This has become especially critical as more users access the system to perform their daily jobs.
Operationalizing the system will extend the data warehouse’s capabilities to more front-line employees, helping them improve their tactical decision making. DGT will also incorporate event-triggered alarms to push decision support data to users.
In addition, business continuity is being addressed in the effort to go active. DGT is installing a 2-node Teradata Active EDW to serve as the new production system. The initial 2-node data warehouse will be used for development and as a redundant system for disaster recovery. This will allow for higher performance and scalability to achieve an active environment.
That’s not all. In the next year, DGT will also be taking advantage of geospatial capabilities in the Teradata Database, enabling analysis based on coordinates from data sources. Traffic incidents can be plotted on a map as an additional way to help identify and address areas with high concentrations of accidents.
Adding sophisticated data mining capabilities is also in the works. Predictive models will allow accident behavior to be analyzed and patterns tracked for further root-cause analysis.
“Road safety analysis is becoming more and more complex,” notes Dominguez. “Understanding the root causes and defining plans to fix them means supporting each entity involved in the analysis with as many attributes as possible and ensuring those attributes are correlated as much as possible.”
With DGT’s improvements in information management and new analytical capabilities, the road ahead is looking clearer. The organization has gained a more complete view of information, providing valuable data for investigations with relevant contributions to the traffic security, helping ensure citizen safety. The benefits of its data warehouse continue to expand and extend to new areas of the traffic administration’s extensive operation in order to achieve safer—and less congested—roadways for Spain’s 25 million drivers.
Brad Hauber is an Ohio-based writer and consultant who writes about business and technology.
Photography by Miquel Gonzalez
