Greater Manchester's Transport Link Migrates Analytics to the Cloud

The ‘Under the Stairs’ project is a data migration project for Transport for Greater Manchester. It involved moving 50+ data feeds in 9 different file formats and 6 different third-party APIs into existing cloud data warehouses. This allows the client to create broader and deeper analytics about customer journeys.

The Challenge

The Enterprise Insights and Analytics (I&A) platform at Transport for Greater Manchester (TfGM) was designed to support the rollout of contactless ticketing across the region. Following successful implementation, TfGM sought to take further advantage of this strategic capability by moving broader workloads onto the platform.

The project name relates to the local client name of the on-premise server set for migration, Under The Stairs. The key requirement was to move a significant on-premise workload focused towards the strategic analysis of passenger journey analytics.

Why AWS

We chose Amazon Web Services (AWS) because it has all the services and providers to do the initial tasks. It also covers any envisioned solutions for the future. The environment request was to fully script end-to-end to allow repeatable patterns. Using Terraform not creates all the native AWS services but extends to create all database objects too.

To enable maximum automation, as with the earlier contactless project, flat file imports would have the row counts, column counts and data types verified using AWS Lambda functions (failures would automatically raise help desk tickets with the supplier without any ETL jobs commencing on the TfGM side). Once verification has taken place in the ETL environment, we were able to initiate using SQS; to orchestrate the instantiation of Matillion as well as other tasks.

Outside of the native AWS tools, other AWS partners including Matillion (for ETL), Snowflake (for the data warehouse) and Tableau Online (for reporting and visualisation) are also in play.

The Benefits

TfGM adopted AWS because it had all the services and providers needed to do the initial tasks as well as any envisioned solution in the future. This has proven to be the case when TfGM sought to migrate a significant number of strategic planning analytical workloads from their on-premise SQL server to the new powered by AWS Enterprise solution.

TfGM were able to use Snowflake and Tableau to give them new insights into travel patterns and behaviours. This allows TfGM to adapt the network and add improvements to contactless services. These new insights came from the ability to store and perform analysis on any volume of data required in the new EDW. Finally, more insights were unlocked with the new visualisation capability.