On This Page
Project Overview
Project Balance partnered with California Creative Solutions (2022-2024) to build an organization-wide data warehouse for Capital Metro, the transportation agency for the City of Austin and neighboring suburbs. Project Balance was engaged to specify the enterprise analytics platform architecture, design the data warehouse model and the Power BI self-service model, and to provide technical and project management support. The data warehouse houses all of CapMetro’s scheduling, ridership, on-time performance, and maintenance data for their bus, rail, and on-demand services. Project Balance assisted in adding a safety and finance data warehouse which includes Capital and Operational Expenditures, Revenue details, and GIS data as part of a second development phase. The primary objective for CapMetro was to have a data warehouse that provides “one source of truth”, where data can easily be accessed across all departments to respond more quickly to daily events and deliver business metrics and KPIs to meet statutory requirements.
Solution
Project Balance convened a cross-functional and cross-department business team at CapMetro to identify the data sources, highlight issues and barriers to collecting and analyzing data, and review the manual processes required to combine data to produce key metrics required by a transport agency such as on-time performance and ridership for bus and rail. An integrated data warehouse was designed with an initial 8 data sources with an additional 3 added as part of the second phase. In order to meet the goal of having one “go-to” data source, a comprehensive self-service data model was developed so that CapMetro staff member with Power BI skills could create their own visualizations and dashboards. The model included all of the data sources and provided calculated metrics so that cross-data-source analytics could be produced quickly and easily, with confidence in the data.
Project Balance also developed a .NET Data Management application where Data Governance and Department staff members can enter on-time performance exceptions, update route dimensions, enter ridership for unscheduled trips, and review and trigger the release of dashboards to their public site. Pubic-facing performance dashboards and reports were designed so that people with visual disabilities can easily use a screen reader and other tools to view and understand the reports effectively.
Technology
| Data Warehouse Technologies | Data Source Integrated |
| Azure Data Factory Snowflake .NET/C# MS SQL Power BI |
Swiftly Trapeze Genfare EAM OrbCad MS Dynamics UTA Oracle Fusion Cloud ERP |
Specialization
Project Balance has expertise in requirements gathering and system and data warehouse design. The process of understanding the data sources and how to handle and standardize all of the many exceptions was difficult and time-consuming for the business users as well as for the technology developers. The team took special care to design and test the dimensional data warehouse model so that the data from the different sources could be combined and displayed together with common dimensions for filtering.
Project Balance specializes in Oracle Fusion Cloud ERP integration and the development of finance-focused Data Warehouse solutions. Our expertise lies in creating seamless financial data pipelines and optimizing financial reporting for organizations.
We have extensive experience in designing and implementing key financial KPIs, including Capital Expenditure (Capex), Operational Expenditure (Opex), and Revenue statements. Our solutions empower businesses with real-time financial insights, enabling better decision-making and strategic planning.
One of our standout achievements is the development of an automated balance sheet reporting system that requires minimal configuration. This solution streamlines financial reporting by auto-generating balance sheets with pre-defined rules and logic, reducing manual effort, improving accuracy, and ensuring compliance with financial standards. Our approach enhances efficiency, scalability, and data integrity, making financial reporting more accessible and insightful for organizations.
Additionally, we developed comprehensive finance dashboards for CapMetro, focusing on Revenue and Expenditures. These dashboards provide a clear and interactive visualization of financial data, allowing stakeholders to track financial performance, analyze trends, and make data-driven decisions. By integrating data from multiple sources, our dashboards offer a unified view of financial health, ensuring transparency and efficiency in financial management.
Project Balance designed the public site Power BI reports, which meet the Web Content Accessibility Guidelines (WCAG), Version 2.1, Levels A and AA global standard for people who are visually impaired or blind. We worked closely with a blind CapMetro staff member, using screen readers and other accessibility testing tools to meet the standards and optimize the user experience for visually impaired users.
The data management application is a tool that Project Balance will develop for large data warehouse projects. The tool is customized to the data sources for data cleaning and mapping that requires human intervention and management. The data management application provided a way for CapMetro to standardize route names across data sources and to resolve data exceptions through an easy-to-use web interface.
Impact
The completion of the first phase of the CapMetro Enterprise marked a huge milestone for the transportation agency. The ability to pull performance data on a timely basis for daily operational use and compliance reporting without intricate manual manipulation revolutionized the agency and allowed the agency to manage the operation much more efficiently. Project Balance was instrumental in delivering a well-engineered and sustainable data warehouse.
