The Transportation Authority is seeking consultant services to design and implement a data warehouse to support on-going acquisition, storage, querying/reporting, and visualization of both spatially-referenced and non-spatially- referenced data.

[ Full Solicitation ]

The San Francisco County Transportation Authority (Transportation Authority) manages an extensive set of transportation-related datasets, including on-going transportation system performance monitoring, detailed representations of transit, bicycle, pedestrian and roadway networks, transit, bicycle and traffic counts, socioeconomic data, and travel model inputs and forecasts. Presently these are maintained in a variety of different file formats and locations. The Transportation Authority is seeking consultant services to design and implement a data warehouse to support on-going acquisition, storage, querying/reporting, and visualization of both spatially-referenced and non-spatially- referenced data. In addition, the Transportation Authority is seeking assistance in designing and implementing procedures for populating this data warehouse, prioritizing dynamic transit assignment model and congestion monitoring data. In order to make the data in the warehouse accessible to Transportation Authority staff as well as to external parties, assistance in the design and implementation of data reporting and visualization capabilities is also sought.

Proposed Project

The objectives of the proposed project are to:

• Design and implement data warehouse to support on-going acquisition, storage, querying/reporting, and visualization of both spatially-referenced and non-spatially- referenced data
• Design and implement procedures for populating data warehouse, prioritizing dynamic transit assignment model and congestion monitoring data
• Design and implement data reporting and visualization capabilities

Consultant Tasks

The Consultant scope of work will include the following tasks:

Task 1. Review Data

Consultant will review the data to be included in the initial version of the data warehouse. These data include observed transit AVL and APC data, dynamic transit assignment model outputs, congestion monitoring data, and observed traffic count data. The Consultant will work with Transportation Authority staff to assess the original data formats, potential schema(s) for the warehouse as well as for each data source and type, and assess the implications of continuous longitudinal updating and expansion of the data warehouse. This assessment should include consideration of additional data sources such as activity-based model inputs and outputs, transportation network data, and land use and socioeconomic data that may ultimately be incorporated into the data warehouse. The assessment should also consider current Transportation Authority data processes and workflow.

Deliverable: Memo assessing data sources, proposing data processes and workflow, and documenting potential warehouse data schema(s).

Task 2. Design Data Warehouse

Consultant will propose a data warehouse structure and implementation platform that can accommodate both near-term and long-term data acquisition, archival, retrieval, and reporting/summarization of warehouse data, as well as support visualization/analysis capabilities. This design will be informed by a review of the practices of other comparable agencies that have implemented data warehouse solutions. The proposed structure will reflect the broad range of data sources, types and sizes anticipated to be included in the warehouse, and will specifically address incorporating dynamic transit assignment model outputs, observed transit AVL and APC data, congestion monitoring data, and observed traffic count data. The data warehouse design will consider runtime performance issues associated with large datasets. The proposed platform will use, to the greatest extent possible, open-source technologies, and will support or integrate with Consultant- identified data reporting and visualization tools. The reporting and visualization tools will support use by both internal and external users. Consultant will also identify and advise on local versus hosted solutions.

Deliverable: Memo documenting data warehouse design and implementation platform and technologies.

Task 3. Develop Data Acquisition and Maintenance Protocols and Procedures

Consultant will work with Transportation Authority staff to develop a set of data acquisition and maintenance protocols and procedures for incorporating dynamic transit assignment model outputs, observed transit AVL and APC data, congestion monitoring data, and observed traffic count data. These protocols and procedures will address issues such as the extent of pre-processing of data, data warehouse and data change management, frequency of data updates, and archiving. When appropriate, procedures will be maintained using the Transportation Authority’s GitHub site.

Deliverable: Memo documenting data acquisition and maintenance protocols and procedures for initial warehouse datasets, and associated code.

Task 4. Implement Data Warehouse

Consultant will work with Transportation Authority staff to install and configure all core technologies required for data warehouse implementation, reporting and visualization. Consultant will support Transportation Authority staff in applying the data acquisition and maintenance protocols to import dynamic transit assignment model and congestion monitoring data. Consultant will work with Transportation Authority staff to devise and implement a set of tests of data warehouse acquisition, querying and visualization capabilities. At minimum, the implementation will incorporate transit assignment model and congestion monitoring data, and provide means for both internal and external users to view, query, and report data.

Deliverable: Data warehouse and visualization technologies and procedures installed on Transportation Authority- specified server.

Task 5. Documentation

Consultant will work with Transportation Authority staff to document the data warehouse structure, implementation, and protocols and procedures on the Transportation Authority’s internal wiki and GitHub site as necessary.

Deliverable: Documentation ofwarehouse structure, implementation, and protocols and procedures on the Transportation Authority’s internal wiki and/or GitHub site

Budget

The Transportation Authority has allocated $75,000 for all tasks. The Transportation Authority may select to implement all or some of the tasks listed above so the cost proposal should clearly identify the scope, schedule and cost for each task separately as individual elements independent of each other to allow for the contract to include or exclude each task.

Schedule

All tasks are to be completed by June 30, 2017.

Proposal

If Consultant is interested and able to complete the work described above, please submit an electronic proposal that incorporates the scope of work, schedule, and budget by Thursday, December 29, 2016 at 5:00 p.m. to info@sfcta.org. Proposals should be limited to 5 pages, excluding cover letter, table of contents, résumés, and certifications for Disadvantaged Business Enterprise (DBE). Proposals should also include the following:

1. Description of the technical approach to the delivery of the services describeda bove. This should include the Consultant’s approach to the following items along with descriptions of previous related work completed: a. Data warehouse architecture b. Data extraction, cleaning, and transformation c. Integrating and storing a variety of transportation systems data d. Backup and archiving data e. Enduser(internalandexternal)interfacesforquerying/reportingandvisualizationof both spatial and non-spatial data
2. TheproposingConsultant’sservicesarefederallyfunded,whichnecessitatecompliancewith additional requirements. Special attention is directed to Exhibit 10-I, Notice to Proposers DBE Information. The proposing Consultant shall complete and submit the following forms with the proposal to be considered responsive. These forms and instructions are provided for the proposer in this IRFP. The required exhibits will not count towards the 5-page limitation. Upon award and through completion of the project, the successful proposing Consultant will be required to follow applicable federal-aid requirements and shall complete and submit with the agreement the following forms at the time of award:
3. Three (3) references (other than the Transportation Authority), including project name, contact name, email address and telephone number and value of contract.
4. A cost proposal table listing labor, direct and indirect costs and hours by name of person or classification proposed for the work shown above, broken down by task. Identify your expected staff time, subcontractor’s involvement and level of commitment in the project. Your cost proposal must encompass all expenses associated with the project, including your time, materials, equipment and/or services provided by others, and any other incidental costs.
5. Résumés of all technical personnel to be assigned to work within the scope of services as outlined above (résumés will not count toward the page limit).

The Transportation Authority reserves the right to modify and/or suspend any and all aspects of this procurement, to obtain further information from any firm or person responding to this procurement, to waive any informality or irregularity as to form or content of this procurement or any response thereto, to be the sole judge of the merits of the proposals received, and to reject any or all proposals. Upon receipt at the Transportation Authority’s offices, all proposals submitted in response to this procurement will become the property of the Transportation Authority.

You are welcome to submit written questions to request clarification about any aspect of this procurement via email to info@sfcta.org by Tuesday, December 13, 2016 at 5:00 p.m.