Transdev is a French-based public transport group operating in 20 countries. Transdev is a worldwide leader in subway and railway transportation, and one of the major players in bus transportation, employing 82.000 people and with a revenue of over €6.6 billion. Present in Portugal since 1997, Transdev is one of the largest bus and coach operators in the country, with a fleet of over 1.500 vehicles.

Transdev’s Portuguese operation grew through a series of mergers and acquisitions but felt some inevitable growing pains. In order to extract reports, the company had to monitor some indicators, such as ticketing and fleet consumption, but faced a huge obstacle: with each assimilated operation using their own set of systems and databases, Transdev had no easy way to connect and cross the amount of information on their hands. The company needed to unify all data from these many different operations and have it working towards the decision-making process.

Transdev’s relationship with DevScope dates back to 2010 when Rui Romano developed an HTML5 reporting platform that allowed employees to analyze and comment on some indicators that still live on their current Business Intelligence solution. When Transdev contacted him again, asking for a way to unify all their different operations, he was well aware of the company’s current situation. The project seemed challenging and presented another perfect opportunity to put Rui Romano’s “Start Small and Grow Big” work methodology to good use. In practice, it means implementing a solution in an Agile way with short and fast stages, modifying it to answer any of the clients’ problems, and scaling it according to their needs. Since this allows for a tailored approach, clients only need to implement solutions according to their business needs.

Stage #1 – Unifying different systems through a Data Warehouse

Nuno Miguel Silva, José Barbosa, and Bruno Ferreira, BI consultants at DevScope, designed a Data Warehouse – nicknamed Prometheus – that would feed on over a dozen of Transdev’s Ticketing (Datacar, Wayfarer, and Card4B) and Operation (ABC) databases.

Due to the different nature of these systems, the DevScope team developed a fast and dynamic ETL Framework for Microsoft SQL Server Integration Services (SSIS), allowing for faster design and development of ETL solutions and making it easier to change configurations. Prometheus serves as an intermediary on the entire processing, receiving data from its original databases and transforming it to be used afterward.

“The diversity of systems made the process a little bit more complicated. And, on top of that, we found that users would be using different methods, even when working on the same system. Because of that, we had to standardize processes.”

– Maria Gabriel, Application Manager and Data Scientist at Transdev

The Data Warehouse with DevScope’s ETL framework gave Transdev a central platform to extract, transform and load data. Whenever a new data source comes in, Transdev’s IT department has no trouble adding it to the data warehouse: all they need to do is configure a few metadata tables. The company also had to normalize the way their workers handled it. Prior to the introduction of Prometheus, Excel was Transdev’s tool of choice but individual edits to files made data management slow and the figures unreliable. The entire decision-making process was compromised.

Stage #2 – Introducing Power BI

After regulating Transdev’s different systems through a data warehouse, the next step was to output such a large amount of information. Given its integration with SQL Server, Power BI was the logical choice for the task. Rui Romano is a well-known advocate of Power BI’s virtues and demoing it was enough for Transdev to realize how interactive reports and dashboards could dramatically change several areas of their operation. The company currently has 30 active Power BI Pro users and generates 10 different reports, ranging from ticketing to fleet consumption.

Prometheus is updated every day so Transdev technicians can always count on a fresh batch of trustworthy data in the morning when they arrive at the office. Real-time analytics is not necessary and because the entire process requires a lot of computing resources, updating outside working hours makes the process seamless.

Speed and information reliability were the first noticeable aspects of Transdev‘s data warehouse used in combination with Power BI. Understandably, some of Transdev’s methods of analysis completely changed to accommodate the new solution.

“Information is now more reliable, available, and has the granularity that allows us to make better decisions.”
– João Soares, Director of Purchases and IT at Transdev

Transdev’s platform is a work in progress and a testament to the “Start Small, Grow Big” approach. After working out the basics – the data warehouse and business analysis platform – the DevScope team keeps introducing new features according to the client’s needs. Just recently, we added the option to import CSV files to the warehouse.

Transdev hopes that the system becomes increasingly dynamic and able to quickly react to changes in ticket prices and promotions from competing companies. Their long-term goal for the platform is to have it dictate the business itself, i.e., use data to launch new products and support the company’s evolution.

Transdev is a multinational group, present in 5 continents, and their technicians meet regularly to see what each team has been doing and exchange some learnings. After watching Transdev Portugal’s Power BI dashboard in action, other teams were very impressed and keen on adopting these solutions.

• Unified Transdev’s radically different databases through a data warehouse.
• Developed a fast and dynamic ETL framework for SQL Server Integration Services that quickly adapts to new changes and data sources.
• Gave Transdev a state-of-the-art reporting tool in the form of Power BI, delivering accurate and up-to-date information to make educated business decisions.

• SQL Server
• Power BI
• Azure Cloud Services
• Microsoft Office 365

Do you want to learn more about DevScope’s solutions? Contact us.
Are you a DevScope client and would like to have your case study featured here? Get in touch