Cascais Próxima is a municipal company providing a wide range of mobility-related services in the Cascais municipality, in Portugal. These services encompass the construction, maintenance, and preservation of public infrastructure, as well as the execution of public contracts involving road construction and interventions, including pavements and pedestrian accesses. The organization is also responsible for traffic signage management, along with the oversight of public parks and parking areas. Additionally, Cascais Próxima engages in studies and project development related to energy efficiency, while also spearheading initiatives to promote environmental consciousness throughout the municipality.

In 2016, Cascais Próxima launched MobiCascais, combining public transportation by bus, bicycle sharing, and public parking areas in the Cascais municipality under this umbrella brand. To handle the substantial data generated by this operation, Cascais Próxima hired an external consultant who generated a parking-specific report, distributing it as a .pbix file to the entire team for local access. While far from an ideal system, Power BI Desktop provided Cascais Próxima with a glimpse of its potential, a significant departure from the accustomed paper reports and Excel spreadsheets that the team used to share.

In the lead-up to the launch of the International Public Tender at the end of 2018, Cascais Próxima had to deliver daily reports on bus demand distribution, leaving them with no time for anything else. On top of this, there were legal reporting duties to the municipal, metropolitan, and national authorities which changed from annual to quarterly with the COVID pandemic.

“There’s a very critical position on our part regarding information for management, not only for us to understand what happened, but so that we can plan to go forward. This is critical to us.”

– João Silva, Operations Management Division, Cascais Próxima

Bus transportation makes up most of Cascais Próxima’s operations, demanding precise access to updated data to enable the team to gauge demand accurately and strategize scheduling and new route development. With the introduction of free bus transportation for residents, students, and workers, the report demand increased to assess the measure’s impact. They were aware they had the correct tools to do it but lacked the expertise to fully harness its potential.

Having previously worked with DevScope, Solange Lima, Head of the Data Analytics Unit of the Cascais City Council, recommended that Cascais Próxima undergo training provided by DevScope. However, the Cascais Próxima project posed numerous challenges that extended well beyond mere personnel training, encompassing a wide range of services provided by the DevScope Analytics team, including report generation, data integration, and data distribution.

Our BI expert, João Marcelino, conducted training sessions during which he dedicated 2 days a week to mentor the Cascais Próxima team on Power BI . These sessions last to this day, and while they started as mere mentoring, they’ve merged into joint work/development. Equipped with this newfound knowledge of Power BI, the team came to the realization that they would need to rebuild all their existing reports. Luckily, their needs coincided with an opportunity that arose when Francisco Fiuza, a member of our Analytics team, embarked on his master’s degree focusing on data visualization. MobiCascais presented a practical application for his thesis, aligning seamlessly with their needs. Recognizing the importance of aesthetically pleasing reports for data presentation, Francisco adhered to best practices and crafted various data visualization perspectives: an executive-level overview, an analytical perspective, and a public view accessible to all. Notably, this public view showcased a Power BI custom visual featuring a bus graphic illustrating the bus service occupancy rate.

Introducing data through these report views effectively addressed a challenge previously identified by the Cascais Próxima team. Often, the recipients of the information lacked the time to drill into the data, preferring concise presentations of indicators, conclusions, projections, and recommendations on a single page. To address this, the team implemented a couple of solutions: the distribution of paginated reports with concise information and the adoption of PowerBI Tiles Pro, a tool that seamlessly integrates Power BI into Microsoft PowerPoint, Word, and Outlook, which allows the team to update quarterly reports with no trouble. This translated into cost reductions for Cascais Próxima by removing the requirement for Power BI Pro licenses among users who didn’t utilize its advanced functionalities. Nonetheless, despite these savings, Cascais Próxima also faced increased expenditures linked to Power BI Premium capacity, driven by their extensive data processing. In response to this scenario, the DevScope team started working on optimizing data retrieval and enhancing data quality to address and mitigate these rising costs.

Most of MobiCascais’ data comes directly from the Cascais City Council’s data warehouse, which serves as the central repository for various public services within the municipality. Of all the services linked to the data warehouse, MobiCascais is the one generating the most data, although the operation also gets data from outside its scope. Buses serve about 35,000 passengers every day, with numerous data points, including ticket sales, validation, passenger counts, card types, and more, playing a crucial role in assessing their supply and demand. Integrating all this data into the warehouse presented a few challenges.

A portion of the data was obtained from an external application through an API, which required some data modeling work. The remaining data was sourced from GTFS (General Transit Feed Specification), an open standard enabling public transit agencies to disseminate transit data in a format compatible with various software applications. Extracting data from GTFS involved handling CSV files that lacked structured ordering, leading to the need for development work to import and dynamically integrate this data into our Power BI model. In addition to managing buses, MobiCascais oversees public parking in the municipality, requiring monitoring of both parking facilities and the associated fine data. Retrieving data from this operation posed a notable challenge as well due to the use of unsupported fine management software by inspectors. As a result, our team had to create a daily Python script to extract the necessary data and integrate it into Power BI.

With Power BI, the Cascais Próxima team enjoys uninterrupted access to critical information regarding their public transportation and parking activities. This empowers them to make faster and more informed decisions and convey high-quality information to the executive level of the Cascais City Council.

“We often go a little beyond our competencies and are the first to alert the municipal authority to problems we see in the data. Power BI is our best means of diagnosis.”

– João Silva, Operations Management Division, Cascais Próxima

Cascais Próxima’s current focus lies in automation, particularly streamlining data source retrieval and integration. They are diligently working on making certain queries more generic, anticipating potential supplier changes and a significant part of their efforts centers around data quality, where they’re meticulously refining each query and operation, and enhancing the integration of GTFS data.

The team also hopes to see more employees at the Cascais City Council receiving more training in Power BI, focusing not on development but on accessing and interpreting data.

• Developed Power BI reports for MobiCascais’ public bus transportation and parking services following data visualization best practices.
• Cleaned and modeled data from very large and demanding datasets.
• Connected data from vastly different and complex sources to the Power BI model.
• Provided continuous training and support to empower the Cascais Próxima team to develop their own Power BI reports and models.