State of Iowa – Dept. of Transportation – Enterprise Reporting System

Brief Description of Project

The purpose of the project was first to assess current business and technical environment of the Iowa Dept. of Transportation and then subsequently design and develop a reporting system to be used by the IDOT - Motor Vehicle Division (MVD) business units and external users of IDOT data. The current 3M ARTS software system did not properly address the reporting needs of the Iowa DOT's business users. Working closely with the Iowa DOT Information Technology (IT) staff, ECI assisted with establishing the requirements of the business units and the inability for users to currently create their own reports and queries. The goal was to allow the business to perform ad hoc queries with minimal assistance from information technology staff. The system had to respond to a large number of ad-hoc queries from a wide variety of authorized customers. Within the confidentiality limits of federal and state law, the system required the ability to provide ad-hoc reports, summary statistics, and detailed information regarding customers, driving records, vehicles registrations and vehicle accidents

• Constrained output limitations of 250 records making trending analysis impossible.
• Minimal user configuration options since ARTS reporting engine offers no ad hoc capabilities (canned reports only).
• Minimal exporting options to useful analysis or reporting tools like MS Excel or Adobe Acrobat.
• Minimal advanced searching features like wildcards or fuzzy searches.

ECI's Role

ECI's role as the selected vendor was to evaluate, document and assist in the prioritization of business requirements so scope of work was well understood. Next, ECI custom designed and developed a Microsoft centric solution using .NET technologies, SQL Server Integration and Reporting Services to provide new reporting capabilities to business users through the DOT. ECI, while working closely with various DOT business units, produced the following project deliverables:

• Project Management Plan
• Requirements Document
• Business needs assessment
• Technical Design Document
• User acceptance test plan
• Implementation plan
• Technical documentation/Product Support Plan
• End User Guide

ECI designed and developed an industry best practices solution integrating with the existing 3M ARTS software Driver and Vehicle Application that provides meaningful analytics to business users throughout the CA Lottery.

ECI implemented a .NET centric solution using .NET Framework 3.5, SQL Server Integration Services, and SQL Server Reporting Services that has well exceeded user expectations. Users are able to create ad hoc report using an intuitive application without technical knowledge of underlying data or table structures. Additionally, the users have access to a library of canned reports, available through the web, that can be user generated providing a wealth of information available 24 X 7.
 
Very quickly after going to production, the IDOT used the application to quickly search for a vehicle that Iowa State authorities thought was involved in child abduction. By using our application, and providing some basic information about the vehicle involved, the CA Lottery was able to find 5 vehicles possibly involved in the incident. The application offers advanced searching capabilities allowing users to enter very limited information and finding matches on vehicles, drivers, sanctions, fees related information. Eventually, the authorities were able to locate and arrest the suspected child abductor all with the use of our custom application. We are pleased with how quickly Iowa DOT has been able to benefit from our solution.

• Unconstrained reporting provides users ability to analyze millions of records without performance degradation.
• Full ad-hoc capabilities allowing users to select fields, filters and output options.
• Full exporting capabilities to MS Office tools and other standard output formats (CSV, PDF, XML, etc).
• Advanced searching features incorporating wildcards and fuzzy searches.
• Implemented full meta data repository providing meaningful data regarding update metrics, source of data, etc of all data in reporting system.
• Extracted source data daily, capturing changes to data each night, providing capabilities for reporting not previously available.
• Ad hoc capabilities included query history searches, meta data capture, grouping, sorting, multi-select, distinct and counts only features.