The Cascais City Council (in Portuguese, Câmara Municipal de Cascais) is the collegiate body representing the municipality of Cascais, located on the Portuguese Riviera. The Council employs around 3,000 civil servants who provide local services in the most diverse areas of the public sector. The municipality of Cascais is a very important tourist destination and serves a population of over 215,000 inhabitants.

The Cascais City Council manages numerous operations, such as the municipal bus service, garbage collection, public parking sensors, social media networks, helpline, the Loja Cascais, etc. Each of these departments collected and processed its data, which meant that the City Council had to search numerous databases, applications, Excel files, and others to access it. The City Council was looking to aggregate all this data into a single data warehouse and use it to create dashboards with an overview of what was happening in the municipality, open the data with ease to everyone who required it, and leverage it for future projects.

“We had plenty of data. Data was never lacking here. The problem was concentrating it in a single base.”

– Solange Lima, Head of the Data Analytics Unit, CM Cascais

After looking at a few Business Analytics solutions, the Cascais City Council opted for Power BI since they were already using Microsoft 365 and welcomed the ability to integrate this new solution into that ecosystem. The Cascais City Council was aware of DevScope’s analytics specialization, so we were selected as a partner for this project.

Our team presented our signature ETL framework as the solution to gather and manage the data collected from Cascais City Council’s multiple sources. DevScope’s ETL framework integrates seamlessly with external systems and tools and packs a series of benefits that would perfectly fit the City Council’s needs and grant the institution enough autonomy to manage it in the future. For starters, it’s metadata-driven, meaning adding new tables and data sources or changing existing ones doesn’t require further development. Data extraction is also parameterized to run in different periods (with or without parallelism), and most importantly, the framework is multi-tenant, an imperative in this project since each area of the Cascais City Council has its tenants. Finally, the entirety of the ETL process is monitored and audited, a level of transparency vital for a public institution.

Alongside the ETL framework, we developed a BI report related to Customer Service at the Loja Cascais, a public space where citizens from the municipality can take care of affairs such as their social security or renew their citizenship card, among other public services.

The data dispersal issue was very visible at this stage, with our assigned consultant having to look among different places and work with different formats to connect the data to Power BI. Keeping up with DevScope’s tradition of ensuring that the client can work with the solutions they are being provided, a small team at the Cascais City Council also took a 3-day training course on Power BI. Training sessions were recorded, and user manuals prepared in order to encourage the sharing of internal knowledge and make new team members autonomous using Power BI and data connections.

Due to the public nature of the City Council, several obstacles came up during the project, namely transparency and data usage authorization issues, but they were eventually overcome and the team managing the project highlighted DevScope’s role and sensitivity do adapt to these setbacks.

“I think the proximity is fantastic. Whenever I have a problem, I send a message to the team at DevScope at any time and they answer me. If they can, they help me right away. This is a great advantage.”

– Solange Lima, Head of the Data Analytics Unit, CM Cascais

Our team helped identify technical issues and obstacles often inherent to public organizations and require coordination between all parts involved for a project to work. The proximity between the DevScope and the City Council’s Data Analytics team played a key role unlocking the relationship between the many services inside the City Council, which brought flexibility and security to services that were not always ready to share their data due to transparency concerns.

“When I meet with Solange after she has had a meeting with DevScope, I often feel that she’s arriving from a team meeting. DevScope’s attitude is not so much of a consultant, but that of a team.”

– Marta Cotrim, Chief of Division, Cascais City Council

As initially planned, the Cascais City Council was able to concentrate its data in one place and use it to create Power BI reports and dashboards for all the different public services it provides. Although initially envisioned to assist the City Council’s operations, the data is now being used to outline strategic objectives and support the decision-making process in the municipality.

The data warehouse has been feeding an app developed for the municipality and will also provide the data for the future Centro de Comando e Controlo de Cascais (Cascais’ Command and Control Centre), allowing it to present data in real-time. The data warehouse also allowed the City Council to identify and cut unnecessary expenses they were incurring, such as APIs they weren’t collecting data from, and to create data flows that allow the various teams working at the City Council to retrieve data without having to go through all the hurdles of accessing the original databases.

The Cascais City Council will keep connecting data sources to the new Data warehouse and display them in Power BI reports until they fulfill their vision of centralizing all data from the public activity in the municipality.

• Deployed our ETL framework at the Cascais City Council, connecting data from multiple public services sources on a single platform.
• Solved the issue of data dispersal: data is now centralized and can be shared with anyone that wishes to leverage it.
• Provided the Cascais City Council’s team training with Power BI, empowering them to autonomously create Power BI reports and dashboards, as well as new data connections.