Overview
IN THIS CASE STUDY YOU WILL LEARN:
- Some benefits of combining several data sources into one enterprise data source.
- How groups can benefit and make cases for change by pulling data from the enterprise system.
- Some efforts that need to continue to make sure the enterprise data system remains successful.
Background
Data-driven decision making is critical to improving everything from operations to customer service to the safety of our transportation system. In an age of unbridled access to data, many agencies face challenges associated with the sheer volume and variety of incoming data, and how to collect, connect and transform quality data into actionable insights. The Oregon Department of Transportation (ODOT) is no different. ODOT began creating its first data warehouse in 2005, but it wasn’t until 2017 that a vision was developed and the organization began taking steps toward a “one enterprise” Data Warehouse, or EDW. Oregon’s goal was to develop a true enterprise data warehouse where data can be shared between owners in a formalized, documented and secure way. More simply, ODOT wanted to create a single source of truth for staff and customers, so that anyone across the agency could make the best decisions possible from the best data available.
TSMO Planning, Strategies, and Deployment
There’s nothing novel about the challenges that arise when it comes to centralizing data and establishing a single source of truth for stakeholders. Challenges include data coming in from different sources and formats, issues with accuracy and access, and lack of collaboration and partnership amongst data teams. These barriers go beyond simply making work more difficult. Ultimately, they short-circuit the potential of the data, as well as impair the ability to make integrated, accurate, and effective decisions. To tackle them, ODOT needed to evaluate and restructure the Data Warehouse architecture, and test design for feasibility and efficacy.
Merging data mart siloes when appropriate was vital to restructuring the Data Warehouse. Prior to 2017 dataflow structures were cumbersome, required more storage space and maintenance, and resulted in longer processing times and data inconsistencies. To achieve ease of access and optimize performance, the Business Intelligence Data Warehouse team developed a data mart flow that works off the snowflake schema principle, essentially keeping data integrated with one-to-one connections.
Attending to the culture, or human side of change, was also critical to success. The BIDW team asked the Intelligent Transportation Systems (ITS) section to be one of the first groups to change its schema to allow for enterprise-wide usage. ITS was an ideal candidate as a power user, as a group that serves multiple sections and divisions across the agency. The change had an immediate impact. ITS was looking for a way to assist the Motor Carrier division formulate information about damage and liability when it came to commercial vehicles not chaining up during active restrictions. Specifically, commercial vehicles found it more cost effective to risk being issued a ticket for not chaining up. The Motor Carrier division needed data to not only show that this created a safety issue for motorists, but also to highlight the closure impact on ODOT roadways. Motor Carrier asked ITS to provide the relationship between when a chain up event occurred compared to the number of crashes or other incidents in the area. With the current structure of the Data Warehouse, ITS could not gather and analyze this data because it lived in separate silos: TOCs housed the events occurring (crashes) on the roadways and TripCheck housed the road and weather information (chain up).
By changing the schema to create an ITS EDW Model and bringing the data together in the same location, ITS could create links between the TOCs and TripCheck data. The new architecture and data structure allow both Motor Carrier and ITS to create and leverage more dynamic reports, which is especially helpful when articulating problems and potential solutions to external stakeholders. For example, Motor Carrier is sharing its damage and liability data with the Oregon State Legislature to propose increasing the fees for non-chain up crashes that cause delays.
Communications Planning and Execution
Although not fully through the legislative process, the ITS/Motor Carrier example is already creating ripples throughout the agency and helping navigate changes to data culture. Other data owners and users are seeing how easy it is to align and pull data from previously “unrelated” sources. Recently, an ITS data analyst received a request about weather reporting stations. One of the five ODOT regions
had an outside consultant make quick summarizations years ago. The region was looking for a way to gather this data again without having to use external sources. ITS began to replicate what the consultant had started with two road and reporting segments. In the initial stages of replicating the consultant’s tables, the analyst noticed there were differences in the data. He believed they were missing some institutional knowledge of how road and weather updates work on the ground. The analyst revised the query and found an accurate duration of that specific road/weather condition, which is now being used in other reports to inform decision-making.
Regional staff also wanted to be able to see the data across winters and compare averages, as well as expand their vantage point and see other road and weather segments in the region. ITS was able to expand the data to include all road and weather segments for all regions in Oregon and provide a combined summary. Between these two examples alone, ODOT has already seen a shift in data management that is used to inform more dynamic reporting and effective decision making.
Outcomes, Benefits, and Learnings
ODOT is already realizing the benefits of operating from a true Enterprise Data Warehouse. Some of these benefits include:
- Having a single source of truth.
- Limiting the duplicated data across the warehouse, saving space on the server, processing power, time and the effort that goes into maintaining various copies of almost the exact same data.
- Better focus for data security because of a consistent understanding of what users need access to, working in conjunction with the business and program manager and expanding upon data sharing agreements.
- Using one model to manage the warehouse environment, housing all tables/columns/schemas and definitions to ensure consistency.
Data and relationships across data are easier to share between customers/reports/processes.
Lessons learned include:
- Break down silos early. Bridging the gaps between more technical data groups and their business customers is critical to making sure the data vision, architecture and delivery is meeting the needs of stakeholders.
- Documentation is critical! Articulating and documenting a vision, roadmap, standards, decisions, etc. regarding the Enterprise Data Warehouse ensures its efficacy and longevity, especially when staff depart or retire.
- Don’t forget upkeep and continual improvement of the EDW and software. After the data warehouse project is complete, the last importance piece is the continual improvement of the measures (the coding behind the scenes).
- ODOT software is updated in-house, which means at times measure logic has to be updated to reflect software changes. Keeping teams connected and maintaining open lines of communication can help remedy these changes for seamless transitions in data.
Ultimately, all stakeholders and partners will benefit from the shift to an Enterprise Data Warehouse as ODOT becomes a smarter agency, relying on facts and data to create safer roads.
Further Information:
2017 TSMO Program Performance Management Plan
2018 Operations Management Plan
ITS Data Warehouse Specifications
Enterprise Data Warehouse Specifications