ORCA Project

ECI was hired through RFP project to develop an enterprise reporting platform for a consortium of 7 distinct transit agencies in the Seattle area for Sound Transit, using a modern, cloud-based solution.  The entire environment (from source to target, including business use of the data) was analyzed and the changing nature of source systems from other vendors established challenges for initial concepts for data aggregation and preparation for reporting.  ECI developed an ISO Standards based Azure environment utilizing Microsoft toolset including SSIS, SSRS, Windows Servers within an Azure environment.  ECI provided Project Managers, Business Analysts, Technical Architects, and several Developers for this effort.

The data access and reporting services requested will ultimately support the ORCA fare collection system. ORCA is made up of seven partner agencies: Community Transit, Everett Transit, King County Metro, Kitsap Transit, Pierce Transit, Sound Transit, and Washington State Ferries (together known as ORCA).  The seven partner agencies provide about 600,000 transit rides on a typical weekday, two thirds of which are paid for using an ORCA card. The current ORCA regional fare collection system, “Legacy ORCA”, charges customers by deducting value and verifying pass value from an ORCA card in accordance with fare policies established by each transit operator. The system apportions the associated revenue to each transit operator through an automated financial settlement process.

The first phase created a system to host Legacy ORCA data and implement new reporting tools. Since October 2017, ECI has worked closely with the RPT (Regional Project Team) to establish initial requirements and architectures.  Specifically, ECI helped the RPT with report/data requirements, as well as designed an Azure based solution to accept Fare Collection information through an ETL process from the Sound Transit hosted ‘Landing Zone’ environment.  Additionally, ECI has worked closely with ST staff to establish a VPN tunnel for secured data transports. ECI developed data extraction, reports, and ad hoc environment for agency access.

In phase two, ECI architected and implemented a PowerBI centric solution supporting 7 distinct transit agencies allowing centralized access using a SSO solution.  ECI also developed a Data Strategy and Governance Plan for growing organization (ST) enabling controlled data growth and management.  In addition, ECI implemented a PowerBI solution to allow centralized access to embedded PowerBI solution for over 2000 private businesses (Microsoft, Amazon, Boeing, etc.) using PowerBI API/Custom Application leveraging Azure resources (Logic App, etc).  Finally, ECI implemented an Azure based ETL process using VPN, Azure SQL, and ADF to orchestrate regular data pipeline process consuming fare collection data for regional reporting purposes.

Overview:

• The project was performed using SCRUM as the methodology.
• The project included the development of a business rules within complicated ETL processes that were customizable using table driven methods.
• ECI implemented custom ETL workflow functionality that was triggered by threshold values maintained by external users (not developers).
• The DARe solution utilized document management features such as tracking and storing repository documents using database objects.
• The DARe system migrated data from a legacy fare collection system (Vix) sourced from a landing zone environment and consumed using cloud based (Azure) solution.
• The DARe solution was 100% custom-developed software, and the system was 100% browser-based.
• ECI also performed extensive knowledge transfer.
• Maintenance of system started in August of 2020 and will continue for up to 10 years.

Project documentation included:

• System Requirements Specification
• User Stories, Backlog, Release Plan
• System Design Document
• User Manual
• Test Plan
• Test Scripts (by Sprint, and by Release)
• External Stakeholder Communication Materials (Agencies)

Accomplishments:

• Completed Azure based cloud design using ISO standards
• Developed ETL processes for undocumented, legacy fare collection system for full load from staging to Azure Stage 1 environment
• Completed report requirements for prioritized datasets
• Developed an agency report survey that accumulated user feedback and prioritized report and dataset delivery.

End Result: Successfully completed DARe project on budget and on time.