Oregon Department of Transportation Logo

Oregon Department of Transportation (ODOT)

Business Intelligence & Data Warehousing Applications Support Service

Client Overview

The Oregon Department of Transportation (ODOT) develops programs related to Oregon’s system of highways, roads, and bridges; railways; public transportation services; transportation safety programs; driver and vehicle licensing; and motor carrier regulation.

Project Challenge

EnSoftek was awarded the Business Intelligence/Data Warehouse Services project by the ODOT’s Highway Program Office, Technical Application Division. EnSoftek team performed a complete gap analysis of the current architecture, identifying areas of improvement and worked with ODOT team to develop and implement the solutions. The implementation included the conversion of data (extract, translate, and load) from numerous source systems into a common format.

The DW/BI system is based on Microsoft SQL Server 2005 technology (SQL Server database engine, Integration Services, and Analysis Services). The Reporting and analysis is delivered through Microsoft Reporting Services and PerformancePoint Server 2007 Analytics.

Solution

Created “As-Is” and “To-Be” business process flow diagrams to clearly show how the business users used the systems.

Ensured that the DW/BI system that extracts, cleanses, validates, and standardizes highway project, finance, and transportation systems data, is highly available for all the critical functionality it supports (to enable business users to quickly access critical data from a number of sources—all in one place so they can rapidly make informed decisions on key initiatives)

Integrated about half a dozen data sources into a common standardized format using best practice techniques for data warehousing and dimensional modeling

Deployed Performance Analysis dashboards that were built to show several “performance analysis blocks” across several systems of interest to the senior management

Built Finance Data Mart/Cube for ad-hoc analysis for the Finance Department

Built Intelligent Transportation Systems’ data mart

Delivered ODODT project tracking data feed  for the general public

Automated federal reporting requirements

Automated several dashboards for Highway Budget Office

Data processing jobs, errors, and status reports are logged and delivered to program management staff and business system owners

Project Results

Since each data from the various departments is getting standardized, each department is now able to produce results that are in line with all the other departments (basis for strong business decisions).

The centralized information online repository enables users to quickly build reports and analyses that are automatically refreshed for managers, analysts, and staff.

Time Saving – Business users are now able to quickly access critical data from a number of sources—all in one place—they can rapidly make informed decisions on key initiatives. They won’t waste precious time retrieving data from multiple sources.

The business executives can query the data themselves with little or no support from IT and don’t have to wait until IT gets around to generating the reports.

Each of the above mentioned projects helped ODOT make better decisions for the Oregon State Citizens and saved thousands of hours of labor from manual reports produced, thus saving the State revenue.