The National Academies

NCHRP 23-29 [Active]

Transportation Enterprise Data Warehouse Implementation Guide

  Project Data
Funds: $350,000
Staff Responsibility: Roberto Barcena
Research Agency: Iowa State University
Principal Investigator: Inya Nlenanya
Effective Date: 4/5/2024
Completion Date: 11/30/2025
Comments: Research in progress.


As part of a robust data governance strategy, transportation agencies must decide how to best manage the storage, access, and dissemination of data products and services for internal use/reuse and external distribution within its data architecture. An important piece of modern data architecture is an enterprise data warehouse, which, conceptually, will provide a way to reduce data redundancy, improve data consistency, and enable data usage for better decision-making. Effective data warehouse implementations are complex, especially when an entity has highly diverse data sets and technology infrastructure.

State departments of transportation (DOTs) need guidance on how to best architect and implement an enterprise data warehouse strategy. DOTs would also benefit from guidance on the complete set of functional requirements including, but not limited to, federation (aggregating from multiple sources), data extract-transform-load (ETL) and extract-load-transform (ELT), storage, naming convention, structure (model), roles and responsibilities, web services, data publishing processes, access by third-party applications, and records management. Any conventions, models, and structure covered by the guidance will also need to support and align with standard frameworks used in transportation, such as Industry Foundation Classes (IFC), to the greatest extent practical.


The objective of this research is to develop a guide for enterprise data warehouse development, implementation, and best practices to support DOT business needs.

DOT business needs include, but are not limited to, efficient upload, use, sharing, security of data, data analytics, operations management, performance management, asset management, safety management, data-driven decision-making, data integration, and federal and state data reporting.

The accomplishment of the project objective will require, at the minimum, the following tasks:


PHASE I—Planning

Task 1. Conduct a critical literature review of research related to enterprise data for transportation and other multidiscipline agencies. The review shall include published and unpublished research conducted through national, international, pooled-fund-sponsored research as well as relevant literature generated within the public sector. 

Task 2. Evaluate and synthesize Task 1 to identify the knowledge gaps for effective transportation enterprise data warehouse implementation. These gaps should be addressed in this research, as budget permits, or included in the recommendations for future research. Present the results and findings in Technical Memorandum 1.

Task 3. Propose a methodology to accomplish the research objective to be fully developed in Phase II of this project. 

At a minimum, the work plan and methodology shall consider:

·     Targeted surveys or interviews with DOT chief data officers or equivalent, as well as business domain experts to learn about analytical and reporting requirements, the status of practice, and current challenges;

·     Strategies to assess current enterprise data warehouse capabilities and opportunities in transportation; 

·     Identification of essential data warehouse capabilities including, but not limited to, input, access, cybersecurity/permission management, privacy, data quality, change management, and data retention;

·     Data management approaches and technology needed to enable the development and implementation of effective enterprise data warehouses;

·     Required documentation to develop and maintain an enterprise data warehouse (e.g., glossary of terms, operating procedures) keeping in mind the diverse operational contexts in different DOTs;

·     Foundational success factors for enterprise data warehouse implementation and adoption;

·     Coordination needs with partner agencies and third-party data (i.e., rail, utilities, data subscription);

·     Enterprise data warehouse governance;

·     Transportation-specific challenges including temporal, geospatial, linear referencing systems (LRS), and building information modeling (BIM) data sets; and

·     Strategies that encompass data in structured (tabular), semi-structured, or unstructured (non-tabular) geospatial file formats (includes geometry); the incorporation of existing and new data sets into enterprise data warehousing; and the corresponding required technology.

The development of the elements above shall be framed from these perspectives:

·     Daily data-based decision-making within transportation agencies;

·     Organizational productivity through improved interoperability of data and automation of common analysis and reporting activities;

·     Cost-effective strategies that can be quickly applied to the current state of data, avoiding duplicating efforts with multiple or unknown outcomes;

·     Data sharing efficiencies across transportation business units and agencies;

·     Building and maintaining historic records of agency data that are separate from operational data stores that are highly dynamic; and

·     Development of data-based performance metrics.

Task 4. Propose a preliminary outline for the guide based on the proposed methodology.

Task 5. Prepare Interim Report No. 1 that documents Tasks 1 through 4 and provides an updated work plan for the remainder of the research no later than 4 months after the contract has been awarded. The updated plan must describe the process and rationale for the work proposed for Phases II and III. 


PHASE II—Work Plan Implementation

Task 6. Execute the work plan and methodology approved Interim Report No. 1.

Task 7. Prepare Interim Report No. 2 that documents Task 6 and provides an updated work plan for the remainder of the research. Interim Report No. 2 must include a draft guide and a table of contents for a webinar based on the guide to disseminate the project’s results to DOT practitioners and other transportation agencies.


PHASE III—Final Deliverables

Task 8. Revise the draft guide based on the NCHRP review comments.

Task 9. Develop materials for the webinar. The materials for the webinar need to be approved by the NCHRP panel before conducting the webinar.

Task 10. Prepare the final deliverables including:

1. The guide for enterprise data warehouse development, implementation, and best practices.

2. Present at least two webinars for DOT and other transportation agency practitioners.

3. A stand-alone technical memorandum titled “Implementation of Research Findings and Products”.

4. A conduct of research that documents the entire research effort and lessons learned, including the recorded webinars, list of participants, questions and answers, feedback, and other records from the webinars.


STATUS: Research in progress.


To create a link to this page, use this URL: http://apps.trb.org/cmsfeed/TRBNetProjectDisplay.asp?ProjectID=5358